PostgreSQL Backup and Restore

SQL Dump

pg_dump dbname > outfile 

psql -f infile postgres

pg_dump dbname | gzip > filename.gz

gunzip -c filename.gz | psql dbname 

pg_dump -Fc dbname > filename

pg_restore -d dbname filename 

File System Level Backup

tar -cf backup.tar /usr/local/pgsql/data

Continuous Archiving

archive_command = 'test ! -f /var/lib/pgsql/backup_in_progress || (test ! -f /var/lib/pgsql/archive/%f && cp %p /var/lib/pgsql/archive/%f)'

touch /var/lib/pgsql/backup_in_progress
psql -c "select pg_start_backup('hot_backup');"
tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
psql -c "select pg_stop_backup();"
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/