PostgreSQL cluster recovery fails

Hello there,
I am trying to restore our Percona PostgreSQL cluster to a recovery cluster via pgBackRest.
The time of the recovery is between the first full backup and the first incremental backup.
Inside the yaml file for the recovery cluster I set the time like this:

restoreOpts: --type=time --target=“2022-05-16 12:00:00+02”

Now when I start the job to create the recovery cluster, the job “Job: test-cluster-clon-bootstrap” crashes.
The error message in the job is:


*Fri May 27 07:03:45 UTC 2022 INFO: Applying SSHD… *

*Fri May 27 07:03:45 UTC 2022 INFO: nss_wrapper: ssh configured *

*Fri May 27 07:03:45 UTC 2022 INFO: Checking for SSH Host Keys in /sshd… *

*Fri May 27 07:03:45 UTC 2022 INFO: Checking for authorized_keys in /sshd *

*Fri May 27 07:03:45 UTC 2022 INFO: Checking for sshd_config in /sshd *

*Fri May 27 07:03:45 UTC 2022 INFO: Starting SSHD… *

*WARNING: ‘UsePAM no’ is not supported in Fedora and may cause several problems. *

*Fri May 27 07:03:45 UTC 2022 INFO: Starting background process to monitor Patroni initization and restart the database if needed *

*Fri May 27 07:03:45 UTC 2022 INFO: Initializing cluster bootstrap with command: ‘/usr/bin/patroni /tmp/postgres-ha-bootstrap.yaml’ *

*Fri May 27 07:03:45 UTC 2022 INFO: Patroni will not run as PID 1. Creating signal handler *

*2022-05-27 07:03:45,664 INFO: No PostgreSQL configuration items changed, nothing to reload. *

*2022-05-27 07:03:45,668 INFO: Lock owner: None; I am test-cluster-clon-bootstrap–1-b5g7k *

*2022-05-27 07:03:45,692 INFO: trying to bootstrap a new cluster *

*2022-05-27 07:03:45,692 INFO: Running custom bootstrap script: /opt/crunchy/bin/postgres-ha/pgbackrest/pgbackrest-create-replica.sh primary *

*Fri May 27 07:03:45 UTC 2022 INFO: Empty PGDATA dir found for primary, a non-delta restore will be peformed *

***ERROR: [075]: unable to find backup set with stop time less than ‘2022-05-16 12:00:00+02’ ***

*Fri May 27 07:03:45 UTC 2022 ERROR: pgBackRest primary Creation: pgBackRest restore failed when creating primary *

*2022-05-27 07:03:45,921 INFO: removing initialize key after failed attempt to bootstrap the cluster *

*2022-05-27 07:03:45,929 INFO: renaming data directory to /pgdata/test-cluster-clon_2022-05-27-07-03-45 *

*Traceback (most recent call last): *

*File “/usr/bin/patroni”, line 11, in *

*load_entry_point(‘patroni==2.1.1’, ‘console_scripts’, ‘patroni’)() *

*File “/usr/lib/python3.6/site-packages/patroni/init.py”, line 171, in main *

*return patroni_main() *

*File “/usr/lib/python3.6/site-packages/patroni/init.py”, line 139, in patroni_main *

*abstract_main(Patroni, schema) *

*File “/usr/lib/python3.6/site-packages/patroni/daemon.py”, line 100, in abstract_main *

*controller.run() *

*File “/usr/lib/python3.6/site-packages/patroni/init.py”, line 109, in run *

*super(Patroni, self).run() *

*File “/usr/lib/python3.6/site-packages/patroni/daemon.py”, line 59, in run *

*self._run_cycle() *

*File “/usr/lib/python3.6/site-packages/patroni/init.py”, line 112, in _run_cycle *

*logger.info(self.ha.run_cycle()) *

*File “/usr/lib/python3.6/site-packages/patroni/ha.py”, line 1471, in run_cycle *

*info = self._run_cycle() *

*File “/usr/lib/python3.6/site-packages/patroni/ha.py”, line 1345, in _run_cycle *

*return self.post_bootstrap() *

*File “/usr/lib/python3.6/site-packages/patroni/ha.py”, line 1238, in post_bootstrap *

*self.cancel_initialization() *

*File “/usr/lib/python3.6/site-packages/patroni/ha.py”, line 1231, in cancel_initialization *

*raise PatroniFatalException(‘Failed to bootstrap cluster’) *

patroni.exceptions.PatroniFatalException: ‘Failed to bootstrap cluster’

The repository says the following:

bash-4.4$ pgbackrest info
stanza: db

  • status: ok*

  • cipher: none*

  • db (current)*

  •    wal archive min/max (14): 00000040000000030000007B/000000400000000400000075*
    
  •    full backup: 20220516-112051F*
    
  •        timestamp start/stop: 2022-05-16 11:20:51 / 2022-05-16 11:22:52
    
  •        wal start/stop: 00000040000000030000007E / 00000040000000030000007F*
    
  •        database size: 814.4MB, database backup size: 814.4MB*
    
  •        repo1: backup set size: 314.5MB, backup size: 314.5MB*
    
  •    incr backup: 20220516-112051F_20220517-040002I*
    
  •        timestamp start/stop: 2022-05-17 04:00:02 / 2022-05-17 04:00:07*
    
  •        wal start/stop: 000000400000000300000085 / 000000400000000300000085*
    
  •        database size: 814.4MB, database backup size: 32.3KB*
    
  •        repo1: backup set size: 314.5MB, backup size: 5.1KB*
    
  •        backup reference list: 20220516-112051F*
    

Interesting is:
“ERROR: [075]: unable to find backup set with stop time less than ‘2022-05-16 12:00:00+02’”
and
“full backup: 20220516-112051F”
“timestamp start/stop: 2022-05-16 11:20:51 / 2022-05-16 11:22:52”
“incr backup: 20220516-112051F_20220517-040002I”
“timestamp start/stop: 2022-05-17 04:00:02 / 2022-05-17 04:00:07”

What could be the problem here?
Thanks in advance for the help,
Marvin

1 Like

The problem was that the server time was shifted and does not match the time of pgBackrest. Problem solved.

1 Like