Обсуждение: Restore database from tablespace

"dev.pho" <dev.pho@gmail.com> wrote:

> I was wondering if I could restore the database from the location
> of the tablespace.

It's not clear exactly what you want to do.  Have you read through
the documentation on backup and restore?:

http://www.postgresql.org/docs/8.4/interactive/backup.html

Perhaps you want to follow the instructions for "File System Level
Backup", or maybe PITR?

-Kevin

On Thu, Jan 28, 2010 at 9:56 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> "dev.pho" <dev.pho@gmail.com> wrote:
>
>> I was wondering if I could restore the database from the location
>> of the tablespace.
>
> It's not clear exactly what you want to do.  Have you read through
> the documentation on backup and restore?:

I don't know if this was the intent but it did provide me with what I
thought was a good idea.
 * Snapshot a schema / tablespace / database to another schema /
tablespace / database.

The applications for this could be
 * an alternative to the existing backup methods
 * using the copy for testing purposes without having to create-drop database
 * providing stock data for application demos without fear of losing
the original
 * quick recovery to a known good schema and data

Could the mechanics behind pg_start_backup() assist with the
development of such a feature?

It's just a rough idea.

Greg

Greg Spiegelberg <gspiegelberg@gmail.com> wrote:

> I don't know if this was the intent but it did provide me with
> what I thought was a good idea.
>  * Snapshot a schema / tablespace / database to another schema /
> tablespace / database.
>
> The applications for this could be
>  * an alternative to the existing backup methods
>  * using the copy for testing purposes without having to
>    create-drop database
>  * providing stock data for application demos without fear of
>    losing the original
>  * quick recovery to a known good schema and data

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.

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.

-Kevin

On Thu, Jan 28, 2010 at 4:26 PM, Kevin Grittner
<Kevin.Grittner@wicourts.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