Config Check
Here are some observations based on the current configuration settings
for this server.
{{if index .CC.Cats "connection_settings"}}
Connections and Authentication: Connection Settings
{{$v := index .CC.Values "listen_addresses"}}{{if $v}}
listen_addresses
: The current value of
{{$v}}
will make PostgreSQL listen on all available network interfaces.
This is a potential security risk.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "authentication"}}
Connections and Authentication: Authentication
{{$v := index .CC.Values "authentication_timeout"}}{{if $v}}
authentication_timeout
: The current value of
{{$v}}
seconds might be too high. This is the time limit
within which the client must complete authentication.
Learn more.
{{end}}
{{$v := index .CC.Values "password_encryption"}}{{if $v}}
password_encryption
: Current value is
md5
. Using the scram-sha-256
authentication
method can be more secure.
Learn more.
{{end}}
{{$v := index .CC.Values "db_user_namespace"}}{{if $v}}
db_user_namespace
: This is currently
enabled. This feature can be unintutive and error-prone, and should
be avoided if possible.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "ssl"}}
Connections and Authentication: SSL
{{$v := index .CC.Values "ssl_ciphers"}}{{if $v}}
ssl_ciphers
: The current value of
{{$v}}
is not a standard recommended setting, and can be a security risk.
Learn more.
{{end}}
{{$v := index .CC.Values "ssl_prefer_server_ciphers"}}{{if $v}}
ssl_prefer_server_ciphers
: The current value of
this setting is {{$v}}
. This is a potential security risk.
Learn more.
{{end}}
{{$v := index .CC.Values "ssl_min_protocol_version"}}{{if $v}}
ssl_min_protocol_version
: The current value of
this setting is {{$v}}
. This is a potential security risk.
Either TLSv1.2
or TLSv1.3
is recommended as the
minimum protocol version.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "memory"}}
Resource Usage: Memory
{{$v := index .CC.Values "shared_buffers"}}{{if $v}}
shared_buffers
: The current value of
{{$v}}
may not be optimal. It is recommended to set this
between 25% and 40% of available RAM.
Learn more.
{{end}}
{{$v := index .CC.Values "huge_pages"}}{{if $v}}
huge_pages
: Is currently set to
{{$v}}
. Configuring PostgreSQL to use huge pages can
result in higher performance and better memory efficiency.
Learn more.
{{end}}
{{$v := index .CC.Values "max_prepared_transactions"}}{{if $v}}
max_prepared_transactions
: Currently,
{{$v}}
prepared transactions are allowed. If the prepared
transactions feature is not being used, it is recommended to set this
to 0
.
Learn more.
{{end}}
{{$v := index .CC.Values "work_mem"}}{{if $v}}
work_mem
: This is currently set to
{{$v}}
. Consider raising this to at least 4 MiB
to avoid creation of temporary files to process queries.
Learn more.
{{end}}
{{$v := index .CC.Values "hash_mem_multiplier"}}{{if $v}}
hash_mem_multiplier
: Increasing this
setting from its current value of
{{$v}}
can help to avoid the creation of temporary files
during hash-based operations.
Learn more.
{{end}}
{{$v := index .CC.Values "shared_memory_type"}}{{if $v}}
shared_memory_type
: Setting this to
sysv
, which it is currently, is generally discouraged.
Learn more.
{{end}}
{{$v := index .CC.Values "dynamic_shared_memory_type"}}{{if $v}}
dynamic_shared_memory_type
: Setting this to
mmap
, which it is currently, is generally discouraged.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "disk"}}
Resource Usage: Disk
{{$v := index .CC.Values "temp_file_limit"}}{{if $v}}
temp_file_limit
: The current value of
-1
allows for unbounded generation of temporary files.
An upper limit on the amount of disk space used for temporary files
at any given time can be configured using this setting.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "async"}}
Resource Usage: Asynchronous Behavior
{{$v := index .CC.Values "effective_io_concurrency"}}{{if $v}}
effective_io_concurrency
: The current value of
1
can be sub-optimal if SSD disks or RAID arrays are
in use. Increasing this value lets PostgreSQL backends issue disk
requests concurrently, and can result in faster query completion
times.
Learn more.
{{end}}
{{$v := index .CC.Values "max_worker_processes"}}{{if $v}}
max_worker_processes
: The current value of
{{$v}}
is less than the number of CPU cores available in
the system. Setting this to at least the number of available
CPU cores can result in improved performance.
Learn more.
{{end}}
{{$v := index .CC.Values "old_snapshot_threshold"}}{{if $v}}
old_snapshot_threshold
: The current value of
-1
disables this feature. By setting this value, old
query snapshot data is allowed to be vacuumed away.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "wal_settings"}}
Write Ahead Log: Settings
{{$v := index .CC.Values "fsync"}}{{if $v}}
fsync
: The current value is
off
. This can result in data corruption in the event
of power failure.
Learn more.
{{end}}
{{$v := index .CC.Values "synchronous_commit"}}{{if $v}}
synchronous_commit
: The current value is
off
. This can affect durability of transactions in case
of a server crash.
Learn more.
{{end}}
{{$v := index .CC.Values "full_page_writes"}}{{if $v}}
full_page_writes
: The current value of
off
might result in unrecoverable or silent data
corruption in case of a system failure.
Learn more.
{{end}}
{{$v := index .CC.Values "wal_compression"}}{{if $v}}
wal_compression
: Is currently set to
off
. Turning this on can reduce WAL volume.
Learn more.
{{end}}
{{$v := index .CC.Values "wal_init_zero"}}{{if $v}}
wal_init_zero
: Is currently set to
on
. It is recommended to turn this off
if
the WAL files reside on a copy-on-write filesystem.
Learn more.
{{end}}
{{$v := index .CC.Values "wal_recycle"}}{{if $v}}
wal_recycle
: Is currently set to
on
. It is recommended to turn this off
if
the WAL files reside on a copy-on-write filesystem.
Learn more.
{{end}}
{{$v := index .CC.Values "commit_delay"}}{{if $v}}
commit_delay
: WAL flushes can be delayed
to let multiple transactions commit via a single WAL flush, by
increasing this setting from its current value of 0
seconds.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "wal_archiving"}}
Write Ahead Log: Archiving
{{$v := index .CC.Values "archive_timeout"}}{{if $v}}
archive_timeout
: The current value of
{{$v}}
seconds might cause WAL files to be generated too
frequently.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "repl_sending"}}
Replication: Sending Servers
{{$v := index .CC.Values "wal_keep_size"}}{{if $v}}
wal_keep_size
: Currently, no past WAL
files are retained to allow standbys to catch up because this
value is set to 0
. Increasing this improves
reliability of streaming replication.
Learn more.
{{end}}
{{$v := index .CC.Values "wal_keep_segments"}}{{if $v}}
wal_keep_segments
: Currently, no past WAL
files are retained to allow standbys to catch up because this
value is set to 0
. Increasing this improves
reliability of streaming replication.
Learn more.
{{end}}
{{$v := index .CC.Values "max_slot_wal_keep_size"}}{{if $v}}
max_slot_wal_keep_size
: This is currently
set to -1
. This will cause PostgreSQL to
keep WAL files around indefinitely if a replication slot becomes
and remains inactive, eventually filling up the disk. Setting this
value places an upper bound on the size of the WAL files retained
for this purpose.
Learn more.
{{end}}
{{$v := index .CC.Values "wal_sender_timeout"}}{{if $v}}
wal_sender_timeout
: The current value of
{{$v}}
seconds may not be optimal. Adjusting this value to suit
the reliability of the TCP connection between the replicating
servers results in faster failure detection.
Learn more.
{{end}}
{{$v := index .CC.Values "track_commit_timestamp"}}{{if $v}}
track_commit_timestamp
: This setting
is currently off
. Enabling this can be used to track
replication lag on standbys easily, at the cost of a few bytes
storage per transaction.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "repl_primary"}}
Replication: Primary Servers
{{$v := index .CC.Values "vacuum_defer_cleanup_age"}}{{if $v}}
vacuum_defer_cleanup_age
: This setting
is currently 0
. Increasing this value makes the vacuum
process defer the cleanup of dead row versions, which allows more
time for queries executing on hot standbys to complete without
conflicts.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "repl_standby"}}
Replication: Standby Servers
{{$v := index .CC.Values "max_standby_streaming_delay"}}{{if $v}}
max_standby_streaming_delay
:
The current value of this setting is
{{$v}}
seconds. This specifies
how long the server will wait before cancelling queries that will
conflict with changes that are about to be applied. (-1 means to
wait forever). Tweaking this can help with query conflicts on this
server if it is a hot standby.
Learn more.
{{end}}
{{$v := index .CC.Values "hot_standby_feedback"}}{{if $v}}
hot_standby_feedback
:
This is currently turned off. Enabling this on a hot standby server
will cause it to send feedback to the primary about currently
executing queries, inorder to reduce query cancellations.
Learn more.
{{end}}
{{$v := index .CC.Values "wal_receiver_timeout"}}{{if $v}}
wal_receiver_timeout
: The current value of
{{$v}}
seconds may not be optimal. Adjusting this value to suit
the reliability of the TCP connection between the replicating
servers results in faster failure detection.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "query_config"}}
Query Tuning: Planner Method Configuration
The following planner methods have been disabled at the global level.
Consider disabling them at a database, user, or table level instead:
{{range $s := .CC.PlannerMethods}}
{{$v := index $.CC.Values $s}}{{if $v}}
{{$s}}
{{end}}
{{end}}
{{end}}
{{if index .CC.Cats "query_cost"}}
Query Tuning: Planner Cost Constants
{{$v := index .CC.Values "random_page_cost"}}{{if $v}}
random_page_cost
:
The current value of this setting is {{$v}}
. Tweaking
this based on your query workload can improve query performance.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "query_geqo"}}
Query Tuning: Genetic Query Optimizer
{{$v := index .CC.Values "geqo"}}{{if $v}}
geqo
: This is currently turned off.
It is usually best to leave this on.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "query_other"}}
Query Tuning: Other Planner Options
{{$v := index .CC.Values "default_statistics_target"}}{{if $v}}
default_statistics_target
: The current
value of {{$v}}
may not be optimal. Consider reducing
this value at table or column level rather than globally.
Learn more.
{{end}}
{{$v := index .CC.Values "constraint_exclusion"}}{{if $v}}
constraint_exclusion
: This is currently
set to on
. Setting this to partition
or on
might benefit simple queries.
Learn more.
{{end}}
{{$v := index .CC.Values "jit"}}{{if $v}}
jit
: JIT is currently turned off.
Unless JIT is causing crashes, it is beneficial to turn this on.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "log_when"}}
Reporting and Logging: When to Log
{{$v := index .CC.Values "log_min_messages"}}{{if $v}}
log_min_messages
: The current value of
this setting is {{$v}}
. Changing this to WARNING
will include warning-level messages also in the log output, which can
assist with troubleshooting.
Learn more.
{{end}}
{{$v := index .CC.Values "log_min_error_statement"}}{{if $v}}
log_min_error_statement
: The current value of
this setting is {{$v}}
. Changing this to ERROR
will include in the log output all SQL statements that cause logs
of level error and above. This can assist with troubleshooting.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "log_what"}}
Reporting and Logging: What to Log
{{$v := index .CC.Values "log_autovacuum_min_duration"}}{{if $v}}
log_autovacuum_min_duration
: This is
currently turned off. This setting can be used to log the execution
of autovacuum runs that exceed a specified duration, which can be
helpful in tuning autovacuum.
Learn more.
{{end}}
{{$v := index .CC.Values "log_lock_waits"}}{{if $v}}
log_lock_waits
: This is
currently turned off. This setting can be used to log messages whenever
a session has to wait too long to acquire a lock. This can be
helpful in debugging slow queries.
Learn more.
{{end}}
{{$v := index .CC.Values "log_recovery_conflict_waits"}}{{if $v}}
log_recovery_conflict_waits
: This is
currently turned off. This setting can be used to log messages whenever
a startup process has to wait too long for recovery conflicts.
Learn more.
{{end}}
{{$v := index .CC.Values "log_temp_files"}}{{if $v}}
log_temp_files
: This is
currently turned off. This setting can be used to log messages whenever
a temporary file exceeding a certain size is created. This can be
helpful in debugging slow queries.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "stats_qi"}}
Statistics: Cumulative Query and Index Statistics
{{$v := index .CC.Values "track_activities"}}{{if $v}}
track_activities
: This is currently
turned off. Enabling this lets you see the current queries being
run in each session.
Learn more.
{{end}}
{{$v := index .CC.Values "track_counts"}}{{if $v}}
track_counts
: This is currently
turned off. This generally should be turned on as it is required
for autovacuum.
Learn more.
{{end}}
{{$v := index .CC.Values "track_io_timing"}}{{if $v}}
track_io_timing
: This is currently
turned off. Enabling this shows how much time is spent in disk
I/O. It is usually ok to turn this on in modern operating systems.
Learn more.
{{end}}
{{$v := index .CC.Values "track_wal_io_timing"}}{{if $v}}
track_wal_io_timing
: This is currently
turned off. Enabling this shows how much time is spent in disk
I/O of WAL files. It is usually ok to turn this on in modern operating systems.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "ccd_statement"}}
Client Connection Defaults: Statement Behavior
{{$v := index .CC.Values "idle_in_transaction_session_timeout"}}{{if $v}}
idle_in_transaction_session_timeout
:
This is currently turned off. This can be used to set a timeout for
sessions that have a transaction open but are idling or waiting for
locks. Long running transactions generally
have an adverse effect on Postgres, and this setting can help
prevent some of them.
Learn more.
{{end}}
{{end}}
{{if index .CC.Cats "ccd_shlib"}}
Client Connection Defaults: Shared Library Preloading
{{if .CC.SuggestPSS}}
shared_preload_libraries
: The current
value of this setting does not include pg_stat_statements
.
pg_stat_statements is a popular extension included in the core PostgreSQL
distribution that provides query-level statistics.
Learn more.
{{end}}
{{if .CC.SuggestAE}}
shared_preload_libraries
: The current
value of this setting does not include auto_explain
.
auto_explain is a popular extension included in the core PostgreSQL
distribution that can be used to log the query execution plans of
slow queries.
Learn more.
{{end}}
{{end}}