Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 407
Greg Kramida Avatar asked Oct 12 '22 14:10

Greg Kramida


People also ask

What is Max_connections in Postgres?

Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb).

What is PostgreSQL Shared_buffers parameter used for?

The shared_buffers parameter determines how much memory is dedicated to the server for caching data. The value should be set to 15% to 25% of the machine's total RAM. For example: if your machine's RAM size is 32 GB, then the recommended value for shared_buffers is 8 GB.


1 Answers

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
like image 320
Erwin Brandstetter Avatar answered Oct 14 '22 02:10

Erwin Brandstetter