A quick step-by-step to backup and restore a remote postgres database

postgres-backup.md

Backing up postgres DBs

Our data is hosted in postgres clusters accessible via the internet. Follow these steps to backup a remote instance, and restore the data on your local instance.

This example assumes the remote cluster is using postgres version 14.

1. Install postgresql 14 on your machine. This will give you access to the pg_dump tool, which we'll use to take a backup of our cloud postgres instance.
   • OSX steps (make sure to replace 13 with 14)
   • (Linux steps) run sudo apt-get install postgres-14
2. Ensure you have access to a DB role that has the proper permissions to access staging data
3. Run pg_dump --column-inserts -d <DATABASE NAME> -h <HOST URI> -p <HOST PORT> -U <YOUR USERNAME> -W > <OUTPUT FILE>.sql. This will dump a replayable sql script that will recreate the staging database in its entirety on your local cluster.
   • If you would like to use pg_restore to restore the database, and -F t as an option to specify a tarball output
   • If you run into an error which complains about a verison mismatch between pg_dump and the server, follow the below steps to install the correct version locally, and re-run:
     • (on linux) Follow the steps here to add the postgres apt repository. Then, install via apt-get the version returned in your error message. For example, if the server's version was 14, I would run sudo apt-get install postgresql-14.
     • (on OSX (untested)) Follow the steps here to install the required version
4. You have successfully backed up a snapshot of the staging database. Good work! Now we'll restore it. Locally, ensure that you have a db with a role granted all priviliges matching in name the role you used for pg_dump
5. To restore, run the following command psql -h 127.0.0.1 -U <LOCAL USER> -f <PATH TO OUTPUT FILE>.sql -d <DATABASE>
   • NB: This assumes you have already
     1. started the local postgres instance (e.g. by running sudo service start postgresql)
     2. created the database (by connecting to your local cluster with psql and running CREATE DATABASE <DATABASE>;)
     3. created a Postgres (not OS-level) user, and have already granted that user access to the database