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'"

Start PostgreSQL for Recovery

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)

Most upvoted comments

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:

  1. Deployed the cluster from scratch
  2. Set up backups
  3. Set the following vars/main settings and run ansible-playbook config_pgcluster.yml
patroni_cluster_bootstrap_method: "pgbackrest"  # or "wal-g", "pgbackrest", "pg_probackup"

patroni_create_replica_methods:
  - pgbackrest
  1. I made a Full backup, added test tables within an hour, filled them with data
  2. I waited about 30 minutes until the WAL files were sent to the backup host

I started the recovery scenarios:

  1. Without deleting the cluster, I deleted the previously created databases, but I did not delete the cluster. 1.1) Deleted the test bases 1.2) I launched the ansible-playbook deploy_pgcluster.yml - The recovery went without problems, the databases were restored at the time I needed in vars/main pgbackrest_patroni_cluster_restore_command
pgbackrest_patroni_cluster_restore_command:
#  '/usr/bin/pgbackrest --stanza={{ pgbackrest_stanza }} --delta restore'  # restore from latest backup
  '/usr/bin/pgbackrest --stanza={{ pgbackrest_stanza }} --type=time "--target=2024-04-16 12:12:00+03" --delta restore'  # Point-in-Time Recovery (example)
  1. Deleted the cluster via the remove_cluster.yml playbook 2.1) Launched remove_cluster.yml 2.2) Fixed postgresql_exists=false in the inventory 2.3) Launched deploy_pgcluster.yml 2.4) Got the working cluster restored for the right time

@FilipBB, Could you try to repeat this scenario from scratch with the current version of postgresql_cluster?

Run remove_cluster.yml with ‘remove_postgres=true’ and ‘remove_etcd=true’

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:

ansible-playbook deploy_pgcluster.yml --tags point_in_time_recovery

This is described here https://github.com/vitabaks/postgresql_cluster?tab=readme-ov-file#restore-and-cloning (“Point-In-Time-Recovery” section)