PostgreSQL : Replication Settings

PostgreSQL : Replication Settings 2015/07/26

	Configure PostgreSQL Replication settings. This configuration is Master-Slave settings.
[1]	Install PostgreSQL Server, refer to here.
[2]	Configure Master Server. Login with PostgreSQL admin user.

[root@dlp ~]#  su - postgres # create a user for replication -bash-4.2$  createuser --replication -P replica  Enter password for new role: Enter it again: -bash-4.2$  vi /var/lib/pgsql/data/postgresql.conf # line 59: uncomment and change listen_addresses = ' * ' # line 165: uncomment and change wal_level =  hot_standby # line 168: uncomment and change # on ⇒ sync # remote_write ⇒ memory sync # local ⇒ slave is asynchronous # off ⇒ asynchronous synchronous_commit =  local # line 194: uncomment and change (enable archive_mode) archive_mode =  on # line 196: uncomment and change (command to get archives) archive_command = ' cp %p /var/lib/pgsql/archive/%f ' # line 212: uncomment and change (slave servers + 1) max_wal_senders =  2 # line 214: uncomment and change wal_keep_segments =  10 # line 221: uncomment and change (any name you like) synchronous_standby_names = ' slave01 ' -bash-4.2$  vi /var/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 password host replication replica 10.0.0.30/32 password host replication replica 10.0.0.51/32 password -bash-4.2$  pg_ctl restart  waiting for server to shut down.... done server stopped server starting

[3]	Configure Slave Server. Login with PostgreSQL admin user.

[root@node01 ~]#  su - postgres # get backup from Master Server -bash-4.2$  pg_basebackup -h 10.0.0.30 -U replica -D /var/lib/pgsql/data -P --xlog  Password: -bash-4.2$  vi/var/lib/pgsql/data/postgresql.conf # line 230: uncomment and change hot_standby =  on -bash-4.2$  cp/usr/share/pgsql/recovery.conf.sample /var/lib/pgsql/data/recovery.conf  -bash-4.2$  vi /var/lib/pgsql/data/recovery.conf # line 44: uncomment and change (command to get archives) restore_command = ' scp 10.0.0.30:/var/lib/pgsql/archive/%f %p ' # line 108: uncomment and change standby_mode =  on # line 115: 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.2$  pg_ctl restart  waiting for server to shut down.... done server stopped server starting

[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.2$  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)