PostgreSQL 9.5 : Replication Settings2017/09/25

	Configure PostgreSQL Replication settings. This configuration is Master-Slave settings.
[1]	Install PostgreSQL 9.5 Server on all Nodes, refer to here.
[1]	Configure Master Host.

[root@dlp ~]#

postgresql-setup --initdb --unit rh-postgresql95-postgresql

* Initializing database in '/var/opt/rh/rh-postgresql95/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_rh-postgresql95-postgresql.log

[root@dlp ~]#

vi /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf

# line 59: uncomment and change

listen_addresses = '

# line 173: uncomment and change

wal_level =

hot_standby

# line 180: uncomment and change

# on ⇒ sync

# remote_write ⇒ memory sync

# local ⇒ slave is asynchronous

# off ⇒ asynchronous

synchronous_commit =

local

# line 206: uncomment and change (enable archive_mode)

archive_mode =

# line 208: uncomment and change (command to get archives)

archive_command = '

cp %p /var/opt/rh/rh-postgresql95/lib/pgsql/archive/%f

# line 224: uncomment and change (slave servers + 1)

max_wal_senders =

# line 226: uncomment and change

wal_keep_segments =

# line 238: uncomment and change (any name you like)

synchronous_standby_names = '

slave01

[root@dlp ~]#

vi /var/opt/rh/rh-postgresql95/lib/pgsql/data/pg_hba.conf

# add to the end

# host replication [replication user] [allowed IP addresses] password

host    replication     replica          127.0.0.1/32            md5
host    replication     replica          10.0.0.30/32            md5
host    replication     replica          10.0.0.51/32            md5

[root@dlp ~]#

/etc/rc.d/init.d/rh-postgresql95-postgresql start

[root@dlp ~]#

chkconfig rh-postgresql95-postgresql on

# create a user for replication

[root@dlp ~]#

su - postgres

-bash-4.1$

createuser --replication -P replica

Enter password for new role:
Enter it again:

[2]	If IPTables is running, allow PostgreSQL service port.

[root@dlp ~]#

iptables -I INPUT 5 -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT

[3]	Configure Slave Host.

[root@node01 ~]#

su - postgres

# get backup from Master Server

-bash-4.1$

pg_basebackup -h 10.0.0.30 -U replica -D /var/opt/rh/rh-postgresql95/lib/pgsql/data -P --xlog

Password:

# replica user's password

-bash-4.1$

vi /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf

# line 247: uncomment and change

hot_standby =

-bash-4.1$

cp /opt/rh/rh-postgresql95/root/usr/share/pgsql/recovery.conf.sample /var/opt/rh/rh-postgresql95/lib/pgsql/data/recovery.conf

-bash-4.1$

vi /var/opt/rh/rh-postgresql95/lib/pgsql/data/recovery.conf

# line 44: uncomment and change (command to get archives)

restore_command = '

scp 10.0.0.30:/var/opt/rh/rh-postgresql95/lib/pgsql/archive/%f %p

# line 116: uncomment and change

standby_mode =

# line 123: uncomment and change (connection info to Master Server)

primary_conninfo = '

host=10.0.0.30 port=5432 user=replica password=password application_name=slave01

-bash-4.1$

exit

logout
[root@node01 ~]#

/etc/rc.d/init.d/rh-postgresql95-postgresql start

[root@node01 ~]#

chkconfig rh-postgresql95-postgresql on

[4]	It's OK all if result of the command below is like follows. Make sure the setting works normally to create databases on Master Server.

-bash-4.1$

psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"

 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 slave01          | streaming |             1 | sync
(1 row)

Matched Content