Starting with Pg – where/how can I set configuration parameters?

Previously I wrote about locating config files.

The thing is – postgresql.conf is not the only place you can set your configuration in.

In here, I'll describe all the places that can be used, why do we even have more than one place, and finally – how to find out where given value comes from.

Obviously you can have config in postgresql.conf file. This file can include more files and directories. Look into your postgresql.conf and see if you have include_* params defined.

For me, it looks like:

#------------------------------------------------------------------------------
# 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 = 'conf.d'
                                        # a directory, e.g., 'conf.d'
#include_if_exists = '...'              # include file only if it exists
#include = '...'                        # include file

There are three params:

include – will include given file, erroring out if it doesn't exist.
include_if_exists – same as include, but will not error out if the file is missing.
include_dir – will load all config files in given directory.

All 3 paths are (unless they start with /) relative to directory with postgresql.conf.

So, in my example, if my postgresql.conf is in /home/pgdba/data/postgresql.conf, defined include_dir clause will load config files from /home/pgdba/data/conf.d.

There are two rules when it comes to naming these config files:

filename has to have .conf extension
filename can't start with .

Order of the files is based on C locale rules:

numbers before letters
uppercase letters before lowercase letters

You can list files in order of reading using:

=$ LC_ALL=C ls -1
000.conf
BBB.conf
aaa.conf

On restart/reload PostgreSQL first reads postgresql.conf, and then all included files, in order. Last mention of the parameter wins.

But that's still not all.

Within Pg we can use:

$ ALTER SYSTEM SET param_name = 'value';

This stores information in special file, which can't be moved, named postgresql.auto.conf which is located within data directory.

This file shouldn't be modified using editor, just via calls to ALTER SYSTEM SET.

There is one thing though – just calling alter system set will not actually set the parameter. It will store the requested value in postgresql.auto.conf, but loading it will happen on next restart/reload.

Let's see:

$ SHOW log_statement;
 log_statement 
───────────────
 NONE
(1 ROW)
 
$ ALTER system SET log_statement = 'all';
ALTER SYSTEM
 
$ SHOW log_statement;
 log_statement 
───────────────
 NONE
(1 ROW)
 
$ SELECT pg_reload_conf();
 pg_reload_conf 
────────────────
 t
(1 ROW)
 
$ SHOW log_statement;
 log_statement 
───────────────
 ALL
(1 ROW)

Please note that not all parameters can be changed on reload – some can be set only when server is starting, and request restart of PostgreSQL to be active.

How do we know which parameters can be set when? That's simple:

$ SELECT name, context FROM pg_settings LIMIT 10;
              name               │  context
─────────────────────────────────┼────────────
 allow_system_table_mods         │ superuser
 application_name                │ USER
 archive_cleanup_command         │ sighup
 archive_command                 │ sighup
 archive_mode                    │ postmaster
 archive_timeout                 │ sighup
 array_nulls                     │ USER
 authentication_timeout          │ sighup
 autovacuum                      │ sighup
 autovacuum_analyze_scale_factor │ sighup
(10 ROWS)

Full explanation of all possible context values is in documentation, but generally speaking:

internal – can't really be changed
postmaster – require restart
sighup, superuser-backend, backend, superuser, and user – will get loaded on reload

Does it wrap all the ways you can set config? No. We still have some other ways.

We can change parameters for specific user, for specific database, or even, for specific user in specific database.

For example, in my DB, I have set, globally, log_min_duration_statement to 0, with this in place, I'll run:

$ ALTER USER depesz SET log_min_duration_statement = 10;
$ ALTER DATABASE depesz SET log_min_duration_statement = 20;
$ ALTER USER pgdba IN DATABASE pgdba SET log_min_duration_statement = 30;

and now, when I'll connect using various user to various databases, I'll get:

User:	Database:
User:	depesz	pgdba
test	20ms	0
depesz	10ms	10ms
pgdba	20ms	30ms

which tells us that the rules are, in order:

if there is a setting for current user in current database – it is used.
if there is a setting for current user – it is used.
if there is a setting for current database – it is used.
value from global config is used.

All these can be set for params in the superuser and user contexts.

Finally, you can also set the superuser/user parameters in you SQL connection using plain SET query, like:

$ SHOW log_min_duration_statement ;
 log_min_duration_statement 
────────────────────────────
 10ms
(1 ROW)
 
$ SET log_min_duration_statement = 100;
SET
 
$ SHOW log_min_duration_statement ;
 log_min_duration_statement 
────────────────────────────
 100ms
(1 ROW)

Of course, you have to be superuser to set parameters in the superuser context.

For all the mentioned ways to set parameters to some value via SQL, you can also remove the setting by using RESET variant, like:

ALTER SYSTEM RESET log_min_duration_statement;
ALTER USER … RESET log_min_duration_statement;
ALTER DATABASE … RESET log_min_duration_statement;
ALTER USER … IN DATABASE … RESET log_min_duration_statement;
RESET log_min_duration_statement;

This leaves one simple question – how do I know where the setting comes from? As previously we need to check pg_settings view, and specifically it's source column. Interesting values are:

configuration file – value comes from some config file. If you're superuser, columns sourcefile and sourceline show which file, and where in the file was it defined
user – value set by ALTER USER … SET
database – value set by ALTER DATABASE … SET
database user – value set by ALTER USER … IN DATABASE … SET
session – value set by SET …

One more interesting bit is column pending_restart in pg_settings view. If a row in the view has it set to true, it means that there will be change to the value on next restart.

This happens, generally, when you do alter system set for parameter that is only loaded on restart:

$ select name, short_desc from pg_settings where context = 'postmaster' order by 1;
name │ short_desc
─────────────────────────────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────
archive_mode │ Allows archiving of WAL files using archive_command.
autovacuum_freeze_max_age │ Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers │ Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_multixact_freeze_max_age │ Multixact age at which to autovacuum a table to prevent multixact wraparound.
bonjour │ Enables advertising the server via Bonjour.
bonjour_name │ Sets the Bonjour service name.
cluster_name │ Sets the name of the cluster, which is included in the process title.
config_file │ Sets the server's main configuration file.
data_directory │ Sets the server's data directory.
data_sync_retry │ Whether to continue running after a failure to sync data files.
dynamic_shared_memory_type │ Selects the dynamic shared memory implementation used.
event_source │ Sets the application name used to identify PostgreSQL messages in the event log.
external_pid_file │ Writes the postmaster PID to the specified file.
hba_file │ Sets the server's "hba" configuration file.
hot_standby │ Allows connections and queries during recovery.
huge_pages │ Use of huge pages on Linux or Windows.
huge_page_size │ The size of huge page that should be requested.
ident_file │ Sets the server's "ident" configuration file.
ignore_invalid_pages │ Continues recovery after an invalid pages failure.
jit_provider │ JIT provider to use.
listen_addresses │ Sets the host name or IP address(es) to listen to.
logging_collector │ Start a subprocess to capture stderr output and/or csvlogs into log files.
max_connections │ Sets the maximum number of concurrent connections.
max_files_per_process │ Sets the maximum number of simultaneously open files for each server process.
max_locks_per_transaction │ Sets the maximum number of locks per transaction.
max_logical_replication_workers │ Maximum number of logical replication worker processes.
max_pred_locks_per_transaction │ Sets the maximum number of predicate locks per transaction.
max_prepared_transactions │ Sets the maximum number of simultaneously prepared transactions.
max_replication_slots │ Sets the maximum number of simultaneously defined replication slots.
max_wal_senders │ Sets the maximum number of simultaneously running WAL sender processes.
max_worker_processes │ Maximum number of concurrent worker processes.
min_dynamic_shared_memory │ Amount of dynamic shared memory reserved at startup.
old_snapshot_threshold │ Time before a snapshot is too old to read pages changed after the snapshot was taken.
pg_stat_statements.max │ Sets the maximum number of statements tracked by pg_stat_statements.
port │ Sets the TCP port the server listens on.
recovery_target │ Set to "immediate" to end recovery as soon as a consistent state is reached.
recovery_target_action │ Sets the action to perform upon reaching the recovery target.
recovery_target_inclusive │ Sets whether to include or exclude transaction with recovery target.
recovery_target_lsn │ Sets the LSN of the write-ahead log location up to which recovery will proceed.
recovery_target_name │ Sets the named restore point up to which recovery will proceed.
recovery_target_time │ Sets the time stamp up to which recovery will proceed.
recovery_target_timeline │ Specifies the timeline to recover into.
recovery_target_xid │ Sets the transaction ID up to which recovery will proceed.
shared_buffers │ Sets the number of shared memory buffers used by the server.
shared_memory_type │ Selects the shared memory implementation used for the main shared memory region.
shared_preload_libraries │ Lists shared libraries to preload into server.
superuser_reserved_connections │ Sets the number of connection slots reserved for superusers.
track_activity_query_size │ Sets the size reserved for pg_stat_activity.query, in bytes.
track_commit_timestamp │ Collects transaction commit time.
unix_socket_directories │ Sets the directories where Unix-domain sockets will be created.
unix_socket_group │ Sets the owning group of the Unix-domain socket.
unix_socket_permissions │ Sets the access permissions of the Unix-domain socket.
wal_buffers │ Sets the number of disk-page buffers in shared memory for WAL.
wal_level │ Set the level of information written to the WAL.
wal_log_hints │ Writes full pages to WAL when first modified after a checkpoint, even for a non-critical modifications.
(55 rows)

And that's it. Hope you'll find it useful.

=$
|

Starting with Pg – where/how can I set configuration parameters?

One thought on “Starting with Pg – where/how can I set configuration parameters?”