How can I load a custom format dump from pg_dump into an RDS while ignoring constraint checks during the import?

Question

I have a number of constraints:

1. I have binary custom format dump from Heroku that I need to load via pg_restore.
2. I need to load this onto an AWS RDS instance, where I don't have full superuser (only rds_superuser).
3. The data is quite large (a few TB), and takes over 24 hours to load.
4. I don't want to remove the constraints permanently, just during the restore.

Looking at the pg_restore output it seems to spend a lot of time checking constraints (foreign keys, unique constraints, etc). That makes sense because we do have a lot of them, and a lot of data. The thing is, I know the constraints are valid for the data in the dump, because the database the data was dumped from has them enforced. I'd like to suppress these constraints just for the import and then resume enforcing them after the import is complete.

Here's what I've tried:

1. Using --disable-triggers with pg_restore. This seems like it's exactly what I want, but it doesn't seem possible to use with RDS. I get a lot of errors like this: ERROR: permission denied: "RI_ConstraintTrigger_c_172425" is a system trigger and that seems to be because the rds_superuser is not super enough to use --disable-triggers
2. Using SET session_replication_role = replica;. This also seems hopeful, but I can't find a way to use this with the pg_restore. I can set session_replication_role, but I don't know how to then import the binary dump from that same session. I see a lot of people suggesting putting that at the top of a text dump, but I have a binary dump, and I don't think I can insert lines to it.

Any ideas?

Answer 1

Any option that allows you to disable triggers (and consequently foreign keys) can affect the integrity of the data and is consequently superuser-only. Now you don't get a superuser on a hosted database. But if what you write is true, and you can modify session_replication_role, Amazon must have hacked up the permission system. In that case, you should be able to set the parameter in your connection string:

pg_restore -d "options='-c session_replication_role=replica' host=... port=... dbname=... user=..." dumpfile