Comments on How to Set-Up Master-Slave Replication for PostgreSQL 9.6 on Ubuntu 16.04

Thanks for newbie manual,

correction of step 5,

you must recover Slave postgresql.conf from backup before Slave startup

e.g. cp etc/postgresql/9.6/main-bekup/postgresql.conf /etc/postgresql/9.6/main/postgresql.conf

I have to disagree with your statement.You should have only copied /var/lib/postgresql/9.6/main into /var/lib/postgresql/9.6/main-bekup.The config-files had nothing to do with that.

I think there's an error at step 3 : wal_level should be set to replica in version 9.6

Tengo un problema cuando reinicio los servidores, se pierde la la conexion, se borran los datos que se encuetran en la tabla pg_stat_replication.

Why you set max_wal_senders to '2' if you have one slave?

For the 'Replication' settings, in this tutorial we use 2 servers only, master and slave, uncomment the 'wal_sender' line and change value to 2, and for the 'wal_keep_segments' value is 10.

btw `uncomment max_wal_sender` you forgot set max because there is no wal_sender parameter

max_wal_sender defines how much slaves there are to receive streams from master. So if you have:

master & slave

there should be : max_wal_sender = 1

Why you set max_wal_senders to '2' if you have one slave?

For the 'Replication' settings, in this tutorial we use 2 servers only, master and slave, uncomment the 'wal_sender' line and change value to 2, and for the 'wal_keep_segments' value is 10.

btw `uncomment max_wal_sender` you forgot set max because there is no wal_sender parameter

max_wal_sender defines how much slaves there are to receive streams from master. So if you have:

master & slave

there should be : max_wal_sender = 1And also setting on slave those parameters?

synchronous_standby_names = 'pgslave001'

And config says:

# - Master Server -

# These settings are ignored on a standby server.

#synchronous_standby_names = ''

So why are setting this on slave?

There are a number of issues with this proposed approach and therefore I wouldn't recommend using it. * Using 'cp' for an archive_command isn't a good idea. * You should add random extra directories into the PGDATA directory. * Using the --xlog method of pg_basebackup means that the backup might fail if it takes too long to transfer all the data and the old WAL files get rotated out before pg_basebackup gets to them. * pg_basebackup has a much better method (streaming) which could be used to address this, but there's still a risk if data is being written to fast enough on the primary. Setting up a proper backup system which archives the WAL somewhere else (*not* the data directory of the primary, and ideally, you probably want it on another system entirely, such as a backup server) would be a much better approach, and then use that system to build a replica. The pgBackRest Quick Start guide covers setting up a proper backup system with archiving and goes through a restore. Setting up the replication user on the primary and adding the primary_conninfo to the generated recovery.conf after doing the restore should be all that's left to do when using pgBackRest and addresses the issues I outline above. http://pgbackrest.org/user-guide.html#quickstart

This is so far I found online was good enough to setup Postgresql 9.6 master/slave in ubuntu14/16, even it is for ubuntu16 instruction, but not much different from 14 in my view. here are my comments to setup correctly.

1>   Step 1: 

ubuntu16

systemctl enable postgresql 

ubuntu14

service postgresql start – just start the service

sudo initctl status postgresql  - auto restart after reboot

2>   Step 3 - Configure the PostgreSQL MASTER Server

this is new in 9.6

https://www.postgresql.org/docs/9.6/static/release-9-6.html

they merge hot_standby and archive

wal_level = hot_standby to

wal_level = replica

if there is only 1 slave

max_wal_senders = 2 change to

max_wal_senders = 1

ubuntu16

systemctl restart postgresql

ubuntu14

service postgresql restart

3>   Step 4 - Configure SLAVE Server

Ubuntu16

systemctl stop postgresql

ubuntu14

service postgresql stop

this is new in 9.6

https://www.postgresql.org/docs/9.6/static/release-9-6.html

they merge hot_standby and archive

wal_level = hot_standby to

wal_level = replica

if there is only 1 slave

max_wal_senders = 2 change to

max_wal_senders = 1

4>   Step 5 - Copy PostgreSQL Data from the MASTER to the SLAVE

cd 9.6/ change to

cd /var/lib/postgresql/9.6/main

ubuntu16

systemctl start postgresql

ubuntu14

service postgresql start

hello,

what to do in the following scenario?The replication has been set up and the data synchronization was working fine. After a while, due to some reason, the master server IP address got changed,post that data sync isn't happening to the slave server. what are the steps in solving the issue?Thanks in advance

pgslave001 should be replaced my postgres slave nb 1, right ? or is it some keyword postgres uses ?

Excellent, works great. Thank you.

and how to make slave server as main server postgresql ?