How to syntax check PostgreSQL config files?

May 2024 ยท 2 minute read

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?

3 Answers

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 :)

3

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.

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.

3

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoaG9rY2iAcoWOoaawZaSkerTFza2YsWWTnbKkt4yppqysl6eytL3LZpqoppaetG6yyKWcrA%3D%3D