On Thu, Oct 27, 2011 at 7:57 PM, rihad <rihad@xxxxxxx> wrote:
Hi, I'm backing up the entire server directory from time to time. pg_xlog/ directory containing WAL files is pretty heavy (wal_level=archive). Can I exclude it from the regular tar archive?
The best would be to perform "pg_switch_xlog()" and take a backup excluding pg_xlog.
To recover the last moment TXNs, you might need pg_xlog (depends on when you would be recovering). pg_switch_xlog() will reduce the dependency on pg_xlog files to a greater extent.
#!/bin/sh
renice 20 $$ 2>/dev/null
pgsql -U pgsql -q -c "CHECKPOINT" postgres # speed up pg_start_backup()
pg_start_backup() performs a checkpoint and ensures that all the data till that particular checkpoint and TXN id will be backed up (or marked as needed for data consistency while restoring and recovering).
pgsql -U pgsql -q -c "select pg_start_backup('sol')" postgres
tar -cjf - /db 2>/dev/null | ssh -q -i ~pgsql/.ssh/id_rsa -p 2022 -c blowfish dbarchive@10.0.0.1 'cat > db.tbz'
pgsql -U pgsql -q -c "select pg_stop_backup()" postgres
sleep 60 #wait for new WAL backups to appear
echo 'ssh -q dbarchive@10.0.0.1 ./post-backup.sh' | su -m pgsql
I want to change tar invocation to be: tar -cjf --exclude 'db/pg_xlog/*' ...
Will there be enough data in case of recovery? (May God forbid... )))
But, all the WAL Archives between backup start time and end time must be backed up. They are needed at any cost for the database to be consistent and the recovery to be smooth.
Recovering to any point-in-time purely depends on your backup strategy.
Thanks
VB
