On 20th of January, Peter Eisentraut committed patch:
Logical replication - Add PUBLICATION catalogs and DDL - Add SUBSCRIPTION catalog and DDL - Define logical replication protocol and output plugin - Add logical replication workers From: Petr Jelinek Reviewed-by: Steve Singer Reviewed-by: Andres Freund Reviewed-by: Erik Rijkers Reviewed-by: Peter Eisentraut
Despite short commit message, this is huge patch. It modified (or created) 119 files. And whole patch is almost 500kB!
For testing purposes, I figured I'll setup the simplest setup: master + slave. Master is on port 5100, and slave on 5101. I will keep prompts showing port, so it will be clear what is ran where. For example:
(depesz@[LOCAL]:5100) 10:57:03 [depesz] $ SELECT version();
This shows that I am ran the select on master database: [local]:5100.
First, obviously, I will need some table. It doesn't have to be complicated, so:
(depesz@[LOCAL]:5100) 10:59:47 [depesz] $ CREATE TABLE test ( id serial PRIMARY KEY, whatever text, when_ts timestamptz ); CREATE TABLE
To make things a bit more interesting, let's create some random data before doing anything with replication itself:
(depesz@[LOCAL]:5100) 11:08:41 [depesz] $ INSERT INTO test (whatever, when_ts) SELECT 'whatever #' || i, now() - '1 year'::INTERVAL * random() FROM generate_series(1,5) i ORDER BY random(); INSERT 0 5 (depesz@[LOCAL]:5100) 11:09:15 [depesz] $ SELECT * FROM test; id | whatever | when_ts ----+-------------+------------------------------- 1 | whatever #1 | 2016-12-18 09:05:36.290453+01 2 | whatever #3 | 2016-10-09 02:51:31.125653+02 3 | whatever #2 | 2016-03-30 10:54:04.456853+02 4 | whatever #5 | 2016-03-21 08:06:02.268053+01 5 | whatever #4 | 2016-02-24 23:12:46.620053+01 (5 ROWS)
Now, I need to create so called publication. This is basically a list of tables (other objects are currently not supported) that will be replicated when a subscriber will fetch data for given publication.
(depesz@[LOCAL]:5100) 11:00:02 [depesz] $ CREATE PUBLICATION test_pub FOR TABLE test; CREATE PUBLICATION
Publications can be limited to specific operations (insert/update/delete), but by default it includes all operations. Important note is that you can't have different set of operations for different tables in single publication.
If you'd need it, just create multiple publications – each table can be in as many publications as needed.
I could also do:
$ CREATE PUBLICATION test_pub FOR ALL TABLES;
this would add all tables in current database to the publication. It would also automatically add all tables created in future. Since I prefer to be explicit, let's use listed tables for now.
Now, it's time to actually subscribe to replication. Before I can do it, though, I need to create all tables that the subscription contains on slave:
(depesz@[LOCAL]:5101) 11:15:18 [depesz] $ CREATE TABLE test ( id serial PRIMARY KEY, whatever text, when_ts timestamptz ); CREATE TABLE
With this in place, I can create subscription:
(depesz@[LOCAL]:5101) 11:22:39 [depesz] $ CREATE SUBSCRIPTION test_sub CONNECTION 'host=127.0.0.1 port=5100 dbname=depesz user=postgres' PUBLICATION test_pub; NOTICE: created replication slot "test_sub" ON publisher CREATE SUBSCRIPTION
A moment later:
(depesz@[LOCAL]:5101) 11:23:01 [depesz] $ SELECT * FROM test; id | whatever | when_ts ----+----------+--------- (0 ROWS)
Hmm, original rows have not been replicated. But what about new ones?
(depesz@[LOCAL]:5100) 11:22:32 [depesz] $ INSERT INTO test (whatever, when_ts) VALUES ('test after sub', now()); INSERT 0 1 (depesz@[LOCAL]:5101) 11:23:25 [depesz] $ SELECT * FROM test; id | whatever | when_ts ----+----------------+------------------------------ 6 | test after sub | 2017-02-06 11:23:30.26246+01 (1 ROW)
OK. New rows have been replicated. But what about initial sync?
Let's try something:
(depesz@[LOCAL]:5101) 11:40:34 [depesz] $ DROP subscription test_sub; NOTICE: dropped replication slot "test_sub" ON publisher DROP SUBSCRIPTION (depesz@[LOCAL]:5101) 11:45:06 [depesz] $ TRUNCATE test; TRUNCATE TABLE
Now, let's restore test table on master to initial state:
(depesz@[LOCAL]:5100) 11:40:32 [depesz] $ DELETE FROM test WHERE id > 5; DELETE 1 (depesz@[LOCAL]:5100) 11:45:44 [depesz] $ SELECT SETVAL('test_id_seq', 5); SETVAL -------- 5 (1 ROW)
And let's try to play with enable/disable subscription:
(depesz@[LOCAL]:5101) 11:46:26 [depesz] $ CREATE SUBSCRIPTION test_sub CONNECTION 'host=127.0.0.1 port=5100 dbname=depesz user=postgres' PUBLICATION test_pub WITH (DISABLED); NOTICE: created replication slot "test_sub" ON publisher CREATE SUBSCRIPTION
Now, with replication setup, let's try to load snapshot of data using pg_dump:
=$ pg_dump -p 5100 -a -t test depesz | psql -p 5101 -qX -d depesz SETVAL -------- 5 (1 ROW)
Now, let's assume that in mean time some things have changed:
(depesz@[LOCAL]:5100) 11:50:57 [depesz] $ INSERT INTO test (whatever, when_ts) VALUES ('test after disabled sub', now()); INSERT 0 1 (depesz@[LOCAL]:5100) 11:51:05 [depesz] $ DELETE FROM test WHERE id = 3; DELETE 1 (depesz@[LOCAL]:5100) 11:51:09 [depesz] $ UPDATE test SET whatever = 'updated' WHERE id = 2; UPDATE 1
And now, let's try to enable subscription:
(depesz@[LOCAL]:5101) 11:49:28 [depesz] $ ALTER subscription test_sub enable; ALTER SUBSCRIPTION
After a while:
(depesz@[LOCAL]:5101) 11:52:06 [depesz $ SELECT * FROM test; id | whatever | when_ts ----+-------------------------+------------------------------- 1 | whatever #1 | 2016-12-18 09:05:36.290453+01 4 | whatever #5 | 2016-03-21 08:06:02.268053+01 5 | whatever #4 | 2016-02-24 23:12:46.620053+01 6 | test after disabled sub | 2017-02-06 11:51:05.110897+01 2 | updated | 2016-10-09 02:51:31.125653+02 (5 ROWS)
Looks like it replicated changes since dump. Will it replicate new changes?
(depesz@[LOCAL]:5100) 11:53:40 [depesz] $ INSERT INTO test (whatever, when_ts) VALUES ('test after enabling sub', now()); INSERT 0 1
and on slave:
(depesz@[LOCAL]:5101) 11:53:42 [depesz] $ SELECT * FROM test; id | whatever | when_ts ----+-------------------------+------------------------------- 1 | whatever #1 | 2016-12-18 09:05:36.290453+01 4 | whatever #5 | 2016-03-21 08:06:02.268053+01 5 | whatever #4 | 2016-02-24 23:12:46.620053+01 6 | test after disabled sub | 2017-02-06 11:51:05.110897+01 2 | updated | 2016-10-09 02:51:31.125653+02 7 | test after enabling sub | 2017-02-06 11:53:40.079853+01 (6 ROWS)
That looks OK. But taking dump will take a moment. What will happen if new rows, or other changes, will happen after creation of subcription, but before getting data dump?
Let's cleanup, and retry:
(depesz@[LOCAL]:5101) 11:53:42 [depesz] $ DROP subscription test_sub; NOTICE: dropped replication slot "test_sub" ON publisher DROP SUBSCRIPTION (depesz@[LOCAL]:5101) 11:55:25 [depesz] $ TRUNCATE test; TRUNCATE TABLE
(depesz@[LOCAL]:5100) 11:53:40 [depesz] $ DROP publication test_pub; DROP PUBLICATION (depesz@[LOCAL]:5100) 11:55:51 [depesz] $ TRUNCATE test; TRUNCATE TABLE (depesz@[LOCAL]:5100) 11:56:20 [depesz] $ ALTER SEQUENCE test_id_seq restart; ALTER SEQUENCE
Now, re-insert base data:
(depesz@[LOCAL]:5100) 11:56:23 [depesz] $ INSERT INTO test (whatever, when_ts) SELECT 'whatever #' || i, now() - '1 year'::INTERVAL * random() FROM generate_series(1,5) i ORDER BY random(); INSERT 0 5 (depesz@[LOCAL]:5100) 11:57:19 [depesz] $ SELECT * FROM test; id | whatever | when_ts ----+-------------+------------------------------- 1 | whatever #3 | 2017-01-23 14:33:14.398735+01 2 | whatever #2 | 2016-12-26 09:43:36.853135+01 3 | whatever #1 | 2016-11-30 13:54:18.401935+01 4 | whatever #4 | 2016-08-29 16:04:23.950735+02 5 | whatever #5 | 2016-07-01 12:08:05.710735+02 (5 ROWS)
With this in place, I can create publication and subscription (for now disabled):
(depesz@[LOCAL]:5100) 11:58:08 [depesz] $ CREATE PUBLICATION test_pub FOR TABLE test; CREATE PUBLICATION (depesz@[LOCAL]:5101) 11:55:28 [depesz] $ CREATE SUBSCRIPTION test_sub CONNECTION 'host=127.0.0.1 port=5100 dbname=depesz user=postgres' PUBLICATION test_pub WITH (DISABLED); NOTICE: created replication slot "test_sub" ON publisher CREATE SUBSCRIPTION
Now, let's do some changes before making dump:
(depesz@[LOCAL]:5100) 11:58:14 [depesz] $ DELETE FROM test WHERE id = 1; DELETE 1 (depesz@[LOCAL]:5100) 11:59:09 [depesz] $ INSERT INTO test (whatever, when_ts) VALUES ('test after disabled sub', now()); INSERT 0 1 (depesz@[LOCAL]:5100) 11:59:23 [depesz] $ UPDATE test SET whatever = 'updated' WHERE id = 2; UPDATE 1 (depesz@[LOCAL]:5100) 11:59:36 [depesz] $ SELECT * FROM test; id | whatever | when_ts ----+-------------------------+------------------------------- 3 | whatever #1 | 2016-11-30 13:54:18.401935+01 4 | whatever #4 | 2016-08-29 16:04:23.950735+02 5 | whatever #5 | 2016-07-01 12:08:05.710735+02 6 | test after disabled sub | 2017-02-06 11:59:23.935594+01 2 | updated | 2016-12-26 09:43:36.853135+01 (5 ROWS)
Now, let's copy data:
=$ pg_dump -p 5100 -a -t test depesz | psql -p 5101 -qX -d depesz SETVAL -------- 6 (1 ROW)
Now, let's change data on master some more:
(depesz@[LOCAL]:5100) 11:59:38 [depesz] $ DELETE FROM test WHERE id = 3; DELETE 1 (depesz@[LOCAL]:5100) 12:00:35 [depesz] $ UPDATE test SET whatever = 'updated 2' WHERE id = 4; UPDATE 1 (depesz@[LOCAL]:5100) 12:00:43 [depesz] $ INSERT INTO test (whatever, when_ts) VALUES ('test after pg_dump', now()); INSERT 0 1 (depesz@[LOCAL]:5100) 12:00:51 [depesz] $ SELECT * FROM test; id | whatever | when_ts ----+-------------------------+------------------------------- 5 | whatever #5 | 2016-07-01 12:08:05.710735+02 6 | test after disabled sub | 2017-02-06 11:59:23.935594+01 2 | updated | 2016-12-26 09:43:36.853135+01 4 | updated 2 | 2016-08-29 16:04:23.950735+02 7 | test after pg_dump | 2017-02-06 12:00:51.386485+01 (5 ROWS)
OK, and now, let's retry enabling subscription:
(depesz@[LOCAL]:5101) 11:58:38 [depesz] $ ALTER subscription test_sub enable; ALTER SUBSCRIPTION
moment later:
(depesz@[LOCAL]:5101) 12:02:02 [depesz] $ SELECT * FROM test; id | whatever | when_ts ----+-------------------------+------------------------------- 3 | whatever #1 | 2016-11-30 13:54:18.401935+01 4 | whatever #4 | 2016-08-29 16:04:23.950735+02 5 | whatever #5 | 2016-07-01 12:08:05.710735+02 6 | test after disabled sub | 2017-02-06 11:59:23.935594+01 2 | updated | 2016-12-26 09:43:36.853135+01 (5 ROWS)
And what about new rows?
(depesz@[LOCAL]:5100) 12:00:53 [depesz] $ INSERT INTO test (whatever, when_ts) VALUES ('test after enabling subs', now()) returning *; id | whatever | when_ts ----+--------------------------+------------------------------ 8 | test after enabling subs | 2017-02-06 12:03:22.76953+01 (1 ROW)
(depesz@[LOCAL]:5101) 12:03:45 [depesz] $ SELECT * FROM test; id | whatever | when_ts ----+-------------------------+------------------------------- 3 | whatever #1 | 2016-11-30 13:54:18.401935+01 4 | whatever #4 | 2016-08-29 16:04:23.950735+02 5 | whatever #5 | 2016-07-01 12:08:05.710735+02 6 | test after disabled sub | 2017-02-06 11:59:23.935594+01 2 | updated | 2016-12-26 09:43:36.853135+01 (5 ROWS)
Looks like replication is stuck.
Logs on slave show:
2017-02-06 12:04:46.969 CET @ 13680 LOG: logical replication apply FOR subscription "test_sub" has started 2017-02-06 12:04:46.971 CET @ 13680 ERROR: duplicate KEY VALUE violates UNIQUE CONSTRAINT "test_pkey" 2017-02-06 12:04:46.971 CET @ 13680 DETAIL: KEY (id)=(6) already EXISTS.
So, the problem is that row with id = 6 was transferred in dump, but it was also in replication set (it was added after subscription was created). So let's remove it on slave:
(depesz@[LOCAL]:5101) 12:06:21 [depesz] $ DELETE FROM test WHERE id = 6; DELETE 1
After couple of seconds:
(depesz@[LOCAL]:5101) 12:06:26 [depesz] $ SELECT * FROM test; id | whatever | when_ts ----+--------------------------+------------------------------- 5 | whatever #5 | 2016-07-01 12:08:05.710735+02 6 | test after disabled sub | 2017-02-06 11:59:23.935594+01 2 | updated | 2016-12-26 09:43:36.853135+01 4 | updated 2 | 2016-08-29 16:04:23.950735+02 7 | test after pg_dump | 2017-02-06 12:00:51.386485+01 8 | test after enabling subs | 2017-02-06 12:03:22.76953+01 (6 ROWS)
All data replicated, and up to date.
We can see progress of replication on master:
(depesz@[LOCAL]:5100) 12:09:22 [depesz] $ SELECT pg_current_xlog_location(), * FROM pg_stat_replication ; -[ RECORD 1 ]------------+------------------------------ pg_current_xlog_location | 0/41DD9600 pid | 14329 usesysid | 16389 usename | postgres application_name | test_sub client_addr | 127.0.0.1 client_hostname | [NULL] client_port | 55048 backend_start | 2017-02-06 12:06:26.282701+01 backend_xmin | [NULL] state | streaming sent_location | 0/41DD9600 write_location | 0/41DD9600 flush_location | 0/41DD9600 replay_location | 0/41DD9600 sync_priority | 0 sync_state | async
and on slave we can see if it was all applied:
(depesz@[LOCAL]:5101) 12:10:17 [depesz] $ SELECT * FROM pg_stat_subscription ; -[ RECORD 1 ]---------+------------------------------ subid | 16437 subname | test_sub pid | 14327 received_lsn | 0/41DD94E8 last_msg_send_time | 2017-02-06 12:06:31.453325+01 last_msg_receipt_time | 2017-02-06 12:06:31.453388+01 latest_end_lsn | 0/41DD94E8 latest_end_time | 2017-02-06 12:06:31.453325+01
My take away from this is: it's not perfect. We could definitely use a proper way to sync initial data in tables. But even as it is now, we can live with it, I think.
Of course, if you plan to keep slave using logical replication, and then failover to it, you will need to update sequences manually, and keep schema up to date on master. But I don't actually think it's all that important. For production slaves, normal WAL replication is better, and it handles all these cases. Logical replication seems to be for different purposes (data warehousing, generating reports, upgrading across pg major versions). And for this, it seems to work really, really well.
All things said – it's great that we have this now in core PostgreSQL. Thanks a lot to everybody involved.
it is definitely NOT logical replication!
it is WAL based,
and whole wal traffic from all cluster affected!
@Misha The new logical replication only sends the logical changes (added, removed and updated rows) of the tables you subscribe to, so it is indeed logical replication. It is only WAL-based in that it reads the WAL to reconstruct a stream of relevant logical changes, it does not send the WAL to the the slave.
Great, that’s a big step forward. Postgres 10 is going to be a really big release.
I wonder why the initial sync isn’t done automatically, e.g. through a foreign server – the information needed for that is already part of the subscription definition as far as I can tell.
FYI, there is patch for initial data copy in the commitfest and it’s expected to be happening for PG10.
>> It would also automatically add all tables created in future
I would suggest to split it into two features:
CREATE PUBLICATION test_pub FOR ALL TABLES AND AUTOADD
…and of course
ALTER PUBLICATION test_pub SET AUTOADD OFF
@Andreas
> It is only WAL-based in that it reads the WAL to reconstruct a stream
Does it mean that now we’ve got a separate buffer instead of wal to accumulate events?
And what would be happen if consumer of only one small table was killed? could we get wal-bomb (like zip-bomb) on whole cluster in that case?