Re: Restoring a database from a file system snapshot - Mailing list pgsql-general

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.