[HACKERS] background writer, WAL and snapshot backups
I really like the idea of taking a snapshot backup with postgres, using either volume manager or hardware splits in a disk array to get a physical backup. In other, lesser database systems :) the system is structured to prevent problems arising from split block writes, meaning that though the database is writing an 8k block at the instant of the split, the intervening drivers may have written a partial block. two ways to avoid this problem, as I understand, are: 1.) freeze all block writes briefly (excluding WAL perhaps). Q:) I saw a patch going out to do writes in a background process. is this only to help write blocks before absolutely necessary, or is it to do *all* writes? if so, this would make adding freeze logic simple 2.) enable logging of entire blocks to the WAL, so recovery can overwrite any partial blocks caught writing at the instant of split. Q.) Does the WAL write tuple-level changes or block-level changes. I've been gradually browsing the code.(WAL at least) I'm a professional DBA but amateur C programmer, I'm in over my head trying to sort out myself. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] background writer, WAL and snapshot backups
Paul Tuckfield wrote: I really like the idea of taking a snapshot backup with postgres, using either volume manager or hardware splits in a disk array to get a physical backup. In other, lesser database systems :) the system is structured to prevent problems arising from split block writes, meaning that though the database is writing an 8k block at the instant of the split, the intervening drivers may have written a partial block. two ways to avoid this problem, as I understand, are: 1.) freeze all block writes briefly (excluding WAL perhaps). Q:) I saw a patch going out to do writes in a background process. is this only to help write blocks before absolutely necessary, or is it to do *all* writes? if so, this would make adding freeze logic simple 2.) enable logging of entire blocks to the WAL, so recovery can overwrite any partial blocks caught writing at the instant of split. Q.) Does the WAL write tuple-level changes or block-level changes. We write entire 8k pages to WAL on first modify so we can recover from partial 8k writes. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] background writer, WAL and snapshot backups
On Wed, 25 Nov 2003, Paul Tuckfield wrote: I really like the idea of taking a snapshot backup with postgres, using either volume manager or hardware splits in a disk array to get a physical backup. Does anyone else see any value in snapshotting. It would, I image, essentially work like this: A snapshot command is issued, the background writer CHECKPOINTS, the background writer/buffer manager writes out all new data to a different disk partition, any reads also need to look at the data in the temporary partition, and while this is happening, the main data directories are being copied some where else. Once it is finished, everything is tied up again. It would be quite fast and would make restores rather painless, but it wouldn't be trivial to implement :-) (could you even handle b-tree or would you have to seqscan any new data?). Just a thought, Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
