When and How do you use pg_rewind() in PostgreSQL?

by

The pg_rewind utility in PostgreSQL is used to synchronize a standby server with a new master server in a scenario where the old master server has been promoted to a new master server and the standby server needs to catch up with the new master server’s state. This utility can be used when the old master server still has the transaction logs (WAL) that were generated after the promotion, and it can be used to “rewind” the state of the standby server to the point in time where the promotion occurred.

To use the pg_rewind utility, you need to perform the following steps:

  1. On the standby server, shut down the PostgreSQL service using the appropriate command for your operating system. For example, on Linux you can use the systemctl command like this:
sudo systemctl stop postgresql

The pg_rewind utility in PostgreSQL is used to synchronize a standby server with a new master server in a scenario where the old master server has been promoted to a new master server and the standby server needs to catch up with the new master server’s state. This utility can be used when the old master server still has the transaction logs (WAL) that were generated after the promotion, and it can be used to “rewind” the state of the standby server to the point in time where the promotion occurred.

To use the pg_rewind utility, you need to perform the following steps:

  1. On the standby server, shut down the PostgreSQL service using the appropriate command for your operating system. For example, on Linux you can use the systemctl command like this:
Copy codesudo systemctl stop postgresql
  1. Run the pg_rewind utility on the standby server, using the following command:
pg_rewind --source-server=host=new_master_server_hostname user=replication_user password=replication_password --target-pgdata=path_to_data_directory

Replace new_master_server_hostname, replication_user, replication_password, and path_to_data_directory with the actual hostname, username, password, and data directory path of the new master server and the user that will be used for replication.

  1. Once the pg_rewind utility has finished running, start the PostgreSQL service on the standby server. This will start the standby server as a standalone server, without the replication configuration.
  2. Connect to the standby server using a PostgreSQL client, such as psql, and run the following query to set up the standby server as a new replica of the old master server:
SELECT pg_create_physical_replication_slot('slot_name');

Replace slot_name with the actual name of the replication slot that you want to create.

  1. On the old master server, edit the postgresql.conf configuration file and set the hot_standby parameter to on. This will enable the old master server to connect to the standby server and start replicating to it.
  2. In the recovery.conf file on the old master server, add the following lines to specify the connection details for the standby server and the replication slot that you created:
standby_mode = 'on'
primary_conninfo = 'host=standby_server_hostname port=5432 user=replication_user password=replication_password'
primary_slot_name = 'slot_name'

Replace standby_server_hostname, replication_user, replication_password, and slot_name with the actual hostname, username, password, and replication slot name of the standby server and the user that will be used for replication.

  1. Restart the old master server for the changes to take effect. The old master server should now be able to connect to the standby server and start replicating to it.

Note that these are the basic steps for using the pg_rewind utility in PostgreSQL. There are many other factors to consider and configure, such as security, performance, and monitoring, to ensure that your replication setup is reliable and efficient. It is recommended

Leave a Comment