Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cssnD-0004WG-JL for pgsql-admin@arkaria.postgresql.org; Tue, 28 Mar 2017 15:12:19 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cssnD-0008Au-2N for pgsql-admin@arkaria.postgresql.org; Tue, 28 Mar 2017 15:12:19 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cssnC-00085M-3F for pgsql-admin@postgresql.org; Tue, 28 Mar 2017 15:12:18 +0000 Received: from mx-out.tlen.pl ([193.222.135.148]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cssn7-00013e-Kv for pgsql-admin@postgresql.org; Tue, 28 Mar 2017 15:12:15 +0000 Received: (wp-smtpd smtp.tlen.pl 1593 invoked from network); 28 Mar 2017 17:12:06 +0200 Received: from localhost.localdomain (HELO localhost) ([127.0.0.1]) (envelope-sender ) by smtp.tlen.pl (WP-SMTPD) with SMTP for ; 28 Mar 2017 17:12:06 +0200 Content-Type: multipart/alternative; boundary="2GGDTIRTCJCIHDOGCRNUQnhgwp" MIME-Version: 1.0 User-Agent: GWP-Draft Date: Tue, 28 Mar 2017 17:12:06 +0200 Subject: =?UTF-8?Q?Odp=3A_Re=3A_=5BADMIN=5D_=5BADMIN=5D_Replication_mode_Master-Slave_-_maintenance_question=2E?= Message-ID: <82438495332c4ac59376751d28835a92@gwp> In-Reply-To: References: <124c7aee04474da9b64dc7760e286956@gwp> From: czezz To: =?UTF-8?Q?John_Scalia?= Cc: =?UTF-8?Q?pgsql-admin=40postgresql=2Eorg?= X-FactoryStamp: H--- X-Originator: 46.140.243.83 X-WP-MailID: cec60d2e5e7e6c6941d8a9b9e5bbe923 X-WP-AV: skaner antywirusowy Poczty o2 X-WP-SPAM: NO 0000014 [sad0] X-Pg-Spam-Score: -0.9 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --2GGDTIRTCJCIHDOGCRNUQnhgwp Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=UTF-8 Hi John, thanks again for your reply. I took some time to read a bit of do= cumentation. My version of PostgreSQL: 9.2.20. Master-Slave setup is asy= nchronous/streaming replication/warm standby configuration - and for the mo= ment it's OK for my needs like that. Still, some things are not quit= e clear to me. This is my recovery.conf: =C2=A0=C2=A0 $ vi /var/lib/pgsql= /9.2/data/recove =C2=A0=C2=A0 restore_command =3D 'gunzip -c /datastor= e/WAL/%f > %p' =C2=A0=C2=A0 standby_mode =3D 'on' =C2=A0= =C2=A0 primary_conninfo =3D 'host=3DMASTER_IP_ port=3D5432 user=3Drep p= assword=3Dpassword' =C2=A0=C2=A0 trigger_file =3D '/var/lib/pgsql/= MASTER.trigger NOTE! NOTE! NOTE! /datastore/WAL/ - is NFS share on MASTE= R machine. 1. restore_command - it restores DB on SLAVE form archive logs= AND this one is executed only once per startup. Correct? 2. primary_conni= nfo (this parameter means also: streaming replication, correct?) - once res= tore_command is done, it starts to read MASTER's pg_xlogs and replicate= it AND this is happening constantly/nearly in real time. Correct? 3. In o= rder to stop and later start again SLAVE, access to MASTER's archive lo= gs is essential (keeping in the mind they are not recycled too early). Corr= ect? Dnia 24 marca 2017 13:03 John Scalia <jayknowsunix@gma= il.com> napisa=C5=82(a): You may need to study up then PostgreSQL replication. Sy= nchronous attaches the slave to the master through a replication slot, if y= ou're using 9.4 or newer, you can check that on the master by querying = pg_stat_replication, but the slave's recovery.conf file has a setting f= or which slot to use. Prior to 9.4 the named slot was not present but it st= ill used a replication channel, but that would appear in the same table on = the master. The other way to do replication is simply with WAL file shippin= g, but you can and should use WAL file shipping even if a replication chann= el or slot is in use. Think of it as insurance. With a WAL replica, all the= slave does is read and process WAL files as they arrive. The master has no= concept that a slave is attached. Thus it is possible over time for a mast= er to expire WAL segments that the slave never received (if it was unavaila= ble). The move to replication slots in 9.4 eliminated this possibility as m= aster knows of the slave(s) and will not expire WAL segments until all of t= hem have received the files. That's the quick and dirty explanation, bu= t like I began, you maybe ought to study how to do replication the way that= suits your firm best. On Fri, Mar 24, 2017 at 6:05 AM, czezz < cze= zz@o2.pl > wrote: Hi John, thanks for this reply. Honestly I do not= know is it synchronous or asynchronous. I didnt set any parameter like thi= s. Is there a quick way to verify that? Test case scenario I tested so f= ar was: stop SLAVE for some time (2-3 hours) and do a lot of INSERTS on MAS= TER. Afterwards,=C2=A0 when SLAVE is up again it synced delta from MASTER&= #39;s WAL files. Do it worked as I expected. Why are you saying that = 4;extended period of time" will be an issue? Is there any time limit fo= r SLAVE to be down or you are just saying about worse case scenario?=C2=A0 = 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=C5=82(a): You did not indicate whether you're Master-Slave= setup is synchronous or asynchronous, but yes, what you described should w= ork. The slave should have a recovery.conf file and the presence of that wi= ll 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 n= ot 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 ma= ke the request to their master. On Thu, Mar 23, 2017 at 1:47 PM, czezz &= lt; czezz@o2.pl > wrote: Hi, I have set my Postgres into replicati= on 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 (briefl= y): 1. Stand Alone postgres (future MASTER) has been set to archive data t= o: /datastore/WAL (archive_command =3D 'cp %p /datastore/WAL/%f &&a= mp; gzip /datastore/WAL/%f ') 2. I have shared /datastore/WAL over NFS= 3. I prepared SLAVE machine and created initial database out of MASTER= 9;s WAL-archival (over NFS) by preparing following file: =C2=A0=C2=A0 $ v= i /var/lib/pgsql/9.2/data/recove ry.conf =C2=A0=C2=A0 restore_command =3D = 'gunzip -c /datastore/WAL/%f > %p' =C2=A0=C2=A0 standby_mode = =3D 'on' =C2=A0=C2=A0 primary_conninfo =3D 'host=3DMASTER_IP_ = port=3D5432 user=3Drep password=3Dpassword' =C2=A0=C2=A0 trigger_file = =3D '/var/lib/pgsql/MASTER.trigger ' 4. One last thing - I keep a= rchive_mode =3D 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 n= ew 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 ex= isting /var/lib/pgsql/9.2/data/recove ry.conf and entry: restore_command = =3D 'gunzip -c /datastore/WAL/%f > %p' - Once restore is done, = SLAVE will keep replicate new data from MASTER (btw. it is SLAVE who reques= t replication, correct?) Can anyone please confirm that my above assumpti= on is correct and if not correct it? Best regards, czezz=0D --2GGDTIRTCJCIHDOGCRNUQnhgwp Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8
Hi John,
thanks again for your reply. I to= ok some time to read a bit of documentation.

M= y version of PostgreSQL: 9.2.20.
Master-Slave setup is asynch= ronous/streaming replication/warm standby configuration - and for the momen= t it's OK for my needs like that.


Still, some things are not quite clear to me.
This is my r= ecovery.conf:
   $ vi /var/lib/pgsql/9.2/data/recov= ery.conf
   restore_command =3D 'gunzip -c /datasto= re/WAL/%f > %p'
   standby_mode =3D 'on'
   primary_conninfo =3D 'host=3DMASTER_IP_ port=3D5432 use= r=3Drep password=3Dpassword'
   trigger_file =3D '/= var/lib/pgsql/MASTER.trigger'

NOTE! NOTE! NOTE= !
/datastore/WAL/ - is NFS share on MASTER machine.
=

1. restore_command - it restores DB on SLAVE form archi= ve logs AND this one is executed only once per startup. Correct?
<= div>2. primary_conninfo (this parameter means also: streaming replication, = correct?) - once restore_command is done, it starts to read MASTER's pg_xlo= gs and replicate it AND this is happening constantly/nearly in real time. C= orrect?
3. In order to stop and later start again SLAVE, acce= ss to MASTER's archive logs is essential (keeping in the mind they are not = recycled too early). Correct?

Dnia 24 marca 2017 13:03 John Scalia <jayknowsunix@gma= il.com> napisa=C5=82(a):

You may nee= d to study up then PostgreSQL replication. Synchronous attaches the slave t= o the master through a replication slot, if you're using 9.4 or newer, = you can check that on the master by querying pg_stat_replication, but the s= lave's recovery.conf file has a setting for which slot to use. Prior to= 9.4 the named slot was not present but it still used a replication channel= , but that would appear in the same table on the master. The other way to d= o replication is simply with WAL file shipping, but you can and should use = WAL file shipping even if a replication channel or slot is in use. Think of= it as insurance. With a WAL replica, all the slave does is read and proces= s WAL files as they arrive. The master has no concept that a slave is attac= hed. Thus it is possible over time for a master to expire WAL segments that= the slave never received (if it was unavailable). The move to replication = slots in 9.4 eliminated this possibility as master knows of the slave(s) an= d will not expire WAL segments until all of them have received the files. T= hat's the quick and dirty explanation, but like I began, you maybe ough= t to study how to do replication the way that suits your firm best.

On Fri, Mar 24, 2017 at 6:05 AM, czezz <czezz@o2.pl> wrote:
Hi John,
=
thanks for this reply.

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

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

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

Best regards,<= br/>
czezz

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

Y= ou did not indicate whether you're Master-Slave setup is synchronous or= asynchronous, but yes, what you described should work. The slave should ha= ve a recovery.conf file and the presence of that will cause that slave to w= ind through all WAL files present before it resumes further processing. At = least this is what happens in synchronous streaming mode. Your problem migh= t be if the slave is off for an extended period of time or if one of the WA= L files disappears. At that point the slave would not have all the transact= ions for consistency.
Again,to your question near the = end. It depends on how you're set up. Generally though slaves do make t= he 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 a= nd it works good.
Though, I have some question about maintai= ning it.

Let me start from the beginning. Ho= w I set it up (briefly):
1. Stand Alone postgres (future MAS= TER) has been set to archive data to: /datastore/WAL (archive_command =3D &= #39;cp %p /datastore/WAL/%f && gzip /datastore/WAL/%f ')
2. I have shared /datastore/WAL over NFS
3. I prepa= red SLAVE machine and created initial database out of MASTER's WAL-arch= ival (over NFS) by preparing following file:

=C2=A0=C2=A0 $ vi /var/lib/pgsql/9.2/data/recovery.conf
=C2=A0=C2=A0 restore_command =3D 'gunzip -c /datastore/WAL/%f &= gt; %p'
=C2=A0=C2=A0 standby_mode =3D 'on'
<= /div>
=C2=A0=C2=A0 primary_conninfo =3D 'host=3DMASTER_IP_ port=3D5= 432 user=3Drep password=3Dpassword'
=C2=A0=C2=A0 trigger= _file =3D '/var/lib/pgsql/MASTER.trigger'

4. One last thing - I keep archive_mode =3D off on the SLA= VE.

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 d= ata) and later I turn SLAVE on.
Is it true/correct:
- SLAVE restores missing data/delta over NFS (from MASTER's arch= ival). This is due to existing /var/lib/pgsql/9.2/data/recovery.= conf and entry: restore_command =3D '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 assumpti= on is correct and if not correct it?

Best re= gards,
czezz
=


--2GGDTIRTCJCIHDOGCRNUQnhgwp--