Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ctCqH-0000ep-Mv for pgsql-admin@arkaria.postgresql.org; Wed, 29 Mar 2017 12:36:49 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ctCqH-0001nA-6F for pgsql-admin@arkaria.postgresql.org; Wed, 29 Mar 2017 12:36:49 +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 1ctCoO-0006xv-8h for pgsql-admin@postgresql.org; Wed, 29 Mar 2017 12:34:52 +0000 Received: from mx-out.tlen.pl ([193.222.135.145]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ctCoK-0007kr-Qa for pgsql-admin@postgresql.org; Wed, 29 Mar 2017 12:34:51 +0000 Received: (wp-smtpd smtp.tlen.pl 31808 invoked from network); 29 Mar 2017 14:34:45 +0200 Received: from localhost.localdomain (HELO localhost) ([127.0.0.1]) (envelope-sender ) by smtp.tlen.pl (WP-SMTPD) with SMTP for ; 29 Mar 2017 14:34:45 +0200 Content-Type: multipart/alternative; boundary="2PRCOMBXIQVWXNLNSPRYWnhgwp" MIME-Version: 1.0 User-Agent: GWP-Draft Date: Wed, 29 Mar 2017 14:34:45 +0200 Subject: =?UTF-8?Q?Odp=3A_Re=3A_Re=3A_=5BADMIN=5D_=5BADMIN=5D_Replication_mode_Master-Slave_-_maintenance_question=2E?= Message-ID: In-Reply-To: References: <124c7aee04474da9b64dc7760e286956@gwp> <82438495332c4ac59376751d28835a92@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: dc8d99173a2bf202cdbba326eb7cc077 X-WP-AV: skaner antywirusowy Poczty o2 X-WP-SPAM: NO 0000014 [0SeE] 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 --2PRCOMBXIQVWXNLNSPRYWnhgwp Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=UTF-8 Hi, thanks for answers. That's the result of: select * from pg_stat_r= eplication; postgres=3D# select * from pg_stat_replication; pid | usesy= sid | usename | application_name | client_addr | client_hostname | client_p= ort |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 backend_start=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0 state=C2=A0=C2=A0 = | sent_location | write_location | flush_location | replay_location | sync_= priority | sync_state -----+----------+---------+--- 135 |=C2=A0=C2=A0=C2= =A0 24576 | rep=C2=A0=C2=A0=C2=A0=C2=A0 | walreceiver=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 | 172.17.0.3=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 56154 | 2017-03-29 12:12:22.800513+00 | streaming | 0/96000000=C2=A0= =C2=A0=C2=A0 | 0/95FFFFF0=C2=A0=C2=A0=C2=A0=C2=A0 | 0/95FFFFF0=C2=A0=C2=A0= =C2=A0=C2=A0 | 0/95FFFFF0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0 | async (1 row) = Best regsrds, czezz Dnia 28 marca 2017 17:52 John Scalia <jayknowsunix@gma= il.com> napisa=C5=82(a): Per your questions, 1: Yes, AFAIK. 2: Yes, again, but= you need to look in the primary database's pg_stat_replication table a= nd the last column indicates the type of replication. 3: Yes, and potentia= lly, depending on how long the database is unavailable, those WAL segments = may have been deleted. For question 3, that's the best reason to move= to a new version of PostgreSQL, one that supports replication slots. On = Tue, Mar 28, 2017 at 11:12 AM, czezz < czezz@o2.pl > wrote: Hi John, thanks again for your reply. I took some time to r= ead a bit of documentation. My version of PostgreSQL: 9.2.20. Master-Sla= ve setup is asynchronous/streaming replication/warm standby configuration -= and for the moment it's OK for my needs like that. Still, some thin= gs are not quite clear to me. This is my recovery.conf: =C2=A0=C2=A0 $ vi= /var/lib/pgsql/9.2/data/ recovery.conf =C2=A0=C2=A0 restore_command =3D &= #39;gunzip -c /datastore/WAL/%f > %p' =C2=A0=C2=A0 standby_mode =3D= 'on' =C2=A0=C2=A0 primary_conninfo =3D 'host=3DMASTER_IP_ por= t=3D5432 user=3Drep password=3Dpassword' =C2=A0=C2=A0 trigger_file =3D= '/var/lib/pgsql/MASTER. trigger' NOTE! NOTE! NOTE! /datastore/W= AL/ - is NFS share on MASTER machine. 1. restore_command - it restores DB= on SLAVE form archive logs AND this one is executed only once per startup.= Correct? 2. primary_conninfo (this parameter means also: streaming replic= ation, correct?) - once restore_command is done, it starts to read MASTER&#= 39;s pg_xlogs and replicate it AND this is happening constantly/nearly in r= eal time. Correct? 3. In order to stop and later start again SLAVE, access= to MASTER's archive logs is essential (keeping in the mind they are no= t recycled too early). Correct? Dnia 24 marca 2017 13:03 John Scalia < jayknowsunix@= gmail.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 < cz= ezz@o2.pl > wrote: Hi John, thanks for this reply. Honestly I do no= t know is it synchronous or asynchronous. I didnt set any parameter like th= is. 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 MA= STER. 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 &#= 34;extended period of time" will be an issue? Is there any time limit f= or 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.co= m > napisa=C5=82(a): You did not indicate whether you're Master-Sla= ve 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 resum= es further processing. At least this is what happens in synchronous streami= ng mode. Your problem might be if the slave is off for an extended period o= f 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 nea= r 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 replic= ation mode Master-Slave and it works good. Though, I have some question ab= out maintaining it. Let me start from the beginning. How I set it up (bri= efly): 1. Stand Alone postgres (future MASTER) has been set to archive dat= a to: /datastore/WAL (archive_command =3D '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: =C2=A0=C2=A0 = $ vi /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_mod= e =3D 'on' =C2=A0=C2=A0 primary_conninfo =3D 'host=3DMASTER_IP= _ port=3D5432 user=3Drep password=3Dpassword' =C2=A0=C2=A0 trigger_fil= e =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 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 restore= s missing data/delta over NFS (from MASTER's archival). This is due to = existing /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 --2PRCOMBXIQVWXNLNSPRYWnhgwp Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8
Hi,
thanks for answers.
That= 's the result of: select * from pg_stat_replication;

postgres=3D# select * from pg_stat_replication;
pid |= usesysid | usename | application_name | client_addr | client_hostname | cl= ient_port |         backend_start&n= bsp;        |   state &nb= sp; | sent_location | write_location | flush_location | replay_location | s= ync_priority | sync_state
-----+----------+---------+--------= ----------+-------------+-----------------+-------------+------------------= -------------+-----------+---------------+----------------+----------------= +-----------------+---------------+------------
135 | &n= bsp;  24576 | rep     | walreceiver  &nb= sp;   | 172.17.0.3  |      &nb= sp;          |  &nbs= p;    56154 | 2017-03-29 12:12:22.800513+00 | streaming | 0/= 96000000    | 0/95FFFFF0     | 0/95FFFFF= 0     | 0/95FFFFF0      | = ;            0 | asy= nc
(1 row)

Best regsrds,
czezz

Dnia 28 marca 2017 17:52 John Scalia <jayknowsunix@gma= il.com> napisa=C5=82(a):

<= div>
Per your questions,

1: Yes, AFAIK.
2: Yes= , again, but you need to look in the primary database's pg_stat_replica= tion table and the last column indicates the type of replication.
3: Yes, and potentially, depending on how long the database is unavailable= , those WAL segments may have been deleted.

For question 3, = that's the best reason to move to a new version of PostgreSQL, one that= supports replication slots.

On Tue, Mar 28, 2017 at 11:1= 2 AM, czezz <czez= z@o2.pl> wrote:
Hi John,
thanks again for your reply. I t= ook some time to read a bit of documentation.

My version of PostgreSQL: 9.2.20.
Master-Slave setup is as= ynchronous/streaming replication/warm standby configuration - and for the m= oment it's OK for my needs like that.

Still, some things are not quite clear to me.
= This is my recovery.conf:
=C2=A0=C2=A0 $ vi /var/lib/p= gsql/9.2/data/recovery.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 prim= ary_conninfo =3D 'host=3DMASTER_IP_ port=3D5432 user=3Drep password=3Dp= assword'
=C2=A0=C2=A0 trigger_file =3D '/var/lib/pgs= ql/MASTER.trigger'

NOT= E! NOTE! NOTE!
/datastore/WAL/ - is NFS share on MASTER mach= ine.

1. restore_command - it restores DB on = SLAVE form archive logs AND this one is executed only once per startup. Cor= rect?
2. primary_conninfo (this parameter means also: stream= ing replication, correct?) - once restore_command is done, it starts to rea= d MASTER's pg_xlogs and replicate it AND this is happening constantly/n= early in real time. Correct?
3. In order to stop and later s= tart again SLAVE, access 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@gmail.com&g= t; napisa=C5=82(a):

=
You may need t= o study up then PostgreSQL replication. Synchronous attaches the slave to t= he 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 slav= e'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, b= ut that would appear in the same table on the master. The other way to do r= eplication 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 process W= AL files as they arrive. The master has no concept that a slave is attached= . Thus it is possible over time for a master to expire WAL segments that th= e slave never received (if it was unavailable). The move to replication slo= ts in 9.4 eliminated this possibility as master knows of the slave(s) and w= ill not expire WAL segments until all of them have received the files. That= 's the quick and dirty explanation, but like I began, you maybe ought t= o study how to do replication the way that suits your firm best.
=
O= n 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 like this.
Is there a quick way to verify that= ?

Test case scenario I tested so far was: st= op SLAVE for some time (2-3 hours) and do a lot of INSERTS on MASTER.
<= /div>
Afterwards,=C2=A0 when SLAVE is up again it synced delta from MAS= TER's WAL files.
Do it worked as I expected.
<= div>
Why are you saying that "extended period of time= 4; will be an issue? Is there any time limit for SLAVE to be down or you ar= e just saying about worse case scenario?=C2=A0
I assume tha= t I will never lose any WAL files on MASTER.

Best regards,
czezz

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

You did not indicate whether you're M= aster-Slave setup is synchronous or asynchronous, but yes, what you describ= ed 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 synchronou= s 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 sl= ave 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 a= rchive data to: /datastore/WAL (archive_command =3D 'cp %p /datastore/W= AL/%f && gzip /datastore/WAL/%f ')
2. I have sha= red /datastore/WAL over NFS
3. I prepared SLAVE machine and = created initial database out of MASTER's WAL-archival (over NFS) by pre= paring following file:

=C2=A0=C2=A0 $ vi /va= r/lib/pgsql/9.2/data/recovery.conf
=C2=A0=C2=A0 r= estore_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 passwor= d=3Dpassword'
=C2=A0=C2=A0 trigger_file =3D '/var/li= b/pgsql/MASTER.trigger'

4. On= e last thing - I keep archive_mode =3D off on the SLAVE.
The above works perfect.

Now, h= ere I need your help to understand what is happening in case following scen= ario.

SCENARIO:
I turn off SL= AVE for some time (and MASTER receives a lot of new data) and later I turn = SLAVE on.
Is it true/correct:
- SLAVE restore= s missing data/delta over NFS (from MASTER's archival). This is due to = existing /var/lib/pgsql/9.2/data/recovery.conf and entry: restor= e_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 assumption is correct and if n= ot correct it?

Best regards,
= czezz



--2PRCOMBXIQVWXNLNSPRYWnhgwp--