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 EDB Postgres Distributed .

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.

• Release Notes
  • pglogical 2.4.3
    • Changes
  • pglogical 2.4.2
    • Changes
  • pglogical 2.4.1
    • Changes
  • pglogical 2.4.0
    • Changes
• 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