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_Connection_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 gust...@justblue.com.br <mailto:gust...@justblue.com.br> justblue.com.br +55(41) 9157-7816 +55(41) 3058-4967 > On 2 Mar 2017, at 13:26, l...@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 up 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.chan...@cyient.com <mailto: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 <mailto:vi...@xs4all.nl>] > Sent: 27 February, 2017 7:31 PM > To: John Gorman <jgor...@eldocomp.com <mailto:jgor...@eldocomp.com>> > Cc: Rick Otten <rottenwindf...@gmail.com <mailto:rottenwindf...@gmail.com>>; > Dinesh Chandra 12108 <dinesh.chan...@cyient.com > <mailto:dinesh.chan...@cyient.com>>; pgsql-performance@postgresql.org > <mailto:pgsql-performance@postgresql.org>; > pgsql-performance-ow...@postgresql.org > <mailto: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> > > [mailto: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 > > <mailto: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 <mailto: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 > <mailto:pgsql-performance@postgresql.org>) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > <http://www.postgresql.org/mailpref/pgsql-performance> >
