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

On 06/08/2015 05:45 AM, otheus uibk wrote:
> 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").
>

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

The important part can be found here:

http://www.postgresql.org/docs/9.4/interactive/wal-intro.html

"Briefly, WAL's central concept is that changes to data files (where
tables and indexes reside) must be written only after those changes have
been logged, that is, after log records describing the changes have been
flushed to permanent storage."

So basicall as long as you have the relevant WAL logs, which is what
pg_start_backup and pg_stop_backup log, then you are covered.

>
> 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.
>
>
>
> --
> Otheus
> otheus(dot)uibk(at)gmail(dot)com <mailto:otheus(dot)uibk(at)gmail(dot)com>
> otheus(dot)shelling(at)uibk(dot)ac(dot)at <mailto:otheus(dot)shelling(at)uibk(dot)ac(dot)at>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com