Thread: PITR and base + full backups

On Tuesday 16 September 2008, "Joey K." <pguser@gmail.com> wrote:
> Hello,
>
> Just to be sure of our backups we plan to do a base + full backups (yes,
> we are overly paranoid)
>
> (1)  (`date`)
>
> (2) perform hot rsync first (while the database is running)
> $ rsync -avr pgdata /backup/`date`/
>
> (3) stop pg
>
> (4) perform cold rsync
> $ rsync -avr --delete pgdata /backup/`date`/
>
> (5) start pg
>
> (6) pg_stop_backup()
>
> This didn't work and not sure if this is supposed to work ;-)
>
> Or should I stick to just plain PITR?

If you can live with the downtime to do that, you don't need steps 1 or 6,
and eliminating them will probably fix your problem. I imagine stopping the
database between pg_start_backup and pg_stop_backup caused whatever problem
you experienced.

Plain PITR is better, though, if you're actually archiving the WAL logs, as
your possibly recovery points will be as current as your last archived log.


--
Alan

On Tue, 16 Sep 2008, Joey K. wrote:

> (1) pg_start_backup(`date`)
> (2) perform hot rsync first (while the database is running)
> $ rsync -avr pgdata /backup/`date`/
> (3) stop pg

You need to call pg_stop_backup() here and wait until the last WAL file it
references has been archived before this backup is complete.  You can't
finish that backup with pg_stop_backup after the server has been stopped.
The upcoming PostgreSQL 8.4 won't even let you stop the server normally if
a backup is in process because you're not supposed to do that.

If you're running 8.2 or later, you can set archive_timeout to bound how
long it will take before that last segment shows up, or you can manually
call pg_switch_xlog.  See http://wiki.postgresql.org/wiki/Warm_Standby for
how to force that on 8.1 (and the walkthrough on that page may be helpful
to you as well).

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Tue, 2008-09-16 at 21:16 +0530, Joey K. wrote:

> This didn't work and not sure if this is supposed to work ;-)
>
> Or should I stick to just plain PITR?

Yes, just drop steps (3) and (5). If you don't trust it for some reason,
then don't use it at all - mixing modes like that won't work. But we add
info to WAL to ensure that inconsistencies are removed from any backup.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support