About the pglogical extension

This page provides an overview of the pglogical extension, its benefits, and limitations.

Overview

The pglogical extension is a robust and flexible logical replication tool designed for PostgreSQL, and it also supports high availability (HA) and disaster recovery (DR).

Traditional binary replication, commonly known as physical replication, replicates changes at the filesystem and block level, resulting in a physical mirror in the target system. Even though the physical replication is robust and protects the entire database cluster, it is unidirectional only and requires access to the underlying database data file and write-ahead log (WAL) files.

Whereas, the pglogical extension extracts SQL changes from a provider database and replicates them, and then replays them against one or more subscriber databases. This replication is known as logical replication.

By using the pglogical extension, you can do the following:

  • Replicate data between multiple AlloyDB Omni databases.
  • Replicate data between AlloyDB Omni and Google Cloud AlloyDB.
  • Replicate data between AlloyDB Omni and other PostgreSQL distributions that include many in third-party cloud services.

Benefits

Logical replication with the pglogical extension offers the following benefits:

  • Selective replication: provides the flexibility to set filters and rules to determine what data you want to replicate and where to. You can choose which tables are included and how new tables are handled whether they're included or not. You can also add column and row filters. An optional apply delay can be added for situations where you want the subscriber to represent some trailing point in time from the provider.

  • Bi-directional and multi-primary replication: all member databases are open in a read/write state and are fully usable. Each endpoint database acts as both provider and subscriber, allowing the creation of advanced replication scenarios, and enabling the possibility of data updates that are made at different endpoints.

  • Cloud provider support: Cloud providers such as Google recognize the value of the pglogical extension and integrate it into their Cloud services, such as Google Cloud SQL for PostgreSQL and AlloyDB. Other cloud providers also include the pglogical extension as an option, allowing multi-cloud or hybrid-cloud configurations.

  • Cross-version replication: as pglogical replicates the actual SQL statements, it allows replication between major versions of PostgreSQL. Especially when the provider source database is a lower version than the subscriber target database, cross-version replication can be implemented with reliability.

    The pglogical extension offers support for many older versions of PostgreSQL such as version 9.4 and higher. This makes it an optimal choice for scenarios where you are dealing with legacy systems and want to replicate data into more modern versions of PostgreSQL such as those used in AlloyDB Omni and Google Cloud AlloyDB.

In summary, the pglogical extension provides a feature-rich logical replication solution, with compatibility for older versions of PostgreSQL and Cloud-managed services that include Google Cloud SQL for PostgreSQL and AlloyDB.

Limitations of logical replication

All logical replication technologies, including those used with other relational database platforms, have some limitations, and any mismanagement can break the replication process.

Consider the following points for a reliable implementation:

  • Consideration on how to handle database-scoped and cluster-scoped objects that are outside of the replication scope. The pglogical extension works at the database level and against a specified set of tables and sequences only. Other object types, such as functions and procedures, must be replicated using some other method.
  • It is recommended that all replication tables must have a primary key. It is possible to utilize the table REPLICA IDENTITY feature to inform the pglogical extension about which columns uniquely identify the rows. This must be avoided where possible. Tables that do not have primary keys, are static in nature, and are never UPDATED or DELETED, and supports only INSERTS. These types of tables do not need primary keys.
  • Management of triggers and sequences in subscriber databases. By default, triggers are defined as ORIGIN or LOCAL triggers, and do not fire on the subscriber database when the rows are replicated. All triggers should be checked to ensure that the REPLICA option is set for any trigger so that it does not fire on the subscriber end unless it is required.
  • Dealing with conflict resolution either manually or automatically through who wins rules.
  • Replication of Data Definition Language (DDL) commands by either manually implementing on all endpoints, or automatically replicating DDL to subscriber databases using the appropriate pglogical API function on the provider database.
  • Ensuring that newly created tables and sequences are manually or automatically added to replication sets on primary databases.
  • Ensuring that a robust, performant, reliable, and secured TCP network exists between all endpoints in the replication topology.

Additional restrictions and limitations of the pglogical extension include the following:

  • Superuser permissions are currently required for pglogical version 2.4.3.
  • While most tables and sequences can be replicated, other object types are not replicated by the pglogical extension, and TEMPORARY and UNLOGGED tables are not replicated.
  • To replicate DDL, the pglogical API function must be used. Native DDL commands are not replicated, except for the TRUNCATE command.
  • Operates on an object level per table and per sequence, and is deployed per database. This means that some objects, including cluster-scoped objects such as users and roles, are excluded from the replication and must be managed separately.

What's next