Stopping client replication services upon contract termination

When a client stops needing our replicated database, we have a few things to do to clean up resources and plug holes in our firewall they were previously using. Some of these tasks may be optional if we know we have new replication customers coming online that can take over the hardware.

That said, the things to consider doing are…

On their server, if you can

1. Log into the subscriber as the admin user:

    psql -h xxxx.us-east-1.rds.amazonaws.com -U postgres --dbname courtlistener
   

2. Show their subscriptions and note the subslotname and subpublications name:

    select * from pg_subscription;
     subdbid | subname  | subowner | subenabled |                       subconninfo                             | subslotname | subsynccommit | subpublications 
    ---------+----------+----------+------------+---------------------------------------------------------------+-------------+---------------+-----------------
       18463 | opendata |    16389 | t          | host=xxx port=5432 password=xxx user=xxx dbname=courtlistener | opendata    | off           | {opendata}
   

3. Delete their subscription to our server (this can take a moment, as it communicates with the publisher):

     DROP SUBSCRIPTION opendata;
   

On our replication server in AWS

1. Log in using the management command:

    ./manage.py dbshell --database replica
   

2. Show all publications:

    select * from pg_publication;
   

3. Drop the publication:

    drop publication opendata;
   

4. Check that the replication slot has been dropped (sometimes this can happen if you don't have the ability to drop the slot yourself (say the server disappeared)):

    select * from pg_replication_slots order by slot_name;
   

   This shouldn't show the slot anymore, but if it does, you can drop it manually with:

    select pg_drop_replication_slot('opendata');
   

5. Delete their user from our server:

    \du;
    REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM xxx;
    DROP USER xxx;
    \du
   

That's it for the databases.

In AWS itself

1. Delete the RDS instance first. This will take some time, but verify that it works. To delete it:

   • Modify the RDS instance to allow deletion.
   • Delete it (don't keep backups, snapshots, etc.)

2. Delete the Route 53 record and note where it forwards to, so you can delete the correct proxy.

3. Delete the EC2 proxy the DNS record pointed to (Set its state to "terminated").

4. Remove any holes from the VPC firewall security groups.

5. Delete any alarms that are tracking the instance:

   • Delete the instance from the composite alarm.
   • Delete the alarm it may have.

Sources

1. #1072 and #1495 are where I first researched this process.