Table of Contents Previous Next


2 Overview : 2.2 Replication Concepts and Definitions

xDB Replication Server is a software product that enables the implementation of a replication system. A replication system is software and hardware whose purpose is to make a copy of data from one location to another and to ensure the copied data is the same as the original over time.
Single-Master Replication (SMR). Changes (inserts, updates, and deletions) to table rows are allowed to occur in a designated master database. These changes are replicated to tables in one or more slave databases. The replicated tables in the slave databases are not permitted to accept any changes except from its designated master database. (This is also known as master-to-slave replication.)
Multi-Master Replication (MMR). Two or more databases are designated in which tables with the same table definitions and initial row sets are created. Changes (inserts, updates, and deletions) to table rows are allowed to occur in any database. Changes to table rows in any given database are replicated to their counterpart tables in every other database.
Replication from Oracle to PostgreSQL
Replication from Oracle to Postgres Plus Advanced Server
Replication from SQL Server to PostgreSQL
Replication from SQL Server to Postgres Plus Advanced Server
Note: A given database cannot simultaneously participate in both a single-master replication system and a multi-master replication system.
xDB Replication Server uses an architecture called publish and subscribe. The data to be made available for copying by a replication system is defined as a publication. To get a copy of that data, you must “subscribe” to that publication. The manner in which you subscribe is slightly different for single-master and multi-master replication systems.
In xDB Replication Server a publication is defined as a named set of tables and views within a database. The database that contains the publication is called the publication database of that publication.
In a single-master replication system, to get a copy of an xDB Replication Server publication, you must create a subscription. An xDB Replication Server subscription is a named association of a publication to a database to which the publication is to be copied. This database is called the subscription database.
In a single-master replication system, replication is said to occur when xDB Replication Server initiates and completes either of the following processes: 1) applies changes that have been made to rows in the publication since the last replication occurred, to rows in tables of the subscription database (called synchronization); or 2) copies rows of the publication to empty tables of the subscription database (called a snapshot). See Section 2.2.6 for further discussion on snapshots and synchronization.
The subscription tables are the tables in the subscription database created from corresponding tables or views in the publication.
Note: In a single-master replication system xDB Replication Server creates a table in the subscription database for each view contained in the publication.
In a multi-master replication system, the concept and definition of replication is nearly identical to a single-master replication system with the following modifications: 1) synchronization can occur between any pair of databases (referred to as master nodes) participating in the replication system; and 2) a snapshot can occur from the publication database designated as the master definition node to any of the other master nodes.
pp_xdb_repsvr_ug_overview_1
pp_xdb_repsvr_ug_overview_2
pp_xdb_repsvr_ug_overview_3
pp_xdb_repsvr_ug_overview_4
pp_xdb_repsvr_ug_overview_4_mmr
xDB Replication Server performs master-to-slave replication when a single-master replication system is implemented. The publication is the master and the subscription is the slave. In a master-to-slave relationship, changes are propagated in one direction only, from the master to the slave.
pp_xdb_repsvr_ug_overview_5
Generally, changes must not be made to the definitions of the publication tables or the subscription tables. If such changes are made to the publication tables, they are not propagated to the subscription and vice versa unless the DDL change replication feature is used as described in Section 7.8. If changes are made to the table definitions without using the DDL change replication feature, there is a risk that future replication attempts may fail.
Changes must not be made to the rows of the subscription tables. If such changes are made, they are not propagated back to the publication. If changes are made to the subscription table rows, it is fairly likely that the rows will no longer match their publication counterparts. There is also a risk that future replication attempts may fail.
A master node is a database participating in a multi-master replication system.
The database (master node) in which the publication is initially defined is specially designated as the master definition node (MDN). There can be only one master definition node at any given time, however, it is possible to change which master node is the master definition node. When it is important to make a distinction between the master definition node and all other master nodes that are not the master definition node, the latter are referred to as non-MDN nodes.
Generally, changes must not be made to the table definitions in any of the master nodes including the master definition node. If such changes are made, they are not propagated to other nodes in the multi-master replication system unless they are made using the DDL change replication feature described in Section 7.8. If changes are made to tables without using the DDL change replication feature, there is a risk that future replication attempts may fail.
pp_xdb_repsvr_ug_overview_5_mmr
xDB Replication Server performs replications asynchronously. The systems hosting the databases do not always have to be running continuously in order for successful replication to occur. If one system goes offline, replication resumes when it comes back online if there is still pending data to replicate.
In either method, the source tables refer to the tables from which the replication data is originating (the publication in a single-master replication system, or the master node whose changes are being replicated to another master node in a multi-master replication system).
The target tables are the tables that are receiving the replication data from the source tables (the subscription tables in a single-master replication system, or the master node receiving changes from another master node in a multi-master replication system).
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: Deletion of 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, execution of the TRUNCATE command on a source table does not replicate the effect to the target tables. You must perform a snapshot from the source table to the target tables if the trigger-based method is used. (The difference between the trigger-based method and the log-based method is discussed as follows.)
In the trigger-based method changes to rows in the source tables result in the firing of row-based triggers. 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 Section 2.2.9 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 Section 2.2.10 for information on the log-based method.
When a publication is created in a single-master replication system, the publication can be defined as a snapshot-only publication. Replication from a snapshot-only publication can only be done using the snapshot replication method. Synchronization replication is not permitted on a snapshot-only publication.
See Section 2.4.4 for a discussion of the advantages of using a snapshot-only publication.
For Oracle and SQL Server only: Oracle and SQL Server target tables are loaded using JDBC batches of INSERT statements.
For Postgres only: In general, Postgres target tables are loaded using the JDBC COPY command since using truncation and COPY is generally faster than if you were to execute an SQL DELETE statement against the entire table and then add the rows using JDBC batches of INSERT statements. If the COPY command fails, the publication server retries the snapshot using JDBC batches of INSERT statements.
If the target table (regardless of database type) contains a large object data type such as BYTEA, BLOB, or CLOB then rows are loaded one at a time per batch using an INSERT statement. This is to avoid a heap space error resulting from potentially large rows. Loading time can be decreased by allowing multiple inserts per batch, which is done by adjusting the configuration option lobBatchSize described in Section 5.8.1.
Note: Advanced Server supports a number of aliases for data types. Such aliases that translate to BYTEA are treated as large object data types. See the Database Compatibility for Oracle Developer’s Guide for a listing of Advanced Server data types.
Under certain circumstances, the corresponding Postgres target table created for certain types of Oracle partitioned tables is a set of inherited tables. In these cases, the SQL DELETE statement is used on the inherited child tables instead of truncation. See Section 10.4.1.4 for additional information on replicating Oracle partitioned tables.
A server configuration option is available that forces the snapshot replication process to use the Oracle database link utility instead of JDBC COPY to populate the Postgres target tables from an Oracle publication. Oracle database link provides an additional performance improvement over JDBC COPY. See Section 5.8.1 for information on using the Oracle database link option.
See Section 5.8.1 for information on various configuration options to optimize snapshot replication.
The publication server also creates a shadow table for each source table on which triggers have been created. A shadow table is a table used by xDB 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: 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 primary key value of the deleted row.
Note: In a multi-master replication system, the before image of an updated row is also stored in the shadow table in order to perform update conflict detection. See Section 6.6 for information on conflict detection in a multi-master replication system.
Though changes made to the source tables since the last replication occurred are applied to the target tables using SQL INSERT, UPDATE, and DELETE statements, the actual SQL statements run against the target tables are not the same SQL statements that were run against the source tables.
When synchronization replication occurs, the publication server executes JDBC batches of SQL statements (also referred to as transaction sets) against the target tables. The batches contain an INSERT statement for each shadow table row recording an insert operation, an UPDATE statement for each shadow table row recording an update operation, and a DELETE statement for each shadow table row recording a delete operation. Each batch is executed in one transaction.
Note: A single SQL statement executed against a source table may result in many rows recorded in a shadow table, and therefore, many SQL statements executed against the target table. For example, if a single UPDATE statement affects 10 rows in the source table, 10 rows will be inserted into the shadow table – one for each row in the source table that was updated. When the publication server applies the changes to the target table, 10 UPDATE statements will be executed.
Note: For greater efficiency, when changes to the source tables consist of SQL statements that each affect a large number of rows, the publication server may employ the use of prepared SQL statements. See Section 5.8.2 for directions on how to control the usage of prepared SQL statements as well as information on various other configuration options to optimize synchronization replication.
In PostgreSQL 9.4 a feature has been introduced called logical decoding (also called logical replication or changeset extraction). This feature provides the capability to extract data manipulation language (DML) changes from the Write-Ahead Log segments (WAL files) in a readable format.
In a single-master replication system, whether the master database uses the trigger-based method or the log-based method has no additional impact on the rules for choosing the subscription database as described in Section 10.1. For example, even if the log-based method is chosen for the master database, the subscription database may be running on Postgres version 9.4 as well as any supported, earlier version of Postgres, as well as Oracle or SQL Server as described in Section 10.1.
wal_level. Set to logical.
max_wal_senders. Specifies the maximum number of concurrent connections (that is, the maximum number of simultaneously running WAL sender processes). Set at minimum, to the total number of master databases of single-master replication systems and master nodes of multi-master replication systems on this database server that will use the log-based method.
max_replication_slots. Specifies the maximum number of replication slots. If the database server supports both single-master replication systems and multi-master replication systems, then max_replication_slots must be set at minimum to the sum of the requirements for both replication systems. For support of SMR systems, the minimum requirement is the total number of master databases of the single-master replication systems that will use the log-based method. For support of MMR systems, the minimum requirement is the total number of master nodes in the multi-master replication system multiplied by the number of master nodes residing on this database server. For information, see Section 2.2.10.4.
track_commit_timestamp. Set to on. This configuration parameter applies only to Postgres database servers of version 9.5. See Section 6.6.1 for additional information.
Also see Section 5.1.2 for setting these parameters for a single-master replication system. See Section 6.1.2 for a multi-master replication system.
In addition, the pg_hba.conf configuration file of the Postgres database server must contain an entry permitting REPLICATION access for each database using the log-based method running on the database server. The access must be permitted to the publication database user specified when creating the publication database definition using the xDB Replication Console (see Section 5.2.2 for a single-master replication system or Section 6.2.2 for a multi-master replication system) or the xDB Replication Server Command Line Interface (CLI) (see Section 8.3.7).
See Section 5.1.6.3 for setting REPLICATION access for a single-master replication system. See Section 6.1.5 for a multi-master replication system.
A logical replication slot represents a changeset stream and applies to a single database. The xDB 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 the xDB Replication Server. All slot names are unique within a Postgres database cluster.
The maximum number of replication slots permitted for a database server is controlled by the max_replication_slots configuration parameter in the postgresql.conf file. Therefore this configuration parameter must be set to a large enough value to account for all publication databases defined with the log-based method of single-master replication systems running on the database server as well as all master nodes of a multi-master replication system defined with the log-based method running on the database server. Additional replication slots are required to support the usage of replication origin (see Section 2.2.10.4). See Section 5.1.2 for additional information on configuration parameters for single-master replication systems. See Section 6.1.2 for multi-master replication systems.
The changeset stream is accessible to the xDB publication server by the WAL sender process (walsender) using the streaming replication protocol.
The xDB publication server connects using the walsender interface through which changes are streamed on a continual basis. The continuous streaming eliminates the need for explicitly polling for changes.
1.
A streaming replication connection to the database server is opened using libpq to establish a walsender communication channel.
4.
On the next scheduled interval, the in-memory cached data changes are applied to each of the target databases in JDBC batches of SQL statements (referred to as transaction sets) in the same manner as described in Section 2.2.9 for the trigger-based method. If one or more target database servers are not accessible, the data changes are saved in a local file on the host running the publication server. Section 2.2.10.5 for information on in-memory caching and data persistence.
Note: A single SQL statement executed against a source table may result in many rows modified and returned in the changeset stream, and therefore, many SQL statements executed against the target table. For example, if a single UPDATE statement affects 10 rows in the source table, 10 rows will be returned in the changeset stream – one for each row in the source table that was updated. When the publication server applies the changes to the target table, 10 UPDATE statements will be executed.
Starting with Postgres version 9.5, a feature called replication origin has been introduced to the logical decoding framework. Replication origin allows an application to identify, label, and mark certain aspects of a logical decoding session.
As previously described, the log-based method uses the WAL files to obtain the changes applied to the publication tables. After the changes are retrieved through the walsender interface, the publication server applies the set of changes to the other master nodes using transaction sets consisting of JDBC batches of SQL statements. When these changes are applied to the tables in the other target master nodes, the same changes are also recorded in the WAL files of each database server hosting the target master nodes.
The max_replication_slots configuration parameter must be set at a certain minimal level to ensure that the publication server can create the additional replication slots for replication origin.
The following table shows the required, minimum settings for max_replication_slots as well as max_wal_senders.
If the max_replication_slots parameter is not set to a high enough value, synchronization replication still succeeds, but without the replication origin performance advantage.
The replication origin name is assigned in the format xdb_srcdbname_pubname_remotedbid where srcdbname is the source database name, pubname is the publication name, and remotedbid is the publication database ID of a remote database.
The xDB Replication Server architecture utilizes Java object serialization to persist the in-memory state of the data. Object serialization is the conversion of object data and other relevant information to a sequence of bytes that can then be stored in a file.
The cache size corresponds to the heap size configured for the publication server by the -Xmxnnnm setting of the JAVA_HEAP_SIZE parameter in the xDB Startup Configuration file. See Section 2.3.1.4 for information on the xDB Startup Configuration file.
Table filters specify the selection criteria for rows in publication tables or views that are to be included during replications to subscriptions from the publication database in a single-master replication system or between master nodes in a multi-master replication system. Rows that do not satisfy the selection criteria are excluded from replications to subscriptions or master nodes on which these table filters have been enabled.
Note (For MMR only): When using table filters in a multi-master replication system, the master definition node, which provides the source of the table content for a snapshot, should contain a superset of all the data contained in the other master nodes of the multi-master replication system. This ensures that the target of a snapshot receives all of the data that satisfies any filtering criteria enabled on the other master nodes.
Note: In the following discussion, a result set refers to the set of rows in a table satisfying the selection criteria of an UPDATE or DELETE statement executed on that table.
When an INSERT statement is executed on a source table followed by a synchronization replication, the row is inserted into the target table of the synchronization if the row satisfies the filtering criteria. Otherwise the row is excluded from insertion into the target table.
When an UPDATE statement is executed on a source table followed by a synchronization replication, the UPDATE result set of the source table determines the action on the target table of the synchronization as follows.
When a DELETE statement is executed on a source table followed by a synchronization replication, the DELETE result set of the source table determines the action on the target table of the synchronization as follows.
Thus, regardless of whether the transaction on the source table is an INSERT, UPDATE, or DELETE statement, the goal of a table filter is to ensure that all rows in the target table satisfy the filter rule.
Note: This REPLICA IDENTITY FULL setting is not required for tables in single-master, snapshot-only publications, See Section 2.2.7 for information on snapshot-only publications.
This setting is done with the ALTER TABLE command as shown by the following:
ALTER TABLE schema.table_name REPLICA IDENTITY FULL
For additional information see the ALTER TABLE SQL command in the PostgreSQL Core Documentation located at (http://www.postgresql.org/docs/9.5/static/sql-altertable.html).
For example, for a publication table named edb.dept, use the following ALTER TABLE command:
The REPLICA IDENTITY setting can be displayed by the PSQL utility using the \d+ command:
The REPLICA IDENTITY FULL setting is required on tables in the following databases of a log-based replication system:
In a single-master replication system, table filters are defined in the master database. Thus, the publication tables in the master database requiring filter definitions must be altered to a REPLICA IDENTITY FULL setting, but only if the publication is not a snapshot-only publication. See Section 2.2.7 for information on snapshot-only publications.
In a multi-master replication system, non-MDN nodes should not have their tables’ REPLICA IDENTITY option set to FULL unless transactions are expected to be targeted on those non-MDN nodes, and the transactions are to be filtered when they are replicated to the other master nodes.
The REPLICA IDENTITY FULL setting on a source table ensures that certain types of transactions on the source table result in the proper updates to the target tables on which filters have been enabled.
Note: In addition to table filtering requirements, the REPLICA IDENTITY FULL setting may be required on publication tables for other reasons in xDB Replication Server. See Section 6.6.1 for additional requirements.
Table filters are not supported on binary data type columns. A binary data type is the Postgres data type BYTEA. In addition, table filters are not supported on Advanced Server columns with data types BINARY, VARBINARY, BLOB, LONG RAW, and RAW as these are alias names for the BYTEA data type.
Section 5.2.3 for information on defining the initial set of table filters that are to be available for selective enablement on subscriptions
Section 5.3.3 for information on enabling available table filters on a newly created subscription
Section 7.6.4 for information on adding, removing, or modifying rules comprising the set of available table filters
Section 5.5.4 for information on changing which table filters have been enabled on an existing subscription
Section 6.2.3 for information on defining the initial set of table filters that are to be available for selective enablement on master nodes
Section 6.3 for information on enabling available table filters on a newly created master node
Section 7.6.4 for information on adding, removing, or modifying rules comprising the set of available table filters
Section 6.9 for information on changing which table filters have been enabled on an existing master node

2 Overview : 2.2 Replication Concepts and Definitions

Table of Contents Previous Next