Friday , December 6 2024

How To Install PostgreSQL Replication By using archive_command On Centos

I will tell how to install PostgreSQL Replication by using archive_command parameter on centos in this article. But usually I prefer to install postgresql replication with slot method. To learn more about installing replication with slot method, read the below article.

“How To Install PostgreSQL Replication With Slots”,

“How To Failover and Failback PostgreSQL Replication”

You may want to read the below article to install PostgreSQL on Centos.

How To Install PostgreSQL On Centos/RedHat

Install PostgreSQL Replication by using archive_command

Switch to the postgres user with the command below and connect to postgres using the psql command.

On Master:

Create a user named replicauser which will be connected to the master server for replica with the help of the following script.

Edit pg_hba file on the master server to backup from the master server using the replicauser user on the standby server  with the below command.

Add the below line to pg_hba.conf. This will allow the standby server to connect to the master server for replication.

192.168.1.29 -> Standby IP

We also add a line to each server that contains the IP of the other server as follows so that the two servers can connect to each other. This line means that the related IP can connect to me with the specified user. I allow it.

Then run the following command while connected with the Postgres user so that the changes take effect.

You can also do this with the following command while connected with root.

In the next step, we use the following command to set some parameters in the postgresql.conf file.

First, we set the value of listen_adress * as follows, and remove the leading # sign.

Our other settings are as follows;

Then we use the following command to create waltransport.sh file.

Below you can see the contents of the waltransport.sh file.

The IP below is the IP of the standby server.

We make the owner of the waltransport file postgres user and grant necessary rights(700) by using chown and chmod.

To create the standby, go to the standby server first and download the repo and install postgresql without initdb.

Then, using the script below, we back up the master postgresql over the standby server.

The following IP is the IP of the master server.

On Standby Server:

Then go to the “/postgres/pg_standby” path on the standby server and create the recovery.conf file. The contents of the recovery.conf shoul be as follows.

The following IP is the IP of the standby server.

Then start the standby service by showing the pg_standby folder and you will see that the replication starts without errors. Congratulations.

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

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