Postgres Configuration

The SGPostgresConfig CRD allows you to specify and manage your Postgres configurations.

If no custom configuration is specified at cluster creation, StackGres will create a default SGPostgresConfig that will use the default configuration, which you can see in the default values table. This default configuration will also be used for those parameters that are not specified during the creation or modification of the resource.

Some of the configuration’s parameters are part of a blocklist and specifying them will not be possible and will result in an error during the creation or modification of the resource. For the complete list of those parameters see the blocked parameters table.

A Postgres configurations can be either created (and/or modified) per cluster, or reused in multiple clusters. There’s no need to repeat the configuration in every cluster if they share the same exact configuration.

The SGPostgresConfig is referenced from one or more Postgres clusters.

This is an example config definition:

apiVersion: stackgres.io/v1
kind: SGPostgresConfig
metadata:
  name: pgconfig
spec:
  postgresVersion: "14"
  postgresql.conf:
    work_mem: '16MB'
    shared_buffers: '2GB'
    random_page_cost: '1.5'
    password_encryption: 'scram-sha-256'
    log_checkpoints: 'on'
    jit: 'off'

This definition is created in Kubernetes (e.g. using kubectl apply) and can be inspected (kubectl describe sgpgconfig pgconfig) like any other Kubernetes resource.

An SGCluster can reference this configuration as follows:

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: cluster
spec:
# [...]
  configurations:
    sgPostgresConfig: 'pgconfig'

StackGres already ships with an expertly tuned Postgres configuration (aka postgresql.conf) by default. However, it’s absolutely possible to specify your own configuration. If you need guidance regarding configuration, consider using the postgresqlCONF service, which gives you detailed parameter information in several langauges, recommendations, a tuning guide, and even a facility to store and manage your Postgres configurations online.

Apply Configuration changes

Each configuration, once applied, is automatically reloaded.

Blocked Parameters

The list of blocked Postgres parameters:

Parameter
archive_command
archive_mode
dynamic_library_path
lc_messages
listen_addresses
logging_collector
log_destination
log_directory
log_filename
log_rotation_age
log_rotation_size
log_truncate_on_rotation
port
wal_log_hints

Default Values

The default Postgres parameters (when not specified):

Parameter Value
archive_command /bin/true
archive_mode on
autovacuum_max_workers 3
autovacuum_vacuum_cost_delay 2
autovacuum_work_mem 512MB
checkpoint_completion_target 0.9
checkpoint_timeout 15min
default_statistics_target 200
default_toast_compression lz4
enable_partitionwise_aggregate on
enable_partitionwise_join on
fsync on
hot_standby on
huge_pages off
jit_inline_above_cost -1
lc_messages C
log_autovacuum_min_duration 0ms
log_checkpoints on
log_connections on
log_destination stderr
log_directory log
log_disconnections on
log_filename postgres-%M.log
log_line_prefix %t [%p]: db=%d,user=%u,app=%a,client=%h
log_lock_waits on
log_min_duration_statement 1000
log_rotation_age 30min
log_rotation_size 0kB
log_statement none
log_temp_files 0
log_truncate_on_rotation on
logging_collector off
maintenance_work_mem 2GB
max_locks_per_transaction 128
max_pred_locks_per_transaction 128
max_prepared_transactions 32
max_replication_slots 20
max_wal_senders 20
max_wal_size 2GB
min_wal_size 1GB
pg_stat_statements.track_utility off
random_page_cost 1.5
shared_preload_libraries pg_stat_statements, auto_explain
superuser_reserved_connections 8
track_activity_query_size 4096
track_commit_timestamp on
track_functions pl
track_io_timing on
wal_compression on
wal_keep_size 1536MB
wal_level logical
wal_log_hints on
work_mem 10MB