PostgreSQL Incremental Backup – How to Do It Every Hour on Windows

backuppostgresqlpostgresql-9.4windows

Trying to do an hourly incremental backup of a single Postgres server (Win7 64).

I have the following setup in postgresql.conf:

max_wal_senders = 2
wal_level       = archive
archive_mode    = on
archive_command = 'copy "%p" "c:\\postgres\\foo\\%f"'

(restart)

I did a base backup with pg_basebackup -U postgres -D ..\foo -F t -x

Which made a big base.tar file in the foo folder and added some 16,384 KB files, which I assume are WALs.

What I don't understand is why the WALs in foo don't change. The WALs in data/pg_xlog change. Is pg not supposed to copy them over? How does it decide to do so?

Perhaps I need to set archive_timeout=3600 ?

I've seen several sites (pg's mailing lists, bacula's postgres page) that say you need to call pg_start_backup() and pg_stop_backup(), but I believe that those are not required. Is that true?

Secondary questions:

  1. How often do the WALs in data/pg_xlog get written? What triggers a write?

    It seems to update a WAL if I do some DML then \q in psql. Or edit a table in pgAdmin then close the window. I figured it would write on commit.

  2. Best practices? pg_basebackup once a week? Archive WALs to same machine as PG or a remote machine?

Best Answer

You want to do an incremental backup of the archive folder to remote storage.

Should you need to restore from the backup, the basic scenario is that you'd need your base backup as the starting point, and the entire contents of the archive folder to replay the transactional activity that happened between the starting point and the crash.

Also to avoid having the files in the archive folder piling up forever, you want to do a new base backup from time to time and delete the files that were archived before the new base backup.

Related Question