[ome-users] pgpool-II with OMERO?

[Roger Leigh]

On 01/05/2012 14:20, Josh Moore wrote:
>
> On Apr 25, 2012, at 3:48 PM, Harri Jäälinoja wrote:
>>
>> Hi all,
>
> Hi Harri,
>
>> we started to think that it would be nice to make a high-availability set up for the PostgrSQL database that OMERO is using, to make sure that no data is lost. Is anyone out there doing something like that? The first tool I found is pgpool-II, is anybody using that?
>
> I don't want to dissuade anyone else out there from responding, but we'd suggest first you look at the newer versions of PG and it's built in log shipping, e.g.
>
>    http://www.postgresql.org/docs/9.1/static/hot-standby.html

I think this is probably the best solution if the primary concern is
high availability.  Being built into the core, it's also the most
performant, and also the most simple to configure.  However, if you want
to be running (read only) queries on the hot standby, there is of course
the chance that the data will be outdated compared with the master due
to there being a delay shipping over the WAL.  And you can only do
writes on the master.

A solution such as pgpool-II would give you the ability to do both read
and write on either node, due to the two-phase commit.  However, there
is a performance cost, and additional complexity in the replication.
Unless you definitely need to do load balancing of writes in addition to
having the high availability of multiple replicas, this is probably both
more complex and slower.

If you follow the postgresql-hackers (devel) list, there's some
interesting discussion about moving more sophisticated replication into
the core in addition to WAL shipping.  But certainly not in time for
9.2; it's only in the early design stage.

A list of the commonly available clustering solutions available to use
the PostgreSQL is here:
http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling


Regards,
Roger

The University of Dundee is a registered Scottish Charity, No: SC015096