RE: PostgreSQL 14.8 - server fails to start even though all transaction logs with base backup are restored

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 <sfrost@snowman.net>
Sent: Tuesday, August 8, 2023 7:48 PM
To: Meera Nair <mnair@commvault.com>
Cc: pgsql-general@lists.postgresql.org; Punit Pranesh Koujalgi <pkoujalgi@commvault.com>
Subject: Re: PostgreSQL 14.8 - server fails to start even though all transaction logs with base backup are restored

Greetings,

* Meera Nair (mnair@commvault.com) 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