Re: Corrupt Incrementally Updated Backup: missing pg_clog file

Hi Jürgen,

am 31.10.2012 um 15:24 schrieb Jürgen Fuchsberger <juergen(dot)fuchsberger(at)uni-graz(dot)at>:

> The replica is backed up once a week using rsync, a full backup runs about 10 hours, so I also keep at least 24h of WAL files to make sure I have a consistent backup.
>
> The backup process also runs fine without errors, only the time (10h) it takes is quite long, so I decided to test the backup:

[...]

> The backup is corrupt. So my question is, what went wrong:
> Obviously as the rsync started it copied everything from the pg_clog (which at this point was until pg_clog/01DC) and then went on for another 10+ hours backing up all the rest of the database. At the time the backup ended, the database content changed but the newer clog files did not go into the backup.
> When restoring the backup and starting the server, the recovery process started at a point where pg_clog was at state 01DE or even further and thus the data from 01DD was missing.

It sounds as if the standby server is running while you take its backup. I'm not sure it is possible to make a consistent backup this way if this is the case.
I would stop the standby, make the backup and then continue.

Regards,
Ralf