Instructions for restoring a database and/or failing over are in the Postgres page in the Notify team manual.

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

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. Note using the notifydb-replica-0 database name will use the read replica to dump the data.

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

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 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_[....]

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

If you only wish to revert changes to the database schema you can run a database downgrade using alembic downgrade. You need to exec into a container running notifications-api (api-web) and run the following command where is formatted like ["dev-a", "development", "preview", "staging", "production"]:

FLASK_APP=application.py NOTIFY_ENVIRONMENT=<env-name> flask db downgrade

To create a database diagram for the notifications-architecture repo complete the following steps.

Install sql2dbml:

brew install dbml-cli

Export the schema from staging:

gds aws notify-staging -- db-connect.sh notifydb-replica-0 -- pg_dump -f ~/Downloads/schema.sql --schema-only --exclude-schema=pglogical

Convert the schema sql to DBML format

sql2dbml --postgres -o ~/Downloads/schema.dbml ~/Downloads/schema.sql

Now visit dbdiagram.io and create a diagram with the DBML in the ~/Downloads/schema.dbml file. Choose the 'compact' auto arrange algorithm.