AWS RDS postgres error when running sql function: RI_constraintrigger_a_19346 is a system trigger

Question

So I need to import django fixtures (e.g. a django's database dump essentially) in postgres on a aws rds instance. I have tested locally that the constraints of the DB are all valid (by importing locally in a replica). Then I have pushed the fixtures to the aws django server, and going to use django's loaddata function to load them, and django will push them to the rds db.

In order to load those fixtures (because many models's fk reference each others), I need to disable the fk checks in postgres in order to load the data. In order to do so, I have a script:

begin;
alter table foo_bar disable trigger all;
.... other tables same cmd ....
commit;

Then I run a similar list of commands with enable to re-instate the constraints.

When I tested this locally in my replica it worked fine. However, on rds, when I try to run the script (connecting to the db with Dbeaver), I get this error:

SQLERROR [42501]: permission denied: "RI_constrainttrigger_a_19346" is a system trigger.

I seem to understand that this would be due to my current user not having the rights (on the rds postgres instance) to perform that operation?

MY question is: how I can check/change the rights on the rds instance so that my postgres user has the right to run that command?

PS: I'm not super knowledgeable in postgres/rds, so ideally provide a bit of background for potentially non-obvious things. Thanks!

Answer 1

Okay, so the issue is that this is just the wrong approach. @Laurenz Albe seems to be right - "Sorry Dave, I'm afraid I cannot do that".

So what's a dba going to do? Well, my personal answer was:

• Setup a local instance of the same DB. So basically for me that was my dev setup for django with postgres backend on docker.

• Upload your fixtures to that. One can easily set the triggers off for that, be it on a table per table basis (as shown above) or even using set session_replication_role = replica; (see postgres docs for info), which is simpler. Or, if you don't want to have to force a constraints checks manually on postgres, you could simply set db_constraint=False on all models.ForeignKey in your django models, migrate, upload, then set to True again, migrate.

• Once you've uploaded the fixtures/data locally on postgres and validated that the constraints checks out, produce a data dump:

  pg_dump your_local_db_name > db_dump_filename
  

That produces your full pg data dump.

You should make sure your local owner for the tables is the same as the remote one. Since those are set by the dump file upon upload (ALTER TABLE your_table OWNER TO owner_name;). Otherwise find + replace the owner_name in the dump before uploading it.

Then, to upload to rds:

psql --host=<youdb_host_from_aws_console>.us-east-2.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=<remote_db_name> < db_dump_filename

You will be asked for the postgres user password.

Then it will all run with outputs in terminal; I think you can use a -L flag to output stuff to a logfile locally (not tested). At any rate, look at the output, and if everything is clean your data is up there.

Warnings: one should probably make sure that the remote db is the same as the local one (in terms of models). My aim was just to first populate an empty db. I'm guessing that in production, you'd want to dump from the remote rds instance first. Upload that locally to. Then upload additional fixtures. Then dump locally, and upload remote.