Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cjTfK-0007Wa-Nw for pgsql-performance@arkaria.postgresql.org; Thu, 02 Mar 2017 16:33:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cjTfK-00080p-1o for pgsql-performance@arkaria.postgresql.org; Thu, 02 Mar 2017 16:33:18 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cjTfI-0007zY-Db for pgsql-performance@postgresql.org; Thu, 02 Mar 2017 16:33:16 +0000 Received: from mxgate01.infotech-enterprises.com ([110.76.168.168] helo=mxgate01.cyient.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cjTfA-0000JO-4Z for pgsql-performance@postgresql.org; Thu, 02 Mar 2017 16:33:15 +0000 X-IronPort-AV: E=Sophos;i="5.35,232,1483986600"; d="scan'208,217";a="63841125" Received: from unknown (HELO INFHYDEX10HT.corp.infotech-enterprises.com) ([172.17.0.189]) by mxgate01.cyient.com with ESMTP/TLS/DHE-RSA-AES256-SHA; 02 Mar 2017 21:49:13 +0530 Received: from CYINMBX4.CORP.CYIENT.COM (172.17.4.164) by INFHYDEX10HT.corp.infotech-enterprises.com (172.17.0.189) with Microsoft SMTP Server (TLS) id 14.3.224.2; Thu, 2 Mar 2017 22:02:55 +0530 Received: from CYINMBX4.CORP.CYIENT.COM (172.17.4.164) by CYINMBX4.CORP.CYIENT.COM (172.17.4.164) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.544.27; Thu, 2 Mar 2017 22:02:54 +0530 Received: from CYINMBX4.CORP.CYIENT.COM ([fe80::e40c:d4fe:9e0c:f465]) by CYINMBX4.CORP.CYIENT.COM ([fe80::e40c:d4fe:9e0c:f465%19]) with mapi id 15.01.0544.027; Thu, 2 Mar 2017 22:02:54 +0530 From: Dinesh Chandra 12108 To: "ldh@laurent-hasson.com" CC: "pgsql-performance@postgresql.org" , Rick Otten Subject: Re: How Can I check PostgreSQL backup is successfully or not ? Thread-Topic: [PERFORM] How Can I check PostgreSQL backup is successfully or not ? Thread-Index: AdKQ3M8yJjjmRjxuRPSNwoQ1I36Zmf//tNKAgAAwVQCAAAi/AP/7D99ggAmaY4CAADWkAP//onYg Date: Thu, 2 Mar 2017 16:32:54 +0000 Message-ID: References: <7080564fb84f4bd0b178d86062b4790f@cyient.com> <0A1B0B276DEE6441A2E68EB66D4540DAD71D12C7@WINEX1.eldocomp.com> <10da6c146fc36f8124f5863bb824c615@xs4all.nl> , <20170302162655.4870214.29055.5711@laurent-hasson.com> In-Reply-To: <20170302162655.4870214.29055.5711@laurent-hasson.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [172.17.4.81] Content-Type: multipart/alternative; boundary="_000_d866a172948b40938a8f34b15f7e3b9acyientcom_" MIME-Version: 1.0 X-Pg-Spam-Score: -3.5 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --_000_d866a172948b40938a8f34b15f7e3b9acyientcom_ Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable May you please share what types of check if there to guarantee the backup i= s trustworthy. Regards, Dinesh Chandra |Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida. From: ldh@laurent-hasson.com [mailto:ldh@laurent-hasson.com] Sent: 02 March, 2017 9:57 PM To: Rick Otten ; Dinesh Chandra 12108 Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] How Can I check PostgreSQL backup is successfully or= not ? It'd be so nice to have some checks to guarantee the backup is trustworthy.= Restoring the db is imho not a very good option in general: - large databases are a problem. My db is about 3TB. Time plus disk space = is a big blocker. - also, what if the backup is incomplete? Just restoring the db successful= ly is not enough right? You'd have to compare with the prod to make sure no= thing was missed... in a fast moving outfit where the db today will have to= ns of new/changed deleted stuff from yesterday.. how to even do that? I am in a warehouse environment, so I have given =FDup on guaranteeing back= ups and in a case of trouble, i'll spend 20h rebuilding my db. So I have a = way out but i'd much prefer working with trustworthy backups. Sent from my BlackBerry 10 smartphone. From: Rick Otten Sent: Thursday, March 2, 2017 08:19 To: Dinesh Chandra 12108 Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] How Can I check PostgreSQL backup is successfully or= not ? This reminds me - I have had a case where the exit code for pg_dump was suc= cessful, but the backup was still corrupted on disk. By all means check th= e exit code, but I strong encourage a second validation, such as the index = listing, to increase your confidence that the backup was successful. The best way to ensure good backups is to establish a regular practice of r= estoring a backup to another database. The easiest such practice to justif= y and implement is to maintain a developer/development database, and to use= your production database backups to rebuild it on a regular basis. Other = approaches could include regularly scheduled Disaster Recovery exercises, o= r simply spinning up throw away cloud instances for the purpose. pg_dump uses the ordinary postgresql COPY command to extract data from the = tables. Beyond that, I'm not sure how it works. Sorry I can't help you th= ere. On Thu, Mar 2, 2017 at 7:05 AM, Dinesh Chandra 12108 > wrote: Hi, When I issue the bleow command > ./bin >pg_dump -U dummy_user dummy_database; echo $? I checked with Linux TOP command on the same server, it was showing COPY da= tabase. What exactly it doing ?? Regards, Dinesh Chandra -----Original Message----- From: vinny [mailto:vinny@xs4all.nl] Sent: 27 February, 2017 7:31 PM To: John Gorman > Cc: Rick Otten >;= Dinesh Chandra 12108 >; pgsql-performance@postgresql.org; pgsql-performance-owner@postgresql.org Subject: Re: [PERFORM] How Can I check PostgreSQL backup is successfully or= not ? On 2017-02-27 14:29, John Gorman wrote: > Even though it's not listed in any of the documentation or =93pg_dump > --help=94 you can check the return code of the process. A return code > greater than 0 (zero) usually indicates a failure > > ./bin >pg_dump -U dummy_user dummy_database; echo $? > > 1 > > FROM: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] ON BEHALF OF Rick > Otten > SENT: Monday, February 27, 2017 3:36 AM > TO: Dinesh Chandra 12108 > CC: pgsql-performance@postgresql.org > SUBJECT: Re: [PERFORM] How Can I check PostgreSQL backup is > successfully or not ? > > Although it doesn't really tell if the pg_dump was successful (you'll > need to do a full restore to be sure), I generate an archive list. If > that fails, the backup clearly wasn't successful, and if it succeeds, > odds are pretty good that it worked: > > On Mon, Feb 27, 2017 at 4:35 AM, Dinesh Chandra 12108 > > wrote: > > Hi, > > We are taking daily full backup of PostgreSQL database using PG_DUMP > which is automatic scheduled through Cronjobs. > > How can I check my yesterday backup is successfully or not? > > Is there any query or view by which I can check it? > > REGARDS, > > DINESH CHANDRA > > |DATABASE ADMINISTRATOR (ORACLE/POSTGRESQL)| CYIENT LTD. NOIDA. It's important to note the distinction between "the backup process did not fail" and "we now have a trustworthy backup" And you can go full-paranoia and say that you can successfully create a per= fectly working backup of the wrong database. So what is it that you want to make sure of: 1. Did the process give an error? 2. Did the process create a usable backup? What are the chances of #1 reporting success but still producing a bad back= up? And can #2 fail on a good database, and if so, can you detect that? ________________________________ DISCLAIMER: This email message is for the sole use of the intended recipient(s) and may= contain confidential and privileged information. Any unauthorized review, = use, disclosure or distribution is prohibited. If you are not the intended = recipient, please contact the sender by reply email and destroy all copies = of the original message. Check all attachments for viruses before opening t= hem. All views or opinions presented in this e-mail are those of the author= and may not reflect the opinion of Cyient or those of our affiliates. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance --_000_d866a172948b40938a8f34b15f7e3b9acyientcom_ Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable

May you please share what types of ch= eck if there to guarantee the backup is trustworthy.  

 

Regards,<= /o:p>

Dinesh Chandra

|Database administrator (Oracle/Postg= reSQL)| Cyient Ltd. Noida.

 

From: ldh@laurent-hasson.com [mailto= :ldh@laurent-hasson.com]
Sent: 02 March, 2017 9:57 PM
To: Rick Otten <rottenwindfish@gmail.com>; Dinesh Chandra 1210= 8 <Dinesh.Chandra@cyient.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How Can I check PostgreSQL backup is successf= ully or not ?

 

It'd be so nice to have som= e checks to guarantee the backup is trustworthy. Restoring the db is imho n= ot a very good option in general:

 - large databases are= a problem. My db is about 3TB. Time plus disk space is a big blocker.=

 - also, what if the b= ackup is incomplete? Just restoring the db successfully is not enough right= ? You'd have to compare with the prod to make sure nothing was missed... in a fast moving outfit where the db today will have tons of= new/changed deleted stuff from yesterday.. how to even do that?=

 

I am in a warehouse environ= ment, so I have given =FDup on guaranteeing backups and in a case of troubl= e, i'll spend 20h rebuilding my db. So I have a way out but i'd much prefer working with trustworthy backups.

 

 

Sent from my = ;BlackBerry 10 smartphone.

From: Rick Otten

Sent: Thursday, March 2, 2017 08:19

To: Dinesh Chandra 12108

Subject: Re: [PERFORM] How Can I check PostgreSQL backup is successfully = or not ?

 

This reminds me - I have had a case where the exit c= ode for pg_dump was successful, but the backup was still corrupted on disk.=   By all means check the exit code, but I strong encourage a second va= lidation, such as the index listing, to increase your confidence that the backup was successful.

 

The best way to ensure good backups is to establish = a regular practice of restoring a backup to another database.  The eas= iest such practice to justify and implement is to maintain a developer/deve= lopment database, and to use your production database backups to rebuild it on a regular basis.  Other approaches = could include regularly scheduled Disaster Recovery exercises, or simply sp= inning up throw away cloud instances for the purpose.

 

pg_dump uses the ordinary postgresql COPY command to= extract data from the tables.  Beyond that, I'm not sure how it works= .  Sorry I can't help you there.

 

 

On Thu, Mar 2, 2017 at 7:05 AM, Dinesh Chandra 12108= <Dinesh.= Chandra@cyient.com> wrote:

Hi,

When I issue the bleow command
  > ./bin >pg_dump -U dummy_user  dummy_database; echo $?
I checked with Linux TOP command on the same server, it was showing COPY da= tabase.
What exactly it doing ??

Regards,
Dinesh Chandra

-----Original Message-----
From: vinny [mailto:v= inny@xs4all.nl]
Sent: 27 February, 2017 7:31 PM
To: John Gorman <jgorman@eldocomp.com>
Cc: Rick Otten <rottenwindfish@gmail.com>; Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>; pgsql-performance@postg= resql.org; pgsql-performance= -owner@postgresql.org

Subject: Re: [PERFORM= ] How Can I check PostgreSQL backup is successfully or not ?

On 2017-02-27 14:29, John Gorman wrote:
> Even though it's not listed in any of the documentation or =93pg_dump<= br> > --help=94 you can check the return code of the process. A return code<= br> > greater than 0 (zero) usually indicates a failure
>
> ./bin >pg_dump -U dummy_user  dummy_database; echo $?
>
> 1
>
> FROM: pgsql-= performance-owner@postgresql.org
> [mailto:pgsq= l-performance-owner@postgresql.org] ON BEHALF OF Rick
> Otten
> SENT: Monday, February 27, 2017 3:36 AM
> TO: Dinesh Chandra 12108
> CC: pgsql-performa= nce@postgresql.org
> SUBJECT: Re: [PERFORM] How Can I check PostgreSQL backup is
> successfully or not ?
>
> Although it doesn't really tell if the pg_dump was successful (you'll<= br> > need to do a full restore to be sure), I generate an archive list.&nbs= p; If
> that fails, the backup clearly wasn't successful, and if it succeeds,<= br> > odds are pretty good that it worked:
>
> On Mon, Feb 27, 2017 at 4:35 AM, Dinesh Chandra 12108
> <Dinesh.Chandra@cyient= .com> wrote:
>
> Hi,
>
> We are taking daily full backup of PostgreSQL database using PG_DUMP > which is automatic scheduled through Cronjobs.
>
> How can I check my yesterday backup is successfully or not?
>
> Is there any query or view by which I can check it?
>
> REGARDS,
>
> DINESH CHANDRA
>
> |DATABASE ADMINISTRATOR (ORACLE/POSTGRESQL)| CYIENT LTD. NOIDA.


It's important to note the distinction between

"the backup process did not fail"

and

"we now have a trustworthy backup"

And you can go full-paranoia and say that you can successfully create a per= fectly working backup of the wrong database.

So what is it that you want to make sure of:
1. Did the process give an error?
2. Did the process create a usable backup?

What are the chances of #1 reporting success but still producing a bad back= up?
And can #2 fail on a good database, and if so, can you detect that?


_____________________= ___________

DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may= contain confidential and privileged information. Any unauthorized review, = use, disclosure or distribution is prohibited. If you are not the intended = recipient, please contact the sender by reply email and destroy all copies of the original message. Check all a= ttachments for viruses before opening them. All views or opinions presented= in this e-mail are those of the author and may not reflect the opinion of = Cyient or those of our affiliates.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-per= formance

 

--_000_d866a172948b40938a8f34b15f7e3b9acyientcom_--