Re: pg_restore and create FK without verification check

Tom Lane wrote:

>Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
>
>
>>This is somewhat complementary to WAL and PITR. I'm seeking for a fast
>>way to dump and restore a complete database, like physical file copy,
>>without shutting down the backend. I was thinking of a BACKUP command
>>that streams out the files including any indexes and non-vacuumed
>>tuples. A database recreated from that wouldn't be as clean as a
>>pg_dump/pg_restored database, but it would be up much faster, and there
>>wouldn't be any dependency problem.
>>
>>
>
>It's already intended to support this as part of the PITR work. The
>idea is you force a checkpoint and then make a tar-format dump of the
>database tree (tar or whatever floats your boat, but anyway a
>filesystem-level backup). The database need not be stopped while you do
>this, and you don't need a filesystem that can do snapshots or anything
>fancy like that. The tar dump itself most likely does not represent a
>consistent state of the database by the time you are done making it.
>That is okay, because you have also been archiving off to tape (or
>someplace) all the WAL data generated since that pre-dump checkpoint.
>You can continue archiving the WAL series for however far forward from
>the original dump you feel like. If you need to recover, you reload the
>database from the tar dump and then replay the WAL series against it.
>This is indistinguishable from a crash recovery situation --- the
>"inconsistent" tar dump looks just like a disk that has received some
>but not all of the updates since the last checkpoint. Replay will fix it.
>
>The cool thing about this is you can actually bring the DB to the state
>it was in at any specific point in time covered by your WAL archive ---
>just run the WAL replay as far as you want, then stop. Solves the
>"junior DBA deleted all my data Saturday morning" problem, thus "PITR".
>Now the uncool thing is you need massive amounts of secondary storage to
>archive all that WAL data,
>
Shouldn't be a problem, since there are few databases out there
worldwide exceeding today's average disk capacity...

>if your installation has heavy update
>activity. But it seems to me it would address the need you mention
>above --- you'd just not bother to continue archiving WAL past the end
>of the dump operation.
>
>
>
PITR is cool, no question, it's more than I've been requesting. When the
database server burns, I'd be quite happy if I could restore to my
latest tape's point in time, since the WAL log disk probably isn't
functional too. So having a fast backup of the snapshot when the backup
CHECKPOINT was issued would be enough, no WAL replay needed.

>In principle you could do this today, but we don't have enough
>support code in place to make it work smoothly, eg WAL segment files
>aren't labeled with enough identifying information to let you manage
>an archive full of 'em. Still it doesn't seem that far away.
>
>

So I issue CHECKPOINT, and tar the cluster or database. Still, I got two
questions:
- how to restore a single database
- while tar is running, CHECKPOINT(n+1) might be recorded in some files,
while others have CHECKPOINT(n). How does the backend know to rollback
to CHECKPOINT(n)?

Regards,
Andreas