Re: Restoring a database from a file system snapshot

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

Because you told it to. If you want everything on Z:\postgresql you just
initdb that location and point PG at that location (or just install
there). Tablespaces let you store sets of tables/indexes on specific
disks (well, filesystem mount-points).

> How is that even possible? Are the
> transaction ID numbers shared across databases too?

Yes. The PG term for this is a database "cluster" - an installation that
shares transaction logs, ids, users.

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

Yes. A dump/restore is probably the simplest way of doing this.

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

Mount a filesystem at the desired point in the directory tree, or just
use soft-links. Which is how tablespaces are implemented, as it happens.

--
Richard Huxton
Archonet Ltd