合 PG逻辑复制插件之pglogical官方说明
- Requirements
- Installation
- Packages
- Installing pglogical with YUM
- Pre-requisites
- Installation
- Installing pglogical with APT
- Pre-requisites
- Installation
- From source code
- Usage
- Quick setup
- Creating subscriber nodes with base backups
- Node management
- Subscription management
- Replication sets
- Automatic assignment of replication sets for new tables
- Additional functions
- Row Filtering
- Row Filtering on Provider
- Row Filtering on Subscriber
- Synchronous Replication
- Conflicts
- Configuration options
- Limitations and restrictions
- Superuser is required
- UNLOGGED and TEMPORARY not replicated
- One database at a time
- PRIMARY KEY or REPLICA IDENTITY required
- Only one unique index/constraint/PK
- Unique constraints must not be deferrable
- DDL
- No replication queue flush
- FOREIGN KEYS
- TRUNCATE
- Sequences
- Triggers
- PostgreSQL Version differences
- Database encoding differences
- Large objects
- Postgres-XL
- Appendix A: Credits and License
- Appendix B: Release Notes
- pglogical 2.4.1
- Changes
- pglogical 2.4.0
- Changes
参考：
https://gitee.com/mirrors/pglogical
https://github.com/2ndQuadrant/pglogical
pglogical 2
The pglogical 2 extension provides logical streaming replication for PostgreSQL, using a publish/subscribe model. It is based on technology developed as part of the BDR project (http://2ndquadrant.com/BDR).
We use the following terms to describe data streams between nodes, deliberately reused from the earlier Slony technology:
- Nodes - PostgreSQL database instances
- Providers and Subscribers - roles taken by Nodes
- Replication Set - a collection of tables
pglogical is utilising the latest in-core features, so we have these version restrictions:
- Provider & subscriber nodes must run PostgreSQL 9.4+
- PostgreSQL 9.5+ is required for replication origin filtering and conflict detection
- Additionally, subscriber can be Postgres-XL 9.5+
Use cases supported are:
- Upgrades between major versions (given the above restrictions)
- Full database replication
- Selective replication of sets of tables using replication sets
- Selective replication of table rows at either publisher or subscriber side (row_filter)
- Selective replication of table columns at publisher side
- Data gather/merge from multiple upstream servers
Architectural details:
- pglogical works on a per-database level, not whole server level like physical streaming replication
- One Provider may feed multiple Subscribers without incurring additional disk write overhead
- One Subscriber can merge changes from several origins and detect conflict between changes with automatic and configurable conflict resolution (some, but not all aspects required for multi-master).
- Cascading replication is implemented in the form of changeset forwarding.
Requirements
To use pglogical the provider and subscriber must be running PostgreSQL 9.4 or newer.
The pglogical extension must be installed on both provider and subscriber. You must CREATE EXTENSION pglogicalon both.
Tables on the provider and subscriber must have the same names and be in the same schema. Future revisions may add mapping features.
Tables on the provider and subscriber must have the same columns, with the same data types in each column. CHECKconstraints, NOT NULL constraints, etc., must be the same or weaker (more permissive) on the subscriber than the provider.
Tables must have the same PRIMARY KEYs. It is not recommended to add additional UNIQUE constraints other than the PRIMARY KEY (see below).
Some additional requirements are covered in "Limitations and Restrictions", below.
Installation
Packages
pglogical is available as RPMs via yum for Fedora, CentOS, & RHEL, and as DEBs via apt for Debian and Ubuntu, or as source code here. Please see below for instructions on installing from source.
Installing pglogical with YUM
The instructions below are valid for Red Hat family of operating systems (RHEL, CentOS, Fedora). Pre-Requisites
Pre-requisites
These RPMs all require the PGDG PostgreSQL releases from http://yum.postgresql.org/. You cannot use them with stock PostgreSQL releases included in Fedora and RHEL. If you don’t have PostgreSQL already:
- Install the appropriate PGDG repo rpm from http://yum.postgresql.org/repopackages.php
- Install PostgreSQL
- PostgreSQL 9.4:
yum install postgresql94-server postgresql94-contrib - PostgreSQL 9.5:
yum install postgresql95-server postgresql95-contrib - PostgreSQL 9.6:
yum install postgresql96-server postgresql96-contrib - PostgreSQL 10:
yum install postgresql10-server postgresql10-contrib - PostgreSQL 11:
yum install postgresql11-server postgresql11-contrib - PostgreSQL 12:
yum install postgresql12-server postgresql12-contrib - PostgreSQL 13:
yum install postgresql13-server postgresql13-contrib - PostgreSQL 14:
yum install postgresql14-server postgresql14-contrib
- PostgreSQL 9.4:
Then install the “2ndQuadrant’s General Public” repository for your PostgreSQL version, by running the following instructions as root on the destination Linux server:
- PostgreSQL 9.4:
curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/9.4/rpm | bash - PostgreSQL 9.5:
curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/9.5/rpm | bash - PostgreSQL 9.6:
curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/9.6/rpm | bash - PostgreSQL 10:
curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/10/rpm | bash - PostgreSQL 11:
curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/11/rpm | bash - PostgreSQL 12:
curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/12/rpm | bash - PostgreSQL 13:
curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/13/rpm | bash - PostgreSQL 14:
curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/14/rpm | bash
Installation
Once the repository is installed, you can proceed to pglogical for your PostgreSQL version:
- PostgreSQL 9.4:
yum install postgresql94-pglogical - PostgreSQL 9.5:
yum install postgresql95-pglogical - PostgreSQL 9.6:
yum install postgresql96-pglogical - PostgreSQL 10:
yum install postgresql10-pglogical - PostgreSQL 11:
yum install postgresql11-pglogical - PostgreSQL 12:
yum install postgresql12-pglogical - PostgreSQL 13:
yum install postgresql13-pglogical - PostgreSQL 14:
yum install postgresql14-pglogical
You may be prompted to accept the repository GPG key for package signing:
1 | Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-2NDQ-DL-DEFAULT Importing GPG key 0xD6BAF0C3: Userid : "Public repository signing key 2ndQuadrant <ci@2ndquadrant.com>" Fingerprint: 8565 305c ea7d 0b66 4933 d250 9904 cd4b d6ba f0c3 From : /etc/pki/rpm-gpg/RPM-GPG-KEY-2NDQ-DL-DEFAULT Is this ok [y/N]: |
If so, accept the key (if it matches the above) by pressing ‘y’ then enter. (It’s signed by the 2ndQuadrant master packaging key, if you want to verify that.)
Installing pglogical with APT
The instructions below are valid for Debian and all Linux flavors based on Debian (e.g. Ubuntu).
Pre-requisites
You can install the “2ndQuadrant’s General Public” repository by running the following instructions as root on the destination Linux server: curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/deb | bash
- Add the http://apt.postgresql.org/ repository. See the site for instructions.
Installation
Once pre-requisites are complete, installing pglogical is simply a matter of executing the following for your version of PostgreSQL:
- PostgreSQL 9.4:
sudo apt-get install postgresql-9.4-pglogical - PostgreSQL 9.5:
sudo apt-get install postgresql-9.5-pglogical - PostgreSQL 9.6:
sudo apt-get install postgresql-9.6-pglogical - PostgreSQL 10:
sudo apt-get install postgresql-10-pglogical - PostgreSQL 11:
sudo apt-get install postgresql-11-pglogical - PostgreSQL 12:
sudo apt-get install postgresql-12-pglogical - PostgreSQL 13:
sudo apt-get install postgresql-13-pglogical - PostgreSQL 14:
sudo apt-get install postgresql-14-pglogical
From source code
Source code installs are the same as for any other PostgreSQL extension built using PGXS.
Make sure the directory containing pg_config from the PostgreSQL release is listed in your PATH environment variable. You might have to install a -dev or -devel package for your PostgreSQL release from your package manager if you don't have pg_config.
Then run make to compile, and make install to install. You might need to use sudo for the install step.
e.g. for a typical Fedora or RHEL 7 install, assuming you're using the yum.postgresql.org packages for PostgreSQL:
1 2 3 | sudo dnf install postgresql95-devel PATH=/usr/pgsql-9.5/bin:$PATH make clean all sudo PATH=/usr/pgsql-9.5/bin:$PATH make install |
Usage
This section describes basic usage of the pglogical replication extension.
Quick setup
First the PostgreSQL server has to be properly configured to support logical decoding:
1 2 3 4 5 6 | wal_level = 'logical' max_worker_processes = 10 # one per database needed on provider node # one per node needed on subscriber node max_replication_slots = 10 # one per node needed on provider node max_wal_senders = 10 # one per node needed on provider node shared_preload_libraries = 'pglogical' |
If you are using PostgreSQL 9.5+ (this won't work on 9.4) and want to handle conflict resolution with last/first update wins (see Conflicts), you can add this additional option to postgresql.conf:
1 2 | track_commit_timestamp = on # needed for last/first update wins conflict resolution # property available in PostgreSQL 9.5+ |
pg_hba.conf has to allow logical replication connections from localhost. Up until PostgreSQL 9.6, logical replication connections are managed using the replication keyword in pg_hba.conf. In PostgreSQL 10 and later, logical replication connections are treated by pg_hba.conf as regular connections to the provider database.
Next the pglogical extension has to be installed on all nodes:
1 | CREATE EXTENSION pglogical; |
If using PostgreSQL 9.4, then the pglogical_origin extension also has to be installed on that node:
1 | CREATE EXTENSION pglogical_origin; |
Now create the provider node:
1 2 3 4 | SELECT pglogical.create_node( node_name := 'provider1', dsn := 'host=providerhost port=5432 dbname=db' ); |
Add all tables in public schema to the default replication set.
1 | SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']); |
Optionally you can also create additional replication sets and add tables to them (see Replication sets).
It's usually better to create replication sets before subscribing so that all tables are synchronized during initial replication setup in a single initial transaction. However, users of bigger databases may instead wish to create them incrementally for better control.
Once the provider node is setup, subscribers can be subscribed to it. First the subscriber node must be created:
1 2 3 4 | SELECT pglogical.create_node( node_name := 'subscriber1', dsn := 'host=thishost port=5432 dbname=db' ); |
And finally on the subscriber node you can create the subscription which will start synchronization and replication process in the background:
1 2 3 4 5 6 | SELECT pglogical.create_subscription( subscription_name := 'subscription1', provider_dsn := 'host=providerhost port=5432 dbname=db' ); SELECT pglogical.wait_for_subscription_sync_complete('subscription1'); |
Creating subscriber nodes with base backups
In addition to the SQL-level node and subscription creation, pglogical also supports creating a subscriber by cloning the provider with pg_basebackup and starting it up as a pglogical subscriber. This is done with thepglogical_create_subscriber tool; see the --help output.
