support quality (Re: dump | restore fails: unknown tape headertype 1853384566)

[Karl Denninger]

Jake Scott wrote:
>
>> That said there are plenty of other reasos to use proper dump tools
>> (data portability, confirming the ability to actually read all rows
>> from a table, using a more often exercised code path and perhaps less
>> likely to have edge case bugs, etc).
>
> Absolutely.  You really must use a tool that interacts with the 
> database to perform the backup.  Most commercial DBs have hooks that 
> allow the backup routines to call out to custom snapshot facilities.  
> One would usually request a backup through the database, which would 
> then freeze IO to its data files and maybe log files, deal with 
> flushing caches etc and then call your snapshot routine.  I'm not 
> aware that MySQL and Postgres do though so the best you can do is a dump.
>
> Jake
VERY careful thought has to go into backup strategy with production 
databases.

Hooks that call out and snapshot are not necessarily good enough 
although they're "necessary" to get a dump that restores without the 
database going into log-replay mode.

It is not difficult to do this with Postgresql; you can quiesce the 
database, snapshot and then release it, then dump the snapshots.  This 
gives you transaction-complete dumps (as opposed to "crashed and rolled 
forward" dumps).  The latter ("crashed and rolled forward"), if its 
sufficient, is trivially able to be done by having Postgresql (and most 
other databases) keep a sufficient number of log segments that a 
rollover cannot happen during the dump process itself, and either 
snapshotting both filesystems at once, keeping both on the same 
filesystem (undesirable for performance reasons) or dumping the database 
first and XLOG second.

However, whether either of these approaches is sufficient is another 
matter.  One of the real problems with live transaction processing 
systems is a means to know when there is a failure exactly what you 
lost.  This is not a trivial problem to solve and requires plenty of 
thought before implementation, especially if you cannot afford the 
outage time necessary to take the snapshots - in some cases even that 
(relatively) short outage time is unacceptable.

-- 
--
Karl Denninger
karl at denninger.net