在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
开源软件名称:pgcat开源软件地址:https://gitee.com/mirrors/pgcat开源软件介绍:pgcat - Enhanced postgresql logical replication
Why pgcat?The built-in logicial replication has below shortages:
pgcat makes below enhancements:
Architecturepgcat is based on logical decoding, and reuses the publication part and pgouputoutput plugin of the pg built-in logical replication. Instead of worker processes and low-level row ingression, pgcat uses sql templateto apply high-level sql commands, so it could make largest compatibility on targettable 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. Build from sourcepgcat has two parts, pgcat binary and postgresql extension. Dependenciesgolang >= 1.12 Assume you uses Centos/RedHat >= 7.0, you needs below rpm packages installed: postgresql11-devel-11.3-1PGDG.rhel7.x86_64 Build pgcatgit build-rpm# check generated rpm, e.g. pgcat-0.1-11568289796.el7.x86_64.rpm Build pgcat-pgxsgit clone https://github.com/kingluo/pgcat-pgxscd pgcat-pgxsexport PATH=$PATH:/usr/pgsql-11/bin/git build-rpm# check generated rpm, e.g. pgcat-pgxs-0.1-11562916936.el7.x86_64.rpm InstallDependenciespostgresql >= 11. postgresql11-server-11.3-1PGDG.rhel7.x86_64postgresql11-11.3-1PGDG.rhel7.x86_64 Install pgcatInstall pgcat on individual machine whatever you want. rpm -Uvh pgcat-0.1-11568289796.el7.x86_64.rpm Install pgcat-pgxsInstall pgcat-pgxs on both the publisher and subscriber databases. rpm -Uvh pgcat-pgxs-0.1-11562916936.el7.x86_64.rpm Add pgcat user
-- adjust your password hereCREATE USER pgcat with REPLICATION PASSWORD 'pgcat';
-- adjust your password hereCREATE USER pgcat with PASSWORD 'pgcat'; Note: if you do bi-directional replication, create pgcat user with replicationattribute on both publisher and subscriber databases. Create extensionUse superuser role to create extension on both the publisherand subscriber databases. CREATE EXTENSION IF NOT EXISTS pgcat; This command would create extension under the pgcat schema. Configure the publisher database
postgresql.conf wal_level = logical
pg_hba.conf host all pgcat 0.0.0.0/0 md5 Restart pg. pg_ctl restart RunSetup the tableGrant pgcatOn 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, thenon publisher database, pgcat needs to select the table. grant select on foobar to pgcat; Setup publicationOn publisher database: CREATE PUBLICATION foobar FOR TABLE foobar;alter publication foobar add table foobar; Setup lww (optional)If you need last-writer-win conflict resolution, then run pgcat_setup_lww -c lww.yml Check /usr/share/pgcat/lww.yml for configuration file example. Setup subscriptionINSERT 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 Run pgcatmkdir /your/deploy/pathcd /your/deploy/pathcp -a /usr/share/pgcat/pgcat.yml .# modify pgcat.yml to fit your needpgcat -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 pgcat -c pgcat.yml &>/dev/null Admin HTTP API: If you configure # rotate the log file# you could use logrotate to rotate pgcat log files as you needcurl http://127.0.0.1:30000/rotate# reload the yaml config file, e.g. you could add new databases to replicatecurl http://127.0.0.1:30000/reload Conflict handlingIf not using lww, it's likely to have conflict, e.g. unique violation,especially for bi-directional replication. When conflict happens, pgcat wouldpanic this subscription, and restarts it in 1 minute, and so on. How to handle conflict? As pg docsaid:
It also works for pgcat. But since pgcat substitutes the subscriber part, so toskip 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 itin pgcat log when conflict happens: dml failed, commit_lsn=0/27FD9B0, err=... Table mappingYou could map publisher table name to different subscriber table name. You could map multiple tables into one table, gathering multiple data sourceinto one target, e.g. partition tables, citus shards. Here the target could bepartitioned table, view, or citus main table, so that you could haveheterogeneous layout at different database and do easy replication. For example, at database1, you have tables 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 Replication identityView and foreign table does not have replica ident, so it needs to configurethem in pgcat. The configuration table is For example, I need to set 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. Limitations
Conflict resolutionConflict 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 andmultiple slaves, where they are connected via physical replication),especially for different data centers, where does no requirereal-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 thenetwork between data centers may be broken for time to time. Each data center changes data independently, which would normally involves datawith same identity, e.g. same primary key. Such data needs some sort of policyto 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? lww (Last-writer-win)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. Thepg deployment would keep time in sync, e.g. via NTP. The How does it work? It would category the columns as:
When you run
|
请发表评论