Mastering the pg_settings view in PostgreSQL

By /

Introduction

PostgreSQL, a powerful and highly customizable open-source relational database system, offers a wide range of tools to manage and optimize its configuration. One of the key resources for interacting with PostgreSQL’s settings is the pg_settings system catalog view. This view acts as a central interface for administrators and developers to access and modify database configuration parameters. Unlike the SHOW and SET commands, which allow for basic configuration management, pg_settings provides more granular details, such as the minimum and maximum allowable values for parameters, the source of the current settings, and whether changes require a database restart. This blog will explore the uses of the pg_settings system catalog view for understanding, viewing, and modifying the PostgreSQL configurations effectively.

Uses of pg_settings

pg_settings is ideal when you need:

  • A comprehensive view of current PostgreSQL configuration.
  • Metadata to understand the implications of specific parameters.
  • To troubleshoot or debug configuration issues.
  • To test parameter changes in a live session.
  • Insights into default settings, minimum/maximum values, and change contexts.

Step1: Understanding pg_settings

The pg_settings table provides a structured view of PostgreSQL configuration parameters. Let’s examine an example query:

postgres=# select * from pg_settings;
-[ RECORD 1 ]---+----------------------------------------
name            | allow_in_place_tablespaces
setting         | off
unit            | 
category        | Developer Options
short_desc      | Allows tablespaces directly inside pg_tblspc, for testing.
extra_desc      | 
context         | superuser
vartype         | bool
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | off
reset_val       | off
sourcefile      | 
sourceline      | 
pending_restart | f

Let’s breakdown the pg_settings’ output

  • name : The name of the configuration parameter.
  • setting : The current value of the parameter.
  • unit : The unit of the setting, if applicable.
  • category : The parameter’s category (e.g., Memory, Developer Options).
  • short_desc : A brief description of what the parameter does.
  • extra_desc : An extended description of the parameter.
  • context : Where and how the parameter can be modified (e.g., superuser, session).
  • vartype : The type of the value (e.g., integer, boolean).
  • source : The origin of the current value.
  • min_val and max_val : The allowed range for the setting, if applicable.
  • boot_val : The value used when PostgreSQL starts up.
  • reset_val : The value that will be used if the parameter is reset.
  • sourcefile and sourceline : The configuration file and line number defining the parameter.
  • pending_restart : Indicates if a restart is pending for the change to take effect.

A few columns that needs to be highlighted are:

CONTEXT: This parameter ascertains whether the parameter value change needs a RESTART or SIGHUP.
UNIT: You may see a difference between the SHOW output and the value seen on the pg_settings.unit – this is because of the measurement units.
SOURCE: As part of our standard database deployments, we follow the best practices of segregating the parameters into:
– parameters that needs restart
– parameters that needs reload
– specific extension related parameters
– custom parameters per instance
This approach helps us to to be more cautious while changing the parameters.

Step 2:Viewing a Specific Parameter

To view the details of a specific configuration parameter, use a SELECT query:

postgres=#  SELECT * FROM pg_settings WHERE name = 'log_statement';
-[ RECORD 1 ]---+------------------------------------
name            | log_statement
setting         | none
unit            | 
category        | Reporting and Logging / What to Log
short_desc      | Sets the type of statements logged.
extra_desc      | 
context         | superuser
vartype         | enum
source          | default
min_val         | 
max_val         | 
enumvals        | {none,ddl,mod,all}
boot_val        | none
reset_val       | none
sourcefile      | 
sourceline      | 
pending_restart | f

This query will return detailed information about the log_statement configuration, including its current value and the possible settings (none, ddl, mod, all).

Step 3:Modifying a Parameter Temporarily

To temporarily change a parameter, use the SET command:

postgres=#  SET log_statement TO mod;
SET

To verify that the change has taken place, run the same query again:

postgres=#  SELECT * FROM pg_settings WHERE name = 'log_statement';
-[ RECORD 1 ]---+------------------------------------
name            | log_statement
setting         | mod
unit            | 
category        | Reporting and Logging / What to Log
short_desc      | Sets the type of statements logged.
extra_desc      | 
context         | superuser
vartype         | enum
source          | session
min_val         | 
max_val         | 
enumvals        | {none,ddl,mod,all}
boot_val        | none
reset_val       | none
sourcefile      | 
sourceline      | 
pending_restart | f

The setting has been updated to mod, and the source column now reflects that the change was made for the current session.

Step 4:Direct Updates in pg_settings

While it might seem intuitive to directly update the pg_settings table, this approach is typically ineffective for permanent changes:

postgres=# UPDATE pg_settings SET setting = 'all' WHERE name = 'log_statement';
 set_config 
------------
 all
(1 row)

UPDATE 0

Verify the change using:

postgres=#  SELECT * FROM pg_settings WHERE name = 'log_statement';
-[ RECORD 1 ]---+------------------------------------
name            | log_statement
setting         | all
unit            | 
category        | Reporting and Logging / What to Log
short_desc      | Sets the type of statements logged.
extra_desc      | 
context         | superuser
vartype         | enum
source          | session
min_val         | 
max_val         | 
enumvals        | {none,ddl,mod,all}
boot_val        | none
reset_val       | none
sourcefile      | 
sourceline      | 
pending_restart | f

This confirms that the setting has been applied for the current session. However, as you will see in the next steps, this change will be lost once the server is restarted.

Step 5: Checking with SHOW Command

To check the current setting of log_statement after making the change and restarting the database server:

postgres=# show log_statement;
 log_statement 
---------------
 none
(1 row)

Notice: Even though you updated the pg_settings view, the setting is still showing as none when queried with SHOW. This is because changes in pg_settings are session-based and temporary, and the underlying configuration hasn’t been permanently updated.

In order to make permanent changes to any database configuration parameter(s), you could either update the postgresql.conf file directly with the appropriate value(s) set against the corresponding parameter(s), or use the Alter System Set … command.

Conclusion

The pg_settings view is a vital resource for PostgreSQL administrators and developers. It provides an in-depth view of the database’s configuration settings, enabling you to optimize performance, diagnose issues, and better understand the system’s behavior. By leveraging pg_settings, you can manage and fine-tune your PostgreSQL instance with enhanced precision and confidence. Furthermore, it empowers you to track and make informed changes to your configuration, ensuring the system operates at peak performance while meeting specific workload requirements. In essence, mastering pg_settings is key to maintaining a robust, efficient, and secure PostgreSQL environment.

Let us know how you handle postgresql.conf parameter changes by commenting below.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top