0

I'm doing DB migration and I would need to disable the foreign key constraints on a table in order to migrate peacefully.

From this: How do I temporarily disable triggers in PostgreSQL?

I've gathered that I would need superuser permissions to run:

SET session_replication_role = replica;

and

SET session_replication_role = DEFAULT;

However; on google cloud platform it is impossible to get those privileges as detailed in the docs:

The postgres user is part of the cloudsqlsuperuser role and has the following attributes (privileges): CREATEROLE, CREATEDB, and LOGIN. It does not have the SUPERUSER or REPLICATION attributes

and to this StackOverflow question: upgrade user to superuser in postgres google cloud

I also tried to disable triggers via:

alter table orders.orders disable trigger all;

However; for this it seems to me, that I also need su rights: ERROR: permission denied: "RI_ConstraintTrigger_a_25017" is a system trigger

So then, what is the solution, can I even disable triggers // foreign key constraints on this particular table or not?

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
Péter Szabó-tóth
  • 393
  • 2
  • 15

1 Answers1

1

Like you have read from the documentation, these commands require superuser privileges, which are not available to users in CloudSQL yet.

Because Cloud SQL for PostgreSQL is a managed service, it restricts access to certain system procedures and tables that require advanced privileges.

There is actually an open feature request focusing on this feature. It does not seem to have an ETA yet as per the available info. Regardless, it appears that there are replication services which do not require superuser, as this thread says. Otherwise, GCP also offers the Data Migration Service, which, according to the documentation, supports migrating foreign key constraints for PostgreSQL instances.

ErnestoC
  • 1,320
  • 1
  • 3
  • 14