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

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

From
otheus uibk
Date:
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").

For example: http://www.postgresql.org/message-id/flat/AANLkTikg70iP91TPUwVuF9gb0XtS4fukaKM4qXLTwUfP@mail.gmail.com#AANLkTikg70iP91TPUwVuF9gb0XtS4fukaKM4qXLTwUfP@mail.gmail.com


On Fri, Apr 16, 2010 at 11:55 AM, raghavendra t
<raagavendra(dot)rao(at)gmail(dot)com> wrote:
> Hi All,
>
> For some setups reason, i started taking Hot backup. In this course I have first issued pg_start_backup('backup') and went to the data directory for backing up in OS format using the command "tar -cf backup.tar  /data" . When i issued this command , tar was generating some errors as show below.
>
> bash-3.00# tar -cf 16aprilstandby.tar /db-data/
> tar: Removing leading `/' from member names
> tar: /db-data/base/24643/151667: file changed as we read it
> tar: /db-data/base/24643/151764.2: file changed as we read it
> tar: /db-data/base/24643/151766: file changed as we read it
> tar: /db-data/base/24643/151768: file changed as we read it
> tar: /db-data/base/66412/151969: file changed as we read it
>
> After sometime tar has ended and i also issued pg_stop_backup() and i continued the process.
>
> My question here is, is this errors generated by tar are to worrisome or whats happening in the backend.
> Is "tar" file is safewell to use. Could you please tell me.
Those are not errors, they are warnings. As long as you use
pg_start_backup() and pg_stop_backup() before and after the tar, they
are perfectly harmless, and can be ignored.

The above scenario is exactly what I saw, albeit with less frequency and severity. 

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 PG's data directories
3. waited a split second
4. Ran md5sum recursively through PG's 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.



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





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

Everything is fine, trust us.

>> bash-3.00# tar -cf 16aprilstandby.tar /db-data/
>> tar: Removing leading `/' from member names
>> tar: /db-data/base/24643/151667: file changed as we read it
>> tar: /db-data/base/24643/151764.2: file changed as we read it
>> tar: /db-data/base/24643/151766: file changed as we read it
>> tar: /db-data/base/24643/151768: file changed as we read it
>> tar: /db-data/base/66412/151969: file changed as we read it

> The above scenario is exactly what I saw, albeit with less frequency and severity.

> I decided to test this claim that these messages are "perfectly harmless" and "can be ignored":
[...]
> As you can see below, there were non-zero changes made to these files.
[...]
> 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.

The files are indeed changing while they are backed up.

The tar archive is not a consistent backup.

Redo using the Write Ahead Log will indeed restore the files to a consistent state,
astonishing as that may be.

Yours,
Laurenz Albe

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

From
Guillaume Lelarge
Date:

Le 8 juin 2015 2:48 PM, "otheus uibk" <otheus.uibk@gmail.com> a écrit :
>
> 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").
>
> For example: http://www.postgresql.org/message-id/flat/AANLkTikg70iP91TPUwVuF9gb0XtS4fukaKM4qXLTwUfP@mail.gmail.com#AANLkTikg70iP91TPUwVuF9gb0XtS4fukaKM4qXLTwUfP@mail.gmail.com
>
> and http://www.postgresql.org/message-id/flat/201004201350.o3KDoKR02633@momjian.us#201004201350.o3KDoKR02633@momjian.us (quoted below)
>
>> On Fri, Apr 16, 2010 at 11:55 AM, raghavendra t
>> <raagavendra(dot)rao(at)gmail(dot)com> wrote:
>> > Hi All,
>> >
>> > For some setups reason, i started taking Hot backup. In this course I have first issued pg_start_backup('backup') and went to the data directory for backing up in OS format using the command "tar -cf backup.tar  /data" . When i issued this command , tar was generating some errors as show below.
>> >
>> > bash-3.00# tar -cf 16aprilstandby.tar /db-data/
>> > tar: Removing leading `/' from member names
>> > tar: /db-data/base/24643/151667: file changed as we read it
>> > tar: /db-data/base/24643/151764.2: file changed as we read it
>> > tar: /db-data/base/24643/151766: file changed as we read it
>> > tar: /db-data/base/24643/151768: file changed as we read it
>> > tar: /db-data/base/66412/151969: file changed as we read it
>> >
>> > After sometime tar has ended and i also issued pg_stop_backup() and i continued the process.
>> >
>> > My question here is, is this errors generated by tar are to worrisome or whats happening in the backend.
>> > Is "tar" file is safewell to use. Could you please tell me.
>> Those are not errors, they are warnings. As long as you use
>> pg_start_backup() and pg_stop_backup() before and after the tar, they
>> are perfectly harmless, and can be ignored.
>
>
> The above scenario is exactly what I saw, albeit with less frequency and severity. 
>
> 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 PG's data directories
> 3. waited a split second
> 4. Ran md5sum recursively through PG's 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?

First, files change during backup. The files backup isn't consistent in itself. You need the archived wal files to make it consistent.

pg_start_backup(), among other things, creates the backup_label file which is needed at restore time so that postgres knows at which wal file it needs to start the replay. pg_stop_backup() gives the last position in the wal file needs to get a consistent backup.

> Might they be changing during the tar, as tar thinks?

Yes.

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

You should because it works great :-)

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

On 06/08/15 14:45, 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").

I don't really see what you find unsatisfactory on those responses?
While performing the backup, the database is running and either
modifying or even deleting the files. Tar does not expect that, and thus
emits warnings/errors. That's clearly explained in the docs, and
actually linked in one of the comments you quoted. And pg_start_backup
takes care of that.

If you don't like that, you have multiple options - stop the database
while performing the backup, perform file system level backup (e.g. lvm
snapshot) or use tools like pg_basebackup.

regards

--
Tomas Vondra                   http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Thank you, all.  The manual for 9.4 is indeed clearer on this point than the 9.1 version.  
On Mon, Jun 8, 2015 at 3:13 PM, otheus uibk <otheus.uibk@gmail.com> wrote:
Thank you, all.  The manual for 9.4 is indeed clearer on this point than the 9.1 version.  

Just to nit-pick, I see nowhere in either version of the manual the indication that it is normal for postgresql to continue to update files in its data catalog between pg_start_backup and pg_stop_backup. The closest I see comes in this paragraph:

** Some file system backup tools emit warnings or errors if the files they are trying to copy change while the copy proceeds. When taking a base backup of an active database, this situation is normal and not an error.

Does "this situation" refer to the tools emitting warnings or to the fact that postgresql is updating the files? It might be the case, for instance, that timestamps are updated but not the contents of the files (this is what I had assumed prior to today).

--
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.uibk@gmail.com <mailto:otheus.uibk@gmail.com>
> otheus.shelling@uibk.ac.at <mailto:otheus.shelling@uibk.ac.at>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


On 06/08/2015 06:26 AM, otheus uibk wrote:
> On Mon, Jun 8, 2015 at 3:13 PM, otheus uibk <otheus.uibk@gmail.com
> <mailto:otheus.uibk@gmail.com>> wrote:
>
>     Thank you, all.  The manual for 9.4 is indeed clearer on this point
>     than the 9.1 version.
>
>
> Just to nit-pick, I see nowhere in either version of the manual the
> indication that it is normal for postgresql to continue to update files
> in its data catalog between pg_start_backup and pg_stop_backup. The
> closest I see comes in this paragraph:
>
> ** Some file system backup tools emit warnings or errors if the files
> they are trying to copy change while the copy proceeds. When taking a
> base backup of an active database, this situation is normal and not an
> error.
>
> Does "this situation" refer to the tools emitting warnings or to the
> fact that postgresql is updating the files? It might be the case, for
> instance, that timestamps are updated but not the contents of the files
> (this is what I had assumed prior to today).

Not sure what you are using these backups for, but have you looked at?:

http://www.postgresql.org/docs/9.4/static/app-pgbasebackup.html

>
> --
> Otheus
> otheus.uibk@gmail.com <mailto:otheus.uibk@gmail.com>
> otheus.shelling@uibk.ac.at <mailto:otheus.shelling@uibk.ac.at>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


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

From
"David G. Johnston"
Date:
On Mon, Jun 8, 2015 at 9:26 AM, otheus uibk <otheus.uibk@gmail.com> wrote:
On Mon, Jun 8, 2015 at 3:13 PM, otheus uibk <otheus.uibk@gmail.com> wrote:
Thank you, all.  The manual for 9.4 is indeed clearer on this point than the 9.1 version.  

Just to nit-pick, I see nowhere in either version of the manual the indication that it is normal for postgresql to continue to update files in its data catalog between pg_start_backup and pg_stop_backup. The closest I see comes in this paragraph:


It may not be explicit because the author assumes that such writing occurring is self-evident.  If it did not then for any reasonably sized database the system would I/O starve or simply run out of memory as the changes continue to pile up in the buffers while the backup is running.

The normal operation of the database operates on the same principle.  In between checkpoints the data files are constantly being written.  A crash means that there is more data in WAL to write out to the data files.  The data files cannot be reverted back to their state at the time of the checkpoint.  Instead, the replay of the WAL simply reapplies every change - even those that did make it out during the period between the checkpoint and the crash.

​As far as the backup routine is concerned a checkpoint occurs only when you call pg_start/end_backup and the system ensures that every WAL file generated during the intervening period is kept around so that the data files being copied, and changed, in the intervening period can be made whole.  The first checkpoint ensure that all data in present in those files and a restoration will replay every WAL file to ensure that the final state of each data file matches the state of the database as of the time the last WAL file present was created (typically the one closed out at the pg_end_backup call).

David J.​

otheus uibk wrote:
> Just to nit-pick, I see nowhere in either version of the manual the indication that it is normal for
> postgresql to continue to update files in its data catalog between pg_start_backup and pg_stop_backup.
> The closest I see comes in this paragraph:
> 
> ** Some file system backup tools emit warnings or errors if the files they are trying to copy change
> while the copy proceeds. When taking a base backup of an active database, this situation is normal and
> not an error.
> 
> Does "this situation" refer to the tools emitting warnings or to the fact that postgresql is updating
> the files? It might be the case, for instance, that timestamps are updated but not the contents of the
> files (this is what I had assumed prior to today).

The manual does not contain all the details how backup and recovery works internally,
you'd have to see the source code for that.

It is normal for the files to change while backup is in progress (in fact, the database
continues working normally, but more information is written to the Write Ahead Log).
It is also normal for backup tools to complain if the file they copy changes while they
read it.

Yours,
Laurenz Albe