Re: Postgres failover

On Mon, Dec 11, 2000 at 12:55:30PM -0800, Peter Schmidt wrote:
> PostgreSQL v7.0.2
>
> My company is looking for a way to implement failover w/Postgres.
>
> I've determined that two postmasters running on different machines (FreeBSD)
> can share a single $PGDATA directory(NFS mount) as long as only one
> postmaster is running at a time. Originally I thought I might be able to use
> postmaster.pid to lock out the second postmaster, but the pid file is
> overwritten by the second postmaster when it starts. I've tested a similar
> approach using a "lockfile" i.e. create a file in $PGDATA and keep it locked
> for the duration of postmaster. If a second postmaster tries to start up, it
> can't because the lockfile is locked. When postmaster exits, the file is
> unlinked, and the second postmaster is able to start.
>
> Anyone have any thoughts or experiences with this? Comments?

What problem is this supposed to solve?

I mean, you plan to have 3 servers, one with the data and two running
postgresql? and NFS protocol in between?

This means that your whole system will fail if either your NFS server
fails, or both postgresql servers fail, or the network in between fails.
In other words the chance of the system failing is _higher_ than with a
single server!
(unless the chance of a NFS server failing is lower than the chance of a
postgresql server failing - I don't see any reason to assume that)

So, to improve your availability, you need to either duplicate your
storage, or make it less likely to fail.

Duplication can be done with a system like drdb (software), or with
hardware (raid-controllers).

Making it less likely to fail can be done by removing the NFS server,
and instead have the two postgresql server connect to a dual-host RAID
system. Middle to high end raid-systems have failover internally (two
SCSI-buses, two raid-controllers, and so on), so they are far less
likely to fail than a ordinary NFS-server. Another benefit is that
performance will be much better than via NFS.

BUT, a problem with all theese approaches is that they assume your
dataset is ok. When a postgresql server fails, it may leave it's
datafiles in an inconsistent state. The only way (I know) around this is
to duplicate the SQL-queries to two sql-servers. There was an
interesting thread about this on this list a couple of weeks ago (maybe
a little more).

--
Ragnar Kjørstad
Big Storage