0

I'm configuring PostgreSQL WAL-based backup for point-in-time recovery. Actually, I don't really need any point in time beyond 1 month ago and don't want to waste time for the logs older than that.

Is that possible to combine the old logs with the current baseline snapshot, to get a new snapshot as if it was made 1 month ago while keeping all the newer logs for the PITR recovery?

To explain that schematically:

current_database == base1.tgz + older_logs + newer_logs
                 == base2.tgz + newer_logs

How to get base2.tgz?

  • I would say you get base2.tgz the same way you got base1.tgz :) – dezso Mar 10 '14 at 16:20
2

There isn't a magic way of doing that, the options I see is:

  1. At a defined interval, do another basebackup and remove old backups. You can use pg_archivecleanup contrib to remove the old archives (read the contents of backup_label file contained in your basebackup to check which files each basebackup needs). This is a broadly used approach, and recommended for its simplicity.

  2. Another approach is to extract the basebackup into a directory, add a recovery.conf file and start PostgreSQL to consume the archives.

Following an example of recovery.conf to achieve that:

# restore the archives
restore_command = 'cp /path/to/archives/%f %p'
# remove archives already restored (you must install pg_archivecleanup contrib)
archive_cleanup_command = 'pg_archivecleanup /path/to/archives %r'
# select a target time of last month (you must create this by hand)
recovery_target_time = '2014-01-10 00:00'
# make PostgreSQL pause when it reaches the target time
pause_at_recovery_target = true
# make the PostgreSQL stop itself
recovery_end_command = 'pg_ctl -w -D /path/to/data stop -mf'

The problem is that you'll need to do it all by hand. I had no time to test it myself, but you can try and see if the following shell script works for such thing:

#!/bin/sh
# create data dir
mkdir -p /path/to/data
cd /path/to/data
# extract base backup
tar xvf /path/to/base1.tar.gz
# create recovery.conf (as the above)
cat > recovery.conf <<EOL
    restore_command = 'cp /path/to/archives/%f %p'
    archive_cleanup_command = 'pg_archivecleanup /path/to/archives %r'
    recovery_target_time = '`date -d '1 month ago'`'
    pause_at_recovery_target = true
    recovery_end_command = 'pg_ctl -w -D /path/to/data stop -mf'
EOL
# start PostgreSQL
pg_ctl -D . start

You can also use the recovery_end_command to recreate the .tar.gz file or just keep the backup as a directory.

Sorry for the not tested solution, but you can try it and I can edit if any mistake is found.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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