(13.1) pg_basebackups ./. pg_verifybackup

On 8/9/21 1:15 PM, Matthias Apitz wrote:

Hello,

We do backups with something like:

$ pg_basebackup -U ${DBSUSER} -Ft -z -D ${BACKUPDIR}-${DATE}-${NUM}

and they seems to work fine:

$ ls -l backup-20210809-1/
total 7656108
-rw------- 1 postgres postgres 4191873 Aug 9 14:19 backup_manifest
-rw------- 1 postgres postgres 7835635150 Aug 9 14:19 base.tar.gz
-rw------- 1 postgres postgres 17671 Aug 9 14:19 pg_wal.tar.gz

But, if I verify the backup with

$ pg_verifybackup /data/postgresql13/backup-20210809-1

it gives thousands of error messages like

pg_verifybackup: error: "base/1032792/1034229" is present in the manifest but not on disk
pg_verifybackup: error: "base/1076178/1101524" is present in the manifest but not on disk

and at the end:

pg_waldump: error: could not open directory "/data/postgresql13/backup-20210809-1/pg_wal": No such file or directory
pg_verifybackup: error: WAL parsing failed for timeline 1

The base files are there:

$ find /data/postgresql13 -name 1101524
/data/postgresql13/data/base/1076178/1101524

$ find /data/postgresql13 -name pg_wal
/data/postgresql13/data/pg_wal

What we do wrong here with pg_verifybackup?

Not read the docs?:

https://www.postgresql.org/docs/13/app-pgverifybackup.html

"pg_verifybackup is used to check the integrity of a database cluster
backup taken using pg_basebackup against a backup_manifest generated by
the server at the time of the backup. The backup must be stored in the
"plain" format; a "tar" format backup can be checked after extracting it."

Thanks

matthias

--
Adrian Klaver
adrian.klaver@aklaver.com

El día lunes, agosto 09, 2021 a las 01:32:58p. m. -0700, Adrian Klaver escribió:

What we do wrong here with pg_verifybackup?

Not read the docs?:

https://www.postgresql.org/docs/13/app-pgverifybackup.html

"pg_verifybackup is used to check the integrity of a database cluster backup
taken using pg_basebackup against a backup_manifest generated by the server
at the time of the backup. The backup must be stored in the "plain" format;
a "tar" format backup can be checked after extracting it."

I've read exactly this page, but missed the sentence about "tar" format
because I jumped to fast to the options sections. Sorry, my fault.

Thanks

matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
27.7.1900: Kaiser Wilhelm II Deutsches Ostasiatisches Expeditionskorps, target China
02.8.2021: Deutsche Kriegsministerin Kramp-Karrenbauer Fregatte "Bayern", target China

El día lunes, agosto 09, 2021 a las 01:32:58p. m. -0700, Adrian Klaver escribió:

https://www.postgresql.org/docs/13/app-pgverifybackup.html

"pg_verifybackup is used to check the integrity of a database cluster backup
taken using pg_basebackup against a backup_manifest generated by the server
at the time of the backup. The backup must be stored in the "plain" format;
a "tar" format backup can be checked after extracting it."

I've studied now the fine docs again and have some additional questions. The
backup was done fine to the directory /data/postgresql133/backup-20210810-1
which contains now:

$ ls -l
total 717252
-rw------- 1 postgres postgres 430331 Aug 10 08:54 backup_manifest
-rw------- 1 postgres postgres 734006592 Aug 10 08:54 base.tar.gz
-rw------- 1 postgres postgres 17667 Aug 10 08:54 pg_wal.tar.gz

I un-tar'ed the two archives with

$ tar xzf base.tar.gz
$ tar xzf pg_wal.tar.gz

And then I run pg_verifybackup it gives the following errors:

$ pg_verifybackup /data/postgresql133/backup-20210810-1
pg_verifybackup: error: "0000000100000000000000D9" is present on disk but not in the manifest
pg_verifybackup: error: "pg_wal.tar.gz" is present on disk but not in the manifest
pg_verifybackup: error: "base.tar.gz" is present on disk but not in the manifest
pg_verifybackup: error: "archive_status/0000000100000000000000D9.done" is present on disk but not in the manifest
pg_waldump: fatal: could not find any WAL file
pg_verifybackup: error: WAL parsing failed for timeline 1

The two messages about the present of pg_wal.tar.gz and base.tar.gz are
clear. One could move the archives out of the sight of pg_verifybackup.
But the others are unclear to me. The files 0000000100000000000000D9 and
archive_status/0000000100000000000000D9.done are part of pg_wal.tar.gz
written by pg_basebackup:

tar tvf pg_wal.tar.gz
-rw------- postgres/postgres 16777216 2021-08-10 08:52 0000000100000000000000D9
-rw------- postgres/postgres 0 2021-08-10 08:54 archive_status/0000000100000000000000D9.done

About WAL the file backup_manifest contains only:

"WAL-Ranges": [
{ "Timeline": 1, "Start-LSN": "0/D9000028", "End-LSN": "0/D9000138" }
],

What is the problem here or what I've missed?
Thanks

matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
27.7.1900: Kaiser Wilhelm II Deutsches Ostasiatisches Expeditionskorps, target China
02.8.2021: Deutsche Kriegsministerin Kramp-Karrenbauer Fregatte "Bayern", target China

El día martes, agosto 10, 2021 a las 09:23:34a. m. +0200, Matthias Apitz escribió:

I've studied now the fine docs again and have some additional questions. The
backup was done fine to the directory /data/postgresql133/backup-20210810-1
which contains now:

$ ls -l
total 717252
-rw------- 1 postgres postgres 430331 Aug 10 08:54 backup_manifest
-rw------- 1 postgres postgres 734006592 Aug 10 08:54 base.tar.gz
-rw------- 1 postgres postgres 17667 Aug 10 08:54 pg_wal.tar.gz

I un-tar'ed the two archives with

$ tar xzf base.tar.gz
$ tar xzf pg_wal.tar.gz

And then I run pg_verifybackup it gives the following errors:

$ pg_verifybackup /data/postgresql133/backup-20210810-1
pg_verifybackup: error: "0000000100000000000000D9" is present on disk but not in the manifest
pg_verifybackup: error: "pg_wal.tar.gz" is present on disk but not in the manifest
pg_verifybackup: error: "base.tar.gz" is present on disk but not in the manifest
pg_verifybackup: error: "archive_status/0000000100000000000000D9.done" is present on disk but not in the manifest
pg_waldump: fatal: could not find any WAL file
pg_verifybackup: error: WAL parsing failed for timeline 1

The two messages about the present of pg_wal.tar.gz and base.tar.gz are
clear. One could move the archives out of the sight of pg_verifybackup.
But the others are unclear to me. The files 0000000100000000000000D9 and
archive_status/0000000100000000000000D9.done are part of pg_wal.tar.gz
written by pg_basebackup:

tar tvf pg_wal.tar.gz
-rw------- postgres/postgres 16777216 2021-08-10 08:52 0000000100000000000000D9
-rw------- postgres/postgres 0 2021-08-10 08:54 archive_status/0000000100000000000000D9.done

About WAL the file backup_manifest contains only:

"WAL-Ranges": [
{ "Timeline": 1, "Start-LSN": "0/D9000028", "End-LSN": "0/D9000138" }
],

What is the problem here or what I've missed?
Thanks

I think, I sorted it out by doing this:

I moved away the tar-archives:

$ cd /data/postgresql133/backup-20210810-1
$ mkdir ../saved
$ mv *.tar.gz ../saved

I unpacked 'base.tar.gz' the usual way:

$ tar xzf ../saved/base.tar.gz

but 'pg_wal.tar.gz' in the sub dir 'pg_wal':

$ cd pg_wal
$ tar xzf ../../saved/pg_wal.tar.gz
$ cd ..
$ find pg_wal
pg_wal
pg_wal/0000000100000000000000D9
pg_wal/archive_status
pg_wal/archive_status/0000000100000000000000D9.done

now pg_verifybackup seems to be happy:

$ pg_verifybackup /data/postgresql133/backup-20210810-1
backup successfully verified

Is this the supposed way it should work? Esp. this unpacking of
'pg_wal.tar.gz' in a directory level below?

Ofc, one could specify the location of the WAL file with '-w path', but
the doc only suggests this when "Try to parse WAL files stored in the
specified directory, rather than in pg_wal. This may be useful if the
backup is stored in a separate location from the WAL archive."

But having to unpack 'pg_wal.tar.gz' below pg_wal is not obvious.

matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
27.7.1900: Kaiser Wilhelm II Deutsches Ostasiatisches Expeditionskorps, target China
02.8.2021: Deutsche Kriegsministerin Kramp-Karrenbauer Fregatte "Bayern", target China

El día martes, agosto 10, 2021 a las 11:38:57a. m. +0200, Matthias Apitz escribió:

I think, I sorted it out by doing this:

I moved away the tar-archives:

$ cd /data/postgresql133/backup-20210810-1
$ mkdir ../saved
$ mv *.tar.gz ../saved

I unpacked 'base.tar.gz' the usual way:

$ tar xzf ../saved/base.tar.gz

but 'pg_wal.tar.gz' in the sub dir 'pg_wal':

$ cd pg_wal
$ tar xzf ../../saved/pg_wal.tar.gz
$ cd ..
$ find pg_wal
pg_wal
pg_wal/0000000100000000000000D9
pg_wal/archive_status
pg_wal/archive_status/0000000100000000000000D9.done

now pg_verifybackup seems to be happy:

$ pg_verifybackup /data/postgresql133/backup-20210810-1
backup successfully verified

Is this the supposed way it should work? Esp. this unpacking of
'pg_wal.tar.gz' in a directory level below?

Ofc, one could specify the location of the WAL file with '-w path', but
the doc only suggests this when "Try to parse WAL files stored in the
specified directory, rather than in pg_wal. This may be useful if the
backup is stored in a separate location from the WAL archive."

But having to unpack 'pg_wal.tar.gz' below pg_wal is not obvious.

Could some kind sol acknowledge me that this is the correct procedure to
use pg_verifybackup? Thanks in advance

matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
August 13: Thank you, GDR, for 28 years of peace in Europe, for 28 years
without impoverishment by Hartz IV and unemployment, for 28 years without
posters of Neonazis "GAS geben" ("Give gas") in German capital, etc. etc. etc.

Could some kind sol acknowledge me that this is the correct procedure to
use pg_verifybackup? Thanks in advance

postgres@db:~/playground/demo$ initdb -D db 2>/dev/null 1>&2
postgres@db:~/playground/demo$ pg_ctl -D db -l logfile start 2>/dev/null
1>&2
postgres@db:~/playground/demo$ psql -c 'create table t(id int primary key);
insert into t select x from generate_series(1, 10000) x;'
INSERT 0 10000
postgres@db:~/playground/demo$ pg_basebackup -U postgres -Ft -z -D
basebackup
postgres@db:~/playground/demo$ echo $?
0
postgres@db:~/playground/demo$ cd basebackup
postgres@db:~/playground/demo/basebackup$ ls
backup_manifest base.tar.gz pg_wal.tar.gz
postgres@db:~/playground/demo/basebackup$ tar xzf base.tar.gz
postgres@db:~/playground/demo/basebackup$ cd pg_wal/
postgres@db:~/playground/demo/basebackup/pg_wal$ mv ../pg_wal.tar.gz . #
pg_wal.tar.gz file has to uncompressed in pg_wal dir
postgres@db:~/playground/demo/basebackup/pg_wal$ tar xzf pg_wal.tar.gz
postgres@db:~/playground/demo/basebackup/pg_wal$ cd ../..
postgres@db:~/playground/demo$ find basebackup -name '*tar.gz'
basebackup/base.tar.gz
basebackup/pg_wal/pg_wal.tar.gz
postgres@db:~/playground/demo$ find basebackup -name '*tar.gz' -delete
postgres@db:~/playground/demo$ find basebackup -name '*tar.gz'
postgres@db:~/playground/demo$ pg_verifybackup basebackup
backup successfully verified

Backup and Restore a PostgreSQL Cluster With Multiple Tablespaces Using
pg_basebackup - Percona Database Performance Blog
<https://www.percona.com/blog/2018/12/21/backup-restore-postgresql-cluster-multiple-tablespaces-using-pg_basebackup/&gt;

--
Thanks,
Vijay
Mumbai, India

btw,
you can also use pgbackrest for backups.
i'll let docs do the talking, pgBackRest - Reliable PostgreSQL Backup &
Restore <https://pgbackrest.org/&gt; but it can help you manage incremental
and differential and full backups
along with parallel support. (so fast and less bandwidth)
also a quick online tutorial to help you get started.
CrunchyData Interactive Learning Portal
<https://learn.crunchydata.com/pg-administration/courses/basic-postgresql-for-dbas-12/pgbackrest/&gt;

not wanting to divert from your original query, but just in case.

El día jueves, agosto 12, 2021 a las 12:47:32a. m. +0530, Vijaykumar Jain escribió:

Could some kind sol acknowledge me that this is the correct procedure to
use pg_verifybackup? Thanks in advance

...

postgres@db:~/playground/demo/basebackup$ tar xzf base.tar.gz
postgres@db:~/playground/demo/basebackup$ cd pg_wal/
postgres@db:~/playground/demo/basebackup/pg_wal$ mv ../pg_wal.tar.gz .
# pg_wal.tar.gz file has to uncompressed in pg_wal dir
postgres@db:~/playground/demo/basebackup/pg_wal$ tar xzf pg_wal.tar.gz
...

This is exactly the point of my question (and I figured it out too):
Where is this explained that «pg_wal.tar.gz file has to uncompressed in
pg_wal dir»?

Or, wouldn't it even be better that the files in
pg_wal.tar.gz would have the dir pg_wal in front?

matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
August 13: Thank you, GDR, for 28 years of peace in Europe, for 28 years
without impoverishment by Hartz IV and unemployment, for 28 years without
posters of Neonazis "GAS geben" ("Give gas") in German capital, etc. etc. etc.

On Thu, 12 Aug 2021 at 01:48, Matthias Apitz <guru@unixarea.de> wrote:

This is exactly the point of my question (and I figured it out too):
Where is this explained that «pg_wal.tar.gz file has to uncompressed in
pg_wal dir»?

indeed, I am not able to find or search relative reference in docs (i never
used pg_basebackup for backups but mostly replica setup),
so i never ran this procedure.
although, i see a reference in test lib file to help atleast confirm the
source repo has a reference to it.
postgres/PostgresNode.pm at 856de3b39cf6041338b286a99257c324ce647f4e ·
postgres/postgres (github.com)
<https://github.com/postgres/postgres/blob/856de3b39cf6041338b286a99257c324ce647f4e/src/test/perl/PostgresNode.pm#L690&gt;

i can link the c source file, but that would not be your answer.
somehow, this procedure is all over the internet, via blogs etc, but i do
not know where it is in the docs.

Or, wouldn't it even be better that the files in
pg_wal.tar.gz would have the dir pg_wal in front?

I am not sure, I am the best person to answer this question.

--
Thanks,
Vijay
Mumbai, India

On 8/11/21 1:18 PM, Matthias Apitz wrote:

El día jueves, agosto 12, 2021 a las 12:47:32a. m. +0530, Vijaykumar Jain escribió:

Could some kind sol acknowledge me that this is the correct procedure to
use pg_verifybackup? Thanks in advance

...

postgres@db:~/playground/demo/basebackup$ tar xzf base.tar.gz
postgres@db:~/playground/demo/basebackup$ cd pg_wal/
postgres@db:~/playground/demo/basebackup/pg_wal$ mv ../pg_wal.tar.gz .
# pg_wal.tar.gz file has to uncompressed in pg_wal dir
postgres@db:~/playground/demo/basebackup/pg_wal$ tar xzf pg_wal.tar.gz
...

This is exactly the point of my question (and I figured it out too):
Where is this explained that «pg_wal.tar.gz file has to uncompressed in
pg_wal dir»?

Nowhere, because it doesn't per:

https://www.postgresql.org/docs/13/app-pgverifybackup.html

-w path
--wal-directory=path

Try to parse WAL files stored in the specified directory, rather
than in pg_wal. This may be useful if the backup is stored in a separate
location from the WAL archive.

Along with other options for shaping the verification:

-i path
--ignore=path

Ignore the specified file or directory, which should be expressed
as a relative path name, when comparing the list of data files actually
present in the backup to those listed in the backup_manifest file. If a
directory is specified, this option affects the entire subtree rooted at
that location. Complaints about extra files, missing files, file size
differences, or checksum mismatches will be suppressed if the relative
path name matches the specified path name. This option can be specified
multiple times.
-m path
--manifest-path=path

Use the manifest file at the specified path, rather than one
located in the root of the backup directory.
-n
--no-parse-wal

Don't attempt to parse write-ahead log data that will be needed to
recover from this backup.

Or, wouldn't it even be better that the files in
pg_wal.tar.gz would have the dir pg_wal in front?

matthias

--
Adrian Klaver
adrian.klaver@aklaver.com