Requirements and restrictions v7
The following are the general requirements and restrictions when using the log-based method for any database of a single-master or multi-master replication system:
- The selection of either the trigger-based method or the log-based method is a characteristic that applies only to the publication database. The choice is made when defining the primary database of a single-master replication system (see Adding a publication database) or the primary definition node of a multi-master replication system (see Adding the primary definition node).
- The logical decoding feature, and hence the log-based method, is supported beginning with PostgreSQL version 9.4. Therefore, to use the log-based method for a publication database, that publication database must be running under PostgreSQL version 9.4 or later or under EDB Postgres Advanced Server version 9.4 or later.
- In a single-master replication system, whether the primary database uses the trigger-based method or the log-based method has no added impact on the rules for choosing the subscription database. For example, even if you choose the log-based method for the primary database, the subscription database can run on Postgres version 9.4 and any supported, earlier version of Postgres, as well as Oracle or SQL Server.
- In a single-master replication system, the primary database can contain one or more publications (that is, named sets of tables for replication). This rule applies to a primary database using either the trigger-based method or the log-based method.
- You can have multiple, single-master replication systems running under a publication server. Some primary databases can use the trigger-based method while others use the log-based method.
- In a multi-master replication system, selecting either the trigger-based method or the log-based method on the primary definition node determines the method for all other primary nodes. In other words, if you choose the trigger-based method for the primary definition node, then all other primary nodes use the trigger-based method. If you choose the log-based method for the primary definition node, then all other primary nodes use the log-based method.
- As a consequence this restriction, to use the log-based method for a multi-master replication system, all of the primary nodes of the system must be running under Postgres version 9.4 or later. All such Postgres database clusters must be configured to use logical decoding for the log-based method.
Selecting the log-based method for any database impacts the configuration of the Postgres database cluster containing that database.
If you plan to use the log-based method with any publication database running under a Postgres database server, the following configuration parameter settings are required in the configuration file
postgresql.conf of that Postgres database server:
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 primary databases of single-master replication systems and primary nodes of multi-master replication systems on this database server that uses 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 set max_replication_slots, 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 primary databases of the single-master replication systems that use the log-based method. For support of MMR systems, the minimum requirement is the total number of primary nodes in the multi-master replication system multiplied by the number of primary nodes residing on this database server. For information, see Replication origin.
track_commit_timestamp. Set to
on. This configuration parameter applies only to Postgres database servers version 9.5 and later. See Configuration parameter and table setting requirements for more information.
Also, see Enabling synchronization replication with the log-based method for setting these parameters for a single-master replication system. See Enabling synchronization replication with the log-based method 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. Give this access to the publication database user specified when creating the publication database definition using the Replication Server console for a single-master replication system, for a multi-master replication system, or the Replication Server command line interface (CLI). For more information, see:
- Adding a publication database
- Adding the primary definition node
- Adding a publication database (addpubdb)
For configuration options in the publication server configuration file that specifically apply to the log-based method see Log-based method of synchronization options.