Re: Restoring a database from a file system snapshot

On Wed, Aug 27, 2008 at 6:16 PM, William Garrison <postgres(at)mobydisk(dot)com> wrote:
>
> 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?

Yes it's possible, and it's how pgsql does it. Yes txid are based on
a common count amongst the dbs in a cluster.

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

Really? I'm not so sure. By having a local mirror set host the db
pg_xlog type files you can get good performance in bursts even if the
SAN is lagging behind a bit.

> (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, but yeah that's exactly what I did tonight. We have a
mirror set where the main pg database lives, then stop the db, copy
out base to another drive, softlink it into the place of the old base
and restart the db. Now the pg_xlog and such are on the mirror set
and the main data store is on a separate array (Tonight it was a
RAID-5 for a reporting server, last week it was a 12 disk RAID-10 for
the big storage / transactional server)

By putting the pg_xlog, which is written sequentially on the mirror
set, I ensure that it's not competing with my random writes on the big
RAID-10.