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 Singer.
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); CREATE TABLE $ 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'); INSERT 0 1 $ 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'); INSERT 0 1 $ BEGIN; BEGIN *$ UPDATE z SET whatever = 'new value' WHERE id = 1; UPDATE 1 *$ UPDATE z SET id = -2000 WHERE id = -1000; UPDATE 1 *$ commit; COMMIT $ DELETE FROM z WHERE id < 0; DELETE 1 $ 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…
Could this be used for change data capture?
Does it work on tables without primary keys?
@Andres: well, no. But:
1. it’s just *test* decoder
2. why do you even have table without primary key ?!
@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.
Hi,
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 http://www.postgresql.org/docs/devel/static/sql-altertable.html
@Andres Freund: Thanks for the answer.
I really look forward to see external projects starting to use the building blocks of logical replication.
@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.
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?
@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”.
I’ve been waiting for this for ages. Anything to get rid of slony. 😉
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.
Thanks for your post !
Can it be used to replicate data to other databases, mssql for example ?
@Mah:
theoretically – sure, you’d just need to write some code that would decode what’s needed, and apply it to the other db.