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.chan...@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:vi...@xs4all.nl] > Sent: 27 February, 2017 7:31 PM > To: John Gorman <jgor...@eldocomp.com> > Cc: Rick Otten <rottenwindf...@gmail.com>; Dinesh Chandra 12108 < > dinesh.chan...@cyient.com>; pgsql-performance@postgresql.org; > pgsql-performance-ow...@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 “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-ow...@postgresql.org > > [mailto:pgsql-performance-ow...@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.chan...@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 >
