Re: Are pg_xlog/* fiels necessary for PITR?

On Thu, Oct 27, 2011 at 7:57 PM, rihad <rihad(at)mail(dot)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(at)10(dot)0(dot)0(dot)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(at)10(dot)0(dot)0(dot)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