Hi Stephen,
The backups we ran were all exclusive backups.
pg_start_backup('label') returned 000000040000000200000041. Then we made a copy
of data directory .
Then pg_stop_backup() was executed and it returned 000000040000000200000046.
After this 2 databases were dropped and recreated (using dropdb and createdb).
Then we ran pg_switch_wal()
This returned 000000040000000200000049. Then we made a copy of the archive log
directory.
To verify the copied data is restorable, we stopped server. Renamed data and
archive log directories.
Replaced it with data and archive log directories copied earlier. In archive
log directory logs from 41 to 49 is present.
Created recovery.signal and added restore_command to postgresql.conf file. And
tried to start server. this failed with error in previous mail.
[root@gkstandby2 wal]# ls -ltr
total 262172
-rw------- 1 postgres postgres 42 Jun 13 01:08 00000002.history
-rw------- 1 postgres postgres 16777216 Jun 22 22:50 00000002000000020000003B
-rw------- 1 postgres postgres 357 Jun 22 22:51
00000002000000020000003C.00000028.backup
-rw------- 1 postgres postgres 16777216 Jun 22 22:51 00000002000000020000003C
-rw------- 1 postgres postgres 16777216 Jun 22 22:52 00000002000000020000003D
-rw------- 1 postgres postgres 357 Jun 22 22:52
00000002000000020000003E.00000028.backup
-rw------- 1 postgres postgres 16777216 Jun 22 22:52 00000002000000020000003E
-rw------- 1 postgres postgres 16777216 Aug 4 14:47 0000000300000005000000E4
-rw------- 1 postgres postgres 16777216 Aug 4 14:48 00000004000000020000003F
-rw------- 1 postgres postgres 16777216 Aug 4 14:48 000000040000000200000040
-rw------- 1 postgres postgres 16777216 Aug 4 14:49 000000040000000200000041
-rw------- 1 postgres postgres 351 Aug 4 14:49
000000040000000200000040.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 14:49 000000040000000200000042
-rw------- 1 postgres postgres 16777216 Aug 4 14:49 000000040000000200000043
-rw------- 1 postgres postgres 16777216 Aug 4 14:49 000000040000000200000044
-rw------- 1 postgres postgres 16777216 Aug 4 14:50 000000040000000200000045
-rw------- 1 postgres postgres 351 Aug 4 14:50
000000040000000200000043.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 14:50 000000040000000200000046
-rw------- 1 postgres postgres 358 Aug 4 14:50
000000040000000200000045.00005AE0.backup
-rw------- 1 postgres postgres 16777216 Aug 4 14:58 000000040000000200000047
-rw------- 1 postgres postgres 16777216 Aug 4 14:58 000000040000000200000048
-rw------- 1 postgres postgres 16777216 Aug 4 14:58 000000040000000200000049
[root@gkstandby2 wal]#
Regards,
Meera
-----Original Message-----
From: Stephen Frost <[email protected]<mailto:[email protected]>>
Sent: Tuesday, August 8, 2023 7:48 PM
To: Meera Nair <[email protected]<mailto:[email protected]>>
Cc:
[email protected]<mailto:[email protected]>;
Punit Pranesh Koujalgi <[email protected]<mailto:[email protected]>>
Subject: Re: PostgreSQL 14.8 - server fails to start even though all
transaction logs with base backup are restored
Greetings,
* Meera Nair ([email protected]<mailto:[email protected]>) wrote:
> We are following
> https://www.postgresql.org/docs/14/continuous-archiving.html#BACKUP-LO
> WLEVEL-BASE-BACKUP
> Making An Exclusive Low-Level Backup.
Exclusive backups have been removed, so you really don't want to be depending
on it.
> After restoring, we see the server fails to start with below error:
Your archive doesn't seem to have all of the WAL which was generated during the
backup, which means that your backup wasn't complete and the system cannot be
restored from this backup.
> We are restoring all the transaction logs required for base backup to be
> consistent.
Evidently not, per the complaint from PG when it starts up.
> BACKUP_END is present in the 000000040000000200000046 segment. Why did
> recovery fail to find this?
It's possible to have multiple backups going concurrently. Presumably, this
was from a different backup and that's why it wasn't picked up as being the end
for this backup. You seem to have quite a few .backup files in your archive
directory which would seem to support this.
Not sure exactly what you're doing, but unless your goal is to spend a great
deal of effort developing a PG backup solution, you're really better off using
one of the existing solutions (eg: pg_basebackup or pgBackRest). If you are
developing your own backup solution for PG, you definitely want to be using the
new APIs and not using the exclusive backup method. Please review the current
(15) documentation:
https://www.postgresql.org/docs/current/continuous-archiving.html
Thanks,
Stephen
