Restore a PostgreSQL backup
Restore a PostgreSQL database backup.
This is a quick example of how to restore a PostgreSQL database dump.
Step 1 - Create a PostgreSQL database
Create a PostgreSQL backup for sampleDatabase (as an example).
Step 2 - Copy your backup URL
- 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.
- 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 - Download your backup file
On your server download and extract the backup by runnig 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 -O - "PasteTheSignedDownloadLinkHereBetweenTheQuotes" | gunzip -c > postgresql-backup.pgsqlStep 4 - Create a new database & restore the backup
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 1
If the output file format is .pgsql (if you are using the quick export, default option) then restore the downloaded backup as shown below by running:
sudo -u DATABASE_USER createdb NEW_DATABASE_NAMEsudo -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.pgsqlExample 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.pgsqlOption 2
If the output file format is .sql (if you are not using the quick export option in SimpleBackups) then restore the downloaded backup as shown below by running:
sudo -u DATABASE_USER createdb NEW_DATABASE_NAMEsudo -u DATABASE_USER PGPASSWORD=DATABASE_PASSWORD psql --single-transaction -U DATABASE_USER -h DATABASE_HOST_IP -p DATABASE_PORT -d NEW_DATABASE_NAME < sampleDatabase.sqlExample 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.sqlLast updated on August 6, 2021