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 hot incremental backup of a single postgres server.

I have the following setup in postgresql.conf:

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

I did a base backup with pg_basebackup -U postgres -D ..\archive -Ft -l 2013-07-07 -x

Which made a big base.tar file in the archive folder and added some long file name files, which I assume are the WALs.

pg_start_backup('label') and pg_stop_backup() seem to create the WAL files in xp_log, and then copy them to the archive folder.

  1. what command(s) do I run to do a new incremental backup (pg_basebackup does a new base backup which I don't want right now)? do I just run select pg_start_backup('label'); select pg_stop_backup(); on a schedule?

  2. What does the label in pg_basebackup and pg_start_backup() do exactly?

  3. WAL Files don't seem to be removed from pg_xlog. What should I do about that? It seems to keep 5 or so WALs in pg_xlog. Is that to be expected?

  4. Do I need to backup the .backup files in the archive folder or just the 16,384KB WAL files?

  5. should I use the --xlog parameter and if so do I need to change wal_keep_segments from 0?

share|improve this question
add comment

2 Answers 2

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
add comment

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
    
There is, but apparently it doesn't work on Windows. Have you successfully ran it on Windows? –  Neil McGuigan Jul 13 '13 at 4:08
    
@NeilMcGuigan Ah, I didn't spot the "Windows" part as it was just a tag. No, I haven't, I don't tend to target Windows as a primary platform. Porting it shouldn't be too hard though. –  Craig Ringer Jul 13 '13 at 4:34
add comment

Your Answer

 
discard

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.