Re: Restoring a database from a file system snapshot

William Garrison wrote:
> I have a PostgreSQL database on Windows Server 2003, and the database is
> kept on a SAN that has the ability to make instantaneous snapshots.
> Once I have made such a snapshot, I am unclear how to re-attach it to
> another postgres database on another machine. Postgres seems to create
> a directory structure that looks like this:
> Z:\MyDatabase
> Z:\MyDatabase\PG_VERSION
> Z:\MyDatabase\1234567
> Z:\MyDatabase\lots of files...
> The "1234567" number above changes with each new database I create.

It doesn't work. There's a procedure for restoring files, but you need
to also save the pg_xlog files as a stream, for which you need to set up
an archive_command in postgresql.conf beforehand. If you're interested
in this, see the "Point in time recovery" chapter in the documentation.

pg_dump/pg_restore is the easiest combination to use anyway.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support