[ADMIN] Odp: Re: [ADMIN] [ADMIN] Replication mode Master-Slave - maintenance question. - Mailing list pgsql-admin

From czezz
Subject [ADMIN] Odp: Re: [ADMIN] [ADMIN] Replication mode Master-Slave - maintenance question.
Date
Msg-id 124c7aee04474da9b64dc7760e286956@gwp
Whole thread Raw
In response to Re: [ADMIN] [ADMIN] Replication mode Master-Slave - maintenance question.  (John Scalia <jayknowsunix@gmail.com>)
Responses Re: [ADMIN] Replication mode Master-Slave - maintenance question.
List pgsql-admin
Hi John,
thanks for this reply.

Honestly I do not know is it synchronous or asynchronous. I didnt set any parameter like this.
Is there a quick way to verify that?

Test case scenario I tested so far was: stop SLAVE for some time (2-3 hours) and do a lot of INSERTS on MASTER.
Afterwards,  when SLAVE is up again it synced delta from MASTER's WAL files.
Do it worked as I expected.

Why are you saying that "extended period of time" will be an issue? Is there any time limit for SLAVE to be down or you are just saying about worse case scenario? 
I assume that I will never lose any WAL files on MASTER.

Best regards,
czezz

Dnia 23 marca 2017 19:06 John Scalia <jayknowsunix@gmail.com> napisał(a):

You did not indicate whether you're Master-Slave setup is synchronous or asynchronous, but yes, what you described should work. The slave should have a recovery.conf file and the presence of that will cause that slave to wind through all WAL files present before it resumes further processing. At least this is what happens in synchronous streaming mode. Your problem might be if the slave is off for an extended period of time or if one of the WAL files disappears. At that point the slave would not have all the transactions for consistency.
Again,to your question near the end. It depends on how you're set up. Generally though slaves do make the request to their master.

On Thu, Mar 23, 2017 at 1:47 PM, czezz <czezz@o2.pl> wrote:
Hi,
I have set my Postgres into replication mode Master-Slave and it works good.
Though, I have some question about maintaining it.

Let me start from the beginning. How I set it up (briefly):
1. Stand Alone postgres (future MASTER) has been set to archive data to: /datastore/WAL (archive_command = 'cp %p /datastore/WAL/%f && gzip /datastore/WAL/%f ')
2. I have shared /datastore/WAL over NFS
3. I prepared SLAVE machine and created initial database out of MASTER's WAL-archival (over NFS) by preparing following file:

   $ vi /var/lib/pgsql/9.2/data/recovery.conf
   restore_command = 'gunzip -c /datastore/WAL/%f > %p'
   standby_mode = 'on'
   primary_conninfo = 'host=MASTER_IP_ port=5432 user=rep password=password'
   trigger_file = '/var/lib/pgsql/MASTER.trigger'

4. One last thing - I keep archive_mode = off on the SLAVE.

The above works perfect.

Now, here I need your help to understand what is happening in case following scenario.

SCENARIO:
I turn off SLAVE for some time (and MASTER receives a lot of new data) and later I turn SLAVE on.
Is it true/correct:
- SLAVE restores missing data/delta over NFS (from MASTER's archival). This is due to existing /var/lib/pgsql/9.2/data/recovery.conf and entry: restore_command = 'gunzip -c /datastore/WAL/%f > %p'
- Once restore is done, SLAVE will keep replicate new data from MASTER (btw. it is SLAVE who request replication, correct?)

Can anyone please confirm that my above assumption is correct and if not correct it?

Best regards,
czezz

pgsql-admin by date:

Previous
From: Mark Kirkwood
Date:
Subject: Re: [ADMIN] Replication Between heterogeneous Databases
Next
From: John Scalia
Date:
Subject: Re: [ADMIN] Replication mode Master-Slave - maintenance question.