Re: Restoring a database from a file system snapshot

[William Garrison <postgres@xxxxxxxxxxxx>]

I have just come to a horrible realization about PostgreSQL that I'm sure is supposed to be pre-requisite knowledge even going into this.  So everyone may laugh at me now.

We have a SAN volume, and we created a tablespace that that points to that SAN volume (Z: drive).  This put all the table files on Z:.  It was our assumption that the table files + the archived transaction would now be on the Z: drive, and that was enough to restore the database.  It is shocking to me that I told PostgreSQL to put the database on Z:, but it is only putting a subset of the necessary files on that drive.  That is just frightening.  A database is not just tables - it is tables and transaction logs.  Why on earth would PostgreSQL put the tables separately from the transaction logs?  This is having a chilling effect on me as I realize that the transaction log files are not separated by database.  So when I have multiple database systems, I have one single shared set of transaction logs.  Even though those databases are on completely separate SANs.  I'm used to things like MS SQL Server where I say "database Foo goes on Z: and this database Bar goes on X:" and you can take it for granted that the transaction logs for database Foo also go on Z: and the transaction logs for database Bar go on X:.  I'm still reeling from the thought that there can somehow be a single transaction log for multiple databases.  How is that even possible?  Are the transaction ID numbers shared across databases too?

I need to educate our IT group about this.  They setup the SAN volumes based on my incorrect assumptions about how PostgreSQL worked.  It sounds like, on Windows, we need to just flat-out reinstall postgres and install it into the Z: drive so that the entire data directory is on the SAN volume.  Installing it to C: and having only parts of the database on the SAN is not good.

(Thanks to everyone who is replying - this is clearing-up a lot of misconceptions for me)

P.S.  I guess on Unix, you guys all just setup the data directory to be a hard-link to some other location?

Alvaro Herrera wrote:

William Garrison wrote:

  

1) I have a file system backup that *IS* consistent.  So I should not  
need any WAL files at all right?
    

It is consistent only if it was taken when the postmaster was down.

  

**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
    

This does not really work, because you're missing the necessay pg_clog
files.  You can make it sort-of-work by doing a VACUUM FREEZE and a
CHECKPOINT on the database before taking the snapshot.  However, you'd
still be missing the entries in shared catalogs.  The only one you've
recreated is the one on pg_database, but there are more.

  

--- 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.
    

Attaching to another database is not supported at all.