Snapshot and synchronization overview
Replication Server performs two different types of replications. These two main types are called snapshot replication and synchronization replication.
In either method, the source tables refer to the tables from which the replication data is originating. In a single-master replication system, it's the publication. In a multi-master replication system it's the primary node whose changes are being replicated to another primary node.
The target tables are the tables that are receiving the replication data from the source tables. In a single-master replication system, it's the subscription tables. In a multi-master replication system, it's the primary node receiving changes from another primary node.
In snapshot replication, all existing rows in the target tables are deleted using the database system’s TRUNCATE command. The tables are then completely reloaded from the source tables of the publication.
In synchronization replication, only the changes (inserts, updates, and deletions) to the rows in the source tables since the last replication are applied to the target tables.
Note
Deleting all rows in a source table executed by the SQL TRUNCATE command results in replication to the target tables only if the log-based method of synchronization replication is used. If the trigger-based method of synchronization replication is used, executing the TRUNCATE command on a source table doesn't replicate the effect to the target tables. You must perform a snapshot from the source table to the target tables if you use the trigger-based method.
Deleting all rows in a source table executed by the SQL TRUNCATE command results in replication to the target tables only if the log-based method of synchronization replication is used. If the trigger-based method of synchronization replication is used, executing the TRUNCATE command on a source table doesn't replicate the effect to the target tables. You must perform a snapshot from the source table to the target tables if you use the trigger-based method.
Synchronization replication is implemented using two different methods: the trigger-based method and the log-based method.
In the trigger-based method, changes to rows in the source tables result in row-based triggers executing. These triggers record the changes in shadow tables. The changes recorded in the shadow tables are then periodically extracted from the shadow tables, converted to an in-memory data structure, and applied to the target tables by means of SQL statements executed using JDBC. See Synchronization replication with the trigger-based method for information on the trigger-based method.
In the log-based method, changes to rows in the source tables are extracted from the write-ahead log segments (WAL files) using asynchronous streaming replication implemented by the logical decoding feature available in Postgres database servers. The extracted changes are converted to an in-memory data structure and applied to the target tables by means of SQL statements executed using JDBC. See Synchronization replication with the log-based method for information on the log-based method.
In a multi-master replication system, the manner in which changes accumulated on all primary nodes are replicated to all other primary nodes is conceptually done in groups identified by the source primary node with the changes to be replicated. See Multi-master parallel replication for information on this process and the improvement for the log-based method with parallel replication.
In a single-master replication system, always do the first replication to a newly created subscription with a snapshot. You can perform subsequent replications by snapshot or by synchronization, provided that the publication isn't defined as a snapshot-only publication, as discussed in Snapshot-only publications.
In a multi-master replication system, you must do the first replication from the primary definition node to a newly added primary node with a snapshot. Subsequent replications between primary nodes occur by synchronization. However, it is possible to perform subsequent snapshots from the primary definition node to any other primary node.
Synchronization replication with the trigger-based method
Oracle and SQL Server databases can only be used as publication databases with the trigger-based method. Additionally, Oracle and SQL Server databases can be used as subscription databases with the WAL-based replication method.
The publication server also creates a shadow table for each source table on which triggers were created. A shadow table is a table used by Replication Server to record the changes (inserts, updates, and deletions) made to a given source table. A shadow table records three types of record images:
Logical replication slots
- For each row inserted into the source table, the shadow table records the image of the inserted row.
- For each existing row that is updated in the source table, the shadow table records the after image of the updated row.
- For each row deleted from the source table, the shadow table records the identity columns (primary key or unique columns) value of the deleted row
Synchronization replication with the log-based method
In PostgreSQL 9.4, a feature was introduced called logical decoding (also called logical replication or changeset extraction). Logical decoding lets you extract data manipulation language (DML) changes from the write-ahead log segments (WAL files) in a readable format.
With logical decoding, you can capture data changes to the publication tables without impacting the online transaction processing rate against these tables that occurs when using the trigger-based method. The trigger-based method results in firing row-level triggers whenever data changes occur and then inserting these data changes into shadow tables for temporary storage. It then applies the changes to the target databases.
Thus, extracting data changes using logical decoding can help improve database server throughput and replication latency. However, the logical decoding interface streams changes for all tables in a given database, which can have a performance overhead associated with it.
Logical replication slots
When using the log-based method on a publication database, the underlying logical decoding framework exposes the data changes (the changeset stream) by means of a logical replication slot.
A logical replication slot represents a changeset stream and applies to a single database. Replication Server assigns a unique identifier, called the slot name, to each logical replication slot it creates in the form xdb_<dboid>_<pubid> where <dboid> is the publication database object identifier (OID) and <pubid> is the publication ID assigned by Replication Server. All slot names are unique in a Postgres database cluster.
Thus, for each single-master replication system using the log-based method, a replication slot is required for the publication database of each such system.
For a multi-master replication system using the log-based method, each primary node requires a replication slot.
Replication origin
Starting with Postgres version 9.5, replication origin was introduced to the logical decoding framework. Replication origin allows an application to identify, label, and mark certain aspects of a logical decoding session ...
With replication origin, the publication server can set up the logical decoding sessions so that these replayed changes aren't included in the changeset stream transmitted over the network to the publication server. This configuration eliminates the performance overhead.
The following are the conditions under which replication origin is used:
- Replication origin applies to multi-master replication systems only, not to single-master replication systems.
- Replication origin eliminates streaming of replayed changes only from Postgres versions 9.5 or later. Replayed changes are still included in the changeset stream from Postgres version 9.4 but are discarded by the publication server. Thus, multi-master replication systems consisting of both Postgres versions 9.4 and 9.5 use the replication origin advantage on the 9.5 database servers.
- You must set the max_replication_slots configuration parameter at a certain minimal level to ensure that the publication server can create the additional replication slots for replication origin