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.