Re: Restoring a database from a file system snapshot

William Garrison wrote:
> 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?

The common use-case for tablespaces is performance. You want to move
your hot tables off to faster storage, or move your cold tables off to
slower (and cheaper!) storage.

> 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

Um, you might need to read up on your SQL Server docs there. Because you
specify the location for the transaction log completely separate from
the data files in SQL Server as well.

You are correct, however, in that SQL Server has one set of transaction
logs for each database, whereas PostgreSQL has one for the whole cluster.

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

Yes.

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

You should install PostgreSQL on C:, and have the complete data
directory on the SAN. You can set this from the MSI installer, or you
can manually move the directory and then change the path in the service
startup command (may require the use of regedit). (obviously with
postgresql stopped)

> (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?

Soft-link, not hard-link. But you can do this on Windows as well, if
that is easier for you. See http://support.microsoft.com/kb/205524 for
some hints. There are also tools from sysinternals, iirc.

//Magnus