参考：

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

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:

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:

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:

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:

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:

If using PostgreSQL 9.4, then the pglogical_origin extension also has to be installed on that node:

Now create the provider node:

Add all tables in public schema to the default replication set.

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:

And finally on the subscriber node you can create the subscription which will start synchronization and replication process in the background:

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.

Unlike pglogical.create_subscription's data sync options, this clone ignores replication sets and copies all tables on all databases. However, it's often much faster, especially over high-bandwidth links.

Node management

Nodes can be added and removed dynamically using the SQL interfaces.

• pglogical.create_node(node_name name, dsn text) Creates a node.

  Parameters:

  • node_name - name of the new node, only one node is allowed per database
  • dsn - connection string to the node, for nodes that are supposed to be providers, this should be reachable from outside

• pglogical.drop_node(node_name name, ifexists bool) Drops the pglogical node.

  Parameters:

  • node_name - name of an existing node
  • ifexists - if true, error is not thrown when subscription does not exist, default is false

• pglogical.alter_node_add_interface(node_name name, interface_name name, dsn text) Adds additional interface to a node.

  When node is created, the interface for it is also created with the dsn specified in the create_node and with the same name as the node. This interface allows adding alternative interfaces with different connection strings to an existing node.

  Parameters:

  • node_name - name of an existing node
  • interface_name - name of a new interface to be added
  • dsn - connection string to the node used for the new interface

• pglogical.alter_node_drop_interface(node_name name, interface_name name) Remove existing interface from a node.

  Parameters:

  • node_name - name of and existing node
  • interface_name - name of an existing interface

Subscription management

• pglogical.create_subscription(subscription_name name, provider_dsn text, replication_sets text[], synchronize_structure boolean, synchronize_data boolean, forward_origins text[], apply_delay interval)Creates a subscription from current node to the provider node. Command does not block, just initiates the action.

  Parameters:

  • subscription_name - name of the subscription, must be unique
  • provider_dsn - connection string to a provider
  • replication_sets - array of replication sets to subscribe to, these must already exist, default is "{default,default_insert_only,ddl_sql}"
  • synchronize_structure - specifies if to synchronize structure from provider to the subscriber, default false
  • synchronize_data - specifies if to synchronize data from provider to the subscriber, default true
  • forward_origins - array of origin names to forward, currently only supported values are empty array meaning don't forward any changes that didn't originate on provider node (this is useful for two-way replication between the nodes), or "{all}" which means replicate all changes no matter what is their origin, default is "{all}"
  • apply_delay - how much to delay replication, default is 0 seconds
  • force_text_transfer - force the provider to replicate all columns using a text representation (which is slower, but may be used to change the type of a replicated column on the subscriber), default is false

  The subscription_name is used as application_name by the replication connection. This means that it's visible in the pg_stat_replication monitoring view. It can also be used in synchronous_standby_names when pglogical is used as part of synchronous replication setup.

  Use pglogical.wait_for_subscription_sync_complete(sub_name) to wait for the subscription to asynchronously start replicating and complete any needed schema and/or data sync.

• pglogical.drop_subscription(subscription_name name, ifexists bool) Disconnects the subscription and removes it from the catalog.

  Parameters:

  • subscription_name - name of the existing subscription
  • ifexists - if true, error is not thrown when subscription does not exist, default is false

• pglogical.alter_subscription_disable(subscription_name name, immediate bool) Disables a subscription and disconnects it from the provider.

  Parameters:

  • subscription_name - name of the existing subscription
  • immediate - if true, the subscription is stopped immediately, otherwise it will be only stopped at the end of current transaction, default is false

• pglogical.alter_subscription_enable(subscription_name name, immediate bool) Enables disabled subscription.

  Parameters:

  • subscription_name - name of the existing subscription
  • immediate - if true, the subscription is started immediately, otherwise it will be only started at the end of current transaction, default is false

• pglogical.alter_subscription_interface(subscription_name name, interface_name name) Switch the subscription to use different interface to connect to provider node.

  Parameters:

  • subscription_name - name of an existing subscription
  • interface_name - name of an existing interface of the current provider node

• pglogical.alter_subscription_synchronize(subscription_name name, truncate bool) All unsynchronized tables in all sets are synchronized in a single operation. Tables are copied and synchronized one by one. Command does not block, just initiates the action. Use pglogical.wait_for_subscription_sync_complete to wait for completion.

  Parameters:

  • subscription_name - name of the existing subscription
  • truncate - if true, tables will be truncated before copy, default false

• pglogical.alter_subscription_resynchronize_table(subscription_name name, relation regclass) Resynchronize one existing table. The table may not be the target of any foreign key constraints. WARNING: This function will truncate the table immediately, and only then begin synchronising it, so it will be empty while being synced

  Does not block, use pglogical.wait_for_table_sync_complete to wait for completion.

  Parameters:

  • subscription_name - name of the existing subscription
  • relation - name of existing table, optionally qualified

• pglogical.wait_for_subscription_sync_complete(subscription_name name)

  Wait for a subscription or to finish synchronization after a pglogical.create_subscription or pglogical.alter_subscription_synchronize.

  This function waits until the subscription's initial schema/data sync, if any, are done, and until any tables pending individual resynchronisation have also finished synchronising.

  For best results, run SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL) on the provider after any replication set changes that requested resyncs, and only then call pglogical.wait_for_subscription_sync_complete on the subscriber.

• pglogical.wait_for_table_sync_complete(subscription_name name, relation regclass)

  Same as pglogical.wait_for_subscription_sync_complete, but waits only for the subscription's initial sync and the named table. Other tables pending resynchronisation are ignored.

• pglogical.wait_slot_confirm_lsn

  SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL)

  Wait until all replication slots on the current node have replayed up to the xlog insert position at time of call on all providers. Returns when all slots' confirmed_flush_lsn passes the pg_current_wal_insert_lsn() at time of call.

  Optionally may wait for only one replication slot (first argument). Optionally may wait for an arbitrary LSN passed instead of the insert lsn (second argument). Both are usually just left null.

  This function is very useful to ensure all subscribers have received changes up to a certain point on the provider.

• pglogical.show_subscription_status(subscription_name name) Shows status and basic information about subscription.

  Parameters:

  • subscription_name - optional name of the existing subscription, when no name was provided, the function will show status for all subscriptions on local node

• pglogical.show_subscription_table(subscription_name name, relation regclass) Shows synchronization status of a table.

  Parameters:

  • subscription_name - name of the existing subscription
  • relation - name of existing table, optionally qualified

• pglogical.alter_subscription_add_replication_set(subscription_name name, replication_set name) Adds one replication set into a subscriber. Does not synchronize, only activates consumption of events.

  Parameters:

  • subscription_name - name of the existing subscription
  • replication_set - name of replication set to add

• pglogical.alter_subscription_remove_replication_set(subscription_name name, replication_set name)Removes one replication set from a subscriber.

  Parameters:

  • subscription_name - name of the existing subscription
  • replication_set - name of replication set to remove

There is also a postgresql.conf parameter, pglogical.extra_connection_options, that may be set to assign connection options that apply to all connections made by pglogical. This can be a useful place to set up custom keepalive options, etc.

pglogical defaults to enabling TCP keepalives to ensure that it notices when the upstream server disappears unexpectedly. To disable them add keepalives = 0 to pglogical.extra_connection_options.

Replication sets

Replication sets provide a mechanism to control which tables in the database will be replicated and which actions on those tables will be replicated.

Each replicated set can specify individually if INSERTs, UPDATEs, DELETEs and TRUNCATEs on the set are replicated. Every table can be in multiple replication sets and every subscriber can subscribe to multiple replication sets as well. The resulting set of tables and actions replicated is the union of the sets the table is in. The tables are not replicated until they are added into a replication set.

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。