kizuna-lek
commented
8 months ago Reloader support is required.
Open JashBook opened 8 months ago
kizuna-lek
commented
8 months ago Reloader support is required.
Describe the bug
To Reproduce Steps to reproduce the behavior:
describe config
ConfigSpecs Meta: CONFIG-SPEC-NAME FILE ENABLED TEMPLATE CONSTRAINT RENDERED COMPONENT CLUSTER
postgresql-consensusset-configuration pg_hba.conf false apecloud-postgresql14-configuration apecloud-postgresql14-cc apepg-cluster-postgresql-postgresql-consensusset-configuration postgresql apepg-cluster
postgresql-consensusset-configuration postgresql.conf true apecloud-postgresql14-configuration apecloud-postgresql14-cc apepg-cluster-postgresql-postgresql-consensusset-configuration postgresql apepg-cluster
History modifications: OPS-NAME CLUSTER COMPONENT CONFIG-SPEC-NAME FILE STATUS POLICY PROGRESS CREATED-TIME VALID-UPDATED
apepg-cluster-reconfiguring-qdwnp apepg-cluster postgresql postgresql-consensusset-configuration postgresql.conf Succeed restart 1/1 Feb 21,2024 13:53 UTC+0800 {"postgresql.conf":"{\"max_connections\":\"200\"}"}
apepg-cluster-reconfiguring-hbd5q apepg-cluster postgresql postgresql-consensusset-configuration postgresql.conf Succeed restart 1/1 Feb 21,2024 14:10 UTC+0800 {"postgresql.conf":"{\"max_connections\":\"500\"}"}
kbcli cluster connect apepg-cluster Connect to instance apepg-cluster-postgresql-0 psql (14.10 (with consensus 0.7.1 PGTAG=CONSENSUS_HOOK_2_1_0_PG_14_10)) Type "help" for help.
postgres=# show max_connections; max_connections
100 (1 row)
postgres=#
kubectl get cm -l app.kubernetes.io/instance=apepg-cluster NAME DATA AGE apepg-cluster-postgresql-apecloud-postgresql-scripts 1 22m apepg-cluster-postgresql-env 7 22m apepg-cluster-postgresql-haconfig 0 22m apepg-cluster-postgresql-leader 0 22m apepg-cluster-postgresql-postgresql-consensusset-configuration 2 22m apepg-cluster-postgresql-rsm-env 13 22m
kubectl get cm apepg-cluster-postgresql-postgresql-consensusset-configuration -oyaml apiVersion: v1 data: pg_hba.conf: | host all all 0.0.0.0/0 md5 host all all ::/0 md5 local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust local replication all trust host replication all 0.0.0.0/0 md5 host replication all ::/0 md5 postgresql.conf: |
- Connection Settings -
kind: ConfigMap metadata: annotations: config.kubeblocks.io/config-applied-version: '{"name":"postgresql-consensusset-configuration","payload":null,"configSpec":{"name":"postgresql-consensusset-configuration","templateRef":"apecloud-postgresql14-configuration","namespace":"default","volumeName":"postgresql-config","defaultMode":511,"keys":["postgresql.conf"],"constraintRef":"apecloud-postgresql14-cc"},"importTemplateRef":null,"configFileParams":{"postgresql.conf":{"content":null,"parameters":{"max_connections":"500"}}}}' config.kubeblocks.io/config-template-version: "" config.kubeblocks.io/configuration-revision: "3" config.kubeblocks.io/disable-reconfigure: "false" config.kubeblocks.io/last-applied-configuration: '{"pg_hba.conf":"host all all 0.0.0.0/0 md5\nhost all all ::/0 md5\nlocal all all trust\nhost all all 127.0.0.1/32 trust\nhost all all ::1/128 trust\nlocal replication all trust\nhost replication all 0.0.0.0/0 md5\nhost replication all ::/0 md5\n","postgresql.conf":"#- Connection Settings -\nlisten_addresses = ''*''\nport = ''5432''\narchive_command = ''/bin/true''\narchive_mode = ''on''\nauto_explain.log_analyze = ''False''\nauto_explain.log_buffers = ''False''\nauto_explain.log_format = ''text''\nauto_explain.log_min_duration = ''-1''\nauto_explain.log_nested_statements = ''False''\nauto_explain.log_timing = ''True''\nauto_explain.log_triggers = ''False''\nauto_explain.log_verbose = ''False''\nauto_explain.sample_rate = ''1''\nautovacuum_analyze_scale_factor = ''0.1''\nautovacuum_analyze_threshold = ''50''\nautovacuum_freeze_max_age = ''200000000''\nautovacuum_max_workers = ''3''\nautovacuum_multixact_freeze_max_age = ''400000000''\nautovacuum_naptime = ''15s''\nautovacuum_vacuum_cost_delay = ''2''\nautovacuum_vacuum_cost_limit = ''200''\nautovacuum_vacuum_scale_factor = ''0.05''\nautovacuum_vacuum_threshold = ''50''\nautovacuum_work_mem = ''131072kB''\nbackend_flush_after = ''0''\nbackslash_quote = ''safe_encoding''\nbgwriter_delay = ''200ms''\nbgwriter_flush_after = ''64''\nbgwriter_lru_maxpages = ''1000''\nbgwriter_lru_multiplier = ''10.0''\nbytea_output = ''hex''\ncheck_function_bodies = ''True''\ncheckpoint_completion_target = ''0.9''\ncheckpoint_flush_after = ''32''\ncheckpoint_timeout = ''15min''\ncheckpoint_warning = ''30s''\nclient_min_messages = ''notice''\n\n#commit_delay = ''20''\ncommit_siblings = ''5''\nconstraint_exclusion = ''partition''\n\n#extension: pg_cron\ncron.database_name = ''postgres''\ncron.log_statement = ''on''\ncron.max_running_jobs = ''32''\ncursor_tuple_fraction = ''0.1''\ndatestyle = ''ISO,YMD''\ndeadlock_timeout = ''1000ms''\ndebug_pretty_print = ''True''\ndebug_print_parse = ''False''\ndebug_print_plan = ''False''\ndebug_print_rewritten = ''False''\ndefault_statistics_target = ''100''\ndefault_transaction_deferrable = ''False''\ndefault_transaction_isolation = ''read committed''\neffective_cache_size = ''256MB''\neffective_io_concurrency = ''1''\nenable_bitmapscan = ''True''\nenable_gathermerge = ''True''\nenable_hashagg = ''True''\nenable_hashjoin = ''True''\nenable_indexonlyscan = ''True''\nenable_indexscan = ''True''\nenable_material = ''True''\nenable_mergejoin = ''True''\nenable_nestloop = ''True''\nenable_parallel_append = ''True''\nenable_parallel_hash = ''True''\nenable_partition_pruning = ''True''\nenable_partitionwise_aggregate = ''True''\nenable_partitionwise_join = ''True''\nenable_seqscan = ''True''\nenable_sort = ''True''\nenable_tidscan = ''True''\nescape_string_warning = ''True''\nextra_float_digits = ''1''\nforce_parallel_mode = ''0''\nfrom_collapse_limit = ''8''\n\n#fsync=off
patroni for Extreme Performance\n#full_page_writes=off # patroni for Extreme
creationTimestamp: "2024-02-21T05:51:29Z" labels: app.kubernetes.io/component: postgresql app.kubernetes.io/instance: apepg-cluster app.kubernetes.io/managed-by: kubeblocks app.kubernetes.io/name: apecloud-postgresql apps.kubeblocks.io/component-name: postgresql config.kubeblocks.io/config-constraints-name: apecloud-postgresql14-cc config.kubeblocks.io/config-hash: 8559c47649 config.kubeblocks.io/config-spec: postgresql-consensusset-configuration config.kubeblocks.io/config-template-name: apecloud-postgresql14-configuration config.kubeblocks.io/config-type: instance config.kubeblocks.io/configmap-keys: postgresql.conf config.kubeblocks.io/last-applied-reconfigure-phase: created config.kubeblocks.io/template-name: apecloud-postgresql14-configuration name: apepg-cluster-postgresql-postgresql-consensusset-configuration namespace: default ownerReferences:
kubectl exec -it apepg-cluster-postgresql-0 bash kubectl exec [POD] [COMMAND] is DEPRECATED and will be removed in a future version. Use kubectl exec [POD] -- [COMMAND] instead. Defaulted container "postgresql" out of: postgresql, lorry, init-syncer (init) root@apepg-cluster-postgresql-0:/# cat postgresql/conf/postgresql.conf
- Connection Settings -
listen_addresses = '*' port = '5432' archive_command = '/bin/true' archive_mode = 'on' auto_explain.log_analyze = 'False' auto_explain.log_buffers = 'False' auto_explain.log_format = 'text' auto_explain.log_min_duration = '-1' auto_explain.log_nested_statements = 'False' auto_explain.log_timing = 'True' auto_explain.log_triggers = 'False' auto_explain.log_verbose = 'False' auto_explain.sample_rate = '1' autovacuum_analyze_scale_factor = '0.1' autovacuum_analyze_threshold = '50' autovacuum_freeze_max_age = '200000000' autovacuum_max_workers = '3' autovacuum_multixact_freeze_max_age = '400000000' autovacuum_naptime = '15s' autovacuum_vacuum_cost_delay = '2' autovacuum_vacuum_cost_limit = '200' autovacuum_vacuum_scale_factor = '0.05' autovacuum_vacuum_threshold = '50' autovacuum_work_mem = '131072kB' backend_flush_after = '0' backslash_quote = 'safe_encoding' bgwriter_delay = '200ms' bgwriter_flush_after = '64' bgwriter_lru_maxpages = '1000' bgwriter_lru_multiplier = '10.0' bytea_output = 'hex' check_function_bodies = 'True' checkpoint_completion_target = '0.9' checkpoint_flush_after = '32' checkpoint_timeout = '15min' checkpoint_warning = '30s' client_min_messages = 'notice'
commit_delay = '20'
commit_siblings = '5' constraint_exclusion = 'partition'
extension: pg_cron
cron.database_name = 'postgres' cron.log_statement = 'on' cron.max_running_jobs = '32' cursor_tuple_fraction = '0.1' datestyle = 'ISO,YMD' deadlock_timeout = '1000ms' debug_pretty_print = 'True' debug_print_parse = 'False' debug_print_plan = 'False' debug_print_rewritten = 'False' default_statistics_target = '100' default_transaction_deferrable = 'False' default_transaction_isolation = 'read committed' effective_cache_size = '256MB' effective_io_concurrency = '1' enable_bitmapscan = 'True' enable_gathermerge = 'True' enable_hashagg = 'True' enable_hashjoin = 'True' enable_indexonlyscan = 'True' enable_indexscan = 'True' enable_material = 'True' enable_mergejoin = 'True' enable_nestloop = 'True' enable_parallel_append = 'True' enable_parallel_hash = 'True' enable_partition_pruning = 'True' enable_partitionwise_aggregate = 'True' enable_partitionwise_join = 'True' enable_seqscan = 'True' enable_sort = 'True' enable_tidscan = 'True' escape_string_warning = 'True' extra_float_digits = '1' force_parallel_mode = '0' from_collapse_limit = '8'
fsync=off # patroni for Extreme Performance
full_page_writes=off # patroni for Extreme Performance
geqo = 'True' geqo_effort = '5' geqo_generations = '0' geqo_pool_size = '0' geqo_seed = '0' geqo_selection_bias = '2' geqo_threshold = '12' gin_fuzzy_search_limit = '0' gin_pending_list_limit = '4096kB' hot_standby_feedback = 'False' huge_pages = 'try' idle_in_transaction_session_timeout = '3600000ms' index_adviser.enable_log = 'on' index_adviser.max_aggregation_column_count = '10' index_adviser.max_candidate_index_count = '500' intervalstyle = 'postgres' join_collapse_limit = '8' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' lock_timeout = '0' log_autovacuum_min_duration = '10000' log_checkpoints = 'True' log_connections = 'False' log_disconnections = 'False' log_duration = 'False' log_executor_stats = 'False'
log_lock_waits = 'True'
log_min_duration_statement = '1000' log_parser_stats = 'False' log_planner_stats = 'False' log_replication_commands = 'False' log_statement = 'ddl' log_statement_stats = 'False' log_temp_files = '128kB' log_transaction_sample_rate = '0'
maintenance_work_mem = '3952MB'
max_connections = 500 max_files_per_process = '1000' max_logical_replication_workers = '32' max_locks_per_transaction = '64' max_parallel_maintenance_workers = '2' max_parallel_workers = '8' max_parallel_workers_per_gather = '2' max_pred_locks_per_page = '2' max_pred_locks_per_relation = '-2' max_pred_locks_per_transaction = '64' max_prepared_transactions = '100' max_replication_slots = '16' max_stack_depth = '2MB' max_standby_archive_delay = '300000ms' max_standby_streaming_delay = '300000ms' max_sync_workers_per_subscription = '2' max_wal_senders = '64' max_worker_processes = '8' min_parallel_index_scan_size = '512kB' min_parallel_table_scan_size = '8MB' max_wal_size = '256MB' min_wal_size = '64MB' old_snapshot_threshold = '-1' parallel_leader_participation = 'True' password_encryption = 'md5' pg_stat_statements.max = '5000' pg_stat_statements.save = 'False' pg_stat_statements.track = 'top'
pg_stat_statements.track_planning = 'False'
pg_stat_statements.track_utility = 'False'
extension: pgaudit
pgaudit.log_catalog = 'True' pgaudit.log_level = 'log' pgaudit.log_parameter = 'False' pgaudit.log_relation = 'False' pgaudit.log_statement_once = 'False'
pgaudit.role = ''
extension: pglogical
pglogical.batch_inserts = 'True' pglogical.conflict_log_level = 'log' pglogical.conflict_resolution = 'apply_remote'
pglogical.extra_connection_options = ''
pglogical.synchronous_commit = 'False' pglogical.use_spi = 'False' plan_cache_mode = 'auto' quote_all_identifiers = 'False' random_page_cost = '1.1' row_security = 'True' session_replication_role = 'origin'
extension: sql_firewall
sql_firewall.firewall = 'disable' shared_buffers = '128MB'
shared_preload_libraries = 'pg_stat_statements,auto_explain,bg_mon,pgextwlist,pg_auth_mon,set_user,pg_cron,pg_stat_kcache'
ssl_min_protocol_version = 'TLSv1' standard_conforming_strings = 'True' statement_timeout = '0' superuser_reserved_connections = '20' synchronize_seqscans = 'True' synchronous_commit = 'off'
synchronous_standby_names=''
tcp_keepalives_count = '10' tcp_keepalives_idle = '45s' tcp_keepalives_interval = '10s' temp_buffers = '8MB' temp_file_limit = '524288kB'
extension: timescaledb
timescaledb.max_background_workers = '6'
timescaledb.telemetry_level = 'off'
TODO timezone
timezone=Asia/Shanghai
track_activity_query_size = '4096' track_commit_timestamp = 'False' track_functions = 'pl' track_io_timing = 'True' transform_null_equals = 'False' vacuum_cost_delay = '0' vacuum_cost_limit = '10000' vacuum_cost_page_dirty = '20' vacuum_cost_page_hit = '1' vacuum_cost_page_miss = '2' vacuum_defer_cleanup_age = '0' vacuum_freeze_min_age = '50000000' vacuum_freeze_table_age = '200000000' vacuum_multixact_freeze_min_age = '5000000' vacuum_multixact_freeze_table_age = '200000000' wal_buffers = '16MB' wal_compression = 'True' wal_init_zero = off wal_level = 'replica' wal_log_hints = 'False' wal_receiver_status_interval = '1s' wal_receiver_timeout = '60000' wal_sender_timeout = '60000' wal_writer_delay = '200ms' wal_writer_flush_after = '1MB' work_mem = '4096kB' xmlbinary = 'base64' xmloption = 'content'
the following are the parameters adjusted for postgresql14 relative to postgresql12
autovacuum_vacuum_insert_scale_factor = '0.2' autovacuum_vacuum_insert_threshold = '1000' client_connection_check_interval = '0' compute_query_id = 'auto' default_toast_compression = 'pglz' enable_async_append = 'True' enable_incremental_sort = 'True' enable_memoize = 'True' hash_mem_multiplier = '1' idle_session_timeout = '0' log_min_duration_sample = '-1' log_parameter_max_length = '-1' log_parameter_max_length_on_error = '0' log_recovery_conflict_waits = 'False' log_statement_sample_rate = '1.0' logical_decoding_work_mem = '65536' maintenance_io_concurrency = '0' max_slot_wal_keep_size = '-1' min_dynamic_shared_memory = '0' remove_temp_files_after_crash = 'on' track_wal_io_timing = 'False' vacuum_failsafe_age = '1600000000' vacuum_multixact_failsafe_age = '1600000000' wal_keep_size = '0' wal_skip_threshold = '2048'
the following parameters are related to consensus extension
shared_preload_libraries = 'consensus' consensus.consensus_enabled = true
root@apepg-cluster-postgresql-0:/# ps aux|grep postgresql.conf postgres 15 0.0 0.2 284960 29640 ? S 06:11 0:00 postgres -D /postgresql/data/pgconsensus_data --config-file=/postgresql/data/pgconsensus_data/postgresql.conf --hba_file=/postgresql/data/pgconsensus_data/pg_hba.conf root 115 0.0 0.0 6040 636 pts/0 S+ 06:20 0:00 grep postgresql.conf
cat /postgresql/data/pgconsensus_data/postgresql.conf cat /postgresql/data/pgconsensus_data/postgresql.conf
-----------------------------
PostgreSQL configuration file
-----------------------------
#
This file consists of lines of the form:
#
name = value
#
(The "=" is optional.) Whitespace may be used. Comments are introduced with
"#" anywhere on a line. The complete list of parameter names and allowed
values can be found in the PostgreSQL documentation.
#
The commented-out settings shown in this file represent the default values.
Re-commenting a setting is NOT sufficient to revert it to the default value;
you need to reload the server.
#
This file is read on server startup and when the server receives a SIGHUP
signal. If you edit the file on a running system, you have to SIGHUP the
server for the changes to take effect, run "pg_ctl reload", or execute
"SELECT pg_reload_conf()". Some parameters, which are marked below,
require a server shutdown and restart to take effect.
#
Any parameter can also be given as a command-line option to the server, e.g.,
"postgres -c log_connections=on". Some parameters can be changed at run time
with the "SET" SQL command.
#
Memory units: B = bytes Time units: us = microseconds
kB = kilobytes ms = milliseconds
MB = megabytes s = seconds
GB = gigabytes min = minutes
TB = terabytes h = hours
d = days
------------------------------------------------------------------------------
FILE LOCATIONS
------------------------------------------------------------------------------
The default values of these variables are driven from the -D command-line
option or PGDATA environment variable, represented here as ConfigDir.
data_directory = 'ConfigDir' # use data in another directory
hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
If external_pid_file is not explicitly set, no extra PID file is written.
external_pid_file = '' # write an extra PID file
------------------------------------------------------------------------------
CONNECTIONS AND AUTHENTICATION
------------------------------------------------------------------------------
- Connection Settings -
listen_addresses = '*'
comma-separated list of addresses;
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
unix_socket_group = '' # (change requires restart)
unix_socket_permissions = 0777 # begin with 0 to use octal notation
bonjour = off # advertise server via Bonjour
bonjour_name = '' # defaults to the computer name
- TCP settings -
see "man tcp" for details
tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 0 # TCP_KEEPCNT;
tcp_user_timeout = 0 # TCP_USER_TIMEOUT, in milliseconds;
client_connection_check_interval = 0 # time between checks for client
- Authentication -
authentication_timeout = 1min # 1s-600s
password_encryption = scram-sha-256 # scram-sha-256 or md5
db_user_namespace = off
GSSAPI using Kerberos
krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
krb_caseins_users = off
- SSL -
ssl = off
ssl_ca_file = ''
ssl_cert_file = 'server.crt'
ssl_crl_file = ''
ssl_crl_dir = ''
ssl_key_file = 'server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
ssl_prefer_server_ciphers = on
ssl_ecdh_curve = 'prime256v1'
ssl_min_protocol_version = 'TLSv1.2'
ssl_max_protocol_version = ''
ssl_dh_params_file = ''
ssl_passphrase_command = ''
ssl_passphrase_command_supports_reload = off
------------------------------------------------------------------------------
RESOURCE USAGE (except WAL)
------------------------------------------------------------------------------
- Memory -
shared_buffers = 128MB # min 128kB
(change requires restart)
huge_pages = try # on, off, or try
huge_page_size = 0 # zero for system default
temp_buffers = 8MB # min 800kB
max_prepared_transactions = 0 # zero disables the feature
Caution: it is not advisable to set max_prepared_transactions nonzero unless
you actively intend to use prepared transactions.
work_mem = 4MB # min 64kB
hash_mem_multiplier = 1.0 # 1-1000.0 multiplier on hash table work_mem
maintenance_work_mem = 64MB # min 1MB
autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
logical_decoding_work_mem = 64MB # min 64kB
max_stack_depth = 2MB # min 100kB
shared_memory_type = mmap # the default is the first option
dynamic_shared_memory_type = posix # the default is the first option
supported by the operating system:
min_dynamic_shared_memory = 0MB # (change requires restart)
- Disk -
temp_file_limit = -1 # limits per-process temp file space
- Kernel Resources -
max_files_per_process = 1000 # min 64
- Cost-Based Vacuum Delay -
vacuum_cost_delay = 0 # 0-100 milliseconds (0 disables)
vacuum_cost_page_hit = 1 # 0-10000 credits
vacuum_cost_page_miss = 2 # 0-10000 credits
vacuum_cost_page_dirty = 20 # 0-10000 credits
vacuum_cost_limit = 200 # 1-10000 credits
- Background Writer -
bgwriter_delay = 200ms # 10-10000ms between rounds
bgwriter_lru_maxpages = 100 # max buffers written/round, 0 disables
bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
bgwriter_flush_after = 512kB # measured in pages, 0 disables
- Asynchronous Behavior -
backend_flush_after = 0 # measured in pages, 0 disables
effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
maintenance_io_concurrency = 10 # 1-1000; 0 disables prefetching
max_worker_processes = 8 # (change requires restart)
max_parallel_workers_per_gather = 2 # taken from max_parallel_workers
max_parallel_maintenance_workers = 2 # taken from max_parallel_workers
max_parallel_workers = 8 # maximum number of max_worker_processes that
parallel_leader_participation = on
old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate
------------------------------------------------------------------------------
WRITE-AHEAD LOG
------------------------------------------------------------------------------
- Settings -
wal_level = replica # minimal, replica, or logical
fsync = on # flush data to disk for crash safety
synchronous_commit = on # synchronization level;
wal_sync_method = fsync # the default is the first option
full_page_writes = on # recover from partial page writes
wal_log_hints = off # also do full page writes of non-critical updates
wal_compression = off # enable compression of full-page writes
wal_init_zero = on # zero-fill new WAL files
wal_recycle = on # recycle WAL files
wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 200ms # 1-10000 milliseconds
wal_writer_flush_after = 1MB # measured in pages, 0 disables
wal_skip_threshold = 2MB
commit_delay = 0 # range 0-100000, in microseconds
commit_siblings = 5 # range 1-1000
- Checkpoints -
checkpoint_timeout = 5min # range 30s-1d
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
checkpoint_flush_after = 256kB # measured in pages, 0 disables
checkpoint_warning = 30s # 0 disables
max_wal_size = 1GB min_wal_size = 80MB
- Archiving -
archive_mode = off # enables archiving; off, on, or always
archive_command = '' # command to use to archive a logfile segment
archive_timeout = 0 # force a logfile segment switch after this
- Archive Recovery -
These are only used in recovery mode.
restore_command = '' # command to use to restore an archived logfile segment
archive_cleanup_command = '' # command to execute at every restartpoint
recovery_end_command = '' # command to execute at completion of recovery
- Recovery Target -
Set these only when performing a targeted recovery.
recovery_target = '' # 'immediate' to end recovery as soon as a
recovery_target_name = '' # the named restore point to which recovery will proceed
recovery_target_time = '' # the time stamp up to which recovery will proceed
recovery_target_xid = '' # the transaction ID up to which recovery will proceed
recovery_target_lsn = '' # the WAL LSN up to which recovery will proceed
recovery_target_inclusive = on # Specifies whether to stop:
recovery_target_timeline = 'latest' # 'current', 'latest', or timeline ID
recovery_target_action = 'pause' # 'pause', 'promote', 'shutdown'
------------------------------------------------------------------------------
REPLICATION
------------------------------------------------------------------------------
- Sending Servers -
Set these on the primary and on any standby that will send replication data.
max_wal_senders = 10 # max number of walsender processes
max_replication_slots = 10 # max number of replication slots
wal_keep_size = '128MB'
max_slot_wal_keep_size = -1 # in megabytes; -1 disables
wal_sender_timeout = 60s # in milliseconds; 0 disables
track_commit_timestamp = off # collect timestamp of transaction commit
- Primary Server -
These settings are ignored on a standby server.
synchronous_standby_names = '' # standby servers that provide sync rep
vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
- Standby Servers -
These settings are ignored on a primary server.
primary_conninfo = '' # connection string to sending server
primary_slot_name = '' # replication slot on sending server
promote_trigger_file = '' # file name whose presence ends recovery
hot_standby = on # "off" disallows queries during recovery
max_standby_archive_delay = 30s # max delay before canceling queries
max_standby_streaming_delay = 30s # max delay before canceling queries
wal_receiver_create_temp_slot = off # create temp slot if primary_slot_name
wal_receiver_status_interval = 10s # send replies at least this often
hot_standby_feedback = off # send info from standby to prevent
wal_receiver_timeout = 60s # time that receiver waits for
wal_retrieve_retry_interval = 5s # time to wait before retrying to
recovery_min_apply_delay = 0 # minimum delay for applying changes during recovery
- Subscribers -
These settings are ignored on a publisher.
max_logical_replication_workers = 4 # taken from max_worker_processes
max_sync_workers_per_subscription = 2 # taken from max_logical_replication_workers
------------------------------------------------------------------------------
QUERY TUNING
------------------------------------------------------------------------------
- Planner Method Configuration -
enable_async_append = on
enable_bitmapscan = on
enable_gathermerge = on
enable_hashagg = on
enable_hashjoin = on
enable_incremental_sort = on
enable_indexscan = on
enable_indexonlyscan = on
enable_material = on
enable_memoize = on
enable_mergejoin = on
enable_nestloop = on
enable_parallel_append = on
enable_parallel_hash = on
enable_partition_pruning = on
enable_partitionwise_join = off
enable_partitionwise_aggregate = off
enable_seqscan = on
enable_sort = on
enable_tidscan = on
- Planner Cost Constants -
seq_page_cost = 1.0 # measured on an arbitrary scale
random_page_cost = 4.0 # same scale as above
cpu_tuple_cost = 0.01 # same scale as above
cpu_index_tuple_cost = 0.005 # same scale as above
cpu_operator_cost = 0.0025 # same scale as above
parallel_setup_cost = 1000.0 # same scale as above
parallel_tuple_cost = 0.1 # same scale as above
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
effective_cache_size = 4GB
jit_above_cost = 100000 # perform JIT compilation if available
jit_inline_above_cost = 500000 # inline small functions if query is
jit_optimize_above_cost = 500000 # use expensive JIT optimizations if
- Genetic Query Optimizer -
geqo = on
geqo_threshold = 12
geqo_effort = 5 # range 1-10
geqo_pool_size = 0 # selects default based on effort
geqo_generations = 0 # selects default based on effort
geqo_selection_bias = 2.0 # range 1.5-2.0
geqo_seed = 0.0 # range 0.0-1.0
- Other Planner Options -
default_statistics_target = 100 # range 1-10000
constraint_exclusion = partition # on, off, or partition
cursor_tuple_fraction = 0.1 # range 0.0-1.0
from_collapse_limit = 8
jit = on # allow JIT compilation
join_collapse_limit = 8 # 1 disables collapsing of explicit
plan_cache_mode = auto # auto, force_generic_plan or
------------------------------------------------------------------------------
REPORTING AND LOGGING
------------------------------------------------------------------------------
- Where to Log -
log_destination = 'stderr' # Valid values are combinations of
This is used when logging to stderr:
logging_collector = off # Enable capturing of stderr and csvlog
These are only used if logging_collector is on:
log_directory = 'log' # directory where log files are written,
logfilename = 'postgresql-%Y-%m-%d%H%M%S.log' # log file name pattern,
log_file_mode = 0600 # creation mode for log files,
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 10MB # Automatic rotation of logfiles will
log_truncate_on_rotation = off # If on, an existing log file with the
These are relevant when logging to syslog:
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
syslog_sequence_numbers = on
syslog_split_messages = on
This is only relevant when logging to eventlog (Windows):
(change requires restart)
event_source = 'PostgreSQL'
- When to Log -
log_min_messages = warning # values in order of decreasing detail:
log_min_error_statement = error # values in order of decreasing detail:
log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
log_min_duration_sample = -1 # -1 is disabled, 0 logs a sample of statements
log_statement_sample_rate = 1.0 # fraction of logged statements exceeding
log_transaction_sample_rate = 0.0 # fraction of transactions whose statements
- What to Log -
debug_print_parse = off
debug_print_rewritten = off
debug_print_plan = off
debug_pretty_print = on
log_autovacuum_min_duration = -1 # log autovacuum activity;
log_checkpoints = off
log_connections = off
log_disconnections = off
log_duration = off
log_error_verbosity = default # terse, default, or verbose messages
log_hostname = off
log_line_prefix = '%m [%p] ' # special values:
log_lock_waits = off # log lock waits >= deadlock_timeout
log_recovery_conflict_waits = off # log standby recovery conflict waits
log_parameter_max_length = -1 # when logging statements, limit logged
log_parameter_max_length_on_error = 0 # when logging an error, limit logged
log_statement = 'none' # none, ddl, mod, all
log_replication_commands = off
log_temp_files = -1 # log temporary files equal or larger
log_timezone = 'Etc/UTC'
------------------------------------------------------------------------------
PROCESS TITLE
------------------------------------------------------------------------------
cluster_name = '' # added to process titles if nonempty
update_process_title = on
------------------------------------------------------------------------------
STATISTICS
------------------------------------------------------------------------------
- Query and Index Statistics Collector -
track_activities = on
track_activity_query_size = 1024 # (change requires restart)
track_counts = on
track_io_timing = off
track_wal_io_timing = off
track_functions = none # none, pl, all
stats_temp_directory = 'pg_stat_tmp'
- Monitoring -
compute_query_id = auto
log_statement_stats = off
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
------------------------------------------------------------------------------
AUTOVACUUM
------------------------------------------------------------------------------
autovacuum = on # Enable autovacuum subprocess? 'on'
autovacuum_max_workers = 3 # max number of autovacuum subprocesses
autovacuum_naptime = 1min # time between autovacuum runs
autovacuum_vacuum_threshold = 50 # min number of row updates before
autovacuum_vacuum_insert_threshold = 1000 # min number of row inserts
autovacuum_analyze_threshold = 50 # min number of row updates before
autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
autovacuum_vacuum_insert_scale_factor = 0.2 # fraction of inserts over table
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
autovacuum_vacuum_cost_delay = 2ms # default vacuum cost delay for
autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
------------------------------------------------------------------------------
CLIENT CONNECTION DEFAULTS
------------------------------------------------------------------------------
- Statement Behavior -
client_min_messages = notice # values in order of decreasing detail:
search_path = '"$user", public' # schema names
row_security = on
default_table_access_method = 'heap'
default_tablespace = '' # a tablespace name, '' uses the default
default_toast_compression = 'pglz' # 'pglz' or 'lz4'
temp_tablespaces = '' # a list of tablespace names, '' uses
check_function_bodies = on
default_transaction_isolation = 'read committed'
default_transaction_read_only = off
default_transaction_deferrable = off
session_replication_role = 'origin'
statement_timeout = 0 # in milliseconds, 0 is disabled
lock_timeout = 0 # in milliseconds, 0 is disabled
idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
idle_session_timeout = 0 # in milliseconds, 0 is disabled
vacuum_freeze_table_age = 150000000
vacuum_freeze_min_age = 50000000
vacuum_failsafe_age = 1600000000
vacuum_multixact_freeze_table_age = 150000000
vacuum_multixact_freeze_min_age = 5000000
vacuum_multixact_failsafe_age = 1600000000
bytea_output = 'hex' # hex, escape
xmlbinary = 'base64'
xmloption = 'content'
gin_pending_list_limit = 4MB
- Locale and Formatting -
datestyle = 'iso, mdy'
intervalstyle = 'postgres'
timezone = 'Etc/UTC'
timezone_abbreviations = 'Default' # Select the set of available time zone
extra_float_digits = 1 # min -15, max 3; any value >0 actually
client_encoding = sql_ascii # actually, defaults to database
These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.utf8' # locale for system error message
strings
lc_monetary = 'en_US.utf8' # locale for monetary formatting lc_numeric = 'en_US.utf8' # locale for number formatting lc_time = 'en_US.utf8' # locale for time formatting
default configuration for text search
default_text_search_config = 'pg_catalog.english'
- Shared Library Preloading -
local_preload_libraries = ''
session_preload_libraries = ''
shared_preload_libraries = 'consensus'
jit_provider = 'llvmjit' # JIT library to use
- Other Defaults -
dynamic_library_path = '$libdir'
gin_fuzzy_search_limit = 0
------------------------------------------------------------------------------
LOCK MANAGEMENT
------------------------------------------------------------------------------
deadlock_timeout = 1s
max_locks_per_transaction = 64 # min 10
max_pred_locks_per_transaction = 64 # min 10
max_pred_locks_per_relation = -2 # negative values mean
max_pred_locks_per_page = 2 # min 0
------------------------------------------------------------------------------
VERSION AND PLATFORM COMPATIBILITY
------------------------------------------------------------------------------
- Previous PostgreSQL Versions -
array_nulls = on
backslash_quote = safe_encoding # on, off, or safe_encoding
escape_string_warning = on
lo_compat_privileges = off
quote_all_identifiers = off
standard_conforming_strings = on
synchronize_seqscans = on
- Other Platforms and Clients -
transform_null_equals = off
------------------------------------------------------------------------------
ERROR HANDLING
------------------------------------------------------------------------------
exit_on_error = off # terminate session on any error?
restart_after_crash = on # reinitialize after backend crash?
data_sync_retry = off # retry or panic on failure to fsync
recovery_init_sync_method = fsync # fsync, syncfs (Linux 5.8+)
------------------------------------------------------------------------------
CONFIG FILE INCLUDES
------------------------------------------------------------------------------
These options allow settings to be loaded from files other than the
default postgresql.conf. Note that these are directives, not variable
assignments, so they can usefully be given more than once.
include_dir = '...' # include files ending in '.conf' from
include_if_exists = '...' # include file only if it exists
include = '...' # include file
------------------------------------------------------------------------------
CUSTOMIZED OPTIONS
------------------------------------------------------------------------------
Add settings for extensions here
consensus.consensus_enabled = 'true' consensus.consensus_repl_user = 'replicator'