session_replication_role
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.
Dropping
create table if not exists dropped_foreign_keys (
seq bigserial primary key,
sql text
);
do $$ declare t record;
begin
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())
loop
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 $$;
Recreating
do $$ declare t record;
begin
-- 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 $$;
Changing Instance Class:
From the documentation "Modifying a DB Instance and Using the Apply Immediately Parameter", changing the instance class and setting "Apply Immediately" to false will result in:
- The change being applied during the next maintenance window.
- The change causing an outage to occur.
Since the instance is Multi-AZ and you're changing the instance class, your outage will be minimized to the amount of time it takes to failover to the standby replica. That isn't very long; likely worst-case a minute or two. You won't lose data that is already in the DB. Any existing connections will be dropped during failover. You should verify that applications connected to your database can withstand these conditions, or alternatively take them down for maintenance during this time.
Identifying RDS instance storage capacity:
You can identify how much of the RDS storage is currently in use by either:
1) Simply selecting your RDS instance from the RDS web console and viewing the "Storage" bar under the "Monitoring" header.
2) Viewing the "FreeStorageSpace" RDS cloudwatch metric for your RDS instance.
Further Reading:
Best Answer
No. It's not supported on RDS at this point. See https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.
If you really need cstore_fdw, you'll have to manage your own PG instance.