Re: Restoring a database from a file system snapshot

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