Re: pg_restore and create FK without verification check - Mailing list pgsql-hackers
From | Oli Sennhauser |
---|---|
Subject | Re: pg_restore and create FK without verification check |
Date | |
Msg-id | 3FC4AF19.7050807@bluewin.ch Whole thread Raw |
In response to | Re: pg_restore and create FK without verification check (Andreas Pflug <pgadmin@pse-consulting.de>) |
List | pgsql-hackers |
Hello I was asking about this too, one or two weeks ago. >>>> It appears there's not a lot of interest in discussing the >>>> possibility of FK >>>> constraint creation WITHOUT the verification check. How then should >>>> one handle >>>> the situation with pg_restore and large dbs where creation of FK >>>> constraint(s) >>>> may take hours? >>> >>> I'd prefer a backup/restore method that dumps physical data, so at >>> restore time there's no need for recreation of FKs. But I didn't see >>> any feedback on this proposal either. >> >> Was this proposal a separate one from using WAL logs for PITR ? > My question then was: >> Q2: New situation: Why is it not a good idea to backup the database >> files of a cluster incl. all c_log and x_log (log files last) to get a >> "physicaly hot backup". >> In principle it is the same situation like a server which is crashing >> (not a once but during some time). After restoring, it should do a redo >> and rollback automatically like after a crash. This methode (physical >> hot backup) would increas backup and restore times dramatically. The answer from Robert Treat was: > Essentially I think you're right, it should behave much like a crashing > server. The main reason why people don't recommend it is that (depending on > your os setup) there is the potential to lose data that has been commited but > not actually written to disk. Note that you shouldn't get corrupted data > from this, but in many cases losing data is just as bad so we don't recomend > it. If you really want to do this, you should really either shut down the > database or get LVM going. I did not yet many tests. But in principle I was able to hot-backup a cluster or only one database and restore it. But the answer from Robert makes me a little afraid. It means for me he/they do not trust theire recovery mechanism. A definitive answer from Robert is still out. In my opinion a high grade professional database system (like PostgreSQL is or want to be) should have some hot backup features. Otherwise you are NEVER able to handle VLDB's. They were discussing about a 32 TB PostgreSQL database. And I bet my next bonus this year :-), that they are also not able to backup and restore it in a reasonable time. Regards Oli ------------------------------------------------------- Oli Sennhauser Database-Engineer (Oracle & PostgreSQL) Rebenweg 6 CH - 8610 Uster / Switzerland Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14 e-Mail oli.sennhauser@bluewin.ch Website http://mypage.bluewin.ch/shinguz/PostgreSQL/ Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import
pgsql-hackers by date: