Mysql Incremental Backup and point in time recovery

There is a cleaner approach

SMS=/tmp/show_master_status.txt
mysql -ANe "SHOW MASTER STATUS" > ${SMS}
CURRENT_LOG=`cat ${SMS} | awk '{print $1}'`
CURRENT_POS=`cat ${SMS} | awk '{print $2}'`
echo ${CURRENT_LOG} ${CURRENT_POS}

This logs into mysql only once.

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.