On Thu, Jan 28, 2010 at 4:26 PM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote: > > I'm still not sure I follow, but there's a technique which isn't > worth much for backup proper, but can be a good way to repeatedly > get to a consistent starting point for tests in some circumstances. > Look at CREATE DATABASE x WITH TEMPLATE y. > > http://www.postgresql.org/docs/8.4/interactive/sql-createdatabase.html > > You can do that once to capture a starting point for testing. You > can drop the testing database and re-create at will. I agree, CREATE DATABASE WITH TEMPLATE isn't worth much for backups. I was imagining a feature that could be used for backups as well as other applications. > If this and the various backup techniques don't do what you want, > I'm at a loss. I think you'd need to post something a bit more > concrete for me to understand what you mean. Okay. The analogy I'd make is file system / disk volume snapshots. I do not know your level of understanding here so forgive my description. File system / disk volume snapshots are instantaneous and immediately available because a complete copy wasn't made. The way file system snapshots are accomplished is by a driver that understands the copy is a snapshot of an original and while the snapshot exists any modification to the original is permissible however the data at the time of the snapshot is preserved. Here is a simple example: 1. Snapshot data-monday is made of the file system /data on Monday 2. data-monday is mounted by the system under /data-monday 3. Any access to /data-monday is "redirected" to the original /data unless a modification to the original exists 4. Modification to the original: the original file /data/X is modified and saved in /data however the original is then copied to the snapshot data-monday 5. Remove the snapshot data-monday and the original file system is still intact I'm imagining a feature where an admin could CREATE SNAPSHOT x OF [ DATABASE | SCHEMA | TABLE ] y; that uses a similar technique that I described above. Now, if a table is modified I wouldn't imagine the entire original table / index being copied but rather just the modified rows. This would be like disk volume snapshots that worry about modifications to blocks rather than files. 1. Snapshot data-vol-monday is created of the disk volume data-vol containing the file system /data on Monday 2. The file system in data-vol-monday is mounted by the system under /data-monday 3. Any access to /data-monday is "redirected" to the original /data unless a modification to the original exists 4. Modification to the original: the original file /data/X is modified in block 3 of 10 and saved in /data however the original 3rd block of X is then copied to the snapshot data-vol-monday 5. Remove the snapshot data-vol-monday and the original disk volume and file system is still intact The snapshots could be used for backups, testing, audit, or even active production access. Another application of snapshots is being able to merge them back to the original. Instead of step 5 above where the snapshot is removed a command could be issued to apply any changes back to the original. Using the snapshot instead of original also has the benefit of reducing I/O overhead. I don't make lite of the feature. If done or even considered it'd surely be a large undertaking and have performance implications with the additional I/O but the applications are as great or greater than file system and disk volume snapshots. Greg -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
