Backup & Restore a PostgreSQL Database

Postgres provides to 3 fundamentally different ways of backing up your database. 

• SQL Dump
• File System Backup
• Continuous Archiving

SQL Dump

All commands should be run as the postgres user.

sudo su - postgres

The SQL dump (pg_dump) is a command that generates a text file that contains necessary SQL instruction that will recreate the database. The dumped file can be fed back to the server to recreate the existing structure of the database, including the data itself.

pg_dump dbname > dbbackupfilename.sql


The pg_dump command can be used to perform database backups from any remote host that you have access to. 
It's important to understand that the command needs access to all tables that you want to backup so it should be initiated by a database user with superuser privilege.


If your are working with a large database it useful to gizip the file as you dump it. You can gizip the dump by running the following command instead. pg_dump dbname | gzip > dbbackupfilename.gz


Restore PostgreSQL pg_dump To restore the pg_dump file we make use of the psql command psql dbname < dbbackupfilename If you want to make sure that the database restoration stops incase of an error related you can set the ON_ERROR_STOP flag. psql --set ON_ERROR_STOP=on dbname < dbbackupfilename If you are handling a large database you might have gzipped the dump to minimize file-size and transfer time. To restore a gzipped pg_dump file you can run the following command. gunzip -c filename.gz | psql dbname


2: File System Backup In some cases the SQL dump is not optimal for your situation. Another useful way to backup PostgreSQL is to do a file system backup that directly copies all the file that PostgreSQL uses to store data in the database.

tar -cf dbbackupfilename.tar /usr/local/pgsql/data It's important to understand that the file system-level backup imposes extra restrictions when compared with the pg_dump method. file system backups require the database to be turned off in order to generate usable backups file system backups only work for complete backup and restoration of an entire database cluster.


--------


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