Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iSmxV-0003eX-7x for pgsql-admin@arkaria.postgresql.org; Thu, 07 Nov 2019 18:56:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iSmxT-0000Va-MX for pgsql-admin@arkaria.postgresql.org; Thu, 07 Nov 2019 18:56:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iSmxT-0000VR-5b for pgsql-admin@lists.postgresql.org; Thu, 07 Nov 2019 18:56:39 +0000 Received: from smtpout1.r2.mail-out.ovh.net ([54.36.141.1]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iSmxQ-0006YD-0X for pgsql-admin@postgresql.org; Thu, 07 Nov 2019 18:56:37 +0000 Received: from ex2.mail.ovh.net (unknown [10.109.159.236]) by mo511.mail-out.ovh.net (Postfix) with ESMTPS id D5257ADE7A96; Thu, 7 Nov 2019 19:56:31 +0100 (CET) Received: from EX12.indiv2.local (172.16.2.12) by EX12.indiv2.local (172.16.2.12) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.1466.3; Thu, 7 Nov 2019 19:56:31 +0100 Received: from EX12.indiv2.local ([fe80::4166:c67a:56f6:c34a]) by EX12.indiv2.local ([fe80::4166:c67a:56f6:c34a%12]) with mapi id 15.01.1466.008; Thu, 7 Nov 2019 19:56:31 +0100 From: PASCAL CROZET To: MichaelDBA , girish R G peetle CC: pgsql-admin Subject: =?Windows-1252?Q?RE=A0:_RE_:_PostgreSQL_logical_incremental_backup_for_ve?= =?Windows-1252?Q?rsion_9.6_and_above?= Thread-Topic: =?Windows-1252?Q?RE=A0:_RE_:_PostgreSQL_logical_incremental_backup_for_ve?= =?Windows-1252?Q?rsion_9.6_and_above?= Thread-Index: AQHVlNgs5yo8/YtMTUmiETrkEFO886d+dzGAgAGZQSI= Date: Thu, 7 Nov 2019 18:56:31 +0000 Message-ID: References: <81433983e7fb485ba294ddad7854f41d@qualis-consulting.com> ,<6ae6b225-44f5-1b19-0d82-5fc1d41b9433@sqlexec.com> In-Reply-To: <6ae6b225-44f5-1b19-0d82-5fc1d41b9433@sqlexec.com> Accept-Language: fr-FR, en-US Content-Language: fr-FR X-MS-Has-Attach: yes X-MS-TNEF-Correlator: Content-Type: multipart/related; boundary="_005_ef5734dfbb6c4d86979494c2c39f4232qualisconsultingcom_"; type="multipart/alternative" MIME-Version: 1.0 X-Ovh-Tracer-Id: 7557040177851115772 X-VR-SPAMSTATE: OK X-VR-SPAMSCORE: 0 X-VR-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrgedufedrudduledguddukecutefuodetggdotefrodftvfcurfhrohhfihhlvgemucfqggfjqdffgfeufgfipdevjffgvefmvefgnecuuegrihhlohhuthemucehtddtnecunecujfgurhephffvufhtfffkfhgjtgggsehrtdhjredttdehnecuhfhrohhmpefrtefuveetnfcuveftqfgkgffvuceophgrshgtrghlrdgtrhhoiigvthesqhhurghlihhsqdgtohhnshhulhhtihhnghdrtghomheqnecuffhomhgrihhnpehquhgrlhhishdqtghonhhsuhhlthhinhhgrdgtohhmpdhpghgsrggtkhhrvghsthdrohhrghenucfkpheptddrtddrtddrtddpfhgvkedtmeemgeduieeimegtieejrgemheeifheimegtfeegrgenucfrrghrrghmpehmohguvgepshhmthhpqdhouhhtpdhhvghlohepvgigvddrmhgrihhlrdhovhhhrdhnvghtpdhinhgvtheptddrtddrtddrtddpmhgrihhlfhhrohhmpehprghstggrlhdrtghrohiivghtsehquhgrlhhishdqtghonhhsuhhlthhinhhgrdgtohhmpdhrtghpthhtohepphhgshhqlhdqrggumhhinhesphhoshhtghhrvghsqhhlrdhorhhgnecuvehluhhsthgvrhfuihiivgeptd List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_005_ef5734dfbb6c4d86979494c2c39f4232qualisconsultingcom_ Content-Type: multipart/alternative; boundary="_000_ef5734dfbb6c4d86979494c2c39f4232qualisconsultingcom_" --_000_ef5734dfbb6c4d86979494c2c39f4232qualisconsultingcom_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Ok all, I=92ve to talk to my collegues working with AWS to provide a best answer. _________________________________ Cordialement, Pascal CROZET DBA - [cid:image002.png@01D595A5.70A4F920] Qualis Consulting =95 www.qualis-consulting.com =95 04 78 = 22 74 90 =95 Le Bois des C=F4tes 1 =96 B=E2timent A =95 300 Route Nationale 6 =96 69760 LIMONEST _________________________________ De : MichaelDBA Envoy=E9 le :mercredi 6 novembre 2019 20:32 =C0 : girish R G peetle Cc : PASCAL CROZET; pgsql-admin= Objet :Re: RE : PostgreSQL logical incremental backup for version 9.6 and a= bove Hi, You can use pgbackrest to store WAL on AWS S3, but you need a binary backup= from which to apply WAL to do PITR. pg_dump is not a binary backup. AWS R= DS does not allow access to the underlying file system. So you cannot use = pgbackrest to do backup or restore, or use the WAL you saved, let's say on = S3, to do PITR. Regards, Michael Vitale girish R G peetle wrote on 11/6/2019 2:26 PM: Hi Pascal, Thanks for the suggestion. Looks like pgBackRest relies on WAL archive logs= . But for Amazon RDS or Azure Database WAL logs are not accessible. We can = only do pg_dump and logical replication. So pgBackRest won't work for my case. Thanks Girish On Wed, Nov 6, 2019 at 2:05 PM PASCAL CROZET > wrote: Hi, I think you can user pgBackRest _________________________________ Cordialement, Pascal CROZET DBA - [cid:part3.56B1EF95.FA6C0497@sqlexec.com] Qualis Consulting =95 www.qualis-consulting.com =95 04 78 = 22 74 90 =95 Le Bois des C=F4tes 1 =96 B=E2timent A =95 300 Route Nationale 6 =96 69760 LIMONEST _________________________________ De : girish R G peetle Envoy=E9 le :mercredi 6 novembre 2019 18:03 =C0 : pgsql-admin Objet :PostgreSQL logical incremental backup for version 9.6 and above Hi, I've a requirement to backup incremental data from PostgreSQL database serv= er which are hosted as 'database as a service' like 'Amazon RDS' , Azure Da= tabase etc. Full dump can be taken using pg_dump. But is there is a way to capture incr= emental changes to the database ? I read about logical replication slots w= hich can be created for each database to capture incremental changes. But how do we know from where we should backup the SQL statements (as we ne= ed need skip those statements already backed up by pg_dump). I thinking of following steps. Step 1: Create logical replication slot Step 2: Run pg_dump ( for full load ). Step 3: Capture the changes since last pg_dump using replication slot - How= to know this information ? Thanks in advance for any advise. Thanks Girish --_000_ef5734dfbb6c4d86979494c2c39f4232qualisconsultingcom_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable

Ok all,

I=92ve to talk to my collegues working with AWS to p= rovide a best answer.

_________________________________

Cordialement, Pascal CROZET

DBA - <= /span>Qualis Consulting

=95 www.qualis-consulting.com<= /span> =95 04 78 22 74 90
=95 Le Bois des C= =F4tes 1 =96 B=E2timent A
=95 300 Route Nati= onale 6 =96 69760 LIMONEST
_________________________________

 

De : <= a href=3D"mailto:MichaelDBA@sqlexec.com">MichaelDBA
Envoy=E9 le :mercredi 6 novembre 2019 20:32
=C0 : girish R G pee= tle
Cc : PAS= CAL CROZET; pgsql-admin
Objet :Re: RE : PostgreSQL logical incremental backup for versi= on 9.6 and above

 

Hi,
You can use pgbackrest to store WAL on AWS S3, but you need a binary backup= from which to apply WAL to do PITR. pg_dump is not a binary backup.  = AWS RDS does not allow access to the underlying file system.  So you c= annot use pgbackrest to do backup or restore, or use the WAL you saved, let's say on S3, to do PITR.

Regards,
Michael Vitale

girish R G peetle wrote on 11/6/2019 2:26 PM:
Hi Pascal, 
Thanks for the suggestion. Looks like pgBackRest relies on WAL ar= chive logs. But for Amazon RDS or Azure Database WAL logs are not accessibl= e. We can only do pg_dump and logical replication. 
So pgBackRest won't work for my case. 

Thanks
Girish

On Wed, Nov 6, 2019 at 2:05 PM PASCAL= CROZET <pascal.crozet@qualis-consulting.com> wrote:

Hi,

 

I think you can user pgBackRest

 

_________________________________=

Cordialement, Pascal CROZET

= DBA - Qualis Consulting

=95 www.qualis-consulting.com =95 = 04 78 22 74 90
=95 Le Bois des C=F4tes 1 =96 B=E2timent A
=95 300 Route Nationale 6 =96 69760 LIMONEST
_____________________________= ____

 

De : <= a href=3D"mailto:giri.anamika0@gmail.com" target=3D"_blank" moz-do-not-send= =3D"true">girish R G peetle
Envoy=E9 le :mercredi 6 novembre 2019 18:03
=C0 : pgsql-admin
Objet :PostgreSQL logical incremental backup for version 9.6 an= d above

 

Hi, 
I've a requirement to backup incremental data from PostgreSQL database= server which are hosted as 'database as a service' like 'Amazon RDS' , Azu= re Database etc. 

Full dump can be taken using pg_dump. But is there is a way to capture= incremental changes to the database ?  I read about logical repl= ication slots which can be created for each database to capture incremental= changes. 
But how do we know from where we should backup the SQL statements (as = we need need skip those statements already backed up by pg_dump). 

I thinking of following steps. 
Step 1: Create logical replication slot
Step 2: Run pg_dump ( for full load ). 
Step 3: Capture the changes since last pg_dump using replication slot = - How to know this information ?

Thanks in advance for any advise. 

Thanks
Girish

--_000_ef5734dfbb6c4d86979494c2c39f4232qualisconsultingcom_-- --_005_ef5734dfbb6c4d86979494c2c39f4232qualisconsultingcom_ Content-Type: image/png; name="image.png" Content-Description: image.png Content-Disposition: inline; filename="image.png"; size=99; creation-date="Wed, 06 Nov 2019 19:32:01 GMT"; modification-date="Wed, 06 Nov 2019 19:32:01 GMT" Content-ID: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAAIsAAAAlCAYAAAByDxphAAAAKklEQVR4nO3BAQ0AAADCoPdPbQ8H FAAAAAAAAAAAAAAAAAAAAAAAAABwb1CBAAEmUMyPAAAAAElFTkSuQmCC --_005_ef5734dfbb6c4d86979494c2c39f4232qualisconsultingcom_ Content-Type: image/png; name="564CD4194D5B488DAE6D85DB59C2CE64.png" Content-Description: 564CD4194D5B488DAE6D85DB59C2CE64.png Content-Disposition: inline; filename="564CD4194D5B488DAE6D85DB59C2CE64.png"; size=150; creation-date="Thu, 07 Nov 2019 18:56:31 GMT"; modification-date="Thu, 07 Nov 2019 18:56:31 GMT" Content-ID: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAAIsAAAAlCAYAAAByDxphAAAAAXNSR0IArs4c6QAAAARnQU1BAACx jwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAAArSURBVHhe7cEBDQAAAMKg909tDwcEAAAAAAAA AAAAAAAAAAAAAAAAALyrAVCBAAEpi4pKAAAAAElFTkSuQmCC --_005_ef5734dfbb6c4d86979494c2c39f4232qualisconsultingcom_--