Unable to start patroni after pgbackrest restore

[kumarashish071092]

I have restored my entire cluster from a other pgbackrest host and after making the necessary changes , I was able to start the database using the below command :

/usr/lib/postgresql/14/bin/pg_ctl -D /mnt/Postgres/postgresql/14/trin-prod/data start

But this is not getting run by patroni service and the playbook execution is not getting completed .

how can I link the patroni and new postgres cluster and does it have to do anything with etcd as well?

[vitabaks]

@kumarashish071092 Please attach the ansible and patroni logs.

[kumarashish071092]

The issue was resolved after inserting restore_command and bootstrap method as pgbackrest in /etc/patroni/patroni.yml file :

bootstrap method: pgbackrest pgbackrest: command: /var/lib/postgresql/bootstrap_pgbackrest.sh keep_existing_recovery_conf: False no_paramas: False recovery_conf: recovery_target_action: promote recovery_target_timeline: latest recovery_command: pgbackrest --stanza=trino-prod archive-get %f %p    

vi bootstrap_pgbackrest.sh

pgbackrest --type=immediate --repo1-host=10.0.XX.XX --repo1-host-user=postgres --target-action=promote --stanza=trino-prod --set=20240507-063127F --log-level-console=detail --archive-mode=off restore

restarted patroni and the issue got resolved

---

From: Vitaliy Kukharik @.> Sent: Thursday, May 9, 2024 12:51 AM To: vitabaks/postgresql_cluster @.> Cc: Kumar Ashish @.>; Mention @.> Subject: Re: [vitabaks/postgresql_cluster] Unable to start patroni after pgbackrest restore (Issue #656)

[Email from a non-Nagarro source: please exercise caution with links and attachments]

@kumarashish071092https://github.com/kumarashish071092 Please attach the ansible and patroni logs.

— Reply to this email directly, view it on GitHubhttps://github.com/vitabaks/postgresql_cluster/issues/656#issuecomment-2101269222, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVXUU3QJXMQJQGNO63UH4MLZBJ3KDAVCNFSM6AAAAABHM2HFDWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMBRGI3DSMRSGI. You are receiving this because you were mentioned.

[vitabaks]

Ok, good!

I will prepare a website with detailed documentation soon.

[vitabaks]

You may have an old version of automation because for pgbackrest we have not used the bootstrap method to provide delta restore functionality for a long time.

See details here https://github.com/vitabaks/postgresql_cluster#restore-and-cloning

[kumarashish071092]

Yes it was not there . In the original automation file , it is initdb. I have modified it to make it sync with Patroni .

Is there any other way to restore pgbackrest with patroni

Get Outlook for iOShttps://aka.ms/o0ukef

---

From: Vitaliy Kukharik @.> Sent: Thursday, May 9, 2024 7:35:02 PM To: vitabaks/postgresql_cluster @.> Cc: Kumar Ashish @.>; Mention @.> Subject: Re: [vitabaks/postgresql_cluster] Unable to start patroni after pgbackrest restore (Issue #656)

[Email from a non-Nagarro source: please exercise caution with links and attachments]

You may have an old version of automation because for pgbackrest we have not used the bootstrap method to provide delta restore functionality for a long time.

See details here https://github.com/vitabaks/postgresql_cluster#restore-and-cloning

— Reply to this email directly, view it on GitHubhttps://github.com/vitabaks/postgresql_cluster/issues/656#issuecomment-2102728494, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVXUU3XDGUCHVDNK54ZRU53ZBN7A5AVCNFSM6AAAAABHM2HFDWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMBSG4ZDQNBZGQ. You are receiving this because you were mentioned.Message ID: @.***>

[vitabaks]

Is there any other way to restore pgbackrest with patroni

And why are you not satisfied with the current method? It is fully automated.

Point-In-Time-Recovery

https://github.com/vitabaks/postgresql_cluster#restore-and-cloning

You can run automatic restore of your existing patroni cluster for PITR, specify the required parameters in the main.yml variable file and run the playbook with the tag:

ansible-playbook deploy_pgcluster.yml --tags point_in_time_recovery

What does automation do? (recovery steps with pgBackRest):

1. Stop patroni service on the Replica servers (if running);
2. Stop patroni service on the Master server;
3. Remove patroni cluster "xxxxxxx" from DCS (if exist);
4. Run "/usr/bin/pgbackrest --stanza=xxxxxxx --delta restore" on Master;
5. Run "/usr/bin/pgbackrest --stanza=xxxxxxx --delta restore" on Replica (if patroni_create_replica_methods: "pgbackrest");
6. Waiting for restore from backup (timeout 24 hours);
7. Start PostgreSQL for Recovery (master and replicas);
8. Waiting for PostgreSQL Recovery to complete (WAL apply);
9. Stop PostgreSQL instance (if running);
10. Disable PostgreSQL archive_command (if enabled);
11. Start patroni service on the Master server;
12. Check PostgreSQL is started and accepting connections on Master;
13. Make sure the postgresql users (superuser and replication) are present, and password does not differ from the specified in vars/main.yml;
14. Update postgresql authentication parameter in patroni.yml (if superuser or replication users is changed);
15. Reload patroni service (if patroni.yml is updated);
16. Start patroni service on Replica servers;
17. Check that the patroni is healthy on the replica server (timeout 10 hours);
18. Check postgresql cluster health (finish).

[vitabaks]

@SDV109 Please confirm that you are already using the automated Point-In-Time-Recovery method. Do you have any difficulties with this?

[SDV109]

@vitabaks, Yes, I confirm that with the latest repository releases, there are no problems with the recovery method at a certain point in time.

@kumarashish071092, Hi, look at my comment on the PITR problem earlier, maybe it will help you: https://github.com/vitabaks/postgresql_cluster/issues/588#issuecomment-2059260095

[vitabaks]

Is the problem still relevant?