How to copy a database from one environment to a dev env

You can copy a database - you might want to take the preview DB and put it on a dev env for example.

Dump database

To dump the db from RDS replace FROM-ENV with the environment you wish to take the database from. This might take a few minutes depending on the database size.

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

Drop the existing data and tables

To drop the old db we'll need to connect with admin

gds aws notify-TO-ENV-admin -- db-connect.sh notifydb -- pgcli

Drop all the tables from public schema 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 database name details

Get the RDS database name from gds cli

gds aws notify-TO-ENV-admin -- db-connect.sh notifydb

It's the field that reads Database: rdsbroker_[....]

Restore database to your chosen environment

Use the db name you've grabbed from above.

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