0
  • There are 2 nodes in the cluster: node1(primary) and node2(replica).
  • I will shutdown node1 and promote node2 as primary.
  • I'll add the node1 back to the cluster (as a replica this time), so I am using the following procedure on failed node1:
systemctl stop postgresql-12
rm -rf /var/lib/pgsql/12/data/*
pg_basebackup -h node2 -D /var/lib/pgsql/12/data/ -U replicator -P -v  -R -X stream -C -S pgstandby1
systemctl start postgresql-12

Is there a better way? I don't like that I'm deleting everything from /var/lib/pgsql/12/data/, and it takes some time until restoring, especially when there's a lot of data. What are your considerations gentleman?

Improve this question

1 Answer 1

Reset to default
1

That's exactly what pg_rewind was written for. It allows you to undo any transactions that have happened on node1 after node2 was promoted. It can be seen as a fast version of pg_basebackup in this case.

There is no guarantee that pg_rewind will succeed. It depends on whether you have all the WAL since the last common checkpoint of node1 and node2. If there is not enough, you have to resort to pg_basebackup.

You can make sure that old WAL is kept around for a while by setting wal_keep_size (wal_keep_segments in older releases) appropriately.

Improve this answer
6
  • Nice. So it seems to work. First I need to stop the postgres service. Then execute "/usr/pgsql-12/bin/pg_rewind --target-pgdata=/var/lib/pgsql/12/data/ --source-server="port=5432 host=node1 password=secretpass". It brings the data, but also it's making the server as master, so I have a split brain situation here. Not really sure how to solve it.
    – catalin
    Nov 26, 2020 at 13:48
  • It does not make the server a master server, it turns it into a backup. You have to configure recovery and set at least primary_conninfo before starting.
    – Laurenz Albe
    Nov 26, 2020 at 14:00
  • @LaurenzAlbe Does wal_keep_size play role as node2 is promoted here and there is no standby yet for node2. Is min_wal_size correct way to preserve?
    – Niraj Nandane
    Dec 13, 2022 at 14:01
  • @NirajNandane I have no idea what you are talking about.
    – Laurenz Albe
    Dec 13, 2022 at 14:17
  • @LaurenzAlbe How can we ensure that on promoted node, pg_wal directory should have minimal WAL segment preserved? For eg. I want to preserve lets say 1GB, then how to do this provided node is promoted and no other node joined to the promoted node. This is for PG 14.
    – Niraj Nandane
    Dec 13, 2022 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.