Restore a PostgreSQL Database Backup

This is a quick example of how to restore a PostgreSQL database dump.

Step 1

Create a PostgreSQL backup for sampleDatabase (as an example).

Step 2

Go to your backup page, under the "Backup History" section click the little "i" on the right, click "Database Backup", then click "Raw Download Link" and copy the output.

Step 3

On your server, run the following command, replace the raw download link you obtained in the previous step:

wget "PasteTheRawDownloadLinkHereBetweenTheQuotes" -O "postgresql-backup.tar.gz"

Step 4

Now that you downloaded the database backup on your server, extract it as shown:

tar -xvf postgresql-backup.tar.gz

This will output a single SQL (or PGSQL) file in this format sampleDatabase.sql (or sampleDatabase.pgsql) in the same directory. The format depends on the way you dump your database.

Step 5

Note: the following commands will create NEW_DATABASE_NAME and import the content of sampleDatabase into it. It is a good practice to inspect the backup first before the restore.

Option 5a) If the output file format is .sql then restore the downloaded backup as shown below by running:

sudo -u DATABASE_USER createdb NEW_DATABASE_NAME

sudo -u DATABASE_USER PGPASSWORD=DATABASE_PASSWORD psql --single-transaction -U DATABASE_USER -h DATABASE_HOST_IP -p DATABASE_PORT -d NEW_DATABASE_NAME < sampleDatabase.sql

Example for most installations:

sudo -u postgres createdb NEW_DATABASE_NAME
sudo -u postgres PGPASSWORD=DATABASE_PASSWORD psql --single-transaction -U postgres -h 127.0.0.1 -p 5432 -d NEW_DATABASE_NAME < sampleDatabase.sql

Option 5b) If the output file format is .pgsql then restore the downloaded backup as shown below by running:

sudo -u DATABASE_USER createdb NEW_DATABASE_NAME

sudo -u DATABASE_USER psql -U DATABASE_USER -d NEW_DATABASE_NAME -c "drop schema public cascade;"

sudo -u DATABASE_USER pg_restore --single-transaction --no-owner -U DATABASE_USER -d NEW_DATABASE_NAME sampleDatabase.pgsql

Example for most installations:

sudo -u postgres createdb NEW_DATABASE_NAME

sudo -u postgres psql -U postgres -d NEW_DATABASE_NAME -c "drop schema public cascade;"

sudo -u postgres pg_restore --single-transaction --no-owner -U postgres -d NEW_DATABASE_NAME sampleDatabase.pgsql

If you need any help in any of these steps, let us know and we can help you with the restore.