Skip to main content
Table of contents

Databases

Copy PostgreSQL dump from one machine to another

This document explains how to back up a PostgreSQL database from one machine and import it to another machine (or even another environment).

Back up your PostgreSQL database

Firstly, SSH into the machine running the PostgreSQL instance you want to copy. Then become the PostgreSQL user (it shouldn’t need a password):

sudo su - postgres

This will change your directory to /var/lib/postgresql.

Find the name of the database you want to back up. Start the PostgreSQL console (psql), then list the databases (\l) if you’re not sure.

Then create a dump:

pg_dump name_of_database > /tmp/name_of_database.bak

Return to being a normal user on the instance:

exit

Now fix the permissions so that you own it:

sudo chown $(whoami):$(whoami) /tmp/name_of_database.bak

Now exit the instance and move on to the next step.

Download the backup to your local machine

Use govuk-connect to download the file. For example:

gds govuk connect scp-pull -e <environment> <ip> /tmp/name_of_database.bak

Upload your backup to the desired machine

Use govuk-connect to upload the file. For example:

gds govuk connect scp-push -e <environment> <ip> name_of_database.bak /tmp

Import the backup to overwrite your PostgreSQL database

SSH into the machine, and then move the backup away from /tmp:

sudo mv /tmp/name_of_database.bak /var/lib/postgresql/

Pass ownership over to the PostgreSQL user:

sudo chown postgres:postgres /var/lib/postgresql/name_of_database.bak

Stop any services that are relying on the database, e.g.:

sudo service mapit stop

Assume the PostgreSQL user role:

sudo su - postgres

Remove active connections on the database:

$ psql
postgres=# REVOKE CONNECT ON DATABASE name_of_database FROM public;
postgres=# SELECT pg_terminate_backend(pg_stat_activity.pid)
           FROM pg_stat_activity
           WHERE pg_stat_activity.datname = 'name_of_database';

Drop the database and create a new one in its place:

postgres=# DROP DATABASE name_of_database; CREATE DATABASE name_of_database;

Import new database:

$ psql -d name_of_database -f name_of_database.bak

Return to being a normal user on the instance:

exit

Restart database (to re-allow public connections):

sudo service postgresql restart

Restart whatever services you stopped, e.g.:

sudo service mapit start

Tidy up after yourself:

sudo rm /var/lib/postgresql/name_of_database.bak
This page was last reviewed on 28 April 2020. It needs to be reviewed again on 28 October 2020 by the page owner #govuk-developers .
This page was set to be reviewed before 28 October 2020 by the page owner #govuk-developers. This might mean the content is out of date.