Thread: high (or at least improved) availability and failover

This thread been saved for the 8.1 release:

    http:/momjian.postgresql.org/cgi-bin/pgpatches2

---------------------------------------------------------------------------

Iain wrote:
> HI,
>
> I've been looking into options for improving the availability of postgres
> servers beyond what you get with a monolithic server and RAID. These are, as
> far as possible "cheap" options and therefore don't include use of shared
> storage and all the good options those systems have.
>
> Here is a very rough summary of my thoughts so far. I havn't actually tried
> any of this yet, so if anyone has any thoughts, comments, additions I'd be
> glad to hear from you.
>
> regards
> iain
>
> Configurations for HA
> =====================
>
> Pgpool Only
> -----------
> Pgpool can detect the failure of a server and failover automatically.
>
> Re-syncing the DBs after a failure will require the databases to be offline
> for a file system copy.
>
> Should pgpool run on the db servers or a separate machine(s)?
>
> Performance impact of using the replication facility? Deadlock possibility?
>
> If the machine that pgpool is running on fails, need manual intervention to
> start pgpool on another machine.
>
> Can only manage 2 servers at most.
>
>
> Pgpool and Heartbeat
> -----------------
> Similar to pgpool only, with the addition that pgpool itself can be failed
> over automatically.
>
>
> Heartbeat and DRDB
> ---------------
> Replication is done at the block level by DRDB.
>
> The database cannot be running on the standby server while it is being
> replicated from the master.
>
> Automatic failover is possible as Heartbeat will start the database server
> and switch to the standby if the master fails.
>
> A full re-sync of the DBs after a failure doesn't require the master to be
> offline.
>
> Failure ofthe standby server typically doesn't require a full re-sync.
>
> Can't do load balancing.
>
> No particular need for pgpool as heartbeat will manage the aliased IP
> address
>
>
> Slony-I only
> ------------
> Relies on triggers in the database, and some administration of the
> replication configuration.
>
> Monitors replicated databases and performs automatic failover.
>
> When a failed server is brought back online it will automatically start
> catching up with the other servers.
>
> When the master fails, clients can reconnect to the new master without
> changing the IP address?
>
>
> Slony-I and pgpool
> ---------------
> All the features of Slony-I
>
> Some load balancing of SELECT SQL is possible
>
> Need manual intervention if pgpool fails.
>
>
> Slony-I and pgpool and Heartbeat
> ----------------------------
> All the features of Slony-I with pgpool
>
> Use heartbeat to monitor the machines running pgpool and switch over
> automatically if one fails.
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073