Re: Corrupt Incrementally Updated Backup: missing pg_clog file

On Wed, Oct 31, 2012 at 7:24 AM, Jürgen Fuchsberger
<juergen.fuchsberger@uni-graz.at> wrote:
> Hi all,
>
> I have a problem with a corrupt backup, fortunately I was only testing so I
> did not loose any data. Unfortunetely what I did is to follow the backup
> guidelines in the documentation, which I thought should work reliably. Here
> are the details:
>
> I am running a postgreSQL 8.4 database on a Debian Squeeze system. For
> Backups I am using the warm standby and "Incrementally Updated Backup"
> method as described in chapter 24.4 of the documentation. So my Setup is as
> follows:
>
> Server 1 (Main): PostgreSQL 8.4 Database with archive_mode enabled shipping
> WAL files to a NFS drive. Size of database is about 370 GB and growing.
>
> Server 2 (Replica): PostgreSQL 8.4 Database in recovery mode. Using
> pg_standby in recovery.conf and getting WAL files from Server 1 NFS drive.
>
> All this works fine and runs without errors.
>
> The replica is backed up once a week using rsync,

You can not safely backup a running server using rsync, except as
described in the documentation.

For this purpose, it does not matter that the running server is a warm standby.

> 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:

Since the backup process could not be restored, then it was *not*
running fine.  It was good that you tested this, but I don't
understand your reasoning behind it.  You test a backup to make sure
it works, but because conducting one is slow.

....

> 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.

And heaven knows what else is missing.

>
> So what I do from now, is an extra daily backup of my clog directory to make
> sure to have working backups.

I think you are inventing ever more clever ways to destroy your data.
Doing this is unlikely to protect your data, but will simply corrupt
it in ways that are harder to detect.

>
> Changes made to the settings in the postgresql.conf file:
> name                  |  current_setting
> ----------------------+-------------------------------------------------
>  version              | PostgreSQL 8.4.13 on i486-pc-linux-gnu, compiled by
> GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit

>  archive_command      | cp -i %p /var/postgres-wal/%f </dev/null && cp -i %p
> /var/postgres-wal/bak/%f </dev/null && gzip /var/postgres-wal/bak/%f

I don't think that cp -i ... </dev/null does what you want on Debian,
in that it returns 0 even if it failed due to refusal to overwrite.

Cheers,

Jeff