Can't get new postgres config file settings to take effect

Question

I have a somewhat large table in my database and I am inserting new records to it. As the number of records grow, I started having issues and can't insert.

My postgresql log files suggest I increase WAL size:

[700] LOG: checkpoints are occurring too frequently (6 seconds apart)
[700] HINT: Consider increasing the configuration parameter "max_wal_size".

I got the path to my config file with =# show config_file; and made some modifications with vim:

max_wal_senders = 0
wal_level = minimal
max_wal_size = 4GB

When I check the file I see the changes I made.
I then tried reloading and restarting the database: (I get the data directory with =# show data_directory ;)

I tried reload:

pg_ctl reload -D path
server signaled

I tried restart

pg_ctl restart -D path
waiting for server to shut down.... done
server stopped
waiting for server to start....
2020-01-17 13:08:19.063 EST [16913] LOG:  listening on IPv4 address 
2020-01-17 13:08:19.063 EST [16913] LOG:  listening on IPv6 address 
2020-01-17 13:08:19.079 EST [16913] LOG:  listening on Unix socket 
2020-01-17 13:08:19.117 EST [16914] LOG:  database system was shut down at 2020-01-17 13:08:18 EST
2020-01-17 13:08:19.126 EST [16913] LOG:  database system is ready to accept connections
 done
server started

But when I connect to the database and check for my settings:

      name       | setting | unit |           category            |                               short_desc                                | extra_desc |  context   | vartype | source  | min_val |  max_val   |         enumvals          | boot_val | reset_val | sourcefile | sourceline | pending_restart
-----------------+---------+------+-------------------------------+-------------------------------------------------------------------------+------------+------------+---------+---------+---------+------------+---------------------------+----------+-----------+------------+------------+-----------------
 max_wal_senders | 10      |      | Replication / Sending Servers | Sets the maximum number of simultaneously running WAL sender processes. |            | postmaster | integer | default | 0       | 262143     |                           | 10       | 10        |            |            | f
 max_wal_size    | 1024    | MB   | Write-Ahead Log / Checkpoints | Sets the WAL size that triggers a checkpoint.                           |            | sighup     | integer | default | 2       | 2147483647 |                           | 1024     | 1024      |            |            | f
 wal_level       | replica |      | Write-Ahead Log / Settings    | Set the level of information written to the WAL.                        |            | postmaster | enum    | default |         |            | {minimal,replica,logical} | replica  | replica   |            |            | f
(3 rows)

I still see the old default settings.
What am I missing here? How can I get these settings to take effect?

What version are you running? Can you share SELECT * FROM pg_settings? I wonder if ALTER SYSTEM may have got you... — richyen
– richyen, Commented Jan 17, 2020 at 18:51
The config file is not necessarily located in the data directory. What does show config_file; give you? — user330315
– user330315, Commented Jan 17, 2020 at 19:52
@richyen My version is 10.9 (Ubuntu 10.9-0ubuntu0.18.04.1). I updated the post to add SELECT * FROM pg_settings. — Lou Salome
– Lou Salome, Commented Jan 17, 2020 at 22:47
@ a_horse_with_no_name SHOW config_file; gives me /etc/postgresql/10/main/postgresql.conf — Lou Salome
– Lou Salome, Commented Jan 17, 2020 at 22:47

Laurenz Albe · Accepted Answer · 2020-01-18 08:41:12Z

7

Configuration settings can come from several sources:

postgresql.conf
postgresql.auto.conf (set with ALTER SYSTEM)
command line arguments at server start
set with ALTER DATABASE or ALTER USER

Moreover, if a parameter occurs twice in a configuration file, the second entry wins.

To figure out from where in this mess your setting originates, run

SELECT name, source, sourcefile, sourceline, pending_restart
FROM pg_settings
WHERE name IN ('wal_level', 'max_wal_size', 'max_wal_senders');

If the source is database or user, you can user the psql command \drds to figure out details.

The result of the queries shows that your PostgreSQL has been modified or built so that these values are the default values.

You'd have to override these defaults with any of the methods shown above.

edited Jan 18, 2020 at 8:41

answered Jan 17, 2020 at 19:44

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lou Salome Over a year ago

hmm so I only see default for all the sources. Please see the updated the post -- now includes all columns from g_settings for the parameters of interest.

Laurenz Albe Over a year ago

I have amended the answer.

Lou Salome Over a year ago

Thanks again for the answer. I guess I 'll try the suggested methods. But I am still having trouble understanding one thing: isn't SHOW config_file; giving me the default config file in this case? Why shouldn't I expect changing parameters in this file would also change the default settings?

A A · Accepted Answer · 2022-08-13 13:49:19Z

0

Locations of config files. Ordered by priority.

/var/lib/postgresql/12/main/postgresql.auto.conf
/etc/postgresql/12/main/postgresql.conf

answered Aug 13, 2022 at 13:49

A A

2,3801 gold badge32 silver badges36 bronze badges

Collectives™ on Stack Overflow

Can't get new postgres config file settings to take effect

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related