DbaKeeda: Step By Step - (Async)How To Setup Physical Steaming Replication in Async Mode in PostgreSQL

Step By Step – (Async)Physical Streaming Replication(Async) in PostgreSQL

Here I am using two nodes and I have named them as Master and Replica respectively.

Step 1 - Initialize a database cluster on master node using initdb utility. I have initialized a cluster named primary on master node.

Step 2 – Change the port number in postgresql.conf file and start it. I have used the port 5448.

Step 3 – Start the cluster using pg_ctl cluster utility.

Step 4 - Prepare the Master Server- Now we have to prepare the master server for physical replication. For this we need to change below parameters on postgresql.conf of primary db cluster.

1. Primary db cluster must be in archive log mode so the parameters should be set as below

1. Wal_level=replica

2. Archive_mode=on

3. Archive_command= cp %p <arch_dir>/%f where arch_dir is the archvie location directory which we have set.

4. Max_wal_sender=3 or more

5. Listen_address=’*’ for connection from any user.

Note – After changing the parameters we need to restart the cluster to take into effect.

[postgres@Master ~]$ pg_ctl -D primary restart

After restart my parameter setting looks like below –

Step 5 – Create a user with replication privilege or user super user

Step 6 – Create a Physical replication slot

postgres=# select * from pg_create_physical_replication_slot('standby1');

Step 7 – Add entries into pg_hba.conf: Here you have add entries into 2 sections -

1)# IPv4 local connections – In this section you need to add entries for your standby server(s).

2) # replication privilege – In this section you have to add entries for your replication user(s).

Example – I have added entries which are highlighted in blues lines.

Note – Restart the cluster after changing the pg_hba.conf file to take it into effect.

Step 8 – Take a bp_basebackup – We can take pg_basebackup from primary and also from standby node. If we are taking backup on primary node then we have to transer this backup to standby node using SCP or any other file transfer method.

In my case I am taking pg_basebackup from standby node. My standby node name is Replica.

Note – Before taking backup make sure you have same archive directory present in the standby node also. In my Master node archive directory is arch_dir so I have created the same directory arch_dir in the standby node also.

I have used below command to take pg_basesbackup from standby server-

[postgres@Replica ~]$ pg_basebackup -h 192.168.204.133 -U postgres -p 5448 -D /home/postgres/replica1 -P -Xs –R

Here I have used used to options in the pg_basebackup –

-R – This option is used to create the standby.signal file on the standby database.

-Xs – This option is used to include the WAL files with sepcified method. Here I have used s which means it will be used for streaming.

Once backup is successful we can see all the required directories and configuration files are created under /home/postgres/replica1 directory.

Step 9 – Prepare Standby Server : Open postgresql.conf on the standby server and modify the below parameters –

1. Port – we need to change the port if we are running the primary and standby in the same node. But in my case I have used different machines so I am keeping the same port number as 5448.

2. Hot_standby=on. By default it is off and it will not allow queries to run into read only mode. It will just be used for high availability.

3. Primary_conninfo= 'user=repl password=welcome host=192.168.204.133 port=5448'. Here we have to specify through which user we are going to connect to the primary host. Also we have to mention primary hostname and port number which we will be connecting.

4. primary_slot_name = 'standby1'

5. recovery_target_timeline = 'latest'

Save the postgresql.conf and start the standby cluster.

[postgres@Replica ~]$ pg_ctl -D replica1 start