Re: pg_restore and create FK without verification check

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(dot)sennhauser(at)bluewin(dot)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