11

Many times, the postgresql service can't restart after some configuration changes.

Is there any command line tool allowing to check the syntax of pg_hba.conf and other pg *.conf files before reloading/restarting the service, or even better, after any config change ?

2 Answers 2

15

You can run:

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

postgres=# 

.. from within psql. or kill -HUP the postmaster process.

Any config validation errors will then get put into the Postgres log file, and it won't reload the config. Example log:

2016-10-05 10:31:57 BST LOG:  received SIGHUP, reloading configuration files
2016-10-05 10:31:57 BST LOG:  invalid connection type "awdawdawdawd"
2016-10-05 10:31:57 BST CONTEXT:  line 1 of configuration file "/etc/postgresql/9.3/main/pg_hba.conf"
2016-10-05 10:31:57 BST WARNING:  pg_hba.conf not reloaded

I'm not aware of a tool that just checks config for validity, but there was a thread on the pgsql-hackers mailing list here that discussed a proposal for a validator.

2
  • Thank you @Phil, and even more for the pgsql-hackers mailing list link ;-)
    – Rémi B.
    Commented Oct 5, 2016 at 14:10
  • 2
    It is not true. After reload conf with "t" in result I have this line in the postgres log: [9692]LOG: configuration file "/var/lib/pgsql/data/postgresql.conf" contains errors; unaffected changes were applied
    – DShost
    Commented Aug 28, 2019 at 15:53
0

pg_reload_conf() (v14 documentation, also see documentation on pgPedia) tells you nothing about whether the configuration is valid. Rather, it just sends a SIGHUP to the Postgres process, causing it to reload the configuration. The return value of pg_reload_conf() merely tells you if this signalling attempt was successful, not whether the config is valid. If the configuration is bad, Postgres will still load it, but only the valid lines will take effect.

Given this, there is no reason to use pg_reload_conf() over the preferred method of reloading the service in Debian/Ubuntu, which is to do so via SystemD: systemctl reload postgresql. Both that and pg_reload_conf() perform the same task.

To check the validity of the configuration files, there are a few views:

Select all records from one of these views to see the validity of each line in each config file that is parsed. Refer to the documentation linked above to see what info is provided by each view.

In particular:

  • For pg_file_settings, there is a column applied which is true iff the config line is valid. Thus:

    • to see whether the entire config is valid: SELECT bool_and(applied) FROM pg_file_settings;
    • to see which lines are invalid and why: SELECT * FROM pg_file_settings WHERE NOT applied;
  • For pg_hba_file_rules, there is a column error which is NULL iff the config line is valid. Thus:

    • to see whether all the HBA rules are valid: SELECT bool_and(error IS NULL) FROM pg_hba_file_rules;

    • to see which lines are invalid and why: SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.