Steps and Concepts of Streaming Replication in PostgreSQL

The process of replication in PostgreSQL using continuous shipping of WAL xlogs to slave servers and applying them in order to keep them current, is called streaming replication in PostgreSQL. This feature has been there in Posgre since  version 9.0

The steps to implement streaming replication is as follows:

First we need to setup the primary server

Step1: check the hostname and port as below:

netstat -nltp

And now in postgresql.conf, update the listen_address parameter and the port number to be used by PostgreSQL, by default the port number is 5432, so that the database is accessible from anywhere:

port= 5432

Step2: Now add few rules in pg_hba.conf to allow password authentication from slaver server/s as below:

#ipv4 local connections
host          all              all           md5
#ipv6 local connections
host          all              all          ::/o                    md5

Step 3: restart the postgreSQL

pgctl restart

Step4: Create replication role

postgres=# CREATE ROLE replicator WITH REPLICATION PASSWORD 'yourpassword' LOGIN;

Step5: Stop the PostgreSQL

Step6: Edit the postgresql.conf file as below:

[yourserver..postgres]$ vi postgresql.conf
max_wal_senders = 7
wal_keep_segments = 45
archive_mode = on
archive_command= 'cp %p /var/pgsql/data/archives/%f'

Make sure that you have the directory created that you have defined above for storing the archives with ownership as postgres:postgres

in case not you can create one and define proper ownership and permissions

Step7: Update pg_hba.conf to allow slave servers to authenticate with replicator user from slave host/s.

$ vi pg_hba.conf
host               replicator              replicator                      md5
Step 8: Restart PostgreSQL server
[....]$ service postgresql restart

Now on Standby server

Step1: Install PosgreSQL on the slave server/s with same directory structures as that of Primary (Master) server (preferably)

Step2: Stop the postgresql

[....]$ service postgresql stop

Step3: Delete everything in data directory

[....]$ sudo rm -rf /var/pgsql/data/*

Step4: Take backup to from the primary server to populate the standby data directory

[...postgres]$ pg_basebackup -h <ip of primary server> -D /var/pgsql/data -P -U replicator --xlog-method=stream

Step5: update the postgresql.conf as below:

[...]$ vi postgresql.conf
hot_standby = on

Rest all the settings of postgresql.conf should be as that of primary server in order to server as primary server in case of failover.

Step6: Create recovery.conf file for applying the xlogs:

standby_mode = on
primary_conninfo = 'host=<primary server ip> port=5432 user=replicator password=yourpassword'
trigger_file = '/var/lib/postresql/trigger'
restor_command = 'cp /var/pgsql/archive/%f "%p"'

To check the master server status

postgres=# SELECT pg_current_xlog_location();

To check on slave

postgre=# SELECT pg_last_xlog_location()

Note: trigger file is used to trigger automatic failover


Who is online?

There are currently 0 users online.