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 thepglogical
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 thepglogical
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 neverUPDATED
orDELETED
, and supports onlyINSERTS
. These types of tables do not need primary keys. - Management of triggers and sequences in subscriber databases. By default, triggers
are defined as
ORIGIN
orLOCAL
triggers, and do not fire on the subscriber database when the rows are replicated. All triggers should be checked to ensure that theREPLICA
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, andTEMPORARY
andUNLOGGED
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
androles
, are excluded from the replication and must be managed separately.
What's next
- Replicate data between Google Cloud AlloyDB and AlloyDB Omni
- Replicate data between AlloyDB Omni and other databases