Learn how to simply backup and restore a PostgreSQL database using SQL dumps. Learn different techniques and get code and scripts you can use.

All commands should be run as the postgres user.

sudo su - postgres

Basic backup and restore commands

# Backup a single database pg_dump db_name > db_backup.sql # Restore a single database psql db_name < db_backup.sql # Backup an entire postgres database cluster pg_dumpall > cluster_backup.sql # Restore an entire postgres database cluster psql -f cluster_backup.sql postgres

Backup and Restore using compression

# Backup a single database pg_dump db_name | gzip > db_backup.gz # Restore a single database gunzip -c db_backup.gz | psql db_name # Backup a database cluster pg_dumpall | gzip > cluster_backup.gz # Restore a database cluster gunzip -c cluster_backup.gz | psql postgres

Backup and be able to restore individual tables

# Backup a single database pg_dump -Fc db_name > db_backup.dmp # Restore a single database pg_restore -d db_name db_backup.dmp # Can use pg_dumpall to backup all global information # then use pg_dump to backup each database pg_dumpall > global_only_backup.sql --globals-only

Restore the database and stop on errors

psql db_name < db_backup.sql --set ON_ERROR_STOP=on

After restore, vacuum and analyze tables

vacuumdb -a -z

Basic Backup script

#!/bin/bash # # Takes a full backup of the database and stores it in the backup folder # Run this script as the postgres user # DATE=`date +%Y-%m-%d` echo `date` - Delete old backups find ~/backup/* -mtime +1 -delete echo `date` - Do a full postgres cluster dump pg_dumpall | gzip > ~/backup/db_cluster_dump_$DATE.gz echo `date` - Sync pg_backups with S3 # /usr/local/bin/aws s3 sync ~/backup s3://bucket_name/backup echo `date` - Sync postgres configuration files with S3 # /usr/local/bin/aws s3 sync /etc/postgresql/9.3/main s3://bucket_name/backup echo `date` - Backup complete

Schedule the script using cron

# Make sure script is executable by postgres chmod 770 backup_script.sh crontab -e

# m h dom mon dow command 0 4 * * * ~/backup_script.sh > ~/backup_script.log 2>&1

Please go ahead and leave a comment below if you have any questions about this tutorial.