Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cr6rS-00062c-Fj for pgsql-admin@arkaria.postgresql.org; Thu, 23 Mar 2017 17:49:22 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cr6rS-00027v-39 for pgsql-admin@arkaria.postgresql.org; Thu, 23 Mar 2017 17:49:22 +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 1cr6pf-0007EX-V9 for pgsql-admin@postgresql.org; Thu, 23 Mar 2017 17:47:32 +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 1cr6pb-000561-Gm for pgsql-admin@postgresql.org; Thu, 23 Mar 2017 17:47:30 +0000 Received: (wp-smtpd smtp.tlen.pl 1022 invoked from network); 23 Mar 2017 18:47:25 +0100 Received: from localhost.localdomain (HELO localhost) ([127.0.0.1]) (envelope-sender ) by smtp.tlen.pl (WP-SMTPD) with SMTP for ; 23 Mar 2017 18:47:25 +0100 Content-Type: multipart/alternative; boundary="2KPHVGRWFDTTBUCTVNHMOnhgwp" MIME-Version: 1.0 User-Agent: GWP-Draft Date: Thu, 23 Mar 2017 18:47:24 +0100 Subject: =?UTF-8?Q?=5BADMIN=5D_Replication_mode_Master-Slave_-_maintenance_question=2E?= Message-ID: <29bce6296d5445849e08d9b07d2ed1ca@gwp> From: czezz To: =?UTF-8?Q?pgsql-admin=40postgresql=2Eorg?= X-FactoryStamp: H--- X-Originator: 84.74.151.104 X-WP-MailID: a4ff40a68201e6216fc0771193678737 X-WP-AV: skaner antywirusowy Poczty o2 X-WP-SPAM: NO 0000010 [wXPU] X-Pg-Spam-Score: -1.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 --2KPHVGRWFDTTBUCTVNHMOnhgwp Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=UTF-8 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 f= rom the beginning. How I set it up (briefly): 1. Stand Alone postgres (fut= ure MASTER) has been set to archive data to: /datastore/WAL (archive_comman= d =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 pr= eparing following file: =C2=A0=C2=A0 $ vi /var/lib/pgsql/9.2/data/recove = =C2=A0=C2=A0 restore_command =3D 'gunzip -c /datastore/WAL/%f > %p&= #39; =C2=A0=C2=A0 standby_mode =3D 'on' =C2=A0=C2=A0 primary_conn= info =3D 'host=3DMASTER_IP_ port=3D5432 user=3Drep password=3Dpassword&= #39; =C2=A0=C2=A0 trigger_file =3D '/var/lib/pgsql/MASTER.trigger 4.= One last thing - I keep archive_mode =3D off on the SLAVE. The above wor= ks 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/c= orrect: - SLAVE restores missing data/delta over NFS (from MASTER's ar= chival). This is due to existing /var/lib/pgsql/9.2/data/recove and entry: = restore_command =3D 'gunzip -c /datastore/WAL/%f > %p' - Once r= estore is done, SLAVE will keep replicate new data from MASTER (btw. it is = SLAVE who request replication, correct?) Can anyone please confirm that m= y above assumption is correct and if not correct it? Best regards, czezz= =0D --2KPHVGRWFDTTBUCTVNHMOnhgwp Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8
Hi,
I have set my Postgres into replication mode Master-= Slave and it works good.
Though, I have some question about m= aintaining 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 = =3D 'cp %p /datastore/WAL/%f && gzip /datastore/WAL/%f ')
=
2. I have shared /datastore/WAL over NFS
3. I prepared S= LAVE machine and created initial database out of MASTER's WAL-archival (ove= r NFS) by preparing following file:

 &nbs= p; $ vi /var/lib/pgsql/9.2/data/recovery.conf
   re= store_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 th= e 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 dat= a) and later I turn SLAVE on.
Is it true/correct:
- SLAVE restores missing data/delta over NFS (from MASTER's archival). T= his is due to existing /var/lib/pgsql/9.2/data/recovery.conf and entry: res= tore_command =3D 'gunzip -c /datastore/WAL/%f > %p'
- Once= restore is done, SLAVE will keep replicate new data from MASTER (btw. it i= s SLAVE who request replication, correct?)

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

Best regards,
czezz
--2KPHVGRWFDTTBUCTVNHMOnhgwp--