PostgreSQL configuration for BDR v4
Several PostgreSQL configuration parameters affect BDR nodes. You can set these parameters differently on each node, although that isn't generally recommended.
PostgreSQL settings for BDR
BDR requires these PostgreSQL settings to run correctly:
wal_level
— Must be set tological
, since BDR relies on logical decoding.shared_preload_libraries
— Must containbdr
, although it can contain other entries before or after, as needed. However, don't includepglogical
.track_commit_timestamp
— Must be set toon
for conflict resolution to retrieve the timestamp for each conflicting row.
BDR 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 overflow the buffer and are stored temporarily on local disk. Default is 64 MB, but you can set it much higher.max_worker_processes
— BDR 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:- One per PostgreSQL instance plus
- One per database on that instance plus
- Four per BDR-enabled database plus
- One per peer node in the BDR group plus
- One for each writer-enabled per peer node in the BDR group You might need more worker processes temporarily when a node is being removed from a BDR group.
max_wal_senders
— Two needed per every peer node.max_replication_slots
— Same asmax_wal_senders
.wal_sender_timeout
andwal_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, BDR requires N slots and WAL senders. During synchronization, BDR temporarily uses another N - 1 slots and WAL senders, so be careful to set the parameters high enough for this occasional peak demand.
max_replication_slots
sets the maximum number of replication origins.
Some of the functionality of parallel apply uses an extra origin per writer.
Hence, when parallel apply is enabled, you must set the max_replication_slots
to
N * (number of writers)
plus the number of slots needed for peak demand as described in the previous paragraph.
When the decoding worker is enabled, this process requires one extra replication slot per BDR group.
Changing these parameters requires restarting the local node:
max_worker_processes
, max_wal_senders
, max_replication_slots
.
You might also want your applications to set these parameters. See Durability and performance options for details.
synchronous_commit
— Affects the durability and performance of BDR replication. in a similar way to physical replication.synchronous_standby_names
— Same as above.
BDR-specific settings
You can also set BDR-specific configuration settings. Unless noted otherwise, you can set the values at any time.
Conflict handling
bdr.default_conflict_detection
— Sets the default conflict detection method for newly created tables. Accepts same values as bdr.alter_table_conflict_detection().
Global sequence parameters
bdr.default_sequence_kind
— Sets the default sequence kind. The default value isdistributed
, which meanssnowflakeid
is used forint8
sequences (i.e.,bigserial
) andgalloc
sequence forint4
(i.e.,serial
) andint2
sequences.
DDL handling
bdr.default_replica_identity
— Sets the default value forREPLICA IDENTITY
on newly created tables. TheREPLICA IDENTITY
defines the information written to the write-ahead log to identify rows that are updated or deleted.The accepted values are:
DEFAULT
— Records the old values of the columns of the primary key, if any (this is the default PostgreSQL behavior).FULL
— Records the old values of all columns in the row.NOTHING