Re: Restore database from tablespace

On Thu, Jan 28, 2010 at 4:26 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> 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