March 6th, 2014 by depesz | Tags: , , , , | 13 comments »
Did it help? If yes - maybe you can help me?

On 3rd of March, Robert Haas committed patch:

Introduce logical decoding.
This feature, building on previous commits, allows the write-ahead log
stream to be decoded into a series of logical changes; that is,
inserts, updates, and deletes and the transactions which contain them.
It is capable of handling decoding even across changes to the schema
of the effected tables.  The output format is controlled by a
so-called "output plugin"; an example is included.  To make use of
this in a real replication system, the output plugin will need to be
modified to produce output in the format appropriate to that system,
and to perform filtering.
Currently, information can be extracted from the logical decoding
system only via SQL; future commits will add the ability to stream
changes via walsender.
Andres Freund, with review and other contributions from many other
people, including รlvaro Herrera, Abhijit Menon-Sen, Peter Gheogegan,
Kevin Grittner, Robert Haas, Heikki Linnakangas, Fujii Masao, Abhijit
Menon-Sen, Michael Paquier, Simon Riggs, Craig Ringer, and Steve

This is BIG

Some time ago I wrote about logical wal_level, and I mentioned that this is one of steps to get to Logical Replication.

Now, with this patch – we're getting really, really close.

Let's see what's going on, and what can be done with it.

Setting up is not really complex, but docs need fixing.

First, you need to setup your PostgreSQL to even allow logical replication. This means two changes in postgresql.conf, and restart:

wal_level = logical
max_replication_slots = 3

It would be enough to have “1" as max_replication_slots, but I like to have more than I need ๐Ÿ™‚

Anyway. After it has been done, I connect to pg, and issue:

SELECT 'init' FROM pg_create_logical_replication_slot('depesz', 'test_decoding');

This creates the slot that will contain data. It will be named depesz, obviously.

From this moment on all changes will be available in here.

Let's see:

$ create table z  (id serial primary key, whatever text);
$ SELECT * FROM pg_logical_slot_get_changes('depesz', null, null, 'include-xids', '0');
  location  | xid |  data  
 0/52042D60 | 930 | BEGIN
 0/5204A540 | 930 | COMMIT
(2 rows)

Nothing really cool – just information that transaction did happen (it could have happened in another connection – it still works.

What I see also is that “create table" didn't get “caught". No worries – we're in here for DML, and not DDL ๐Ÿ™‚

Next test – let's insert something:

$ insert into z (id, whatever) values (-1000, 'depesz');
$ SELECT * FROM pg_logical_slot_get_changes('depesz', null, null, 'include-xids', '0');
  location  | xid |                               data                                
 0/5204A650 | 931 | BEGIN
 0/5204A650 | 931 | table public.z: INSERT: id[integer]:-1000 whatever[text]:'depesz'
 0/5204A758 | 931 | COMMIT
(3 rows)

Looks promising. Now, let's see how it will work out with updates, inserts with not-all-columns, and deletes:

$ insert into z (whatever) values ('row2');
$ begin;
*$ update z set whatever = 'new value' where id = 1;
*$ update z set id = -2000 where id = -1000;
*$ commit;
$ delete from z where id < 0;
$ SELECT * FROM pg_logical_slot_get_changes('depesz', null, null, 'include-xids', '0');
  location  | xid |                                                  data                                                   
 0/5204A858 | 932 | BEGIN
 0/5204A858 | 932 | table public.z: INSERT: id[integer]:1 whatever[text]:'row2'
 0/5204A928 | 932 | COMMIT
 0/5204A960 | 933 | BEGIN
 0/5204A960 | 933 | table public.z: UPDATE: id[integer]:1 whatever[text]:'new value'
 0/5204AA00 | 933 | table public.z: UPDATE: old-key: id[integer]:-1000 new-tuple: id[integer]:-2000 whatever[text]:'depesz'
 0/5204AAF0 | 933 | COMMIT
 0/5204AAF0 | 934 | BEGIN
 0/5204AAF0 | 934 | table public.z: DELETE: id[integer]:-2000
 0/5204AB78 | 934 | COMMIT
(10 rows)

This is very cool. Insert got proper value extracted. Update has new rows, and if I change primary key column – it is shown there. And delete is nice too.

Actually – with just a bit of logic, you could get output from test_decoding and convert it to real, sensible, SQL queries.

Andres – you're way above great for this. All the guys that reviewed and helped – thank you too.

Logical replication is so much closer now…

  1. 13 comments

  2. # Bob Henkel
    Mar 7, 2014

    Could this be used for change data capture?

  3. # Andreas
    Mar 7, 2014

    Does it work on tables without primary keys?

  4. Mar 7, 2014

    @Andres: well, no. But:

    1. it’s just *test* decoder
    2. why do you even have table without primary key ?!

  5. # Andreas
    Mar 7, 2014

    @depesz: I do not think I have any. I was just wondering how and if it would work on tables without PKs since most logical replication solutions require PKs.

  6. # Andres Freund
    Mar 7, 2014


    Depesz, thanks for the flowers and the explanations. There’s a larger documentation patch pending, that gives a lot more explanation.

    Bob, yes, it essentially *is* change capture.

    Andreas, yes, you can use it for tables without a pkey. INSERT is replicated for all tables without a pkey, the old key is only replicated for updates and deletes if an alternative replica identity has been configured. Check

  7. # Andreas
    Mar 7, 2014

    @Andres Freund: Thanks for the answer.

    I really look forward to see external projects starting to use the building blocks of logical replication.

  8. # Bob Henkel
    Mar 9, 2014

    @Andres Sorry I was excited and posted a bit brief reaction. I assume this would meet the needs for getting changes down into a data warehouse.Microsoft SQL Server has a feature in 2012 they call CDC that is advertised as the ideal means to capture change vs the standard method of timestamp or dml triggers.

  9. # Gunnar "Nick" Bluth
    Mar 10, 2014

    That’s pretty cool, as someone recently mentioned it might not make it in…

    Out of curiosity: did you try things like now(), random(), stored procedure calls and the like?

  10. Mar 10, 2014

    @Gunnar: I didn’t, but given that the value from sequence got properly propagated, I don’t doubt that values from random or now() will too. As far as I can see, it just gets real values, and not “what was in the query”.

  11. Mar 18, 2014

    I’ve been waiting for this for ages. Anything to get rid of slony. ๐Ÿ˜‰

  12. # Lennie
    Jan 20, 2015

    Actually, I’m not so sure you want to get rid of Slony.

    The original developers of this are the BDR project. But Slony is actually the first developers helping test and adopt this code to make it generic and tested.

    I wouldn’t be surprised Slony will probably be the first project to support this after 9.4 is released.

  13. # Mah
    Oct 15, 2015

    Thanks for your post !
    Can it be used to replicate data to other databases, mssql for example ?

  14. Oct 15, 2015

    theoretically – sure, you’d just need to write some code that would decode what’s needed, and apply it to the other db.

Leave a comment