Обсуждение: [ADMIN] Replication with a third-party plug-in
We're adopting a third-party plug-in (Jchem-psql from ChemAxon), an excellent product that does chemical queries. Because Jchem is written in Java, the Postgres extension is implemented as a separate service; under the covers I suspect it's a bit like a foreign data wrapper.
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.
Jchem-psql keeps its chemical-index data in its own section of the file system (typically /var/lib/jchem-psql/store). We considered using rsync to keep the master and slave synced, but that's not transactional. It might be OK for a warm standby, though.
So here's my question (finally!): If a server is in recovery mode, either as streaming replication or consuming WAL files, will each SQL operation that is executed on the master also trigger communications to the third-party plug-in on the slave? Here's the specific scenario that I'm hoping will work (we're using pgbackrest):
- Create a backup and WAL stream to an archive with pgbackrest.
- Restore that backup to a standby (slave) server (it will use WAL shipping, not streaming replication), but don't start it yet.
- Stop postgres and the Jchem-psql service on the master.
- Rsync the Jchem-psql files from the master to the slave.
- Start postgres and the Jchem-psql service on the slave.
- Start postgres and the Jchem-psql service on the master.
If the slave Postgres instance actually calls the Jchem-psql service when replaying WAL files, this should work, right?
Thanks,
Craig
On Fri, Nov 17, 2017 at 7:53 AM, Craig James <cjames@emolecules.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.