Re: Replication with a third-party plug-in

On Fri, Nov 17, 2017 at 7:53 AM, Craig James <cjames(at)emolecules(dot)com> wrote:

> The problem is with replication; it's a sort of chicken-and-egg problem.
> Installation of Jchem-psql requires some transactions to happen, but on a
> standby server, transactions aren't allowed. So you can't install
> Jchem-psql on the slave server. That means that a hot-standby server
> doesn't work, because even if the Postgres database is replicated, chemical
> queries can't be executed. Worse, if I need to do a failover to the standby
> server, I have to install Jchem-psql after the failover, then recreate all
> of the chemical indexes, which takes many hours.
>

An alternate solution could potentially be to use logical replication if
you are on 9.4+. The "replica", which is in fact another master database
getting changes fed to it by the logical replication process, can have all
the changes from the master fed to it in the form of queries which will
trigger your Jchem-psql tool.