Disabling foreign key constraints for snapshot replications v7

In a snapshot replication, the publication server calls EDB’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 must be set to Yes.

If the Update Catalogs property is set to No:

  1. With the user name selected in the Object Browser, from the context menu, select Properties.
  2. Select the Role Privileges tab.
  3. Select Can Modify Catalog Directly and select OK.