Wednesday , October 5 2022

Creating A Hot Standby In PostgreSQL

In today’s post, I will show you Creating A Hot Standby In PostgreSQL.

PostgreSQL Community 13 Installation:

To create Stanby, we need two servers with Postgresql Community 13 installed.

If you have not installed, you can install from the link above.

There are operations that need to be done on both the master and standby server.

We will start with the operations that need to be done on the master server.

Master Server IP:192.168.1.12

Standby Server IP:192.168.1.14

Actions to be taken on the Master Server:

1.We check if the master database is in archive mode.

2. Since the database is not in archive mode, we put the database in archive mode.

We ensure that our archives are sent to the standby database with the archive_command parameter.

With vi, we change the contents of the postgresql.conf file as follows.

3. Since we will create stanby later, we also set the following parameters in the postgresql.conf file.

4. We must restart the postgresql service for the changes we made in the postgresql.conf file to take effect.

5. Replication user is created.

6. To access the database from outside, the listen address should be set as follows and the following changes should be made in pg_hba.conf.

7. Add the following line to the pg_hba.conf file. (We set the IP of the standby server.)

8. After the change is made, we restart the service.

Actions to be taken on Standby Server:

1.We stop the service.

2.We back up the second data directory.

3. We connect to the master server, take a backup and restore it.

4. We create the standby.signal file.

5. We start the service.

When we complete the above steps, we will have the standby database.

If you want to test it, you can query a table on the master server on the standby server, or you can check the standby with the query below.

I hope it will be a useful document for you.

 

Melek Durdu
Author: Melek Durdu

About Melek Durdu

Leave a Reply

Your email address will not be published. Required fields are marked *