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.