autobase: PITR fails when 'pgbackrest' in patroni_create_replica_methods
When recovering from a backup using PITR with pgbackrest, the following command is run in the “Start PostgreSQL for Recovery” task:
"/usr/bin/pg_ctlcluster {{ postgresql_version }} {{ postgresql_cluster_name }} start -o '-c hot_standby=off'"
When run on the master, this command returns 0 because postgres stays running after recovery. On the replicas, this command returns a non-zero exit code:
fatal: [10.60.1.156]: FAILED! => {"changed": true, "cmd": ["/usr/bin/pg_ctlcluster", "16", "main", "start", "-o", "-c hot_standby=off"], "delta": "0:00:01.486977", "end": "2024-02-28 17:44:11.565582", "msg": "non-zero return code", "rc": 1, "start": "2024-02-28 17:44:10.078605", "stderr": "The PostgreSQL server failed to start. Please check the log output:\n2024-02-28 17:44:10 UTC [95842-1] LOG: redirecting log output to logging collector process\n2024-02-28 17:44:10 UTC [95842-2] HINT: Future log output will appear in directory \"/var/log/postgresql\".", "stderr_lines": ["The PostgreSQL server failed to start. Please check the log output:", "2024-02-28 17:44:10 UTC [95842-1] LOG: redirecting log output to logging collector process", "2024-02-28 17:44:10 UTC [95842-2] HINT: Future log output will appear in directory \"/var/log/postgresql\"."], "stdout": "", "stdout_lines": []}
The postgres log from the replica:
2024-02-28 17:44:11.891 P00 INFO: unable to find 00000001000000000000000C in the archive asynchronously
2024-02-28 17:44:11.891 P00 INFO: archive-get command end: completed successfully (308ms)
2024-02-28 17:44:11 UTC [95474-9] LOG: completed backup recovery with redo LSN 0/8000028 and end LSN 0/8000100
2024-02-28 17:44:11 UTC [95474-10] LOG: consistent recovery state reached at 0/8000100
2024-02-28 17:44:11 UTC [95474-11] LOG: recovery stopping before commit of transaction 752, time 2024-02-21 16:14:23.996154+00
2024-02-28 17:44:11 UTC [95470-7] LOG: shutdown at recovery target
2024-02-28 17:44:11 UTC [95472-1] LOG: shutting down
2024-02-28 17:44:11 UTC [95470-8] LOG: database system is shut down
It does work with a full restore, just not a PITR. If I remove ‘pgbackrest’ from patroni_create_replica_methods and leave only ‘basebackup’ then PITR works as well. Am I doing something wrong?
About this issue
- Original URL
- State: closed
- Created 3 years ago
- Comments: 26 (12 by maintainers)
Hello, sorry I don’t have a lot of time to do testing right now. I was able to restore full backups without issues so I’m not using PITR for the time being. Please close the ticket if you cannot reproduce and I will reopen it when I can do some more thorough testing.
@FilipBB, Hi! Sorry for the delay, there was no way to try to reproduce the problem.
Today I tried the following scenario:
I started the recovery scenarios:
@FilipBB, Could you try to repeat this scenario from scratch with the current version of postgresql_cluster?
Just for information (this is not the cause of the problem), it is not necessary to do this, just launch a playbook with a tag to restore the current cluster:
This is described here https://github.com/vitabaks/postgresql_cluster?tab=readme-ov-file#restore-and-cloning (“Point-In-Time-Recovery” section)