PostgreSQL Configuration

Users that are familiar with PostgreSQL are aware of the existence of the following two files to configure an instance:

  • postgresql.conf: main run-time configuration file of PostgreSQL
  • pg_hba.conf: clients authentication file

Due to the concepts of declarative configuration and immutability of the PostgreSQL containers, users are not allowed to directly touch those files. Configuration is possible through the postgresql section of the Cluster resource definition by defining custom postgresql.conf and pg_hba.conf settings via the parameters and the pg_hba keys. A reference for custom settings usage is included in the samples, see cluster-example-custom.yaml.

These settings are the same across all instances.

Warning

OpenShift users: due to a current limitation of the OpenShift user interface, it is possible to change PostgreSQL settings from the YAML pane only.

The postgresql section

The PostgreSQL instance in the pod starts with a default postgresql.conf file, to which these settings are automatically added:

listen_addresses = '*'
include custom.conf

The custom.conf file will contain the user-defined settings. Refer to the PostgreSQL documentation for more information on the available parameters. The content of custom.conf is automatically generated and maintained by the operator by applying the following sections in this order:

  • Global default parameters
  • Default parameters that depend on the PostgreSQL major version
  • User-provided parameters
  • Fixed parameters

The global default parameters are:

logging_collector = 'off'
max_parallel_workers = '32'
max_replication_slots = '32'
max_worker_processes = '32'

The default parameters for PostgreSQL 13 or higher are:

wal_keep_size = '512MB'

The default parameters for PostgreSQL 10 to 12 are:

wal_keep_segments = '32'

The following parameters are fixed and exclusively controlled by the operator:

archive_command = '/controller/manager wal-archive %p'
archive_mode = 'on'
archive_timeout = '5min'
full_page_writes = 'on'
hot_standby = 'true'
listen_addresses = '*'
port = '5432'
ssl = 'on'
ssl_ca_file = '/tmp/ca.crt'
ssl_cert_file = '/tmp/server.crt'
ssl_key_file = '/tmp/server.key'
unix_socket_directories = '/var/run/postgresql'
wal_level = 'logical'
wal_log_hints = 'on'

Since the fixed parameters are added last, they can't be overridden by the user via the YAML configuration. Those parameters are required for correct WAL archiving and replication.

Replication settings

The primary_conninfo and recovery_target_timeline parameters are managed automatically by the operator according to the state of the instance in the cluster.

primary_conninfo = 'host=cluster-example-rw user=postgres dbname=postgres'
recovery_target_timeline = 'latest'

The pg_hba section

pg_hba is a list of PostgreSQL Host Based Authentication rules used to create the pg_hba.conf used by the pods.

Since the first matching rule is used for authentication, the pg_hba.conf file generated by the operator can be seen as composed of three sections:

  1. Fixed rules
  2. User-defined rules
  3. Default rules

Fixed rules:

local all all peer

hostssl postgres streaming_replica all cert clientcert=1
hostssl replication streaming_replica all cert clientcert=1

Default rules:

host all all all md5

The resulting pg_hba.conf will look like this:

local all all peer

hostssl postgres streaming_replica all cert clientcert=1
hostssl replication streaming_replica all cert clientcert=1

<user defined rules>

host all all all md5

Refer to the PostgreSQL documentation for more information on pg_hba.conf.

Changing configuration

You can apply configuration changes by editing the postgresql section of the Cluster resource.

After the change, the cluster instances will immediately reload the configuration to apply the changes. If the change involves a parameter requiring a restart, the operator will perform a rolling upgrade.

Fixed parameters

Some PostgreSQL configuration parameters should be managed exclusively by the operator. The operator prevents the user from setting them using a webhook.

Users are not allowed to set the following configuration parameters in the postgresql section:

  • allow_system_table_mods
  • archive_cleanup_command
  • archive_command
  • archive_mode
  • archive_timeout
  • bonjour_name
  • bonjour
  • cluster_name
  • config_file
  • data_directory
  • data_sync_retry
  • dynamic_shared_memory_type
  • event_source
  • external_pid_file
  • full_page_writes
  • hba_file
  • hot_standby
  • huge_pages
  • ident_file
  • jit_provider
  • listen_addresses
  • log_destination
  • log_directory
  • log_file_mode
  • log_filename
  • log_rotation_age
  • log_rotation_size
  • log_truncate_on_rotation
  • logging_collector
  • port
  • primary_conninfo
  • primary_slot_name
  • promote_trigger_file
  • recovery_end_command
  • recovery_min_apply_delay
  • recovery_target_action
  • recovery_target_inclusive
  • recovery_target_lsn
  • recovery_target_name
  • recovery_target_time
  • recovery_target_timeline
  • recovery_target_xid
  • recovery_target
  • restart_after_crash
  • restore_command
  • shared_memory_type
  • ssl_ca_file
  • ssl_cert_file
  • ssl_ciphers
  • ssl_crl_file
  • ssl_dh_params_file
  • ssl_ecdh_curve
  • ssl_key_file
  • ssl_max_protocol_version
  • ssl_min_protocol_version
  • ssl_passphrase_command_supports_reload
  • ssl_passphrase_command
  • ssl_prefer_server_ciphers
  • ssl
  • stats_temp_directory
  • synchronous_standby_names
  • syslog_facility
  • syslog_ident
  • syslog_sequence_numbers
  • syslog_split_messages
  • unix_socket_directories
  • unix_socket_group
  • unix_socket_permissions
  • wal_level
  • wal_log_hints