In response to "Ralf Gross" <[EMAIL PROTECTED]>: > Bill Moran said: > > In response to "Ralf Gross" <[EMAIL PROTECTED]>: > > > >> the / disk of my bacula (1.38.5) server crashed Friday night. The > >> postgres > >> db is on a separate disk. I restored the filesystem from a second backup > >> system. Since I only perform full backups on weekend the db should have > >> been in a consistent state. But to get some experience with desaster > >> recovery, I restored the postgres db from my bacula.sql dump file from > >> last weeks backup (cat bacula.sql | psql bacula) and ran dbcheck with > >> the > >> fix option. > > > > The command you show above will append the data in the bacula.sql file to > > the existing database (unless that file explicitly contains commands to > > delete data from the tables before recreating it). I doubt that's what > > you wanted to do and it's likely the cause of your errors. > > Does this part of the manual makes sense at all? > > http://www.bacula.org/rel-manual/Catalog_Maintenance.html#SECTION000237000000000000000 > > Compacting Your PostgreSQL Database > [snip] > pg_dump bacula > bacula.sql > cat bacula.sql | psql bacula > rm -f bacula.sql
That is _WRONG_. I never looked at it that carefully before, but the described process is incorrect and will cause PostgreSQL to attempt to append the data in the sql file to the existing database. Were you not worried by the numerous errors this must have generated? Doing pg_dump with the -c option will create "clean" statements to drop and recreate each table prior to importing the data, which will make the above procedure work. However, it's not really a recommended procedure. PostgreSQL does not need to be dumped/restored to keep the database efficient. A normal process of vacuuming will prevent the database from every getting too large. If you want to fine-tweak the database storage, commands such as VACUUM FULL, REINDEX, and CLUSTER exist specifically to keep you from having to do a dump/restore. Trying to re-document this all in the Bacula documentation seems counter- productive. Especially with the changes that are occurring to the autovacuum system, this is likely to change over the next few releases anyway, and documenting it in the Bacula docs would only create ugly doc overhead. Would it make more sense to provide a pointer in the Bacula docs to this part of the PostgreSQL docs? http://www.postgresql.org/docs/8.1/interactive/maintenance.html -- Bill Moran Collaborative Fusion Inc. **************************************************************** IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. **************************************************************** ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys -- and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
