With PostgreSQL 10, along with the existing Streaming Replication (Physical Replication) feature, Logical Replication was introduced. Thanks to this feature, we can now use some flexible replication capabilities that we couldn't before. To define Logical Replication fundamentally, it will be easier first to explain its differences from physical Streaming Replication.
Streaming Replication, means that XLOG records, in other words data, are continuously transferred to a physically different server. During this physical data transfer, we had certain limitations. For example, we could replicate an entire PostgreSQL cluster, but we couldn't send just a single database or table to the target. Physical replication met our Hot Standby server needs, meaning it allowed us to keep an up-to-date copy of the entire PostgreSQL server (data, roles, users, etc.) on the remote side.
Logical Replication Logical Replication is a mechanism that applies changes (insert, update, delete) occurring on a table or tables in a source database to a target database. It operates on a Pub-Sub logic. It works in such a way that the Target Database server subscribes (like a publication subscription) to a Publication to be set up on the Source Database server. pglogical It works using the module.
Logical Replication Features
- Logical Replication is generally determined by being defined by a primary key or a unique key that defines the replication items (primary key or unique index).
- Different security or access permissions can be defined on the tables of the target database. Even different indexes can be created.
- Unlike physical replication, logical replication can work between different versions. PostgreSQL (provided it is > 9.4 and above)
- Logical replication uses event-based filtering, which is performed on the source database side and published.
- Multiple subscriptions can be made to a single publication.
- By selecting less data, it can be kept up to date on the other side, and partitioned tables are also included.
- Xdb Replicator consumes much fewer resources than trigger-based replications and similar methods.
- Can be used during data migration.
- Data transformation, meaning the modification of data to be replicated before it is published, can be enabled.
- The source and target tables may have a different number of columns or order, but the column data types and column names must be the same.
When can it be used?
When to use Logical Replication must be well defined. Currently, Physical Replication (Streaming Replication) should remain your primary choice as the existing backup solution. However;
- If you want to consolidate multiple databases into a single database for analytical analysis,
- When you want to perform replication between different PostgreSQL versions,
- When you want to collect incremental changes on another database,
- When you want to replicate certain tables and define access with different permissions for different user groups,
- When you want to display a portion of the data in tables containing critical data by transforming it for different users
Using Logical Replication will be the ideal solution.
When not to use?
- Tables must have the same name on both the publisher and subscriber sides.,
- Tables must have at least one primary key or a unique key,
- Bi-directional replication is not possible. Only changes on one side are propagated to the other. (Depending on the situation, this can be both a disadvantage and an advantage),
- DDL changes, sequences, TRUNCATE are not replicated to the other side,
- Subscriptions are not possible via the same host (the subscription will be locked).
