On Fri, Nov 17, 2017 at 7:53 AM, Craig James <cjames@xxxxxxxxxxxxxx> 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.
