I have an existing Postgres 9.3 server acting as a read-only replica using the built-in streaming replication.
I need to replicate a single table from a database on that server to another physical server.
I've tried using bucardo but it doesn't like talking to read-only databases.
Is there a way to trigger an update on the remote server from the replica?
Best Answer
You will need to replicate the table from the read/write master.
PostgreSQL's built-in physical streaming replication can only replicate the whole database server instance.
The replication systems that support single-table replication require the ability to use a trigger to write a record of changes. This can only be done on the upstream read/write master.
The BDR project plans to deliver a unidirectional version that can stream changes using only the transaction logs, with individual table selection. That would meet your needs, but it won't be ready for a few months at best, possibly longer. Keep an eye out on the PostgreSQL mailing lists and the above wiki for discussion of "UDR" if you're interested.