Re: pg_restore and create FK without verification check - Mailing list pgsql-hackers

Tom Lane wrote:

>Andreas Pflug <pgadmin@pse-consulting.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