Steps to change Postgres Server in Django Project

Before downtime

• Create database users and databases on the new server, using this guide.
• Make sure the CA Cert is available under Django server's home directory (ls /home/piratedev/.postgresql/root.crt).
• Test the connection from the Django box:

# install psql if needed: `sudo apt update && sudo apt install postgresql-client`
# Replace the placeholders
sudo -u piratedev psql "host=<new private IP> dbname=<db> user=<db user> sslmode=verify-full sslrootcert=/home/piratedev/.postgresql/root.crt" -c "SELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid();"
# You should see t | TLSv1.3 (or 1.2)

Downtime

• Stop Django and anything else that writes:

sudo systemctl stop apache2      # or: sudo systemctl stop gunicorn
sudo systemctl stop cron         # plus any celery workers / systemd timers

• (Optional) Lock the old database (old server, sudo -u postgres psql):

ALTER ROLE <db user> NOLOGIN;
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = '<db user>';

• Follow this guide to perform following operations:

  • Back up (old server) and copy to the new server.
  • Restore into the EMPTY database (new server).
    • You may receive some errors due to -c option, which tells pg_restore to drop every object before recreating it, but the database is empty. Use the counts comparison to confirm if the restore is successful.

• Compare row counts. Save as counts.sql, run on both servers, and diff the outputs (should print nothing):

SELECT table_name,
       (xpath('/row/n/text()', query_to_xml(format('SELECT count(*) AS n FROM %I.%I', table_schema, table_name), false, true, '')))[1]::text::bigint
FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
ORDER BY 1;

psql -At -U <db user> -d <db> -f counts.sql > counts_old.txt   # old server
psql -At -U <db user> -d <db> -f counts.sql > counts_new.txt   # new server
diff counts_old.txt counts_new.txt

• Update Django settings.py to switch to the new server: update POSTGRES_HOST (and user/password if changed) and then:

python manage.py migrate --check    # exits cleanly if nothing is pending

• Resume Django Webserver:

sudo systemctl start apache2     # or: sudo systemctl start gunicorn
sudo systemctl start cron        # plus anything else you stopped

• Smoke-test with a write (create a record) to confirm sequences carried over.

Rollback (only before real users write to the new server)

Nothing to undo on the new server. On the OLD server run ALTER ROLE <db user> LOGIN;, point POSTGRES_HOST back to the old server, set POSTGRES_SSLMODE=prefer, and restart the web server. Keep the old server for about a week before deleting it.