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