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
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
rolcatupdateis set to
truefor 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
rolcatupdateno 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:
- With the user name selected in the Object Browser, from the context menu, select Properties.
- Select the Role Privileges tab.
- Select Can Modify Catalog Directly and select OK.