[ome-users] pgpool-II with OMERO?

[Roger Leigh]

On 02/05/2012 10:16, Harri Jäälinoja wrote:
> Hi,
>
> On 01/05/12 19:53, Roger Leigh wrote:
>>>
>>> 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.
>
> this sounds good. Of course there are trade-offs, so we have to discuss
> first a bit. Also with the IT department, it seems that in the standard
> RedHat repositories we have, PostgreSQL 8.4 is the latest available.

While I think 9.1 with hot standby (WAL shipping) is the preferred
solution here, if you do have to use 8.4, pgpool-II does work with
PostgreSQL 8.4.  There are some important constraints to consider when
setting it up, such as not mixing database versions or hardware or
operating system:
   http://www.pgpool.net/mediawiki/index.php/FAQ

>> 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.
>
> From the point of view of OMERO, is there any reason we would need
> this? Or put differently, which OMERO features will fail in case the
> user is connected to the hot-standby database that cannot perform
> certain actions (as listed in the Hot Standby documentation)?
>
> If it is only data import that will fail, I think this is acceptable.
> Because that is something that is easy to repeat after the master db is
> back.
>
> The hot standby documentation also says:
> "Users will be able to tell whether their session is read-only by
> issuing SHOW transaction_read_only."
> Can OMERO check if this is the case, and give an approroate error
> message to the user?

I don't think this really matters.  The OMERO server should only use the
master database, and only ever switch to the hot standby if it falls
over.  So it doesn't need to worry about using a read only database,
since it will always be using the writable one.  In the case of
pgpool-II, both are writable, but you will probably only want to use one
at once unless you arrange both to be used using e.g. round-robin DNS.
OMERO itself won't need to know or care about the replication set up you
use, since it should be entirely transparent if set up correctly.


Regards,
Roger

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