Even the most seasoned DBA sometimes forgets if a setting change needs a restart or a reload. So, how do we check, in a PostgreSQL database if I can reload or restart a setting?

Config File

The quickest way is to look at the default configuration file for the version of PostgreSQL that you’re running. The default file is very well commented, the most notable comment in the file will let you know that “(change requires restart)”. See the following excerpt from postgresql.conf with the associated comments:

listen_addresses = '*'      # what IP address(es) to listen on;
          # comma-separated list of addresses;
          # defaults to 'localhost'; use '*' for all
          # (change requires restart)
port = 5436       # (change requires restart)
max_connections = 100     # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)

If you’re new to postgres or really need a super-fast way of determining if you have to restart or not, this is the quickest way.

Catalogs

There’s actually more than one way to ‘set a setting’ in postgres. The config file method (above) shows us if we need a restart or not. I’d actually like to know a bit more:

  • Can I set this per-user
  • Can I set this per-database
  • Do I need to modify this globally
  • etc…

How can we tell? Let’s look in the database at the pg_catalog.pg_settings catalog view. (This is from my 9.5.2 instance).

[scottsmac@[local]:9520]
postgres@postgres=# \d+ pg_catalog.pg_settings
 View "pg_catalog.pg_settings"
 Column | Type | Modifiers | Storage | Description
-----------------+---------+-----------+----------+-------------
 name | text | | extended |
 setting | text | | extended |
 unit | text | | extended |
 category | text | | extended |
 short_desc | text | | extended |
 extra_desc | text | | extended |
 context | text | | extended |
 vartype | text | | extended |
source | text | | extended |
 min_val | text | | extended |
 max_val | text | | extended |
 enumvals | text[] | | extended |
 boot_val | text | | extended |
 reset_val | text | | extended |
 sourcefile | text | | extended |
 sourceline | integer | | plain |
 pending_restart | boolean | | plain |
View definition:
 SELECT a.name,
 a.setting,
 a.unit,
 a.category,
 a.short_desc,
 a.extra_desc,
 a.context,
 a.vartype,
 a.source,
 a.min_val,
 a.max_val,
 a.enumvals,
 a.boot_val,
 a.reset_val,
 a.sourcefile,
 a.sourceline,
 a.pending_restart
 FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline, pending_restart);
Rules:
 pg_settings_n AS
    ON UPDATE TO pg_settings DO INSTEAD NOTHING
 pg_settings_u AS
    ON UPDATE TO pg_settings
   WHERE new.name = old.name DO  SELECT set_config(old.name, new.setting, false) AS set_config
[scottsmac@[local]:9520]
postgres@postgres=#

This catalog view gives us quite a bit of information about each setting in postgres.

Aside: If you’re grokkin’ (or hatin’ on ) my psql prompt. Check it out here

So, how do we use this view, in the context of ‘do I need a restart, etc…’

[scottsmac@[local]:9520]
postgres@postgres=# select name, context, setting::varchar(25) from pg_settings;
 name | context | setting
-------------------------------------+-------------------+---------------------------
 allow_system_table_mods | postmaster | off
 application_name | user | psql
 archive_command | sighup | (disabled)
 archive_mode | postmaster | off
 archive_timeout | sighup | 0
 array_nulls | user | on
 authentication_timeout | sighup | 60
 autovacuum | sighup | on
 autovacuum_analyze_scale_factor | sighup | 0.1
 autovacuum_analyze_threshold | sighup | 50
 autovacuum_freeze_max_age | postmaster | 200000000
 autovacuum_max_workers | postmaster | 3
 autovacuum_multixact_freeze_max_age | postmaster | 400000000
 autovacuum_naptime | sighup | 60
 autovacuum_vacuum_cost_delay | sighup | 20
 autovacuum_vacuum_cost_limit | sighup | -1
 autovacuum_vacuum_scale_factor | sighup | 0.2
 autovacuum_vacuum_threshold | sighup | 50
 autovacuum_work_mem | sighup | -1
 backslash_quote | user | safe_encoding
 bgwriter_delay | sighup | 200
 bgwriter_lru_maxpages | sighup | 100
 bgwriter_lru_multiplier | sighup | 2
 block_size | internal | 8192
 etc.....

There’s actually 247 rows that get spit out as part of that query (I’ve cut it to this list to give you a cross section).

The context column is what we’re after here. This column tells us “in what context this setting can be applied”. Let’s take a look at what contexts we have.

SELECT context,
         count(context) contextcount
FROM pg_settings
GROUP BY context
ORDER BY  contextcount DESC;
[scottsmac@[local]:9520]
postgres@postgres=# SELECT context,
postgres-#          count(context) contextcount
postgres-# FROM pg_settings
postgres-# GROUP BY context
postgres-# ORDER BY  contextcount DESC;
      context      | contextcount
-------------------+--------------
 user              |           96
 sighup            |           55
 postmaster        |           47
 superuser         |           30
 internal          |           15
 backend           |            2
 superuser-backend |            2
(7 rows)
[scottsmac@[local]:9520]
postgres@postgres=#

So, what do each of these mean?

user

This means that the configuration can be set by any one of:

  • In your psql session
[scottsmac@[local]:9520]
postgres@postgres=# set work_mem='400MB';
SET

NB: This will only last while you’re logged in.  You can also ‘reset work_mem’

  • ALTER USER [username] set [setting] = [value]
[scottsmac@[local]:9520]
postgres@postgres=# alter user postgres set work_mem = '400MB';
ALTER ROLE

NB: Each time the user logs in, this setting will be applied

  • ALTER DATABASE [dbname] set [setting = [value]
[scottsmac@[local]:9520]
postgres@postgres=# alter database postgres set work_mem = '400MB';
ALTER DATABASE

NB: Whenever someone logs in to this database, they’ll inherit that value

NB: If you set a value at both the user and database layer, the ‘user’ setting wins.

  • Config file
  • As always, you can also set this globally by modifying the config file

sighup

This means that you can reload postgres after changing the setting

postmaster

This means that you need to restart the database instance / cluster after making the change

superuser

You can set these in the config file. If you are a database superuser, you can set them in your session with ‘set’ command

internal

These are settings that are quite a bit harder to change. These are typically things that are

  • set in source code and compiled
  • configured at initdb time
    Either way, you cannot change these settings for the instance you’re using. You would need to either recompile or re-initdb. And then, you would need to dump / restore your data. These settings ‘blocksize’ for example will completely change the way that the server accesses data.

backend

These are settings that you can change in a config file, but, cannot be modified for a live session. (You can also set some of these on your connection request). Existing sessions need to drop and reconnect in order to pickup the change.

superuser-backend

Same as backend above, but, only applies for users with the ‘superuser’ flag.

See https://www.postgresql.org/docs/current/static/view-pg-settings.html For more details.

Testing setting contexts

From time to time, it’s important to see exactly how a setting behaves. This perl program will connect to a database and run forever (ctrl+c) to exit. You’ll need ‘DBD::Pg’ and ‘DBI’ installed.

Aside: Installing DBD::Pg is a bit out of scope, but, you can try:

perl -MCPAN -e 'install DBI'
perl -MCPAN -e 'install DBD::Pg'

If you have permissions issues, try that as root or with ‘sudo’…

Now, You can start this program. While it’s running, go ahead and change a setting and reload, the check runs every 2 seconds and will print the current value of ‘work_mem’ (change line 7 for the setting you care about).

#!/usr/bin/perl
use POSIX;
use DBI;
my $dbh = DBI->connect("dbi:Pg:host=127.0.0.1 port=5432 dbname=mydb",'user','mypass');
my $sth=$dbh->prepare('show work_mem');
print "Checking state of 'port'\n\n";
while ( true )
{
    $sth->execute();
    while ( @row=$sth->fetchrow_array())
    {
        foreach (@row)
        {
            print $_;
        }
        print "\n";
    }
    sleep 2;
}

Happy PostgreSQL-ing!