Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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:
1 | su - postgres |
Create a user named replicauser which will be connected to the master server for replica with the help of the following script.
1 | create user replicauser with replication encrypted password 'rp'; |
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.
1 | vim /postgres/pg_data/pg_hba.conf |
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
1 | host replication replicauser 192.168.1.29/32 trust |
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.
1 | host all all 192.168.1.29/32 trust |
Then run the following command while connected with the Postgres user so that the changes take effect.
1 | /usr/pgsql-10/bin/pg_ctl reload -D /postgres/pg_data |
You can also do this with the following command while connected with root.
1 | systemctl reload postgresql-10.service |
In the next step, we use the following command to set some parameters in the postgresql.conf file.
1 | vim /postgres/pg_data/postgresql.conf |
First, we set the value of listen_adress * as follows, and remove the leading # sign.
1 | listen_addresses = '*' |
Our other settings are as follows;
1 2 3 4 5 | archive_mode = on wal_level=replika max_wal_senders=2 wal_keep_segments=256 archive_command = '/postgres/waltransport.sh %p %f' |
Then we use the following command to create waltransport.sh file.
1 | vim /postgres/waltransport.sh |
Below you can see the contents of the waltransport.sh file.
The IP below is the IP of the standby server.
1 2 | #!/bin/bash rsync -ave ssh $1 postgres@192.168.1.29:/postgres/xlogarchive/$2 |
We make the owner of the waltransport file postgres user and grant necessary rights(700) by using chown and chmod.
1 2 | chown postgres:postgres /postgres/waltransport.sh chmod 700 /postgres/waltransport.sh |
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:
1 | /usr/pgsql-10/bin/pg_basebackup -D /postgres/pg_standby -c fast -P -Fp -h 192.168.1.28 -p 5432 -U replicauser |
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.
1 2 3 4 5 6 7 | standby_mode='on' primary_conninfo='host=192.168.1.29 port=5432 user=replicauser password=rp' restore_command='cp /postgres/xlogarchive/ %f %p' archive_cleanup_command='/usr/pgsql-10/bin/pg_archivecleanup /postgre/xlogarchive %r' |
Then start the standby service by showing the pg_standby folder and you will see that the replication starts without errors. Congratulations.
