pglogical 2: per-database logical replication with filters and conflict detection

Logical streaming that stops at the database boundary

pglogical 2 is a C extension for PostgreSQL that replicates data changes between databases rather than shipping whole page images. The unit of replication is the database, not the server. That single design decision has consequences for how you plan a rollout, because a cluster hosting several databases is not a replication target here in the way that physical streaming replication treats a whole instance as one object.

The use cases the project lists are narrow and specific: upgrades between major versions, full database replication, selective replication of sets of tables, selective replication of rows on either the publisher or subscriber side, selective replication of columns on the publisher side, and data gather or merge from several upstream servers. Those are the jobs the project claims. It does not claim to be a general data integration tool, and there is no mention of heterogeneous targets such as external message queues or non-PostgreSQL databases anywhere in the documentation.

The project describes itself as providing much faster replication than Slony, Bucardo or Londiste, and as enabling cross-version upgrades. Those older technologies gave the vocabulary its shape: the documentation deliberately reuses terms such as nodes, providers and subscribers from Slony, and defines a replication set as a collection of tables.

Provider, subscriber, and the replication set vocabulary

The model is a publish and subscribe arrangement. A node is a PostgreSQL database instance, and nodes take on the roles of provider or subscriber. A replication set is a named collection of tables, and it is the object you subscribe to. Because the terms are inherited from Slony, a reader who has run Slony will recognise the shape of the configuration immediately, while a reader coming from an entirely different background may find the names less intuitive than they first appear.

Two architectural points are worth separating from the vocabulary. First, one provider may feed multiple subscribers without incurring additional disk write overhead, which is the main structural argument for the approach when you need fan-out. Second, a subscriber can merge changes coming from several origins, and it can detect conflicts between those changes with automatic and configurable conflict resolution.

That second point is where the project itself draws a line. The documentation states that some, but not all, aspects required for multi-master are covered. Read that as a boundary rather than a hedge. A subscriber that merges several origins is not the same thing as a system where every node accepts writes and reconciles conflicts, and the schema requirements described below make the difference concrete.

Row filters and column selection narrow the stream

Where physical replication copies everything, pglogical lets you decide what travels. Selective replication of table rows is available at either the publisher or the subscriber side, which is the more useful of the two arrangements in practice: filtering at the provider keeps unwanted rows out of the stream entirely, while filtering at the subscriber lets one stream feed several differently scoped destinations. Selective replication of table columns is only available on the publisher side, so the shape of the data on the wire is decided where the stream is produced.

Replication sets add the coarser filter. Because a replication set is a collection of tables, subscribing to a set rather than to a whole database gives you a way to move a subset of a schema without moving the rest of it. Combine that with row filters and column selection and you can replicate a narrow slice of a wide table set to a reporting target while the operational target receives everything.

The catch is that the filters operate against a schema the project insists must line up exactly on both ends. The filtering is a distribution decision, not a transformation feature. The repository topics list includes data-transformation, but the requirements described for tables are about identity of names, columns, types and keys rather than about reshaping records on the way through. If your plan depends on converting values or reconciling differing column layouts between the two databases, that is a gap you will have to close outside the extension.

Installing the extension on both provider and subscriber

Both ends need the extension installed and created before any subscription can exist. Packages are available as RPMs through yum for Fedora, CentOS and RHEL, and as DEBs through apt for Debian and Ubuntu, with source builds also possible. The RPM route requires the PGDG PostgreSQL releases, and the documentation is explicit that the packages cannot be used with the stock PostgreSQL releases shipped in Fedora and RHEL.

On a Red Hat family system with the PGDG repository already configured, the server and the extension are separate installs:

yum install postgresql17-server postgresql17-contrib

yum install pglogical_17

The package name follows the server version, so a PostgreSQL 16 install uses `pglogical_16` and a PostgreSQL 13 install uses `pglogical_13`. On Debian and Ubuntu the layout differs, with the extension packaged under the server package name:

sudo apt-get install postgresql-17-pglogical

Before either install, the underlying server has to be present, which on Debian means a command such as:

sudo apt-get install postgresql-17

The final step is identical on both sides and is the one the requirements section is emphatic about. The extension must be installed on provider and subscriber, and you must `CREATE EXTENSION pglogical` on both:

CREATE EXTENSION pglogical

If that step is skipped on either node, the corresponding role cannot be established. There is no configuration file to edit, no port to set and no environment variable to export; the settings live in the database as catalog entries and SQL objects once the extension is present.

Schema parity rules that break a subscription quietly

The requirements section is the strictest part of the documentation, and it is where a first attempt most often goes wrong. Tables on the provider and the subscriber must have the same names and be in the same schema. The documentation notes that future revisions may add mapping features, which tells you plainly that no mapping exists today. If your source and target schemas diverge in naming, you have to align them yourself before replicating.

Columns must match with the same data types in each column. Constraints may differ, but only in one direction: `CHECK` and `NOT NULL` constraints must be the same or weaker, in other words more permissive, on the subscriber than on the provider. A subscriber that enforces something the provider does not will reject changes the provider happily produced.

Primary keys must be identical on both ends, and the documentation advises against adding extra `UNIQUE` constraints beyond the `PRIMARY KEY`. Without a key there is no reliable way to identify a row for insert, update or delete, so an unmatched table is a table that will not replicate. The remaining restrictions are collected in a dedicated limitations section, which the requirements section points to but does not reproduce, so the full set of edge cases has to be read there before you commit a schema to this mechanism.

There is a version floor to keep in view. Provider and subscriber nodes must run PostgreSQL 9.4 or newer, and PostgreSQL 9.5 or newer is required for replication origin filtering and for conflict detection. A subscriber can additionally be Postgres-XL 9.5 or newer.

Conflict detection that stops short of full multi-master

When several upstream servers write to the same subscriber, changes can collide, and pglogical handles this with automatic and configurable conflict resolution. The same machinery supports cascading replication, which is implemented as changeset forwarding: a node that receives a change can pass it on to the next node in the chain rather than every node needing a direct path back to the original provider.

This is genuinely useful for gather and merge topologies, where a central subscriber collects data from several regional sources. It is also where the project's own hedging becomes the most important line in the documentation, because some, but not all, aspects required for multi-master are implemented. The mechanism for handling conflicts between merged origins is present; the guarantees usually associated with multi-master are not.

The failure mode to plan for is not a crash but a divergence. Because schema identity and primary keys must match exactly, a subscriber that received only filtered rows may be missing the key values it needs to apply a later change, and the result is a mismatch that surfaces as missing or rejected rows rather than as a clean error at subscription time. The documentation does not describe a dry-run mode, a validation command, or a reconciliation tool that would check a schema pair in advance, so the safest preparation is your own comparison of table names, column types and key definitions between the two databases before you create the subscription.

Where new features are landing instead of pglogical

The maintenance picture here needs two separate statements, and conflating them is the most common misreading of this project.

The first is about the codebase itself. The repository is not archived, and the last push was on 2026-07-28, which carried the release pglogical 2.4.8, following 2.4.7 on 2026-06-01 and 2.4.6 on 2025-08-23. The default branch is REL2_x_STABLE, and the tree carries a long chain of upgrade scripts, ending at pglogical--2.4.8.sql in the Makefile's DATA list alongside every earlier version from 1.0.0 onwards. The source is arranged in per-version compatibility directories, compat94/ through compat19/, and the build lists `pglogical_output` in `MODULES` and an EXTENSION entry named pglogical.

The second statement comes from the documentation itself, which says that while pglogical is actively maintained, EnterpriseDB, having acquired 2ndQuadrant in 2020, focuses new feature development on a descendant of pglogical called Postgres Distributed. The documentation names what that descendant added: DDL replication, write leaders and parallel apply. The homepage of the project points at the EDB documentation for pglogical 2, so the operational documentation lives with the same vendor that is investing in the successor.

That split defines the real alternative. Rather than switching replication mechanisms, the documented path is moving to a descendant that adds the features pglogical lacks. The practical difference is one of scope: pglogical replicates data and offers filtering and conflict handling, while the descendant is positioned to carry schema changes and distribute write ownership across nodes.

A custom licence and the questions to ask before installing

The licensing position deserves a note rather than advice. The project is marked as carrying a custom licence that GitHub cannot classify, and the repository carries a LICENSE file plus a COPYRIGHT file. Any organisation with a policy on open source licence text, or an internal list of approved licences, needs to read those files directly and have them reviewed. Nothing in the documentation says what the terms are, so no conclusion about commercial use, redistribution or support obligations can be drawn from the repository metadata alone.

The upgrade path is easier to reason about. Because the SQL extension version chain is maintained as a linear set of scripts, moving between pglogical versions follows the ordinary PostgreSQL extension model, with a numbered upgrade script for each step, for example the file that bridges 2.4.7 to 2.4.8. The presence of compatibility directories for every supported server generation means the C code is compiled against a matching PostgreSQL major version, and the packages are published per version, so an upgrade of the server and an upgrade of the extension are coordinated steps rather than independent ones.

What the documentation does not provide is as telling as what it does. There is no documented rollback procedure, no guide to backing out of a subscription after data has flowed, and no compatibility matrix beyond the stated minimum versions and the per-version package names. If you are adopting this for a production pipeline, the gaps to close are yours to close: read the LICENSE file, read the limitations and restrictions section in full, and check the two schemas against each other column by column before the first subscription carries any traffic.

Editorial conclusion

Adopt pglogical 2 when you need selective table or row level logical replication between two PostgreSQL databases and you can live with strict schema parity and a hand-managed extension. Skip it if you need DDL replication, parallel apply, write leaders, or a product with an unambiguous open source licence. Before installing, verify that both nodes meet the 9.4 minimum, that every replicated table has matching primary keys on provider and subscriber, and that your organisation accepts the custom LICENSE file rather than an OSI licence.

Frequently asked questions

Official sources

1. 2ndQuadrant/pglogical on GitHub
2. Issues
3. Project website
4. README
5. Releases