I'm migrating existing test environment to Amazon RDS PostgreSQL. The test framework has a feature of reloading data in certain tables to an earlier state. For this it disables foreign keys, deletes existing data, loads save state and enables foreign keys again.

Currently, the test framework disables foreign keys by disabling all triggers (of course, this requires superuser):

alter table tablename disable trigger all;

On RDS, this fails with:

ERROR: permission denied: "RI_ConstraintTrigger_a_20164" is a system trigger

How can I temporarily disable foreign keys in Amazon RDS PostgreSQL?

Note: similar question has already been asked (PostgreSQL on RDS: How to bulk import data with FK constraints?) but it was specifically about offline import and the solution is specific to offline import too.


1 Answer 1



I found an alternative way of disabling foreign keys -- https://stackoverflow.com/a/18709987

set session_replication_role = replica;

And re-enabling them with

set session_replication_role = default;

This works on RDS but still requires unusual privileges (i.e. not granted by default).

dropping and recreating FKs

Alternative solution is, as suggested in comments, to temporarily drop the FKs. This brings additional advantage that data is verified when FKs are re-enabled.


create table if not exists dropped_foreign_keys (
        seq bigserial primary key,
        sql text

do $$ declare t record;
    for t in select conrelid::regclass::varchar table_name, conname constraint_name,
            pg_catalog.pg_get_constraintdef(r.oid, true) constraint_definition
            from pg_catalog.pg_constraint r
            where r.contype = 'f'
            -- current schema only:
            and r.connamespace = (select n.oid from pg_namespace n where n.nspname = current_schema())

        insert into dropped_foreign_keys (sql) values (
            format('alter table %s add constraint %s %s',
                quote_ident(t.table_name), quote_ident(t.constraint_name), t.constraint_definition));

        execute format('alter table %s drop constraint %s', quote_ident(t.table_name), quote_ident(t.constraint_name));

    end loop;
end $$;


do $$ declare t record;
    -- order by seq for easier troubleshooting when data does not satisfy FKs
    for t in select * from dropped_foreign_keys order by seq loop
        execute t.sql;
        delete from dropped_foreign_keys where seq = t.seq;
    end loop;
end $$;
  • 3
    Sadly this doesn't seem to work with a Postgres RDS database. Setting the session to replica doesn't disable the foreign keys at least in my case with Postgres 12.4 on aws rds.
    – Nico
    Commented Jan 13, 2021 at 12:47
  • @Nico it would be sad to see this answer to become outdated after being helpful for couple of years, but not inconceivable either. I hope you'll figure it out though! Commented Jan 15, 2021 at 12:22
  • 1
    Still relevant in 2021, I made this script work with an RDS postgres database by removing every "quote_ident" method, exactly what I needed to just run my DMS migrations...
    – barnacle.m
    Commented Jun 17, 2021 at 22:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.