PostgreSQL Configuration
Users that are familiar with PostgreSQL are aware of the existence of the following three files to configure an instance:
postgresql.conf: main run-time configuration file of PostgreSQLpg_hba.conf: clients authentication filepg_ident.conf: map external users to internal users
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 , pg_hba.conf , and pg_ident.conf settings
via the parameters , the pg_hba , and the pg_ident keys.
These settings are the same across all instances.
Warning
Please don't use the ALTER SYSTEM query to change the configuration of the PostgreSQL instances in an imperative way. Changing some of the options that are normally controlled by the operator might indeed lead to an unpredictable/unrecoverable state of the cluster. Moreover, ALTER SYSTEM changes are not replicated across the cluster. See Enabling ALTER SYSTEM below for details.
A reference for custom settings usage is included in the samples, see the deployment manifest below:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-custom
spec:
instances: 3
# Parameters and pg_hba configuration will be append
# to the default ones to make the cluster work
postgresql:
parameters:
max_worker_processes: "60"
pg_hba:
# To access through TCP/IP you will need to get username
# and password from the secret cluster-example-custom-app
- host all all all md5
# Example of rolling update strategy:
# - unsupervised: automated update of the primary once all
# replicas have been upgraded (default)
# - supervised: requires manual supervision to perform
# the switchover of the primary
primaryUpdateStrategy: unsupervised
# Require 1Gi of space per instance using default storage class
storage:
size: 1Gi
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 in the
postgresql section, as in the following example:
# ...
postgresql:
parameters:
shared_buffers: "1GB"
# ...
See also
Refer to the PostgreSQL documentation for more information on the available parameters , also known as GUC (Grand Unified Configuration). Please note that CloudNativePG accepts only strings for the PostgreSQL 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:
archive_timeout = 5min
dynamic_shared_memory_type = posix
full_page_writes = on
logging_collector = on
log_destination = csvlog
log_directory = /controller/log
log_filename = postgres
log_rotation_age = 0
log_rotation_size = 0
log_truncate_on_rotation = false
max_parallel_workers = 32
max_replication_slots = 32
max_worker_processes = 32
shared_memory_type = mmap
shared_preload_libraries =
ssl_max_protocol_version = TLSv1.3
ssl_min_protocol_version = TLSv1.3
wal_keep_size = 512MB
wal_level = logical
wal_log_hints = on
wal_sender_timeout = 5s
wal_receiver_timeout = 5s
Warning
It is your duty to plan for WAL segments retention in your PostgreSQL cluster and properly configure either wal_keep_size or wal_keep_segments , depending on the server version, based on the expected and observed workloads.
Alternatively, if the only streaming replication clients are the replica
instances running in the High Availability cluster, you can take
advantage of the replication slots feature, which adds support for
replication slots at the cluster level. You can enable the feature with
the replicationSlots.highAvailability option (for more information,
please refer to the About the replication user .)
Without replication slots nor continuous backups in place, configuring
wal_keep_size or wal_keep_segments is the only way to protect
standbys from falling out of sync. If a standby did fall out of sync it
would produce error messages like:
"could not receive data from WAL stream: ERROR: requested WAL segment ************************ has already been removed"
. This will require you to dedicate a part of your PGDATA , or the
volume dedicated to storing WAL files, to keep older WAL segments for
streaming replication purposes.
The following parameters are fixed and exclusively controlled by the operator:
archive_command = /controller/manager wal-archive %p
hot_standby = true
listen_addresses = *
port = 5432
restart_after_crash = false
ssl = on
ssl_ca_file = /controller/certificates/client-ca.crt
ssl_cert_file = /controller/certificates/server.crt
ssl_key_file = /controller/certificates/server.key
unix_socket_directories = /controller/run
Since the fixed parameters are added at the end, they can’t be overridden by the user via the YAML configuration. Those parameters are required for correct WAL archiving and replication.
Write-Ahead Log Level
The wal_level
parameter in PostgreSQL determines the amount of information written to the Write-Ahead Log (WAL). It accepts the following values:
minimal: Writes only the information required for crash recovery.replica: Adds sufficient information to support WAL archiving and streaming replication, including the ability to run read-only queries on standby instances.logical: Includes all information fromreplica, plus additional information required for logical decoding and replication.
By default, upstream PostgreSQL sets wal_level to replica .
CloudNativePG, instead, sets wal_level to logical by default to
enable logical replication out of the box. This makes it easier to
support use cases such as migrations from external PostgreSQL servers.
If your cluster does not require logical replication, it is recommended
to set wal_level to replica to reduce WAL volume and overhead.
Finally, CloudNativePG allows wal_level to be set to minimal
only for single-instance clusters with WAL archiving disabled.
Replication Settings
The primary_conninfo , restore_command , and
recovery_target_timeline parameters are automatically managed by the
operator based on the instance’s role within the cluster. These
parameters are effectively applied only when the instance is operating
as a replica.
primary_conninfo = host=<PRIMARY> user=postgres dbname=postgres
recovery_target_timeline = latest
The STANDBY_TCP_USER_TIMEOUT , if specified, sets the tcp_user_timeout
parameter on all standby instances managed by the operator.
The tcp_user_timeout parameter determines how long transmitted data
can remain unacknowledged before the TCP connection is forcibly closed.
Adjusting this value allows you to fine-tune the responsiveness of
standby instances to network disruptions. For more details, refer to the
PostgreSQL documentation .
Log control settings
The operator requires PostgreSQL to output its log in CSV format, and the instance manager automatically parses it and outputs it in JSON format. For this reason, all log settings in PostgreSQL are fixed and cannot be changed.
For further information, please refer to the Logging .
Managed extensions
As anticipated in the previous section, CloudNativePG automatically
manages the content in shared_preload_libraries for some well-known
and supported extensions. The current list includes:
auto_explainpg_stat_statementspgauditpg_failover_slots
Some of these libraries also require additional objects in a database
before using them, normally views and/or functions managed via the
CREATE EXTENSION command to be run in a database (the
DROP EXTENSION command typically removes those objects).
For such libraries, CloudNativePG automatically handles the creation and removal of the extension in all databases that accept a connection in the cluster, identified by the following query:
SELECT datname FROM pg_database WHERE datallowconn
Note
The above query also includes template databases like template1 .
Important
With the introduction of declarative extensions
in the Database CRD, you can now manage extensions directly. As a
result, the managed extensions feature may undergo significant changes
in future versions of CloudNativePG, and some functionalities might be
deprecated.
Enabling auto_explain
The auto_explain
extension provides a means for logging execution plans of slow
statements automatically, without having to manually run EXPLAIN
(helpful for tracking down un-optimized queries).
You can enable auto_explain by adding to the configuration a
parameter that starts with auto_explain. as in the following example
excerpt (which automatically logs execution plans of queries that take
longer than 10 seconds to complete):
# ...
postgresql:
parameters:
auto_explain.log_min_duration: "10s"
# ...
Note
Enabling auto_explain can lead to performance issues. Please refer to the auto explain documentation
Enabling pg_stat_statements
extension is one of the most important capabilities available in PostgreSQL for real-time monitoring of queries.
You can enable pg_stat_statements by adding to the configuration a
parameter that starts with pg_stat_statements. as in the following
example excerpt:
# ...
postgresql:
parameters:
pg_stat_statements.max: "10000"
pg_stat_statements.track: all
# ...
As explained previously, the operator will automatically add
pg_stat_statements to shared_preload_libraries and run
CREATE EXTENSION IF NOT EXISTS pg_stat_statements on each database,
enabling you to run queries against the pg_stat_statements view.
Enabling pgaudit
The pgaudit extension provides detailed session and/or object audit
logging via the standard PostgreSQL logging facility.
CloudNativePG has transparent and native support for PGAudit on PostgreSQL clusters. For further information, please refer to the PGAudit Logs
You can enable pgaudit by adding to the configuration a parameter
that starts with pgaudit. as in the following example excerpt:
#
postgresql:
parameters:
pgaudit.log: "all, -misc"
pgaudit.log_catalog: "off"
pgaudit.log_parameter: "on"
pgaudit.log_relation: "on"
#
#### Enabling `pg_failover_slots`
The [`pg_failover_slots`](https://github.com/EnterpriseDB/pg_failover_slots)
extension by EDB ensures that logical replication slots can survive a
failover scenario. Failovers are normally implemented using physical
streaming replication, like in the case of CloudNativePG.
You can enable `pg_failover_slots` by adding to the configuration a parameter
that starts with `pg_failover_slots.`: as explained above, the operator will
transparently manage the `pg_failover_slots` entry in the
`shared_preload_libraries` option depending on this.
Please refer to [`the `pg_failover_slots` documentation`](https://www.enterprisedb.com/docs/pg_extensions/pg_failover_slots)
for details on this extension.
Additionally, for each database that you intend to you use with `pg_failover_slots`
you need to add an entry in the `pg_hba` section that enables each replica to
connect to the primary.
For example, suppose that you want to use the `app` database with `pg_failover_slots`,
you need to add this entry in the `pg_hba` section:
``` yaml postgresql: pg_hba: - hostssl app streaming_replica all cert
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.
!!! Important See the PostgreSQL documentation for more information on ``pg_hba.conf` <https://www.postgresql.org/docs/current/auth-pg-hba-conf.html>`__.
Since the first matching rule is used for authentication, the
pg_hba.conf file generated by the operator can be seen as composed
of four sections:
Fixed rules
User-defined rules
Optional LDAP section
Default rules
Fixed rules:
``` text local all all peer
hostssl postgres streaming_replica all cert map=cnpg_streaming_replica hostssl replication streaming_replica all cert map=cnpg_streaming_replica hostssl all cnpg_pooler_pgbouncer all cert map=cnpg_pooler_pgbouncer
Default rules:
``` text host all all all <default-authentication-method>
From PostgreSQL 14 the default value of the password_encryption
database parameter is set to scram-sha-256. Because of that, the
default authentication method is scram-sha-256 from this PostgreSQL
version.
PostgreSQL 13 and older will use md5 as the default authentication
method.
The resulting pg_hba.conf will look like this:
``` text local all all peer
hostssl postgres streaming_replica all cert map=cnpg_streaming_replica hostssl replication streaming_replica all cert map=cnpg_streaming_replica hostssl all cnpg_pooler_pgbouncer all cert map=cnpg_pooler_pgbouncer
host all all all scram-sha-256 # (or md5 for PostgreSQL version <= 13)
edb_notranlate_13 yaml postgresql: pg_hba: - hostssl app app 10.244.0.0/16 md5
In the above example we are enabling access for the `app` user to the `app`
database using MD5 password authentication (you can use `scram-sha-256`
if you prefer) via a secure channel (`hostssl`).
### LDAP Configuration
Under the `postgres` section of the cluster spec there is an optional `ldap` section available to define an LDAP
configuration to be converted into a rule added into the `pg_hba.conf` file.
This will support two modes: `simple bind` mode which requires specifying a `server`, `prefix` and `suffix` in the LDAP
section and the `search+bind` mode which requires specifying `server`, `baseDN`, `binDN`, and a `bindPassword` which is
a secret containing the ldap password. Additionally, in `search+bind` mode you have the option to specify a
`searchFilter` or `searchAttribute`. If no `searchAttribute` is specified the default one of `uid` will be used.
Additionally, both modes allow the specification of a `scheme` for ldapscheme and a `port`. Neither scheme nor port are
required, however.
This section filled out for search+bind could look as follows:
``` yaml postgresql: ldap: server: 'openldap.default.svc.cluster.local' bindSearchAuth: baseDN: 'ou=org,dc=example,dc=com' bindDN: 'cn=admin,dc=example,dc=com' bindPassword: name: 'ldapBindPassword' key: 'data' searchAttribute: 'uid'
The pg_ident section
pg_ident is a list of PostgreSQL User Name Maps that CloudNativePG
uses to generate and maintain the ident map file (known as
pg_ident.conf) inside the data directory.
!!! Important See the PostgreSQL documentation for more information on ``pg_ident.conf` <https://www.postgresql.org/docs/current/auth-username-maps.html>`__.
The pg_ident.conf file written by the operator is made up of the
following two sections:
Fixed rules
User-defined rules
Currently the only fixed rule, automatically generated by the operator, is:
``` text local postgres
The instance manager detects the user running the PostgreSQL instance and
automatically adds a rule to map it to the `postgres` user in the database.
If the `postgres` user is not properly configured inside the container, the
instance manager will allow any local user to connect and then log a warning
message like the following:
``` text Unable to identify the current user. Falling back to insecure mapping.
The resulting pg_ident.conf will look like this:
``` text local postgres
edb_notranlate_18 yaml postgresql: pg_ident: - “mymap /^(.*)@mydomain\.com$ \1”
## 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.
## Enabling `ALTER SYSTEM`
CloudNativePG strongly advocates employing the Cluster manifest as the
exclusive method for altering the configuration of a PostgreSQL cluster. This
approach guarantees coherence across the entire high-availability cluster and
aligns with best practices for Infrastructure-as-Code.
In CloudNativePG the default configuration disables the use of `ALTER SYSTEM`
on new Postgres clusters. This decision is rooted in the recognition of
potential risks associated with this command. To enable the use of `ALTER SYSTEM`,
you can explicitly set `.spec.postgresql.enableAlterSystem` to `true`.
!!! Warning
Proceed with caution when utilizing `ALTER SYSTEM`. This command operates
directly on the connected instance and does not undergo replication.
CloudNativePG assumes responsibility for certain fixed parameters and complete
control over others, emphasizing the need for careful consideration.
Starting from PostgreSQL 17, the `.spec.postgresql.enableAlterSystem` setting
directly controls the [`allow_alter_system` GUC in PostgreSQL](https://www.postgresql.org/docs/17/runtime-config-compatible.html#GUC-ALLOW-ALTER-SYSTEM)
— a feature directly contributed by CloudNativePG to PostgreSQL.
Prior to PostgreSQL 17, when `.spec.postgresql.enableAlterSystem` is set to
`false`, the `postgresql.auto.conf` file is made read-only. Consequently, any
attempt to execute the `ALTER SYSTEM` command will result in an error. The
error message might look like this:
``` output ERROR: could not open file "postgresql.auto.conf": Permission denied
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_alter_systemallow_system_table_modsarchive_cleanup_commandarchive_commandarchive_modebonjourbonjour_namecluster_nameconfig_filedata_directorydata_sync_retryevent_sourceexternal_pid_filehba_filehot_standbyident_filejit_providerlisten_addresseslog_destinationlog_directorylog_file_modelog_filenamelog_rotation_agelog_rotation_sizelog_truncate_on_rotationlogging_collectorportprimary_conninfoprimary_slot_namepromote_trigger_filerecovery_end_commandrecovery_min_apply_delayrecovery_targetrecovery_target_actionrecovery_target_inclusiverecovery_target_lsnrecovery_target_namerecovery_target_timerecovery_target_timelinerecovery_target_xidrestart_after_crashrestore_commandshared_preload_librariessslssl_ca_filessl_cert_filessl_crl_filessl_dh_params_filessl_ecdh_curvessl_key_filessl_passphrase_commandssl_passphrase_command_supports_reloadssl_prefer_server_ciphersstats_temp_directorysynchronous_standby_namessyslog_facilitysyslog_identsyslog_sequence_numberssyslog_split_messagesunix_socket_directoriesunix_socket_groupunix_socket_permissions