Re: Restoring a database from a file system snapshot

Alvaro Herrera wrote:
> 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.
>
>
I've read (and I am reading it again now) that chapter, and I'm making
this post because that documentation doesn't seem to address the
scenario I am in. The PITR article
(http://www.postgresql.org/docs/8.2/static/continuous-archiving.html)
shows you how to use the WAL archives after a file system backup that is
not consistent. But it doesn't address two vital things:
1) I have a file system backup that *IS* consistent. So I should not
need any WAL files at all right?
2) It doesn't explain exactly what folders you should be creating a
backup of, or how exactly to restore those files onto another system, or
how to munge the tablespaces to work. Specifically, it says
"Restore the database files from your backup dump... If you are
using tablespaces, you should verify that the symbolic links in
pg_tblspc/ were correctly restored."
That's exactly what I'm asking how to do.

**update**
I got it working. Here's how
1) I have a file system snapshot. No WAL files were required.
2) Using my example from my first post, here is how I manually
rearranged the files so that postgres saw the new database.
- Create a new tablesspace on the new server
- Create a new database on the new server, using that tablespace. I
placed it into X:\NewDatabase\
- PostgreSQL will create a folder X:\NewDatabase\98765. Notice that the
number postgres created is NOT the same as your old one.
- Stop PostgreSQL
- Move your files from Z:\MyDatabase\1234567 into the X:\NewDatabase folder
- Delete the 98765 directory.
- Start PostgreSql

What I was doing before was moving the files from Z:\MyDatabase\1234567
into X:\NewDatabase\98765. The log file would then log a message like
2008-08-27 13:24:23 FATAL: database "Your Database Name" does not exist
2008-08-27 13:24:23 DETAIL: The database subdirectory
"pg_tblspc/32789/12345" is missing.
It specifically wants the old folder name, not the new folder name of
the new database you are attaching into.

--- The referenced article is lacking an explanation for how postgres
arranges the files and how the symlinks for the tablespaces are made.
Without that knowledge, attaching to another database is guesswork. It
really only took me a few tries to get the folder correct, but I think
there needs to be an attach procedure for attaching an existing
database, or the article should describe the folder structure used by
postgres. It isn't as simple as just copying the folder. You have to
also rename the directory structure to match. That's what I needed to
know. meh, it was easy to do, just unclear.