Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cjTxp-0008M9-Ff for pgsql-performance@arkaria.postgresql.org; Thu, 02 Mar 2017 16:52:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cjTxo-0002rA-3k for pgsql-performance@arkaria.postgresql.org; Thu, 02 Mar 2017 16:52:24 +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 1cjTvo-0007xs-GA for pgsql-performance@postgresql.org; Thu, 02 Mar 2017 16:50:20 +0000 Received: from mail-qk0-x243.google.com ([2607:f8b0:400d:c09::243]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cjTvl-0000cI-9F for pgsql-performance@postgresql.org; Thu, 02 Mar 2017 16:50:19 +0000 Received: by mail-qk0-x243.google.com with SMTP id s186so20436130qkb.1 for ; Thu, 02 Mar 2017 08:50:16 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=1Z6LenLxVNpMcIzgdIAYu+93h9eSpOUcdUOdP2n6ZqE=; b=Y8uXipYK136aBYJUBtBJOMm4/gHUg/ulw/dmd+Ls2rlHXWpL0bM7SKU6QDaSDqZ4s4 Yb7zorgGMQg6xOY01BA8j1cLA9M7XDzAM6nEuOYzzjRJdiV58R32bHnNnSAit5khVbkf m2C+Pu7zjg4Q1vXrT+qeirwP0ZPbIX4ohMsud6M5woycfGPjsiJRzw8CwPTLNbVNezSA Qe23XfGOTP0tOHEUs3gYRSybVOlhl9rtZjPmNLDf+6lGcrVV83ck29lAebssE3m7+ElW sWSCToEGHBHF7/AiBVYrw3DFlLFhUKfSKIADk5LvGLhjJEbV5lvpmfmviZ8xXG9MNcA6 B+KQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=1Z6LenLxVNpMcIzgdIAYu+93h9eSpOUcdUOdP2n6ZqE=; b=iPTgCQBRoEQJ5a8ciPI3PHfKE7pPf8nF5GT8PlCS+skcH4YYQj8+aOzNr9I0Nyj7h9 nKIQwZxSaGFokXFMiDdpX4uO3ksLzvEpbx2l+E75cjlshcOR1GBG/AkauufxfP/Ch2+R 8+U4jiGgNEgNy021L98phVRE6iyOzeZFzeKQ8fxz/T33iL46ZCsxrXaGI21pK8TuF+UC TF71JBWFJb5pvVTLcCAL48AqlTZmEgURPt98GuJcdYuwUmul82+5pzIQwR4hVFX4cj5x sPGggzuiI3Uqm6bzykHJMwZaZ1aUVEoxC2NX9ytdxDIsRILe4USf213sC5ZbALIXWigo Fnng== X-Gm-Message-State: AMke39nx+4VwK65Uc1PzrJL9lEft7iAzpbC8sHZdYY/e4RSmSf7sLOZkglG4mGfXaY/2Uw== X-Received: by 10.233.239.201 with SMTP id d192mr17866714qkg.294.1488473415357; Thu, 02 Mar 2017 08:50:15 -0800 (PST) Received: from gustavos-mbp.home ([177.19.1.37]) by smtp.gmail.com with ESMTPSA id f126sm5693672qkc.47.2017.03.02.08.50.13 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 02 Mar 2017 08:50:14 -0800 (PST) From: Gustavo Vargas Message-Id: <37F475AB-57C3-4D41-BFD1-C9CDB3656769@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_6A4B0AF0-1B21-457C-B5E2-62BD6666B5E3" Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3271\)) Subject: Re: How Can I check PostgreSQL backup is successfully or not ? Date: Thu, 2 Mar 2017 13:50:10 -0300 In-Reply-To: <20170302162655.4870214.29055.5711@laurent-hasson.com> Cc: Rick Otten , Dinesh Chandra 12108 , "pgsql-performance@postgresql.org" To: "ldh@laurent-hasson.com" References: <7080564fb84f4bd0b178d86062b4790f@cyient.com> <0A1B0B276DEE6441A2E68EB66D4540DAD71D12C7@WINEX1.eldocomp.com> <10da6c146fc36f8124f5863bb824c615@xs4all.nl> <20170302162655.4870214.29055.5711@laurent-hasson.com> X-Mailer: Apple Mail (2.3271) X-Pg-Spam-Score: -2.7 (--) 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 --Apple-Mail=_6A4B0AF0-1B21-457C-B5E2-62BD6666B5E3 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi Rick,=20 You Always have a doubt, if backup is full and done. In my case, I have one Client using a large database, and the base in = mysql, an when we stop traffic about 8:00PM start a lot of routines. I = have about 2hours of Exclusive lock. For solve one problem like these in this Client. We prepared a = replication on other servers, and doing a backup in a replications = servers. In My Opinion Postgres Replication work better way than mysql. = https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_P= ooling Pros.: You have a second and third server and (n). Can compare data and have a control of replication log. And dont need do a directly backup in production enviroment Best Regards Gustavo Neves Vargas gustavo@justblue.com.br justblue.com.br +55(41) 9157-7816 +55(41) 3058-4967 > On 2 Mar 2017, at 13:26, ldh@laurent-hasson.com wrote: >=20 > 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 = 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? >=20 > I am in a warehouse environment, so I have given =E2=80=8Eup on = guaranteeing backups 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. >=20 >=20 > 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 ? >=20 > This reminds me - I have had a case where the exit code 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 validation, = such as the index listing, to increase your confidence that the backup = was successful. >=20 > The best way to ensure good backups is to establish a regular practice = of restoring a backup to another database. The easiest such practice to = justify 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, or simply spinning up throw away cloud instances for = the purpose. >=20 > 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. >=20 >=20 > On Thu, Mar 2, 2017 at 7:05 AM, Dinesh Chandra 12108 = > wrote: > Hi, >=20 > When I issue the bleow command > > ./bin >pg_dump -U dummy_user dummy_database; echo $? >=20 > I checked with Linux TOP command on the same server, it was showing = COPY database. > What exactly it doing ?? >=20 > Regards, > Dinesh Chandra >=20 > -----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 ? >=20 > On 2017-02-27 14:29, John Gorman wrote: > > Even though it's not listed in any of the documentation or = =E2=80=9Cpg_dump > > --help=E2=80=9D 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. >=20 >=20 > It's important to note the distinction between >=20 > "the backup process did not fail" >=20 > and >=20 > "we now have a trustworthy backup" >=20 > And you can go full-paranoia and say that you can successfully create = a perfectly working backup of the wrong database. >=20 > 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? >=20 > What are the chances of #1 reporting success but still producing a bad = backup? > And can #2 fail on a good database, and if so, can you detect that? >=20 >=20 >=20 > ________________________________ >=20 > DISCLAIMER: >=20 > 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 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. >=20 > -- > Sent via pgsql-performance mailing list = (pgsql-performance@postgresql.org = ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance = >=20 --Apple-Mail=_6A4B0AF0-1B21-457C-B5E2-62BD6666B5E3 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
Hi Rick, 

You Always have a doubt, = if backup is full and done.

In my case, I have one Client using a large database, and = the base in mysql, an when we stop traffic about 8:00PM  start a = lot of routines. I have about 2hours of Exclusive lock.

For solve one problem = like these in this Client. We prepared a replication on other servers, = and doing a backup in a replications servers.

In My Opinion Postgres Replication work = better way than mysql.

https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_C= onnection_Pooling

Pros.:  You have a second and third server and = (n).
Can compare data and have a control of = replication log.
And dont need do a directly backup = in production enviroment

Best Regards



Gustavo Neves = Vargas

gustavo@justblue.com.br
justblue.com.br
+55(41) 9157-7816
+55(41) = 3058-4967




On 2 Mar 2017, at 13:26, ldh@laurent-hasson.com wrote:

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 = 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 environment, so I have given =E2=80=8Eup on = guaranteeing backups 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 successful, but the backup was still = corrupted on disk.  By all means check the 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 restoring a backup to another database.  The = easiest such practice to justify 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, or 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 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 database.
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 <jgorman@eldocomp.com>
Cc: Rick Otten <rottenwindfish@gmail.com>; Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>; 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 = =E2=80=9Cpg_dump
> --help=E2=80=9D 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
> <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 = perfectly 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 = backup?
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 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-performance


= --Apple-Mail=_6A4B0AF0-1B21-457C-B5E2-62BD6666B5E3--