How to syntax check PostgreSQL config files?

Question

I am often editing the pg_hda.conf file and I was just wondering if there is a way to make sure that what I just wrote is correct.

So far I'm using a test server to check my changes.

Like Apache has its apache2ctl -t command, does Postgres has something similar?

score 18 · Answer 1 · edited Dec 28 '21 at 08:44

18

It's bee a long time, but now check is available for pg_hba.conf file:

select pg_hba_file_rules();

Also, you may use pg_file_settings view to define error in postgresql.conf:

select sourcefile, name,sourceline,error from pg_file_settings where error is not null;

example output:

postgres=# select sourcefile, name,sourceline,error from pg_file_settings where error is not null;
               sourcefile               |  name  | sourceline |                error
----------------------------------------+--------+------------+--------------------------------------
 /var/lib/pgsql/11/data/postgresql.conf | lalala |          1 | unrecognized configuration parameter

here I put some bad stuff to check if it's true :)

edited Dec 28 '21 at 08:44

Martin von Wittich

350
1
5
19

answered Dec 20 '19 at 16:30

Mikhail Aksenov

284
2
5

This is is very interesting. I will have a look as soon as I find a job! – SamK Nov 17 '20 at 18:07
The second option worked great for me in checking postgresql.conf. Thanks! – Digital Impermanence Sep 10 '21 at 13:25
This also reports errors for parameters whose values have changed, and which would be valid, but which cannot be applied without a server restart. So not every reported error would prevent a server restart. – qris May 27 '22 at 09:18

score 15 · Accepted Answer · answered May 02 '13 at 14:22

There is no way to do this that is similar to apache2ctl. If you reload the configuration files and there is a syntax error, the PostgreSQL server will complain in the log and refuse to load the new file. So there is very little risk of messing something up by making a syntax typo. (Of course, this won't guard you against writing semantically wrong things, but apache2ctl won't do that either.) Other than that, it is probably a good idea to test changes in a test server, and have a system that propagates those changes to production in a controlled way.

score 6 · Answer 3 · answered Apr 19 '19 at 08:22

6

refer to: https://dba.stackexchange.com/a/151457/177071

you have another approach to test if the config file is correct.

into the command line, type select pg_reload_conf();

postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

This indicates your file is correct. otherwise it would fail.

answered Apr 19 '19 at 08:22

Siwei

19,858
7
75
95

9

It is not true. After reload conf with "t" in result I have this line the postgres log: [9692]LOG: configuration file "/var/lib/pgsql/data/postgresql.conf" contains errors; unaffected changes were applied – DShost Aug 28 '19 at 15:46
1

@DShost , it may be restart required config changes. ``` select sourcefile, name, sourceline, error from pg_file_settings where error is not null; ``` – Keith Brings Jan 18 '23 at 01:15

How to syntax check PostgreSQL config files?

3 Answers3