What’s new in PostgreSQL 10? Part 3 – Native Logical Replication

What’s new in PostgreSQL 10? Part 3 – Native Logical Replication

Introduction

Before 10 version PostgreSQL was supported only Physical Streaming Replication (PSR). PSR is based on the Write-Ahead Log (WAL), which means that all changes are written to the binary transaction log and sent to all standby servers.  The changes  described there are very low-level and it’s give garanties that the data was consistensy. Also, PSR provides minimal overhead for data replication and it is very reliable. But it is not flexibility. It is impossible to replicate only one database, it is not possible to replicate several tables. Sometimes it’s very uncomfortable. For solving this problems usually using logical replication. PostgreSQL has many foreign solutions that support logical replication. Most popular of them: Slony-ILondiste, BucardoThey are based on triggers and give overhead for primary and standby servers. They also need to be installed separately as extensions. 

In PostgreSQL 9.4 was inplemented Logical Decoding . This mechanism provides support for decoding WAL to SQL commands. After that developers from 2ndQuadrant announced pglogical extension based on Logical Decoding solution.  It requires no triggers and is a highly efficient method of replicating data using a publish/subscribe model for selective replication. In version 10, some of the pglogical functionality was implemented with patch in the PostgreSQL core.

Tuning postgresql.conf

Before using logical replication we need tuning postgresql.conf file. Primary server:

listen_address = '*'
wal_level = logical
synchronous_commit = remote_apply
synchronous_standby_names = 'sub_points'

We will synchronous replication with most high level. Standby server:

 wal_level = logical
 max_logical_replication_workers = 4
 max_sync_workers_per_subscription = 2

New parameters “max_logical_replication_workers” and  “max_sync_workers_per_subscription“. More details for documentation.

Tuning pg_hba.conf

Primary server:

# TYPE    DATABASE      USER         ADDRESS        METHOD

host      replication        postgres     10.0.0.0/8        md5

A simple example of logical replication

We will used the partitioned table “points” from the last post by Michael. Let’s create a publication for this table:

postgres=# CREATE PUBLICATION test_pub FOR TABLE points;
ERROR: "points" is a partitioned table
DETAIL: Adding partitioned tables to publications is not supported.
HINT: You can add the table partitions individually.

Oh… OK. The partition table does not support logical replication for the parent table level. Let’s create a publication for all tables:

postgres=# CREATE PUBLICATION pub_points FOR ALL TABLES;
CREATE PUBLICATION

OK.  See the CREATE PUBLICATION documentation for more details.

Let’s check the state of replication:

postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 21110
usesysid | 10
usename | postgres
application_name | sub_points
client_addr | 10.0.0.191
client_hostname |
client_port | 44932
backend_start | 2017-08-07 09:13:09.210055+00
backend_xmin |
state | streaming
sent_lsn | 2/C0388BC0
write_lsn | 2/C0388BC0
flush_lsn | 2/C0388BC0
replay_lsn | 2/C0388BC0
write_lag |
flush_lag |
replay_lag |
sync_priority | 1
sync_state | sync

Nice. PostgreSQL created replication. Let’s go to the standby server and create a SUBSCRIPTION :

postgres=# CREATE SUBSCRIPTION sub_points CONNECTION 'host=10.0.0.190 port=5432 dbname=postgres user=postgres password=bigsecret' PUBLICATION pub_points;
NOTICE: created replication slot "sub_points" on publisher
CREATE SUBSCRIPTION

Check the status of the subscription:

postgres=# SELECT * FROM pg_stat_subscription;
-[ RECORD 1 ]---------+------------------------------
subid | 24605
subname | sub_points
pid | 4348
relid |
received_lsn | 2/C03908D0
last_msg_send_time | 2017-08-07 10:29:29.636991+00
last_msg_receipt_time | 2017-08-07 10:29:29.637124+00
latest_end_lsn | 2/C03908D0
latest_end_time | 2017-08-07 10:29:29.636991+00

OK, the subscription exists. Let’s check the data of the points table:

postgres=# SELECT * FROM points;
 id | serial_num | range_dt | lat | lon
----+------------+---------------------+-------+-------
 1 | 12345678 | 2017-07-01 00:10:00 | 55.32 | 45.34
 2 | 12345678 | 2017-07-02 02:30:00 | 56.21 | 44.38
 3 | 12345678 | 2017-01-01 17:55:00 | 54.02 | 47.34
 6 | 12345677 | 2017-07-04 00:40:00 | 55.32 | 45.34
 7 | 12345678 | 2017-07-04 01:30:00 | 56.21 | 44.38
(5 rows)

The data is same. Let’s insert the row on the primary server:

postgres=# INSERT INTO points(serial_num, range_dt,lat,lon) VALUES (11223344, '2017-07-01 10:00:00', 54.32, 44.12);
INSERT 0 1

postgres=# SELECT * FROM points_20170701;
 id | serial_num | range_dt | lat | lon
----+------------+---------------------+-------+-------
 1 | 12345678 | 2017-07-01 00:10:00 | 55.32 | 45.34
 9 | 11223344 | 2017-07-01 10:00:00 | 54.32 | 44.12
(2 rows)

And look the row on the standby:

postgres=# SELECT * FROM points_20170701;
 id | serial_num | range_dt | lat | lon
----+------------+---------------------+-------+-------
 1 | 12345678 | 2017-07-01 00:10:00 | 55.32 | 45.34
 9 | 11223344 | 2017-07-01 10:00:00 | 54.32 | 44.12
(2 rows)

The row is inserted in the appropriate partition. Delete the row on the primary PostgreSQL:

postgres=# DELETE FROM points_20170701 WHERE id = 9;
DELETE 1

, check the row on the standby:

postgres=# SELECT * FROM points_20170701 WHERE id = 9;
 id | serial_num | range_dt | lat | lon
----+------------+----------+-----+-----
(0 rows)

Conclusion

The appearance of native logical replication in the PostgreSQL core is a very big step forward. As we see, configuring logical replication is very simple. Many DBA have been waiting for this opportunity for many years now and can now use it without having to install foreign extensions. Now logical replication has basic capabilities, but I think that gradually all functionality from pglogical will be transferred.

Emil Shkolnik
No Comments

Post a Comment

Comment
Name
Email
Website

This site uses Akismet to reduce spam. Learn how your comment data is processed.