In this article will see howto adding new-slave to postgresql.

This is the current architecture

Steps to add slave is simple, just install postgresql on server new-slave and then add IP address of new-slave on file pg_hba.conf slave1

postgres@slave1 $ vi /data/ssd/pgsql/pg_hba.conf

host    replication     postgres         172.17.0.4/32            md5

Restart service on slave1

postgres@slave1:~$ pg_ctl -m fast -D /data/ssd/pgsql/ restart

On new-slave, run this command to create base_backup with option xlog-stream

postgres@slave2:~$ pg_basebackup -h 172.17.0.3 -D /data/ssd/pgsql -U postgres -v -P --xlog-method=stream

After backup process finish edit file recovery.conf and change host pointing to IP slave1, like this

postgres@slave2:~$ vi /data/ssd/pgsql/recovery.conf 
standby_mode = 'on'
primary_conninfo = 'host=172.17.0.3 port=5432 user=postgres password=password'
recovery_target_timeline='latest'

Save changes and start service postgres on slave2

postgres@slave2:~$ pg_ctl -D /data/ssd/pgsql/ start

Check replication status with run this command on slave1

postgres@slave1:~$ psql -Upostgres -h127.0.0.1
psql (9.5.3)
Type "help" for help.

postgres=# select * from pg_stat_replication ;
 pid | usesysid | usename | application_name | client_addr | client_hostname | client_port |        backend_start         | backend_xmin |   state   | sent_location | write_location | fl
ush_location | replay_location | sync_priority | sync_state 
-----+----------+---------+------------------+-------------+-----------------+-------------+------------------------------+--------------+-----------+---------------+----------------+---
-------------+-----------------+---------------+------------
  70 |    16384 | postgres    | walreceiver      | 172.17.0.4  |                 |       33600 | 2017-07-07 20:46:01.50763+07 |              | streaming | 0/8031C60     | 0/8031C60      | 0/
8031C60      | 0/8031C60       |             0 | async
(1 row)

##replication stat on master##

postgres@master:~$ psql -Upostgres -h127.0.0.1
psql (9.5.3)
Type "help" for help.

postgres=# select * from pg_stat_replication ;
 pid | usesysid | usename | application_name | client_addr | client_hostname | client_port |        backend_start         | backend_xmin |   state   | sent_location | write_location | fl
ush_location | replay_location | sync_priority | sync_state 
-----+----------+---------+------------------+-------------+-----------------+-------------+------------------------------+--------------+-----------+---------------+----------------+---
-------------+-----------------+---------------+------------
 343 |    16384 | postgres    | walreceiver      | 172.17.0.3  |                 |       48598 | 2017-07-07 20:45:47.55065+07 |              | streaming | 0/8031E20     | 0/8031E20      | 0/
8031E20      | 0/8031E20       |             0 | async
(1 row)

and new architecture is

Finish,

Hope this article will help you

Advertisements