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-I, Londiste, Bucardo. They 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.