Main difference of Logical replication in Postgresql is the ability to replicate some tables, instead of replicating the entire database.
To achieve this, wal_level setting should be configured to "logical". This parameter change needs an instance restart.
The publication
postgres=# alter system set wal_level to 'logical';
ALTER SYSTEM
postgres=# exit
[postgres@centos01 pg_wal]$ exit
logout
[root@centos01 ~]# systemctl restart postgresql-15
[root@centos01 ~]# sudo su - postgres
Lets create a test table to replicate. This test table will have two column, one for text data and the second for timestamp. I am defining a default value of current_timestamp for this column, so wa can track the replicated data.
CREATE TABLE ttest (col1 text, col2 timestamp DEFAULT current_timestamp);
One of the important point of the Logical replication is the primary keys and/or unique keys. Since we are not replicating the physical wal anymore, the complete dml command should define the exact row on the replica side.
This means, the where clause of the dml statement should be distinctive which brings us the replica table should include a primary key or a unique key. If this is not the case as in our test case we can set the replica identity to full which adds all rows to the where clause.
This necessity comes with the extra informarion on the wal files and some performance degradation. So It is always better to find a way to create a pk or unique key on the table.
alter table ttest replica identity full;
-- replica identity should be set if there is no pk or unique key
-- else update and delete operations are not monitored
Since our wal_level and replica identity as well as test table is ready, we can create the publication. Publication can be created with or without tables attached to them. You can always alter the publication to add or remove tables, but keep in mind that on the replica side, the replica process should be refreshed on every alteration of the publication process.
create publication publication_01;
alter publication publication_01 add table ttest;
You can check on the publication with the following catalog tables.
select * from pg_publication;
oid |pubname |pubowner|puballtables|pubinsert|pubupdate|pubdelete|pubtruncate|pubviaroot|
-----+--------------+--------+------------+---------+---------+---------+-----------+----------+
16536|publication_01| 10|false |true |true |true |true |false |
select * from pg_publication_tables;
pubname |schemaname|tablename|attnames |rowfilter|
--------------+----------+---------+-----------+---------+
publication_01|public |ttest |{col1,col2}| |
The subscription
Now create the subscription, detailed command reference can be found in the references section.
CREATE SUBSCRIPTION subscription_01
CONNECTION 'host=10.211.55.4 port=5432 dbname=postgres user=postgres password=postgres'
PUBLICATION publication_01 WITH (copy_data = true, create_slot=true, enabled=true, slot_name=replication_slot_01);
-- if an alter is executed on the primary side
alter subscription subscription_01 refresh publication;
Check on the publication we have created.
select * from pg_subscription;
oid |subdbid|subskiplsn|subname |subowner|subenabled|subbinary|substream|subtwophasestate|subdisableonerr|subconninfo |subslotname |subsynccommit|subpublications |
-----+-------+----------+---------------+--------+----------+---------+---------+----------------+---------------+--------------------------------------------------------------------------+-------------------+-------------+----------------+
16545| 5|0/0 |subscription_01| 10|true |false |false |d |false |host=10.211.55.4 port=5432 dbname=postgres user=postgres password=postgres|replication_slot_01|off |{publication_01}|
select * from pg_subscription_rel;
srsubid|srrelid|srsubstate|srsublsn |
-------+-------+----------+---------+
16545| 16539|r |0/1568950|
Testing
Now lets insert a record on the publisher and check the subscriber .
-- on publisher
insert into ttest (col1) values ('test');
-- on subscriber
select * from ttest;
col1|col2 |
----+-----------------------+
test|2024-05-07 17:44:06.814|
Logical replication seems working.
Monitoring
There are some dictionary views to monitor and follow the replication process.
To follow if the replication origin is keeping up and which file it is processing, you can check the following query result.
select * from pg_replication_origin_status;
local_id|external_id|remote_lsn|local_lsn|
--------+-----------+----------+---------+
1|pg_16545 |0/15689C8 |0/9021008|
select pg_walfile_name('0/15689C8');
pg_walfile_name |
------------------------+
000000020000000000000001|
To find out which subscription is associated with which replication origin you can use the following query.
select * from pg_replication_origin;
roident|roname |
-------+--------+
1|pg_16545|
select
roident, subname, roname
from
pg_subscription sub,
pg_replication_origin ro
where 'pg_' || sub.oid = ro.roname;
roident|subname |roname |
-------+---------------+--------+
1|subscription_01|pg_16545|
references:
コメント