[GENERAL] Are pg_xlog/* fiels necessary for PITR?

2011-10-27 Thread rihad
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?



#!/bin/sh

renice 20 $$ 2/dev/null
pgsql -U pgsql -q -c CHECKPOINT postgres # speed up pg_start_backup()
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... )))

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Are pg_xlog/* fiels necessary for PITR?

2011-10-27 Thread Venkat Balaji
On Thu, Oct 27, 2011 at 7:57 PM, rihad ri...@mail.ru 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