Nodes with differences ====================== Replicating between nodes with differences ------------------------------------------ By default, DDL is sent to all nodes. You can control this behavior, as described in :ref:`DDL replication filtering ` , and you can use it to create differences between database schemas across nodes. PGD is designed to allow replication to continue even with minor differences between nodes. These features are designed to allow application schema migration without downtime or to allow logical standby nodes for reporting or testing. Currently, replication requires the same table name on all nodes. A future feature might allow a mapping between different table names. It’s possible to replicate between tables with dissimilar partitioning definitions, such as a source that’s a normal table replicating to a partitioned table, including support for updates that change partitions on the target. It can be faster if the partitioning definition is the same on the source and target since dynamic partition routing doesn’t need to execute at apply time. For details, see :ref:`Replication sets ` . By default, all columns are replicated. PGD replicates data columns based on the column name. If a column has the same name but a different data type, PGD attempts to cast from the source type to the target type, if casts were defined that allow that. PGD supports replicating between tables that have a different number of columns. If the target has missing columns from the source, then PGD raises a ``target_column_missing`` conflict, for which the default conflict resolver is ``ignore_if_null`` . This throws an error if a non-NULL value arrives. Alternatively, you can also configure a node with a conflict resolver of ``ignore`` . This setting doesn’t throw an error but silently ignores any additional columns. If the target has additional columns not seen in the source record, then PGD raises a ``source_column_missing`` conflict, for which the default conflict resolver is ``use_default_value`` . Replication proceeds if the additional columns have a default, either NULL (if nullable) or a default expression. If not, it throws an error and halts replication. Transform triggers can also be used on tables to provide default values or alter the incoming data in various ways before apply. If the source and the target have different constraints, then replication is attempted, but it might fail if the rows from source can’t be applied to the target. Row filters can help here. Replicating data from one schema to a more relaxed schema doesn’t cause failures. Replicating data from a schema to a more restrictive schema can be a source of potential failures. The right way to solve this is to place a constraint on the more relaxed side, so bad data can’t be entered. That way, no bad data ever arrives by replication, so it never fails the transform into the more restrictive schema. For example, if one schema has a column of type TEXT and another schema defines the same column as XML, add a CHECK constraint onto the TEXT column to enforce that the text is XML. You can define a table with different indexes on each node. By default, the index definitions are replicated. To specify how to create an index on only a subset of nodes or just locally, see :ref:`DDL replication filtering ` . Storage parameters, such as ``fillfactor`` and ``toast_tuple_target`` , can differ between nodes for a table without problems. An exception to that behavior is that the value of a table’s storage parameter ``user_catalog_table`` must be identical on all nodes. A table being replicated must be owned by the same user/role on each node. See :ref:`Security and roles ` for details. Roles can have different passwords for connection on each node, although by default changes to roles are replicated to each node. See :ref:`DDL replication filtering ` to specify how to alter a role password on only a subset of nodes or locally. Comparison between nodes with differences ----------------------------------------- LiveCompare is a tool for data comparison on a database against PGD and non-PGD nodes. It needs a minimum of two connections to compare against and reach a final result. Starting with LiveCompare 1.3, you can configure with ``all_bdr_nodes`` set. This setting saves you from clarifying all the relevant DSNs for each separate node in the cluster. An EDB Postgres Distributed cluster has N amount of nodes with connection information, but it’s only the initial and output connection that LiveCompare 1.3 and later needs to complete its job. Setting ``logical_replication_mode`` states how all the nodes are communicating. All the configuration is done in a ``.ini`` file named ``bdrLC.ini`` , for example. Find templates for this configuration file in ``/etc/2ndq-livecompare/`` . While LiveCompare executes, you see N+1 progress bars, N being the number of processes. Once all the tables are sourced, a time displays as the transactions per second (tps) was measured. This mechanism continues to count the time, giving you an estimate and then a total execution time at the end. This tool offers a lot of customization and filters, such as tables, schemas, and replication_sets. LiveCompare can use stop-start without losing context information, so it can run at convenient times. After the comparison, a summary and a DML script are generated so you can review it. Apply the DML to fix any differences found. Replicating between different release levels -------------------------------------------- The other difference between nodes that you might encounter is where there are different major versions of PostgreSQL on the nodes. PGD is designed to replicate between different major release versions. This feature is designed to allow major version upgrades without downtime. PGD is also designed to replicate between nodes that have different versions of PGD software. This feature is designed to allow version upgrades and maintenance without downtime. However, while it’s possible to join a node with a major version in a cluster, you can’t add a node with a minor version if the cluster uses a newer protocol version. Doing so returns an error. Both of these features might be affected by specific restrictions. See :ref:`EDB Postgres Distributed Release notes ` for any known incompatibilities.