7

I am generating postgresql configuration files via ansible, and putting them in /etc/postgresql/XX/main/conf.d/whatever.conf. I accidentally made a syntax error and broke my postgresql, requiring manual fixing.

Is there any postgresql command to validate that a file is a valid postgresql.conf file?

sudoers files can be validates with /usr/sbin/visudo -cf path/to/file. Is there anything for postgresql?

I'm currently running Ubuntu 18.04 & 20.04, with PostgreSQL 10, 12 etc (yes several different versions).

3 Answers 3

7

Yes.

At least as of version 10 there is a mechanism to check the config file from within postgres.

This is the result of adding a fubar to my config file, didn't reload it.

postgres=# select sourcefile, name, sourceline, error from 
           pg_file_settings where error is not null;
               sourcefile                | name | sourceline |    error
-----------------------------------------+------+------------+--------------
 /etc/postgresql/12/main/postgresql.conf |      |         33 | syntax error
(1 row)

Docs will be: https://www.postgresql.org/docs/10/view-pg-file-settings.html, also, you might be interested in https://www.postgresql.org/docs/10/view-pg-hba-file-rules.html

5
  • 4
    But you have to start PG for this.. Commented Jan 17, 2022 at 16:18
  • Oh, great, I have learned something new! Commented Jan 18, 2022 at 11:31
  • @StanislavBashkyrtsev If it is not running, then start it. If it starts, the file is valid. I think the problem he wants to solve is not shutting down the running server until he knows it will be able to start again. If it is already down, that isn't much of an issue.
    – jjanes
    Commented Jan 18, 2022 at 17:08
  • In my case it is not running and I am trying to discover why. Commented Nov 8, 2022 at 8:57
  • Could it be possible to check why the settings was not applied? /dbdata/postgres/postgresql.conf | 820 | 20 | archive_mode | off | f | setting could not be applied Commented Jan 17 at 17:21
5

The only program that I know is postgres, the database server.

I have two approaches to deal with that problem:

  • After you edit postgresql.conf and reload, examine the PostgreSQL log file. If there was a problem, you will see that in the log, an the server will keep running with the previous configuration (no outage). Fix the file and try again until it the reload is successful.

  • Use ALTER SYSTEM rather than postgresql.conf to change configuration parameters. That works just as well, and ALTER SYSTEM will catch most of the possible syntax errors. Of course, you should still look at the PostgreSQL log after reloading.

As an alternative to checking the log file, you may use the pg_file_settings view as described in the other answer.

3
  • "examine the PostgreSQL log file" - the irony is that I was trying to enable logging and instead pgsql is not starting at all. Commented Nov 8, 2022 at 8:56
  • Yes, but it is producing errors that explain why it refuses to start. Commented Nov 8, 2022 at 8:58
  • Maybe, but I have no idea where it logs this problems. Commented Nov 8, 2022 at 9:00
3

With the command postgres (see man postgres for details) you can query a config parameter and if something in one of the config files is invalid, it shows an error.

Example Postgresql version 12 on Debian as user postgres:

$ /usr/lib/postgresql/12/bin/postgres --config-file=/etc/postgresql/12/main/postgresql.conf -C data_directory
/var/lib/postgresql/13/main
$ echo $?
0

now with an error in the config:

$ /usr/lib/postgresql/12/bin/postgres --config-file=/etc/postgresql/12/main/postgresql.conf -C data_directory
2023-03-31 11:15:22.550 [5041] LOG:  unrecognized configuration parameter "unknown" in file "/etc/postgresql/12/main/conf.d/example_error.conf" line 2
2023-03-31 11:15:22.550 [5041] FATAL:  configuration file "/etc/postgresql/12/main/conf.d/example_error.conf" contains errors
$ echo $?
1
1
  • Best way if you want to automate things.
    – Larsen
    Commented Aug 17, 2023 at 9:53

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.