The second huge feature coming in PostgreSQL 9.4 with jsonb is called
logical decoding. In
short, it is a new plugin facility that can be used to decode changes
that happen on a database and stream them to external sources. It can
be used for many things like replication, auditing or even online upgrade
solutions.
Logical decoding has been introduced in the core of PostgreSQL
incrementally with a set of features that could roughly be listed as follows:
Logical replication slots, similar to physical slots except
that they are attached to a single database.
WAL level "logical" in wal_level, level of WAL generated by server
to be able to decode changes to the database into a coherent format.
Creation of a SQL interface to view the changes of a replication slot.
Extension of the replication protocol to support logical replication
(with particularly the possibility to provide a database name in parameter
"replication" of a connection string)
Addition of REPLICA IDENTITY, a table
parameter to modify how updated and deleted tuple data is written to WAL.
Then, two new utilities are present to help users to grab an understanding
of how things work:
test_decoding, providing an example of output plugin for decoding.
pg_recvlogical, an example of utility that can be used to receive
changes from a logical replication slot.
Logical decoding introduces a lot of new concepts and features, making it
impossible to write everything in a single post. Remember however that
it is possible to customize the decoding plugin, or in this post
test_decoding, and the remote source receiving the changes, pg_recvlogical
in the case of this post. So for now, using what Postgres core offers, let's
see how to simply set up logical replication. First, be sure that the
following parameters are set in postgresql.conf:
max_replication_slots needs to be at least 1. test_decoding needs to be
installed as well on your server. In order to work, logical replicaton
needs first a logical replication slot, which can be created using
pg_create_logical_replication_slot like that. Providing a plugin
name is mandatory:
xlog_position corresponds to the XLOG position where logical decoding
starts. After its creation, it is listed in the system view
pg_replication_slots, it will be marked as active once a remote source
using a replication connection starts the logical decoding with this slot:
The next step is to enable the utility consuming the decoded changes,
pg_recvlogical, like that for example:
Note that you need to connect to the database where the replication slot
has been created, in my case "postgres". This command will also make all
the decoded changes to be printed in stdout.
pg_recvlogical provides as well options to create and drop slots, this
is not really mandatory but it is rather handy when testing logical
decoding on multiple slots.
OK, now you will be able to see the logical changes received by
pg_recvlogical. Logical decoding cannot replicate DDL changes,
so a simple DDL like that:
Results in that at the logical data receiver level (in the case of
test_decoding of course):
Now here is how an insertion is decoded:
Similarly to physical slots, as long as a receiver has not consumed the
changes of a slot, WAL files will be retained in pg_xlog, so be careful
that you pg_xlog partition or disk does not get completely filled up!
This post shows only the top of the iceberg of this feature, and there
are really a lot of things to tell about it. So stay tuned! For the time
being, feel free to have a look at this very promising infrastructure.