Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1crM7i-0005T2-Ol for pgsql-admin@arkaria.postgresql.org; Fri, 24 Mar 2017 10:07:10 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1crM7h-00072r-KR for pgsql-admin@arkaria.postgresql.org; Fri, 24 Mar 2017 10:07:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1crM5u-0003pT-KY for pgsql-admin@postgresql.org; Fri, 24 Mar 2017 10:05:18 +0000 Received: from mx-out.tlen.pl ([193.222.135.140]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1crM5r-0007cO-Kn for pgsql-admin@postgresql.org; Fri, 24 Mar 2017 10:05:18 +0000 Received: (wp-smtpd smtp.tlen.pl 23420 invoked from network); 24 Mar 2017 11:05:14 +0100 Received: from localhost.localdomain (HELO localhost) ([127.0.0.1]) (envelope-sender ) by smtp.tlen.pl (WP-SMTPD) with SMTP for ; 24 Mar 2017 11:05:14 +0100 Content-Type: multipart/alternative; boundary="2JIITPPGJUKNHJGEPSXXEnhgwp" MIME-Version: 1.0 User-Agent: GWP-Draft Date: Fri, 24 Mar 2017 11:05:12 +0100 Subject: =?UTF-8?Q?Odp=3A_Re=3A_=5BADMIN=5D_=5BADMIN=5D_Replication_mode_Master-Slave_-_maintenance_question=2E?= Message-ID: <124c7aee04474da9b64dc7760e286956@gwp> In-Reply-To: References: 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: 6fd7b85edf9f553d2a9ca88586f4238a X-WP-AV: skaner antywirusowy Poczty o2 X-WP-SPAM: NO 0000014 [QRek] 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 --2JIITPPGJUKNHJGEPSXXEnhgwp Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=UTF-8 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 wa= y 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,=C2=A0= 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 ti= me" will be an issue? Is there any time limit for SLAVE to be down or y= ou 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 2= 017 19:06 John Scalia <jayknowsunix@gmail.com> napisa=C5=82(a): You = did not indicate whether you're Master-Slave setup is synchronous or as= ynchronous, 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 lea= st this is what happens in synchronous streaming mode. Your problem might b= e if the slave is off for an extended period of time or if one of the WAL f= iles disappears. At that point the slave would not have all the transaction= s 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 mas= ter. On Thu, Mar 23, 2017 at 1:47 PM, czezz < czezz@o2.pl > wro= te: 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 st= art 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_c= ommand =3D 'cp %p /datastore/WAL/%f && gzip /datastore/WAL/%f &= #39;) 2. I have shared /datastore/WAL over NFS 3. I prepared SLAVE machin= e and created initial database out of MASTER's WAL-archival (over NFS) = by preparing following file: =C2=A0=C2=A0 $ vi /var/lib/pgsql/9.2/data/ r= ecovery.conf =C2=A0=C2=A0 restore_command =3D 'gunzip -c /datastore/WA= L/%f > %p' =C2=A0=C2=A0 standby_mode =3D 'on' =C2=A0=C2=A0= primary_conninfo =3D 'host=3DMASTER_IP_ port=3D5432 user=3Drep passwor= d=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 w= hat 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 SLAV= E on. Is it true/correct: - SLAVE restores missing data/delta over NFS (f= rom MASTER's archival). This is due to existing /var/lib/pgsql/9.2/data= / recovery.conf and entry: restore_command =3D 'gunzip -c /datastore/WA= L/%f > %p' - Once restore is done, SLAVE will keep replicate new da= ta from MASTER (btw. it is SLAVE who request replication, correct?) Can a= nyone please confirm that my above assumption is correct and if not correct= it? Best regards, czezz=0D --2JIITPPGJUKNHJGEPSXXEnhgwp Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8
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 J= ohn Scalia <jayknowsunix@gmail.com> napisa=C5=82(a):

You= did not indicate whether you're Master-Slave setup is synchronous or async= hronous, but yes, what you described should work. The slave should have a r= ecovery.conf file and the presence of that will cause that slave to wind th= rough all WAL files present before it resumes further processing. At least = this is what happens in synchronous streaming mode. Your problem might be i= f the slave is off for an extended period of time or if one of the WAL file= s disappears. At that point the slave would not have all the transactions f= or 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.
<= /div>
Though, I have some question about maintaining it.
=
Let me start from the beginning. How I set it up (briefly):<= br>
1. Stand Alone postgres (future MASTER) has been set to archi= ve data to: /datastore/WAL (archive_command =3D 'cp %p /datastore/WAL/%f &a= mp;& gzip /datastore/WAL/%f ')
2. I have shared /datastor= e/WAL over NFS
3. I prepared SLAVE machine and created initia= l database out of MASTER's WAL-archival (over NFS) by preparing following f= ile:

   $ vi /var/lib/pgsql/9.2/data= /recovery.conf
   restore_command =3D 'gunzip = -c /datastore/WAL/%f > %p'
   standby_mode =3D '= on'
   primary_conninfo =3D 'host=3DMASTER_IP_ port= =3D5432 user=3Drep password=3Dpassword'
   trigger_= file =3D '/var/lib/pgsql/MASTER.trigger'

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

The above works perfect.

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

SCENARIO:
I turn off SLAV= E for some time (and MASTER receives a lot of new data) and later I turn SL= AVE on.
Is it true/correct:
- SLAVE restores mi= ssing 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 =3D = 'gunzip -c /datastore/WAL/%f > %p'
- Once restore is done,= SLAVE will keep replicate new data from MASTER (btw. it is SLAVE who reque= st replication, correct?)

Can anyone please co= nfirm that my above assumption is correct and if not correct it?
<= div>
Best regards,
czezz
=

--2JIITPPGJUKNHJGEPSXXEnhgwp--