Backup a big PostgreSQL database continually
Backups are extensively covered in the PostgreSQL manual.
To achieve a continuous backup, archive the write-ahead log. Suggested settings are:
archive_mode = on
wal_level = hot_standby
archive_command = '/usr/bin/rsync --archive --ignore-existing "%p" "/backup-dest/%p"'
# Ensures that a log file is written at least once every 30 minutes even if little
# activity has occurred
archive_timeout = 30min
Instead of doing a pg_dump
for your baseline, you can do pg_basebackup
, which does not require you to freeze the database. However, if you do not already have archive_mode
on, you'll need to restart the database to change that setting.