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 1iSQcc-0004Tg-IN for pgsql-admin@arkaria.postgresql.org; Wed, 06 Nov 2019 19:05:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iSQcb-0000l5-5F for pgsql-admin@arkaria.postgresql.org; Wed, 06 Nov 2019 19:05:37 +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 1iSQca-0000ky-Mk for pgsql-admin@lists.postgresql.org; Wed, 06 Nov 2019 19:05:36 +0000 Received: from smtpout5.r2.mail-out.ovh.net ([54.36.141.5]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iSQcT-0002DK-7q for pgsql-admin@postgresql.org; Wed, 06 Nov 2019 19:05:35 +0000 Received: from ex2.mail.ovh.net (unknown [10.109.143.202]) by mo511.mail-out.ovh.net (Postfix) with ESMTPS id D2DE2AD78C82; Wed, 6 Nov 2019 20:05:24 +0100 (CET) Received: from EX12.indiv2.local (172.16.2.12) by EX11.indiv2.local (172.16.2.11) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.1466.3; Wed, 6 Nov 2019 20:05:24 +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; Wed, 6 Nov 2019 20:05:24 +0100 From: PASCAL CROZET To: girish R G peetle , pgsql-admin Subject: =?Windows-1252?Q?RE=A0:_PostgreSQL_logical_incremental_backup_for_version?= =?Windows-1252?Q?_9.6_and_above?= Thread-Topic: =?Windows-1252?Q?RE=A0:_PostgreSQL_logical_incremental_backup_for_version?= =?Windows-1252?Q?_9.6_and_above?= Thread-Index: AQHVlMQfOTADK477LUu8Eesw+Bj/Xad+gMHa Date: Wed, 6 Nov 2019 19:05:24 +0000 Message-ID: <81433983e7fb485ba294ddad7854f41d@qualis-consulting.com> References: In-Reply-To: Accept-Language: fr-FR, en-US Content-Language: fr-FR X-MS-Has-Attach: yes X-MS-TNEF-Correlator: Content-Type: multipart/related; boundary="_004_81433983e7fb485ba294ddad7854f41dqualisconsultingcom_"; type="multipart/alternative" MIME-Version: 1.0 X-Ovh-Tracer-Id: 1834372425973671986 X-VR-SPAMSTATE: OK X-VR-SPAMSCORE: 0 X-VR-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrgedufedruddujedguddvudcutefuodetggdotefrodftvfcurfhrohhfihhlvgemucfqggfjqdffgfeufgfipdevjffgvefmvefgnecuuegrihhlohhuthemucehtddtnecunecujfgurhephffvufhtfffkfhgjtgggsehrtdhjredttdehnecuhfhrohhmpefrtefuveetnfcuveftqfgkgffvuceophgrshgtrghlrdgtrhhoiigvthesqhhurghlihhsqdgtohhnshhulhhtihhnghdrtghomheqnecuffhomhgrihhnpehquhgrlhhishdqtghonhhsuhhlthhinhhgrdgtohhmpdhpghgsrggtkhhrvghsthdrohhrghenucfkpheptddrtddrtddrtddpfhgvkedtmeemgeduieeimegtieejrgemheeifheimegtfeegrgenucfrrghrrghmpehmohguvgepshhmthhpqdhouhhtpdhhvghlohepvgigvddrmhgrihhlrdhovhhhrdhnvghtpdhinhgvtheptddrtddrtddrtddpmhgrihhlfhhrohhmpehprghstggrlhdrtghrohiivghtsehquhgrlhhishdqtghonhhsuhhlthhinhhgrdgtohhmpdhrtghpthhtohepphhgshhqlhdqrggumhhinhesphhoshhtghhrvghsqhhlrdhorhhgnecuvehluhhsthgvrhfuihiivgeptd List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_004_81433983e7fb485ba294ddad7854f41dqualisconsultingcom_ Content-Type: multipart/alternative; boundary="_000_81433983e7fb485ba294ddad7854f41dqualisconsultingcom_" --_000_81433983e7fb485ba294ddad7854f41dqualisconsultingcom_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi, I think you can user pgBackRest _________________________________ Cordialement, Pascal CROZET DBA - [cid:image002.png@01D594DD.849024A0] 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_81433983e7fb485ba294ddad7854f41dqualisconsultingcom_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable

Hi,

 

I think you can user pgBackRest

 

_________________________________

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:giri.anamika0@gmail.com">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_81433983e7fb485ba294ddad7854f41dqualisconsultingcom_-- --_004_81433983e7fb485ba294ddad7854f41dqualisconsultingcom_ Content-Type: image/png; name="F104AAF7493A4A499CBC7ADF00B83A70.png" Content-Description: F104AAF7493A4A499CBC7ADF00B83A70.png Content-Disposition: inline; filename="F104AAF7493A4A499CBC7ADF00B83A70.png"; size=150; creation-date="Wed, 06 Nov 2019 19:05:24 GMT"; modification-date="Wed, 06 Nov 2019 19:05:24 GMT" Content-ID: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAAIsAAAAlCAYAAAByDxphAAAAAXNSR0IArs4c6QAAAARnQU1BAACx jwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAAArSURBVHhe7cEBDQAAAMKg909tDwcEAAAAAAAA AAAAAAAAAAAAAAAAALyrAVCBAAEpi4pKAAAAAElFTkSuQmCC --_004_81433983e7fb485ba294ddad7854f41dqualisconsultingcom_--