Known issues and limitations ============================ Known issues ------------ These are currently known issues in EDB Postgres Distributed Version 6.4. These known issues are tracked in PGD’s ticketing system and are expected to be resolved in a future release. - Replication of materialized views only works with newly-created materialized views. Materialized views that were created before upgrading to Version 6.4.0 do not get replicated after upgrading. To prevent this issue, you can do one of the following: - Before upgrading to PGD Version 6.4.0., manually create the materialized view on each node. - OR - After upgrading to PGD Version 6.4.0., drop and create the materialized view so that it gets replicated. - If the resolver for the ``update_origin_change`` conflict is set to ``skip`` , ``synchronous_commit=remote_apply`` is used, and concurrent updates of the same row are repeatedly applied on two different nodes, then one of the update statements might hang due to a deadlock with the PGD writer. As mentioned in :ref:`bdr_read_all_conflicts ` , ``skip`` isn’t the default resolver for the ``update_origin_change`` conflict, and this combination isn’t intended to be used in production. It discards one of the two conflicting updates based on the order of arrival on that node, which is likely to cause a divergent cluster. In the rare situation that you do choose to use the ``skip`` conflict resolver, note the issue with the use of the ``remote_apply`` mode. - The Decoding Worker feature doesn’t work with CAMO/Eager/Group Commit. Installations using CAMO/Eager/Group Commit must keep ``enable_wal_decoder`` disabled. - Lag Control doesn’t adjust commit delay in any way on a fully isolated node, that’s in case all other nodes are unreachable or not operational. As soon as at least one node connects, replication Lag Control picks up its work and adjusts the PGD commit delay again. - For time-based Lag Control, PGD currently uses the lag time, measured by commit timestamps, rather than the estimated catch up time that’s based on historic apply rates. - Changing the CAMO partners in a CAMO pair isn’t currently possible. It’s possible only to add or remove a pair. Adding or removing a pair doesn’t require a restart of Postgres or even a reload of the configuration. - Group Commit can’t be combined with :ref:`Commit At Most Once ` . - Transactions using Eager Replication can’t yet execute DDL. The TRUNCATE command is allowed. - Parallel Apply isn’t currently supported in combination with Group Commit. Make sure to disable it when using Group Commit by either (a) Setting ``num_writers`` to 1 for the node group using `bdr.alter_node_group_option `_ or (b) using the GUC `bdr.writers_per_subscription `_ . See `Configuration of generic replication `_ . - There currently is no protection against altering or removing a commit scope. Running transactions in a commit scope that’s concurrently being altered or removed can lead to the transaction blocking or replication stalling completely due to an error on the downstream node attempting to apply the transaction. Make sure that any transactions using a specific commit scope have finished before altering or removing it. - The `PGD CLI `_ can return stale data on the state of the cluster if it’s still connecting to nodes that were previously parted from the cluster. Edit the `pgd-cli-config.yml `_ file, or change your `--dsn `_ settings to ensure only active nodes in the cluster are listed for connection. To modify a commit scope safely, use `bdr.alter_commit_scope `_ . - DDL run in serializable transactions can face the error: ``ERROR: could not serialize access due to read/write dependencies among transactions`` . A workaround is to run the DDL outside serializable transactions. - The EDB Postgres Advanced Server 17 data type `BFILE `_ is not currently supported. This is due to ``BFILE`` being a file reference that is stored in the database, and the file itself is stored outside the database and not replicated. - When running PGD on community PostgreSQL with Parallel Apply enabled, writer processes may terminate unexpectedly due to lock timeouts. This occurs because PGD sets a 30 second ``lock_timeout`` in the writer processes, which can be insufficient when one writer waits on a transaction lock held by another writer that is processing a large transaction. When the timeout is reached, the waiting writer exits, causing other writers and the receiver to terminate as well, which resets the replication process. As a workaround, disable parallel apply by setting ``bdr.enable_parallel_apply = off`` or migrate to an EDB Postgres variant. This issue is resolved in PGD 6.3. - In PGD 6.3.0, user login attributes aren’t replicated to peer nodes after a ``CREATE USER`` statement. Users created on one node can log in on the originating node but are blocked on all other nodes because the login attribute is missing from the replicated role definition. This issue is resolved in PGD 6.3.1. To identify affected roles, run the following command in psql: :: \du In the **Attributes** column, any role that should have login access but displays ``Cannot login`` is affected. Alternatively, query the system catalog directly: .. code:: sql SELECT rolname AS role_name, rolcanlogin FROM pg_roles WHERE rolcanlogin = false; To resolve the issue without upgrading, manually grant login access on each affected node: .. code:: sql ALTER USER WITH LOGIN; Limitations ----------- Take these EDB Postgres Distributed (PGD) design limitations into account when planning your deployment. Nodes ^^^^^ - PGD can run hundreds of nodes, assuming adequate hardware and network. However, for mesh-based deployments, we generally don’t recommend running more than 48 nodes in one cluster. If you need extra read scalability beyond the 48-node limit, you can add subscriber-only nodes without adding connections to the mesh network. - The minimum recommended number of nodes in a group is three to provide fault tolerance for PGD’s consensus mechanism. With just two nodes, consensus would fail if one of the nodes were unresponsive. Consensus is required for some PGD operations, such as distributed sequence generation. For more information about the consensus mechanism used by EDB Postgres Distributed, see :ref:`Architectural details ` . Multiple databases on single instances ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Support for using PGD for multiple databases on the same Postgres instance is **deprecated** beginning with PGD 5 and will no longer be supported with PGD 6. As we extend the capabilities of the product, the added complexity introduced operationally and functionally is no longer viable in a multi-database design. It’s best practice and we recommend that you configure only one database per PGD instance. The tooling such as the CLI and Connection Manager currently codify that recommendation. While it’s still possible to host up to 10 databases in a single instance, doing so incurs many immediate risks and current limitations: - If PGD configuration changes are needed, you must execute administrative commands for each database. Doing so increases the risk for potential inconsistencies and errors. - You must monitor each database separately, adding overhead. - Connection Manager works at the Postgres instance level, not at the database level, meaning the leader node is the same for all databases. - Each additional database increases the resource requirements on the server. Each one needs its own set of worker processes maintaining replication, for example, logical workers, WAL senders, and WAL receivers. Each one also needs its own set of connections to other instances in the replication cluster. These needs might severely impact performance of all databases. - Synchronous replication methods, for example, CAMO and Group Commit, won’t work as expected. Since the Postgres WAL is shared between the databases, a synchronous commit confirmation can come from any database, not necessarily in the right order of commits. - CLI integration assumes one database. Durability options (Quorum Commit/Group Commit/CAMO) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ There are various limits on how the PGD durability options work. These limitations are a product of the interactions between Quorum Commit, Group Commit, and CAMO, and how they interact with PGD features such as the :ref:`Decoding worker ` and :ref:`Using with transaction streaming ` . Also, there are limitations on interoperability with legacy synchronous replication, interoperability with explicit two-phase commit, and unsupported combinations within commit scope rules. The following limitations apply to the use of commit scopes and the various durability options they enable. General durability limitations ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ - :ref:`Legacy synchronous replication using PGD ` uses a mechanism for transaction confirmation different from the one used by CAMO, Eager, Group Commit, and Quorum Commit. The two aren’t compatible, so don’t use them together. Whenever you use Quorum Commit, Group Commit, CAMO, or Eager, make sure none of the PGD nodes are configured in ``synchronous_standby_names`` . - Postgres two-phase commit (2PC) transactions (that is, `PREPARETRANSACTION `_ ) can’t be used with CAMO, Group Commit, Quorum Commit, or Eager because those features use two-phase commit underneath. Group Commit ^^^^^^^^^^^^ :ref:`Group Commit (legacy) ` enables configurable synchronous commits over nodes in a group. If you use this feature, take the following limitations into account: - Not all DDL can run when you use Group Commit. If you use unsupported DDL, a warning is logged, and the transactions commit scope is set to local. The only supported DDL operations are: - Nonconcurrent ``CREATE INDEX`` - Nonconcurrent ``DROP INDEX`` - Nonconcurrent ``REINDEX`` of an individual table or index - ``CLUSTER`` (of a single relation or index only) - ``ANALYZE`` - ``TRUNCATE`` - Explicit two-phase commit isn’t supported by Group Commit as it already uses two-phase commit. - Combining different commit decision options in the same transaction or combining different conflict resolution options in the same transaction isn’t supported. - Currently, Raft commit decisions are extremely slow, producing very low TPS. We recommended using them only with the ``eager`` conflict resolution setting to get the Eager All-Node Replication behavior of PGD 4 and older. Eager ^^^^^ :ref:`Eager conflict resolution ` is available through Group Commit and is always active in Quorum Commit. It avoids conflicts by eagerly aborting transactions that might clash. When used through Group Commit, it’s subject to the same limitations as Group Commit. Eager doesn’t allow the ``NOTIFY`` SQL command or the ``pg_notify()`` function. It also doesn’t allow ``LISTEN`` or ``UNLISTEN`` . Quorum Commit ^^^^^^^^^^^^^ :ref:`How Quorum Commit works ` is the commit scope kind for distributed transaction consistency. If you use this feature, take the following limitations into account: - All nodes in the cluster must be running PGD 6.4.0 or later. A cluster with any node on an earlier version can’t use Quorum Commit, including clusters undergoing a rolling upgrade. - ``MAJORITY`` with an explicit group list, ``ANY n`` , and ``NOT`` aren’t supported. Supported groups are ``ALL`` , ``MAJORITY ORIGIN GROUP`` , ``MAJORITY CLUSTER`` , and ``MAJORITY FOR EACH GROUP`` . - During a rolling upgrade from a version earlier than PGD 6.5, keep :ref:`Node configuration using bdr.default_streaming_mode ` set to ``file`` or ``off`` until every node is on PGD 6.5 or later. See :ref:`Feature compatibility ` for details. - Explicit two-phase commit (2PC) isn’t supported by Quorum Commit, as Quorum Commit already uses 2PC internally. - ``DEGRADE TO`` isn’t supported. CAMO ^^^^ :ref:`Commit At Most Once ` (CAMO) is a feature that aims to prevent applications committing more than once. If you use this feature, take these limitations into account when planning: - CAMO is designed to query the results of a recently failed COMMIT on the origin node. In case of disconnection, the application must request the transaction status from the CAMO partner. Ensure that you have as little delay as possible after the failure before requesting the status. Applications must not rely on CAMO decisions being stored for longer than 15 minutes. - If the application forgets the global identifier assigned, for example, as a result of a restart, there’s no easy way to recover it. Therefore, we recommend that applications wait for outstanding transactions to end before shutting down. - For the client to apply proper checks, a transaction protected by CAMO can’t be a single statement with implicit transaction control. You also can’t use CAMO with a transaction-controlling procedure or in a ``DO`` block that tries to start or end transactions. - CAMO resolves commit status but doesn’t resolve pending notifications on commit. CAMO doesn’t allow the ``NOTIFY`` SQL command or the ``pg_notify()`` function. They also don’t allow ``LISTEN`` or ``UNLISTEN`` . - When replaying changes, CAMO transactions might detect conflicts just the same as other transactions. If timestamp-conflict detection is used, the CAMO transaction uses the timestamp of the prepare-on-the-origin node, which is before the transaction becomes visible on the origin node itself. - CAMO isn’t currently compatible with transaction streaming. Be sure to disable transaction streaming when planning to use CAMO. You can configure this option globally or in the PGD node group. See :ref:`Transaction streaming configuration ` . - CAMO isn’t currently compatible with decoding worker. Be sure to not enable decoding worker when planning to use CAMO. You can configure this option in the PGD node group. See :ref:`Decoding worker disabling ` . - Not all DDL can run when you use CAMO. If you use unsupported DDL, a warning is logged and the transactions commit scope is set to local only. The only supported DDL operations are: - Nonconcurrent ``CREATE INDEX`` - Nonconcurrent ``DROP INDEX`` - Nonconcurrent ``REINDEX`` of an individual table or index - ``CLUSTER`` (of a single relation or index only) - ``ANALYZE`` - ``TRUNCATE`` - Explicit two-phase commit isn’t supported by CAMO as it already uses two-phase commit. - You can combine only CAMO transactions with the ``DEGRADE TO`` clause for switching to asynchronous operation in case of lowered availability. Mixed PGD versions ^^^^^^^^^^^^^^^^^^ PGD was developed to :ref:`enable rolling upgrades of PGD ` by allowing mixed versions of PGD to operate during the upgrade process. We expect users to run mixed versions only during upgrades and, once an upgrade starts, that they complete that upgrade. We don’t support running mixed versions of PGD except during an upgrade. Postgres distribution support ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ EDB Postgres Extended Server ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ - The `password_profile `_ extension in `EDB Postgres Extended Server `_ stores password profiles in system catalogs that PGD can’t automatically replicate to other nodes. To ensure correct replication of password profiles, execute the extension’s functions using :ref:`bdr.run_on_all_nodes() ` . Other limitations ^^^^^^^^^^^^^^^^^ This noncomprehensive list includes other limitations that are expected and are by design. We don’t expect to resolve them in the future. Consider these limitations when planning your deployment: - A ``galloc`` sequence might skip some chunks if you create the sequence in a rolled back transaction and then create it again with the same name. Skipping chunks can also occur if you create and drop the sequence when DDL replication isn’t active and then you create it again when DDL replication is active. The impact of the problem is mild because the sequence guarantees aren’t violated. The sequence skips only some initial chunks. Also, as a workaround, you can specify the starting value for the sequence as an argument to the ``bdr.alter_sequence_set_kind()`` function.