DB Commands

Dump database

To dump the db from RDS replace dev-x to environment

gds aws notify-dev-x -- db-connect.sh notifydb -- pg_dump -Fc -f db_copy --exclude-schema=hint_plan --exclude-schema=pglogical

Connect to pg cli

To dump the db from RDS replace dev-x to environment and use admin role.

gds aws notify-dev-x-admin -- db-connect.sh notifydb -- pgcli

Drop the tables

Drop all the tables from public schema and with table owner notify_schema_owner.

DO $$ 
 DECLARE 
     r RECORD;
 BEGIN 
     FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public' and tableowner='notify_schema_owner') LOOP
         EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
     END LOOP; 
 END $$;

After running this command double check that all tables are deleted.

Get RDS broker

Get the RDS database broker connection details.

gds aws notify-dev-x-admin -- db-connect.sh notifydb

Restore database

To restore the db to RDS replace dev-x to environment and use admin role. Update the db name to rdsbroker.

gds aws notify-dev-x-admin -- db-connect.sh notifydb -- pg_restore -d rdsbroker_xxxx -c -v db_copy