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 2a

Go to your backup page, then select the "Logs" tab. Afterwards, click the little "( i )" on the right next to the copy you want to download.

Download Your Backup

Step 2b

Under the backup restore section on the modal that just popped up, click on "Click to generate a signed download link" then copy the resulting link

Step 3

On your server, run the following command, and use the signed download link you obtained in the previous step (ensure you enclose it in double quotes as shown):

wget "PasteTheSignedDownloadLinkHereBetweenTheQuotes" -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.

Did this answer your question?