configuration - Query a parameter (postgresql.conf setting) like "max_connections"

Question

Does anyone know if it's even possible (and how, if yes) to query a database server setting in PostgreSQL (9.1)?

I need to check the max_connections (maximum number of open db connections) setting.

Answer 1

You can use SHOW:

SHOW max_connections;

This returns the currently effective setting. Be aware that it can differ from the setting in postgresql.conf as there are a multiple ways to set run-time parameters in PostgreSQL. To reset the "original" setting from postgresql.conf in your current session:

RESET max_connections;

However, not applicable to this particular setting. The manual:

This parameter can only be set at server start.

To see all settings:

SHOW ALL;

There is also pg_settings:

The view pg_settings provides access to run-time parameters of the server. It is essentially an alternative interface to the SHOW and SET commands. It also provides access to some facts about each parameter that are not directly available from SHOW, such as minimum and maximum values.

For your original request:

SELECT *
FROM   pg_settings
WHERE  name = 'max_connections';

Finally, there is current_setting(), which can be nested in DML statements:

SELECT current_setting('max_connections');

Related:

• How to test my ad-hoc SQL with parameters in Postgres query window