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 1iSnn3-0005h7-FD for pgsql-admin@arkaria.postgresql.org; Thu, 07 Nov 2019 19:49:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iSnn1-00007V-Ti for pgsql-admin@arkaria.postgresql.org; Thu, 07 Nov 2019 19:49:55 +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 1iSnn1-00007N-3N for pgsql-admin@lists.postgresql.org; Thu, 07 Nov 2019 19:49:55 +0000 Received: from sonic316-11.consmr.mail.bf2.yahoo.com ([74.6.130.121]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iSnmt-0006yo-Ty for pgsql-admin@postgresql.org; Thu, 07 Nov 2019 19:49:53 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1573156186; bh=dCqodsJV/FCz0oAbb25VgkWqKAyIpfMB0D5xQyBch/4=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=X9ADLcsWZJmulFbXDHQCjaVh/fildOtK7KqBrAKyCZdiPJc6RHVUppVZmvK6z7PHHF/uLNafEvuO2YHjrYtQRn22YuoTWhrCLSmJ99a8+WK8Q2+Edgh2M0oyXm6/8nczKBfqrNs+E2mDbONll7nEVpPf4kaEftZamXrxskOKEAOT3WCTI4jLvWvZUERTHYCRCnFg3xcgN4eXqOkJ32ftdconHhQaq/3cTn9O/vVeezn6AkRdzr3xizj0HQRCpko7SXrIeN3hBMakkMdxYyU+3Qb568fLUe5GBKr5iiPePmHFHBPovtJ5szIMT85ggu2N3U3M7N0LZv22tssfNmOhGg== X-YMail-OSG: v9QxDbUVM1kd76frK2pIKVX3uGMvYQkyD3o2p6PDxjk7jlFtydfJbcQ5beD6Pi. 3YRo0HIwWWPk2lGsce4flGrmLI4hHFbp_ZlDctWpIwq0.lyNq1w64aCCplVGC47WL2HES.GI2A0C _kSq90po1CbzZ7ID1Afu4KH3aOBOx.PTufDnhPrxopHY5M_nrP1kVn_El4TnbnPuE9ThN3LXQLXW dEgZouCQr5F1til2v1HMppq8or7rouLMgftRj_BNMVylyj_8pqgdvIlk_YQ4a3CIxr4lv5hEJYNB c5GRZSr4rJl5xi1QveQ_Q371qIPIQr0Pq8802wdHddLmSKd328dhy7yk_Bj9lvt5.5nk1oFLyzSE _eO.1.ql5_3U7QAeIv.Hbd.6.FEv45PbDuVeP4wJtGjWILjK7vetjykIVJC45zdnjPIBc4j9c1aW jCYA2NqM1uQZOp37A5dTYLYi1_yHT7FK_2_YUHaWCpB_PE.IJTICF7t2poqxObiYbBB0PSm.1RnH PkZsyQpHgcmQdAEzxy0jy1rAV4YJWbnFw8GQy01lVNMs__gzKgZ8U9idpm4nFM5h9kHiPz_LMHP2 LNo53_THXM.aBbyLn..TRCIjImJ4NjQlZW.i.0oM0La1LFULqttEU.LuG9B9tPrQeaeGcMjSc3Tf V_FwEV8yZDgD7uyDT.X6CKQ4tgWSKs_OHeTSMBOCq9gsUy5bTCvQtHODtfMxiecPucf2Fst4ZMHc n5eZozQ6xnB8GgTq_rD6SfX2GtPLJeJ1bxhCEIiqTcaiRHDtcYKfqZGEf2CfFO4kwjhudShEHgLJ 9Zl.18DQZdQC1D3DliXrmzzwH2R_oHexSih42w8Ezryv4XnSrC3ZGeUxtkbKkVYTpnDSvGIvdXa8 sUFQAOxz6QBLHVl2BuQIo5KUcKkR2sWHupov4S7EhggDqobobzBO_ERX5QhgYCuQnVry62zk8oXn 2E087C2YE3X_UFek1RWsHO6k7yT8CxwrJ_bG3LAGyV2Uc7_v4yDtt0gDPrRIefCon5hVoK2EuW6O oOrm4o7fdHmHlPTR3rjkvKNnm.d6Cas7rOb8GOWKXsxyRvfBr667wygllG12w5JbwG_tz5C5SLYh xYBauP3M2oF0gbhxWm7HPy3FA673BTKm7pgxpWfDuwpHKqaUWlLl7YhAxLm4ULykGfNN9sSFk9dS WbrlVHDRVzDuA6QRb7O_M2UMXh_G9X.M0WFiumP0PuP1YdYi6tmW2MSw_rtBA4grhVFIXRf0SEpA XNmsP90CkJVvK_mVceT60BxKr7Ef6_HgnR9zni3IKucw0YWbzk6sCX1vmkbn3.0dlzqn1yD.gXaW zbJOR.7farnKTDOe3inQVB6cIjxIGFDEGwc9bqm72s9pBncbkgfI- Received: from sonic.gate.mail.ne1.yahoo.com by sonic316.consmr.mail.bf2.yahoo.com with HTTP; Thu, 7 Nov 2019 19:49:46 +0000 Date: Thu, 7 Nov 2019 19:49:41 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: PASCAL CROZET , Asomba Djala Cc: MichaelDBA , girish R G peetle , pgsql-admin Message-ID: <1643045264.737000.1573156181422@mail.yahoo.com> In-Reply-To: References: <81433983e7fb485ba294ddad7854f41d@qualis-consulting.com> <6ae6b225-44f5-1b19-0d82-5fc1d41b9433@sqlexec.com> Subject: Re: RE : RE : PostgreSQL logical incremental backup for version 9.6 and above MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_736999_1037799716.1573156181419" X-Mailer: WebService/1.1.14680 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.70 Safari/537.36 Content-Length: 17505 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_736999_1037799716.1573156181419 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable have you checked this urlhttps://docs.aws.amazon.com/AmazonRDS/latest/UserG= uide/USER_WorkingWithAutomatedBackups.html Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir mistakes you will be alone. So judge less, love and forgive more.To call= him a dog hardly seems to do him justice though in as much as he had four = legs, a tail, and barked, I admit he was, to all outward appearances. But t= o those who knew him well, he was a perfect gentleman (Hermione Gingold) **Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely = rich *** Faithful talent *** Sharing success=20 On Thursday, November 7, 2019, 02:38:17 PM EST, Asomba Djala wrote: =20 =20 You could also ask the question to AWS support. They are very good at answ= ering=C2=A0questions with steps and useful documentation.=C2=A0 On Thu, Nov 7, 2019, 12:56 PM PASCAL CROZET wrote: Ok all, I=E2=80=99ve to talk to my collegues working with AWS to provide a best ans= wer. _________________________________ Cordialement,Pascal CROZET DBA - Qualis Consulting =E2=80=A2=C2=A0www.qualis-consulting.com=C2=A0=E2=80=A2=C2=A004 78 22 74 90 =E2=80=A2=C2=A0Le Bois des C=C3=B4tes 1 =E2=80=93 B=C3=A2timent A =E2=80=A2=C2=A0300 Route Nationale 6 =E2=80=93 69760 LIMONEST _________________________________ =C2=A0 De=C2=A0: MichaelDBA Envoy=C3=A9 le=C2=A0:mercredi 6 novembre 2019 20:32 =C3=80=C2=A0: girish R G peetle Cc=C2=A0: PASCAL CROZET;pgsql-admin Objet=C2=A0:Re: RE : PostgreSQL logical incremental backup for version 9.6 = and above =C2=A0 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.=C2=A0 = AWS RDS does not allow access to the underlying file system.=C2=A0 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,=C2=A0Thanks=C2=A0for the suggestion. Looks like pgBackRest relie= s on WAL archive logs. But for Amazon RDS or Azure Database WAL logs are no= t accessible. We can only do pg_dump and logical replication.=C2=A0So pgBac= kRest won't work for my case.=C2=A0 ThanksGirish On Wed, Nov 6, 2019 at 2:05 PM PASCAL CROZET wrote: Hi, =C2=A0 I think you can user pgBackRest =C2=A0 _________________________________ Cordialement,Pascal CROZET DBA - Qualis Consulting =E2=80=A2=C2=A0www.qualis-consulting.com=C2=A0=E2=80=A2=C2=A004 78 22 74 90 =E2=80=A2=C2=A0Le Bois des C=C3=B4tes 1 =E2=80=93 B=C3=A2timent A =E2=80=A2=C2=A0300 Route Nationale 6 =E2=80=93 69760 LIMONEST _________________________________ =C2=A0 De=C2=A0: girish R G peetle Envoy=C3=A9 le=C2=A0:mercredi 6 novembre 2019 18:03 =C3=80=C2=A0: pgsql-admin Objet=C2=A0:PostgreSQL logical incremental backup for version 9.6 and above =C2=A0 Hi,=C2=A0I've a requirement to backup incremental data from PostgreSQL data= base server which are hosted as 'database as a service' like 'Amazon RDS' ,= Azure Database etc.=C2=A0 Full dump can be taken using pg_dump. But is there is a way to capture incr= emental changes to the database ?=C2=A0 I read about logical=C2=A0replicati= on slots which can be created for each database to capture incremental chan= ges.=C2=A0But 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).=C2= =A0 I thinking of following steps.=C2=A0Step 1: Create logical replication slot= Step 2: Run pg_dump ( for full load ).=C2=A0Step 3: Capture the changes sin= ce last pg_dump using replication slot - How to know this information ? Thanks in advance for any advise.=C2=A0 ThanksGirish =20 ------=_Part_736999_1037799716.1573156181419 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


=20
=20
On Thursday, November 7, 2019, 02:38:17 PM EST, Asomba = Djala <asombadjala8@gmail.com> wrote:


You could also ask= the question to AWS support. They are very good at answering question= s with steps and useful documentation. 

On Thu, Nov 7, 2019, 12:56 PM PASCAL CROZET <pascal.croz= et@qualis-consulting.com> wrote:

Ok all,

I=E2=80=99ve to talk to my collegues wo= rking with AWS to provide a best answer.

_________________________________

Cordialement, Pascal CROZET

DBA - = Qualis Consulting

=E2=80= =A2 = www.qualis-consulting.com =E2=80=A2 04 78 22 74 90
=E2=80=A2 Le Bo= is des C=C3=B4tes 1 =E2=80=93 B=C3=A2timent A
=E2=80=A2 300 R= oute Nationale 6 =E2=80=93 69760 LIMONEST
_________________________________<= /span>

 

D= e : Mic= haelDBA
Envoy=C3=A9 le :mercredi 6 novembre 2019 20:32
=C3=80 : girish R G peetle
Cc : PASCAL CROZET; pgsql-ad= min
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&= gt; wrote:

Hi,

 

I think you can user pgBackRest

 

________________________________= _

Cordialement, Pascal CROZET

DBA - Qualis Consulting

= =E2=80=A2 www.quali= s-consulting.com =E2=80=A2 04 78 22 74 90
=E2=80=A2 Le Bois des C=C3=B4tes 1 =E2=80=93 B=C3=A2timent A
=E2=80=A2 300 Route Nationale 6 =E2=80=93 69760 LIMONEST<= span style=3D"font-size:8pt;color:rgb(127,127,127);">
____________________________= _____

 

D= e : g= irish R G peetle
Envoy=C3=A9 le :mercredi 6 novembre 2019 18:03
=C3=80 : 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

------=_Part_736999_1037799716.1573156181419--