Re: 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(dot)Chandra(at)cyient(dot)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(at)xs4all(dot)nl]
> Sent: 27 February, 2017 7:31 PM
> To: John Gorman <jgorman(at)eldocomp(dot)com>
> Cc: Rick Otten <rottenwindfish(at)gmail(dot)com>; Dinesh Chandra 12108 <
> Dinesh(dot)Chandra(at)cyient(dot)com>; pgsql-performance(at)postgresql(dot)org;
> pgsql-performance-owner(at)postgresql(dot)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 “pg_dump
> > --help” 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(at)postgresql(dot)org
> > [mailto:pgsql-performance-owner(at)postgresql(dot)org] ON BEHALF OF Rick
> > Otten
> > SENT: Monday, February 27, 2017 3:36 AM
> > TO: Dinesh Chandra 12108
> > CC: pgsql-performance(at)postgresql(dot)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(dot)Chandra(at)cyient(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>