Postgres configuration ====================== Several Postgres configuration parameters affect PGD nodes. You can set these parameters differently on each node, although we don’t generally recommend it. For PGD’s own settings, see the `PGD settings reference `_ . Postgres settings ----------------- To run correctly, PGD requires these Postgres settings: - ``wal_level`` - Must be set to ``logical`` , since PGD relies on logical decoding. - ``shared_preload_libraries`` - Must include ``bdr`` to enable the extension. Most other extensions can appear before or after the ``bdr`` entry in the comma-separated list. Don’t include ``pglogical`` in this list. - ``track_commit_timestamp`` - Must be set to ``on`` for conflict resolution to retrieve the timestamp for each conflicting row. PGD requires these PostgreSQL settings to be set to appropriate values, which vary according to the size and scale of the cluster: - ``logical_decoding_work_mem`` - Memory buffer size used by logical decoding. Transactions larger than this size overflow the buffer and are stored temporarily on local disk. Default is 64MB, but you can set it much higher. - ``max_worker_processes`` - PGD uses background workers for replication and maintenance tasks, so you need enough worker slots for it to work correctly. The formula for the correct minimal number of workers for each database is to add together these values: - One per PostgreSQL instance - One per database on that instance - Four per PGD-enabled database - One per peer node in the PGD group - The number of peer nodes times the (number of writers (bdr.num_writers) plus one) You might need more worker processes temporarily when a node is being removed from a PGD group. - ``max_wal_senders`` - Two needed for every peer node. - ``max_replication_slots`` - Two needed for every peer node. - ``max_active_replication_origins`` - Three needed for every peer node. Available for PostgreSQL 18 and later. - ``wal_sender_timeout`` and ``wal_receiver_timeout`` - The default of one minute is usually sufficient, but large transactions may require longer than this amount of time to process. Since the WAL sender must process the full size of the transaction before transmitting it to a waiting replication connection, Postgres can see that as a timeout. If the problem is actually due to a large transaction, raising wal_sender_timeout to a higher value, like ``3600s`` or higher, and reloading the server could solve the problem. Additionally, this setting determines how quickly a node considers its CAMO partner as disconnected or reconnected. See :ref:`CAMO failure scenarios ` for details. In normal running for a group with N peer nodes, PGD requires N slots and WAL senders. During synchronization, PGD temporarily uses another N-1 slots and WAL senders, so be careful to set the parameters high enough for this occasional peak demand. With Parallel Apply turned on, the number of slots must be increased to N slots from the formula \* writers. This is because ``max_replication_slots`` also sets the maximum number of replication origins, and some of the functionality of Parallel Apply uses an extra origin per writer. When the :ref:`Decoding worker ` is enabled, this process requires one extra replication slot per PGD group. Changing the ``max_worker_processes`` , ``max_wal_senders`` , and ``max_replication_slots`` parameters requires restarting the local node. A legacy synchronous replication mode is supported using the following parameters. See :ref:`Migration to commit scopes ` for details and limitations. - ``synchronous_commit`` and ``synchronous_standby_names`` - Affects the durability and performance of PGD replication. in a similar way to `physical replication `_ . Max prepared transactions ------------------------- max_prepared_transactions ^^^^^^^^^^^^^^^^^^^^^^^^^ Needs to be set high enough to cope with the maximum number of concurrent prepared transactions across the cluster due to explicit two-phase commits, CAMO, or Eager transactions. Exceeding the limit prevents a node from running a local two-phase commit or CAMO transaction and prevents all Eager transactions on the cluster. This parameter can be set only at Postgres server start. Considerations for global configuration --------------------------------------- While certain PostgreSQL configuration parameters are useful for standalone instances, they can negatively impact PGD cluster stability, background workers, and replication processes. The following parameters should not be enabled globally in ``postgresql.conf`` , nor should they be included as options in PGD node data source names (DSNs): - ``idle_session_timeout`` - ``transaction_timeout`` Set these parameters on a per-session basis instead to make sure that they do not interfere with system-level operations.