0    600    2


👉 本文共约13294个字,系统预计阅读时间或需50分钟。


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 (

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.


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.



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


These RPMs all require the PGDG PostgreSQL releases from 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
  • 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 | bash
  • PostgreSQL 9.5: curl | bash
  • PostgreSQL 9.6: curl | bash
  • PostgreSQL 10: curl | bash
  • PostgreSQL 11: curl | bash
  • PostgreSQL 12: curl | bash
  • PostgreSQL 13: curl | bash
  • PostgreSQL 14: curl | bash

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).


You can install the “2ndQuadrant’s General Public” repository by running the following instructions as root on the destination Linux server: curl | bash


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 packages for PostgreSQL:


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.



Avatar photo





  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部