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 file system 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 earlier 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 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.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-25 UTC."],[[["\u003cp\u003eThe \u003ccode\u003epglogical\u003c/code\u003e extension is a logical replication tool for PostgreSQL that supports high availability, disaster recovery, and allows for replication between multiple AlloyDB Omni databases, AlloyDB Omni and Google Cloud AlloyDB, and AlloyDB Omni and other PostgreSQL distributions.\u003c/p\u003e\n"],["\u003cp\u003e\u003ccode\u003epglogical\u003c/code\u003e offers benefits like selective replication, bi-directional and multi-primary replication, cloud provider support, and cross-version replication between different major versions of PostgreSQL.\u003c/p\u003e\n"],["\u003cp\u003eLogical replication with \u003ccode\u003epglogical\u003c/code\u003e has limitations, including the need for manual handling of database and cluster-scoped objects, consideration of primary keys or \u003ccode\u003eREPLICA IDENTITY\u003c/code\u003e for tables, and managing triggers and sequences in subscriber databases.\u003c/p\u003e\n"],["\u003cp\u003eThe \u003ccode\u003epglogical\u003c/code\u003e extension operates at the object level per table and per sequence, requires superuser permissions, does not replicate all object types, and necessitates the use of its API function for DDL replication.\u003c/p\u003e\n"],["\u003cp\u003eProper management and consideration are needed to ensure reliable replication, including handling conflict resolution, replicating DDL commands, and ensuring a robust network between all endpoints.\u003c/p\u003e\n"]]],[],null,["# About the pglogical extension\n\nSelect a documentation version: 15.5.2keyboard_arrow_down\n\n- [Current (16.8.0)](/alloydb/omni/current/docs/about-pglogical)\n- [16.8.0](/alloydb/omni/16.8.0/docs/about-pglogical)\n- [16.3.0](/alloydb/omni/16.3.0/docs/about-pglogical)\n- [15.12.0](/alloydb/omni/15.12.0/docs/about-pglogical)\n- [15.7.1](/alloydb/omni/15.7.1/docs/about-pglogical)\n- [15.7.0](/alloydb/omni/15.7.0/docs/about-pglogical)\n- [15.5.5](/alloydb/omni/15.5.5/docs/about-pglogical)\n- [15.5.4](/alloydb/omni/15.5.4/docs/about-pglogical)\n- [15.5.2](/alloydb/omni/15.5.2/docs/about-pglogical)\n\n\u003cbr /\u003e\n\nThis page provides an overview of the `pglogical` extension, its benefits, and limitations.\n\n\u003cbr /\u003e\n\n\n| The information on this page applies only to AlloyDB Omni for containers. It does not apply to AlloyDB Omni for Kubernetes.\n\n\u003cbr /\u003e\n\nOverview\n--------\n\nThe [`pglogical` extension](https://github.com/2ndQuadrant/pglogical) is a robust and flexible\nlogical replication tool designed for PostgreSQL, and it also supports\nhigh availability (HA) and disaster recovery (DR).\n\nTraditional binary replication, commonly known as *physical replication*, replicates\nchanges at the file system and block level, resulting in a physical mirror in the\ntarget system. Even though the physical replication is robust and protects the\nentire database cluster, it is unidirectional only and requires access to the\nunderlying database data file and write-ahead log (WAL) files.\n\nWhereas, the `pglogical` extension extracts SQL changes from a provider\ndatabase and replicates them, and then replays them against one or more subscriber\ndatabases. This replication is known as *logical replication*.\n| **Note:** The `pglogical` extension uses the terms `provider` and `subscriber` to describe the endpoints that are used in a replication topology. These terms are synonyms for terms such as source/target, publisher/subscriber, and primary/standby.\n\nBy using the `pglogical` extension, you can do the following:\n\n- Replicate data between multiple AlloyDB Omni databases.\n- Replicate data between AlloyDB Omni and Google Cloud AlloyDB.\n- Replicate data between AlloyDB Omni and other PostgreSQL distributions that include many in third-party cloud services.\n\nBenefits\n--------\n\nLogical replication with the `pglogical` extension offers the following benefits:\n\n- **Selective replication:** provides the flexibility to set filters and rules\n to determine what data you want to replicate and where to. You can choose which\n tables are included and how new tables are handled whether they're included or not.\n You can also add column and row filters. An optional `apply delay`\n can be added for situations where you want the subscriber to represent some\n trailing point in time from the provider.\n\n- **Bi-directional and multi-primary replication:** all member databases are open\n in a read/write state and are fully usable. Each endpoint database acts as both\n provider and subscriber, allowing the creation of advanced replication scenarios,\n and enabling the possibility of data updates that are made at different endpoints.\n\n- **Cloud provider support:** Cloud providers such as Google recognize the value\n of the `pglogical` extension and integrate it into their Cloud services,\n such as Google Cloud SQL for PostgreSQL and AlloyDB. Other cloud\n providers also include the `pglogical` extension as an option, allowing multi-cloud\n or hybrid-cloud configurations.\n\n- **Cross-version replication:** as pglogical replicates the actual SQL statements,\n it allows replication between major versions of PostgreSQL. Especially when the\n provider source database is a lower version than the subscriber target database,\n cross-version replication can be implemented with reliability.\n\n The `pglogical` extension offers support for many earlier versions of PostgreSQL\n such as version 9.4 and higher. This makes it an optimal choice for scenarios\n where you are dealing with legacy systems and want to replicate data into more\n modern versions of PostgreSQL such as those used in AlloyDB Omni\n and Google Cloud AlloyDB.\n\nIn summary, the `pglogical` extension provides a feature-rich logical replication\nsolution, with compatibility for older versions of PostgreSQL and Cloud-managed\nservices that include Google Cloud SQL for PostgreSQL and AlloyDB.\n\nLimitations of logical replication\n----------------------------------\n\nAll logical replication technologies, including those used with other\nrelational database platforms, have some limitations, and any mismanagement can\nbreak the replication process.\n\nConsider the following points for a reliable implementation:\n\n- 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.\n- 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.\n- 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.\n- Dealing with conflict resolution either manually or automatically through `who wins` rules.\n- 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.\n- Ensuring that newly created tables and sequences are manually or automatically added to replication sets on primary databases.\n- Ensuring that a robust, performant, reliable, and secured TCP network exists between all endpoints in the replication topology.\n\nAdditional restrictions and limitations of the `pglogical` extension include the\nfollowing:\n\n- Superuser permissions are required for `pglogical` version 2.4.3.\n- 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.\n- To replicate DDL, the pglogical API function must be used. Native DDL commands are not replicated, except for the `TRUNCATE` command.\n- 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.\n\nWhat's next\n-----------\n\n- [Replicate data between AlloyDB for PostgreSQL and AlloyDB Omni](/alloydb/omni/15.5.2/docs/replicate-data-alloydb-omni).\n- [Replicate data between AlloyDB Omni and other databases](/alloydb/omni/15.5.2/docs/replicate-data-omni-other-db)."]]