postgresqlwal

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

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?

Solution

  • Configuration settings can come from several sources:

    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.