Re: pg_start_backup does not actually allow for consistent, file-level backup

Am 2015-06-08 14:45, schrieb otheus uibk:
> The manual and in this mailing list, the claim is made that
> consistent, file-level backups may be made by bracketing the
> file-copy
> operation with the postgresql pg_start_backup and pg_stop_backup
> operations.  Many people including myself have found that in some
> circumstances, using "tar" to copy these files will result in an
> error
> if one of the data files changes during the tar operation. The
> responses to those queries on this mailing list are unsatisfactory
> ("everything is fine, trust us").

[...]

> I decided to test this claim that these messages are "perfectly
> harmless" and "can be ignored":
>
> 1. I executed pg_start_backup() on server
> 2. Ran md5sum recursively through PGs data directories
> 3. waited a split second
> 4. Ran md5sum recursively through PGs data directories as in step 2
> 5. Compared output from #2 and #4
>
> As you can see below, there were non-zero changes made to these
> files. 
>
> < a1a571bfd1e4a98b20245edbdfce6d9a
>  /var/lib/pgsql/data/base/41514/809275
> ---
>> 21de5b864019c96c55e81a38fa1c9ccf
>  /var/lib/pgsql/data/base/41514/809275
> 1783c1783
> < 8eb4a578ecb56667e1698174f89c462c
>  /var/lib/pgsql/data/base/41514/809280
> ---
>> b4c7b4ef30dda9543181465f53a85d72
>  /var/lib/pgsql/data/base/41514/809280
>
> Such changes occurred EVEN WHEN TAR DID NOT WARN of changed files.
> Further, when step 3 involved an actual backup, involving minutes,
> not
> milliseconds, dozens of differences to files in data/base/... are
> reported. To be clear, I excluded from consideration all files in
> pg_xlog, pg_clog, pg_subtrans, pg_stat_tmp.
>
> If these files are changing during the pg_start_backup() and
> pg_stop_backup, then exactly what is their purpose? Might they be
> changing during the tar, as tar thinks? How may an operator be
> assured
> the snapshot is consistent (unless one stops the databases)?  Will
> the redo logs restore the files to a consistent state, no matter when
> these files are changed? I find it hard to believe that would be the
> case.
>
> This test was performed using Postgresql 9.1.8. A scan of the
> CHANGELOG since then indicates that if this is a bug, it has not been
> reported as fixed.

Still everything is fine here. You need to understand that in between
pg_start_ and pg_stop_backup Postgres continues to operate aus usual -
so files in $PGDATA directory WILL change. That's why it is necessary to
also keep all the WAL segments that where created during _start and
_stop to actually recover to a consistent state. When you recover from a
full (file based) backup the WAL files file be applied, too (that is why
you need a recovery.conf and a restore_command.

You should possibly re-read
http://www.postgresql.org/docs/9.4/static/continuous-archiving.html#BACKUP-PITR-RECOVERY
especially 24.3.3 and 24.3.4.

hth

Jan