Logical decoding is a superset of the existing standby protocol. Hence after decoding changes from WAL an output plugin can shape it in any kind of ways, making for example possible to have a plugin that does the exact revert operation of the decoding portion a PostgreSQL server instancedid by reproducing similar WAL records that could be replayed similarly to a standby. Not sure if this would be actually useful, but well that’s possible…
One of the great things in this new 9.4 infrastructure is then the possibility to have a client receiving the logical changes able to let the PostgreSQL instance decoding the changes think that what receives the changes is itself a standby by having it using the replication protocol that vanilla streaming standbys use and is present since 9.0 for the asynchronous node, and 9,1 for the “synchronous” node (having master node wait for the commit confirmation from a standby) guaranteeing no loss of data after a commit. There are three things that are important to be aware of on the receiver side when looking for such a behavior with a logical receiver.
First, using the [replication protocol] (https://www.postgresql.org/docs/devel/static/protocol-replication.html) is necessary to let the master node think that what is connected is a kind of standby. Extracting logical changes is possible as well with the set of dedicated functions called pg_logical_slot_peek_changes and pg_logical_slot_get_changes (and their binary equivalent), but do not count on that if you want to wait from the receiver that a change has been committed (abuse of term as this depends on how this receiver consumes those changes).
Second, a master node classifies the standbys by priority using the parameter synchronous_standby_names, the synchronous standby being the lowest one strictly higher than zero. So when using a receiver, be sure that it connects to the master node using application_name to give to it a proper identifier, resulting in a connection string similar to that with a minimum configuration:
dbname=my_logical_database replication=database application_name=my_receiver
Finally, be sure that the receiver sends feedback to the master node. This has been already mentioned in a [previous post] (/postgresql-2/postgres-9-4-feature-highlight-logical-replication-receiver/). In the case of a receiver consuming logical information, this is important of course to release information on the replication slot being used, so as pg_xlog partition does not bloat on the master. But as well this is essential to let the master know that there is no delta in the changes being replayed, making the node able to really perform synchronous replication (similarly to vanilla standbys, this depends as well on the setting value of synchronous_commit).
Using the output plugin called [decoder_raw] (https://github.com/michaelpq/pg_plugins/tree/master/decoder_raw) coupled with the receiver [receiver_raw] (https://github.com/michaelpq/pg_plugins/tree/master/receiver_raw) (that is simply a [background worker] (https://www.postgresql.org/docs/9.3/static/bgworker.html) able to fetch the changes decoded and apply on the database this worker is connected to the raw queries generated by the output plugin) presented a couple of months back on this blog, it is actually possible to replication DML queries from one master node to another, as long as the schema is stable and similar (somebody mentioned me as well that receiver_raw could be used on the same master generating the changes on a different database but that’s more spectacular to do it on two different nodes). The following table is created on both master nodes, both running on local host listening to ports 5432 and 5433:
=# CREATE TABLE replicated_table (time timestamp default now(), quote text);
CREATE TABLE
As there is no cheating, both nodes are indeed not in recovery:
$ psql -At -c 'SELECT pg_is_in_recovery()' -p 5432
f
$ psql -At -c 'SELECT pg_is_in_recovery()' -p 5433
f
First node has a logical replication slot using decoder_raw, with a “synchronous” standby called receiver_raw:
$ psql -p 5432 -c 'SELECT application_name, sync_state FROM pg_stat_replication'
application_name | sync_state
------------------+------------
receiver_raw | sync
(1 row)
$ psql -At -p 5432 -c 'SHOW synchronous_standby_names'
receiver_raw
Also the second node runs a background worker able to fetch and apply the changes:
$ ps x | grep 42787
42787 ?? Ss 0:00.06 postgres: bgworker: receiver_raw
Note as well the connection string used by the background worker on the second node to connect to the first node:
$ psql -At -p 5433 -c 'SHOW receiver_raw.conn_string'
replication=database dbname=my_db application_name=receiver_raw
With all those things in place, changes get replicated, and the second node is thought as in sync:
$ psql -At -p 5433 -c "SELECT pid FROM pg_stat_activity WHERE state = 'idle'"
42787
$ psql -p 5433 -c "SELECT * FROM replicated_table"
time | quote
----------------------------+------------------------
2014-10-25 18:12:19.923825 | Tuple data from node 1
(1 row)
$ psql -p 5432 -c "SELECT * FROM replicated_table"
time | quote
----------------------------+------------------------
2014-10-25 18:12:19.923825 | Tuple data from node 1
(1 row)
Note as well that making it crash-safe, aka by reporting to the master the correct WAL position that a given client has really fsync’d or written using respectively flush_position and write_position is as well an essential thing to take into account or the master node holding logical slot information would simply release it, losing it in the wild for the client receiving the changes that did not consume those changes properly if it has failed for a reason or another.
Note that this has been covered as well, not in so much details though, in the presentation about logical decoding that has been done in Madrid for Postgres Europe 2014 and Chicago for PG Open 2014, with the slides of the presentation being available here.
For people show attended any of those conferences, be sure to have a look as well at the PostgreSQL wiki on the following pages dedicated to [Postgres Europe 2014] (https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2014) and Postgres Open 2014 where all the talk slides should be available. If you were a speaker, be sure as well to provide an URL of where your presentation slides are.