Thread: (13.1) pg_basebackups ./. pg_verifybackup
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?
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
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
Thanks,
Vijay
Mumbai, India
btw,
you can also use pgbackrest for backups.
i'll let docs do the talking, pgBackRest - Reliable PostgreSQL Backup & Restore 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.
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.
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