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

Le 8 juin 2015 2:48 PM, "otheus uibk" <otheus(dot)uibk(at)gmail(dot)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(at)mail(dot)gmail(dot)com#AANLkTikg70iP91TPUwVuF9gb0XtS4fukaKM4qXLTwUfP@mail.gmail.com
>
> and
http://www.postgresql.org/message-id/flat/201004201350(dot)o3KDoKR02633(at)momjian(dot)us#201004201350(dot)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.