Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"'


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?

share|improve this question

2 Answers 2

There is an existing tool that'll help you a great deal, WAL-E. It provides an archive_command and restore_command for PITR to S3.

There are no commands to do incremental or differential logical backups. pg_dump can't take an incremental or differential. The only way to do that is via log archiving.

In theory you could take a new full backup, do a binary diff between it and the last backup, and upload the diff. This strikes me as a fragile and inefficient way to do things, though, and I really wouldn't recommend it.

Additionally, PgBarman supports integration with S3 via hook scripts, and will automate much of the backup rotation and management for you. Again, this may not be an option on Windows.

share|improve this 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.

share|improve this answer
Thanks. A few questions: 1. Do I do pg_start_backup(), copy the data, then run pg_stop_backup(), or pg_start_backup(); pg_stop_backup(), then copy? –  Neil McGuigan Jul 8 '13 at 20:30
@Neil: 1. and 2. don't apply when you use pg_basebackup, it already takes care of this. 3. postgres will automatically delete the WAL files in pg_log when they're no longer needed. You shouldn't do anything manually in pg_log. Otherwise see the wal_keep_segments parameter –  Daniel Vérité Jul 8 '13 at 20:44
by "archive" folder you mean pg_xlog, yes? –  Neil McGuigan Jun 26 at 21:43
@NeilMcGuigan: not at all. The archive folder is the destination folder of your archive command, for instance here "c:\postgres\foo". pg_xlog is entirely managed automatically by postgres, whereas the archive folder is entirely managed by the DBA. –  Daniel Vérité Jun 26 at 23:09
I guess I'm confused because the WALs in foo never change after the initial pg_basebackup –  Neil McGuigan Jun 26 at 23:15

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.