Postgres configuration v5

Suggest edits

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. One exception to that is pgaudit, which must appear in the list before bdr. Also, 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
    • One for each writer-enabled per peer node in the PGD group 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.
  • wal_sender_timeout and wal_receiver_timeout — Determines how quickly a node considers its CAMO partner as disconnected or reconnected. See 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 the max_replication_slots also sets the maximum number of replication origins, and some of the functionality of parallel apply uses extra origin per writer.

When the 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 Durability and performance options for details and limitations.

  • synchronous_commitand synchronous_standby_names — Affects the durability and performance of PGD replication. in a similar way to physical replication.

Time-based snapshots

snapshot_timestamp

Turns on the use of timestamp-based snapshots and sets the timestamp to use.

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 only be set at Postgres server start.