Disabling Foreign Key Constraints for Snapshot Replications v6.2

In a snapshot replication, the publication server calls EnterpriseDB’s Migration Toolkit, which disables foreign key constraints on tables so it can truncate the target tables before loading rows. In Postgres, foreign key constraints are implemented using triggers, so in actuality, the Migration Toolkit disables triggers on the target tables by setting column relhastriggers of pg_catalog.pg_class to false for each target table.

No user (not even a superuser) is allowed to directly modify the data in a Postgres system catalog table unless the following conditions are satisfied:

• The database user attempting to modify the rows of a system catalog table is a superuser.
• In the Postgres system catalog table pg_catalog.pg_authid, the column rolcatupdate is set to true for the row identifying the superuser attempting to update a system catalog table. This requirement applies only to Postgres version 9.4 or earlier. The column rolcatupdate no longer exists in Postgres 9.5 or later.

To verify that a user has the privilege to update the system catalog tables, select the user name under the Login Roles node in pgAdmin (Postgres Enterprise Manager Client in Advanced Server). The Update Catalogs property should be set to Yes.

Figure 10-6: User with privilege to update system catalogs

If the Update Catalogs property is set to No, click the secondary mouse button on the user name in the Object Browser and choose Properties from the menu. Select the Role Privileges tab, check the Can Modify Catalog Directly box, and click the OK button.

Figure 10-7: Granting system catalog update privilege