I was asking about this too, one or two weeks ago.
My question then was: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 ?
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 [EMAIL PROTECTED] 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
smime.p7s
Description: S/MIME Cryptographic Signature
