代码拉取完成，页面将自动刷新
The built-in logicial replication has below shortages:
pgcat makes below enhancements:
pgcat is based on logical decoding, and reuses the publication part and pgoutput output plugin of the pg built-in logical replication.
Instead of worker processes and low-level row ingression, pgcat uses sql templates to apply high-level sql commands, so it could make largest compatibility on target table type. It is written in golang and runs in separate process.
The lww uses one additional jsonb column to store meta info, e.g. timestamp. It supports timestamp-override columns or cassandra-like counter columns.
pgcat has two parts, pgcat binary and postgresql extension.
golang >= 1.12
Assume you use Centos/RedHat >= 7.0, you need the below RPM packages installed:
postgresql11-devel-11.3-1PGDG.rhel7.x86_64
git build-rpm
# check generated rpm, e.g. pgcat-0.1-11568289796.el7.x86_64.rpm
git clone https://github.com/kingluo/pgcat-pgxs
cd pgcat-pgxs
export PATH=$PATH:/usr/pgsql-11/bin/
git build-rpm
# check generated rpm, e.g. pgcat-pgxs-0.1-11562916936.el7.x86_64.rpm
postgresql >= 11.
postgresql11-server-11.3-1PGDG.rhel7.x86_64
postgresql11-11.3-1PGDG.rhel7.x86_64
Install pgcat on individual machine whatever you want.
rpm -Uvh pgcat-0.1-11568289796.el7.x86_64.rpm
Install pgcat-pgxs on both the publisher and subscriber databases.
rpm -Uvh pgcat-pgxs-0.1-11562916936.el7.x86_64.rpm
-- adjust your password here
CREATE USER pgcat with REPLICATION PASSWORD 'pgcat';
-- adjust your password here
CREATE USER pgcat with PASSWORD 'pgcat';
Note: if you do bi-directional replication, create pgcat user with replication attribute on both publisher and subscriber databases.
Use superuser role to create extension on both the publisher and subscriber databases.
CREATE EXTENSION IF NOT EXISTS pgcat;
This command would create extension under the pgcat schema.
postgresql.conf
wal_level = logical
pg_hba.conf
host all pgcat 0.0.0.0/0 md5
Restart pg.
pg_ctl restart
On subscriber database, pgcat needs to read/write the table.
grant select,insert,update,delete,truncate on foobar to pgcat;
If you configure to copy the table in the subscription, then on publisher database, pgcat needs to select the table.
grant select on foobar to pgcat;
On publisher database:
CREATE PUBLICATION foobar FOR TABLE foobar;
alter publication foobar add table foobar;
If you need last-writer-win conflict resolution, then run pgcat_setup_lww on all pg instances.
pgcat_setup_lww -c lww.yml
Check /usr/share/pgcat/lww.yml for configuration file example.
INSERT INTO pgcat.pgcat_subscription(name, hostname, port, username, password,
dbname, publications, copy_data, enabled) VALUES ('foobar', '127.0.0.1', 5433,
'pgcat', 'pgcat', 'tmp', '{foobar}', true, true);
The pgcat would check pgcat_subscription, if it changes, pgcat would apply the changes.
mkdir -p /your/deploy/path
cd /your/deploy/path
cp -a /usr/share/pgcat/pgcat.yml .
# modify pgcat.yml to fit your need
pgcat -c pgcat.yml
pgcat uses golang proxy dialer, so if you need to access your database via proxy, you could run below command:
all_proxy=socks5h://127.0.0.1:20000 pgcat -c pgcat.yml
If you need to run pgcat in daemon on Linux, just use setsid command:
setsid pgcat -c pgcat.yml &>/dev/null
Admin HTTP API:
If you configure admin_listen_address, e.g. admin_listen_address: 127.0.0.1:30000,
then you could run below commands to admin the pgcat process:
# rotate the log file
# you could use logrotate to rotate pgcat log files as you need
curl http://127.0.0.1:30000/rotate
# reload the yaml config file, e.g. you could add new databases to replicate
curl http://127.0.0.1:30000/reload
If not using lww, it's likely to have conflict, e.g. unique violation, especially for bi-directional replication. When conflict happens, pgcat would panic this subscription, and restarts it in 1 minute, and so on.
How to handle conflict? As pg doc said:
either by changing data on the subscriber so that it does not conflict with the incoming change or by skipping the transaction that conflicts with the existing data.
It also works for pgcat. But since pgcat substitutes the subscriber part, so to skip transaction, you need to use below sql command:
update pgcat.pgcat_subscription_progress set lsn='0/27FD9B0';
The lsn is the lsn of the commit record of this transaction, you could find it in pgcat log when conflict happens:
dml failed, commit_lsn=0/27FD9B0, err=...
You could map publisher table name to different subscriber table name.
You could map multiple tables into one table, gathering multiple data source into one target, e.g. partition tables, citus shards. Here the target could be partitioned table, view, or citus main table, so that you could have heterogeneous layout at different database and do easy replication.
For example, at database1, you have tables foobar2 and foobar3, and you need
to configure them be put into table foobar2 at database2.
In database2 (subscriber database), run below sql command via superuser:
insert into pgcat.pgcat_table_mapping(subscription,priority,src,dst)
values('foobar',1,'^public.foobar[2-3]$','public.foobar2');
Note that the regexp and table name should be full qualified,
i.e. with schema prefix. And the regexp is better to be surrounded with
^ and $, otherwise the matching is error prone.
View and foreign table does not have replica ident, so it needs to configure
them in pgcat. The configuration table is pgcat.pgcat_replident.
For example, I need to set id1 and id2 columns as replica ident of view
foobar2_view.
insert into pgcat.pgcat_replident values('public.foobar2_view', '{"id1", "id2"}');
Note that the table name should be full qualified, i.e. with schema prefix.
copy_data to false in the subscription, and do
not use instead of trigger on view, please use view rule instead.
copy to view requires instead of trigger attached, but the current pg
has no way to set always role on the view instead of trigger.
On the other hand, pgcat sets its role to replica, so instead of
trigger would not be called, and then copy to view would be no-op.Conflict resolution is necessary for logical replication.
Logical replication is normally used for loose-coupling different pg deployments (or different pg HA deployments, one HA deployment consists of one master and multiple slaves, where they are connected via physical replication), especially for different data centers, where it does not require real-time data consistence).
For example, we have two groups of pg:
pg1 consists of pg1-master and pg1-slave, run in datacenter1.
pg2 consists of pg2-master, pg2-slave1 and pg2-slave2, run in datacenter2.
They replicate data changes to each other, but not in real time, and the network between data centers may be broken for time to time.
Each data center changes data independently, which would normally involves data with same identity, e.g. same primary key. Such data needs some sort of policy to keep consistent in both data centers.
For example, pg1 writes "foo" to row foo, before this change is applied to pg2, pg2 writes "bar" to row foo. Then which would be the final version of value?
Similar to Cassandra, it could use write timestamp to resolve the conflict. The write with latest timestamp would be the last version of data value. The pg deployment would keep time in sync, e.g. via NTP.
The pgcat_setup_lww command is used to setup the table for used by lww. Note
that conflict resolution is optional, so if you're sure the data changes are
consistent by nature or by design, you do not need to run this command on your
tables.
How does it work?
It would category the columns as:
When you run pgcat_setup_lww, it would:
__pgcat_lww to the target table, which
is in jsonb type, used to record the meta info of the row and columns.__pgcat_lww concurrently (i.e. would
not block concurrent data r/w when the table setup is processing).__pgcat_lww
__pgcat_lww, use
system identifier from pg_control_system() to identify different
remote peers__pgcat_lww so that it would not be exported
to application level. For example, if you have table foobar,
then it would create a view foobar_pgcat_lww_view.pgcat_lww_counter() used to sum up counter column.
You could call pgcat_lww_counter(__pgcat_lww, 'foobar') to
get the value of column foobar.__pgcat_lww, so pgcat_setup_lww would create index
for tombstone rows and create a helper function to vacuum them whenever you need,
e.g. remove tombstones older than 3 days:
foobar_pgcat_lww_vaccum(interval '3 days') .此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
1. 开源生态
2. 协作、人、软件
3. 评估模型