Infrastructure at your Service

Daniel Westermann

Which parameter changes do require a restart of my PostgreSQL instance?

Starting wiht PostgreSQL 9.4 you can change parameters in postgresql.conf with the “alter system” command in psql. In principle every parameter can be changed that way but not every parameter is applied immediately. Some of the parameters require a reload others require a complete restart of the instance. But which of these parameter require what? How can you know which method you have to use for which parameter?

The first thing to know is that parameter changes you trigger by using “alter system” are not written to postgresql.conf which is the parameter file for the instance. Instead parameter changes are written to postgresql.auto.conf. Lets do a simple example by changing the work_mem parameter:

([email protected][local]:5432) [postgres] > show work_mem;
 work_mem 
----------
 32MB
(1 row)

Time: 0.204 ms
([email protected][local]:5432) [postgres] > alter system set work_mem='16MB';
ALTER SYSTEM
Time: 320.400 ms

The initial setting was 32MB and I changed that to 16MB. Does it go into effect immediately?

([email protected][local]:5432) [postgres] > show work_mem;
 work_mem 
----------
 32MB
(1 row)

No. Before looking at how we can make this parameter change going live lets look at what was written to posstgresql.auto.conf:

[email protected]:/u02/pgdata/PG1/ [PG1] cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
work_mem = '16MB'

So we have the new setting in the postgresql.auto.conf file. What about postgresql.conf?

[email protected]:/u02/pgdata/PG1/ [PG1] grep work_mem postgresql.conf 
work_mem=32MB
maintenance_work_mem=64MB

We still see the old value there and this never gets changed. What does that mean? When the server starts it reads postgresql.conf and in addition the values from postgresql.auto.conf are applied and the values for parameters in postgresql.conf are overwritten/replaced by the values for the parameters in postgresql.auto.conf. This is important to undertand. If you change the value of a parameter in postgresql.conf but you have the same parameter specified in postgresql.auto.conf you will never succeed:

[email protected]:/u02/pgdata/PG1/ [PG1] sed -i 's/work_mem=32MB/work_mem=8MB/g' postgresql.conf 
[email protected]:/u02/pgdata/PG1/ [PG1] grep work_mem postgresql.conf 
work_mem=8MB
maintenance_work_mem=64MB

I changed the parameter in postgresql.conf to 8MB and then I’ll restart the instance and check the value of the parameter:

[email protected]:/u02/pgdata/PG1/ [PG1] pg_ctl -D /u02/pgdata/PG1/ restart -m fast
[email protected]:/u02/pgdata/PG1/ [PG1] psql
([email protected][local]:5432) [postgres] > show work_mem;
 work_mem 
----------
 16MB
(1 row)

There is a value of 16MB now and not 8MB what I did write to the postgresql.conf file. Conclusion: Parameters set in postgresql.conf are overwritten if the same parameter is set in postgresql.auto.conf.

Back to the original question: How can I know what action a parameter change requires to go into effect? The answer is pg_settings:

([email protected][local]:5432) [postgres] > \d pg_settings
     View "pg_catalog.pg_settings"
     Column      |  Type   | Modifiers 
-----------------+---------+-----------
 name            | text    | 
 setting         | text    | 
 unit            | text    | 
 category        | text    | 
 short_desc      | text    | 
 extra_desc      | text    | 
 context         | text    | 
 vartype         | text    | 
 source          | text    | 
 min_val         | text    | 
 max_val         | text    | 
 enumvals        | text[]  | 
 boot_val        | text    | 
 reset_val       | text    | 
 sourcefile      | text    | 
 sourceline      | integer | 
 pending_restart | boolean | 

The important column is the “context” column. By knowing the context you know what you need to do:

([email protected][local]:5432) [postgres] > select context from pg_settings where name = 'work_mem';
 context 
---------
 user
(1 row)

In this case it means that every user can change the setting for the local session. Do we need to restart if we change it globally?

([email protected][local]:5432) [postgres] > alter system set work_mem='12MB';
ALTER SYSTEM
Time: 316.481 ms
([email protected][local]:5432) [postgres] > select pending_restart from pg_settings where name = 'work_mem';
 pending_restart 
-----------------
 f
(1 row)

No. So a reload should be fine:

([email protected][local]:5432) [postgres] > select * from pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Time: 4.158 ms
([email protected][local]:5432) [postgres] > show work_mem;
 work_mem 
----------
 12MB
(1 row)

Now we have it applied and we didn’t need to restart. What about changing parameters that reuqire a restart?

([email protected][local]:5432) [postgres] > show port;
 port 
------
 5432
(1 row)

Time: 0.177 ms
([email protected][local]:5432) [postgres] > alter system set port=5555;
ALTER SYSTEM
Time: 311.963 ms
([email protected][local]:5432) [postgres] > select pending_restart from pg_settings where name = 'port';
 pending_restart 
-----------------
 f
(1 row)

Hm. Changing the port for sure requires a restart. Why does pg_settings tell us that we do not need to restart? Lets try a reload and see what happens:

([email protected][local]:5432) [postgres] > select * from pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

When you look at the log file for the last action you’ll notice this:

2016-06-10 08:11:23.987 CEST - 4 - 3111 -  - @ LOG:  received SIGHUP, reloading configuration files
2016-06-10 08:11:23.988 CEST - 5 - 3111 -  - @ LOG:  parameter "port" cannot be changed without restarting the serve

So at least here we can see that we need to restart for our parameter to be applied. What does pg_settings now show?

([email protected][local]:5432) [postgres] > select pending_restart from pg_settings where name = 'port';
 pending_restart 
-----------------
 t
(1 row)

Now it shows true. This means you first need to reload PostgreSQL for the pending_restart column being updated (This is also true for the work_mem test above. We have just been lucky in that case). Conclusion: For whatever parameter you change do a reload afterwards and then check pg_settings if you need to additionally restart the instance.

7 Comments

  • sergei says:

    Hello! Thank you for the article. I am new in database administration, so it was really helpful knowledge for me!
    But what about autovacuum on/off parameter, pg_settings show me the ‘sighup’ context:
    postgres=# select context from pg_settings where name like ‘autovacuum’;
    context
    ———
    sighup
    (1 row)

    I assume that after modifying this parameter I need to restart my postgres. (Am I right?)

  • Casey says:

    As per Sergei, a much simpler way is as follows:

    select name, setting, short_desc from pg_settings where context not in (‘postmaster’, ‘internal’) and name == MY_SETTING_TO_CHECK ORDER BY context

    replace MY_SETTING_TO_CHECK with the setting you’re concerned with.

    If this SQL returns a row then you can safely reload.

    If not, you need to restart.

  • Casey says:

    Or, if you want a simple true/false:

    For example, if concerned with effective_cache_size

    select ‘effective_cache_size’ in (select name from pg_settings where context in (‘postmaster’, ‘internal’)) as requires_restart;

  • dk.jessn says:

    Does changes of both log_statement and log_min_duration_statement require a restart?
    The instance is running as an AWS RDS managed instance.

  • A simple way to see which parameters are static, is like that :

    select name, boot_val
    from pg_settings
    where context = ‘postmaster’;

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure