• Ashiwini
• 23 Jul, 2024
• 0 Comments
• 6 Mins Read

Documentation of Postgresql.conf file

PostgreSQL Parameter Contexts (GUCs) – Explained with Examples

PostgreSQL provides a powerful configuration system where parameters (also known as GUCs – Grand Unified Configuration) control various aspects of the database. But not all parameters can be changed in the same way.

Each parameter belongs to a context, which defines when and how it can be modified:

• Some require a server restart

• Some just need a configuration reload

• Some apply per session

• Some can only be set by a superuser

You can view parameter contexts in PostgreSQL using the system view pg_settings:

Output:

Meaning of context values in PostgreSQL 

• internal → Cannot be changed (compile-time fixed). 

• postmaster → Needs server restart (like max_connections, shared_buffers). 

• sighup → Needs config reload (pg_reload_conf() or SELECT pg_reload_conf();). 

• backend → Applied at session start, requires reconnect (like client_encoding). 

• user → Can be changed at session, user, or database level (like work_mem). 

🔹 Quick Demo: Checking Context of a Parameter 

You can check the context of any parameter from pg_settings: 

SELECT name, context, vartype, setting 

FROM pg_settings 

WHERE name IN ('max_connections','work_mem','log_min_duration_statement'); 

🔹 Types of Parameter Contexts in PostgreSQL 

1. internal 

• Parameters that are set internally by PostgreSQL. 

• Cannot be modified by users. 

• Example:
  

  SHOW block_size; 

Output might be 8192 (8 KB). This is fixed at compile time. 

1. postmaster 

• Requires a server restart to take effect. 

• Set in postgresql.conf or via ALTER SYSTEM. 

• Example:
  

  SHOW max_connections; 

If you change it: 

ALTER SYSTEM SET max_connections = 300; 

🔄 You need to restart PostgreSQL. 

1. sighup 

• Can be reloaded without restart (like ALTER SYSTEM RELOAD in Oracle). 

• Use:
  

  SELECT pg_reload_conf(); 

• Examples: archive_mode, archive_command, log_directory, work_mem. 

SHOW work_mem; 

SET work_mem = '64MB';  -- applies only for current session 

But if you want to persist globally, you edit postgresql.conf and then: 

SELECT pg_reload_conf(); 

1. backend 

• Parameter takes effect only when a new backend (new session/connection) starts. 

• You need to disconnect and reconnect. 

• Example:
  

  SET search_path TO myschema; 

But default_transaction_isolation is a backend parameter: 

ALTER SYSTEM SET default_transaction_isolation = 'serializable'; 

✅ Requires reconnect. 

1. superuser-backend 

• Similar to backend, but only superusers can set it. 

• Example:
  

  ALTER SYSTEM SET vacuum_cost_delay = 50;

1. superuser 

• Can be changed at session level using SET, but only by a superuser. 

• Example:
  

  SET session_replication_role = replica; 

1. user 

• Can be changed freely by any user at session or transaction level. 

• Example:
  

  SET work_mem = '64MB'; 
  SHOW work_mem; 

You can change work_mem at the session level in PostgreSQL because its context is user. 

Change work_mem for the current session 

SET work_mem = '64MB'; 

• This affects only the current session (your connection). 

• Once you disconnect, it goes back to the default value. 

🔹 Verify the change 

SHOW work_mem; 

Output: 

 work_mem 

———- 

 64MB 

🔹 Example 

postgres=# SHOW work_mem; 

 work_mem 

———- 

 4MB 

postgres=# SET work_mem = '64MB'; 

SET postgres=# SHOW work_mem; 

 work_mem 

———- 

 64MB 

⚡ Note:
If you want this to apply permanently at: 

• Database level:
  

  ALTER DATABASE mydb SET work_mem = '64MB'; 

• User level:
  

  ALTER USER u2 SET work_mem = '64MB'; 

But session level is just with SET. 

postgresql.conf is the primary configuration file for PostgreSQL, used to set various parameters that control the behavior of the PostgreSQL database server. This file includes settings for performance tuning, authentication, logging, and other operational aspects of the database.

Location of postgresql.conf file –

The location of the postgresql.conf file can vary depending on the operating system and the installation method. Common locations include:

• /etc/postgresql/<version>/main/postgresql.conf (Debian/Ubuntu)
• /var/lib/pgsql/<version>/data/postgresql.conf (CentOS/Red Hat)
• /usr/local/pgsql/data/postgresql.conf (custom source installations)

Key Configuration Parameters

Here are some key parameters commonly found in postgresql.conf:

1) Connection Settings

listen_addresses:

Specifies the IP addresses on which the server listens for connections. Default is 'localhost'.

listen_addresses = 'localhost'

port:

The port number on which the server listens for connections. Default is 5432

port = 5432

max_connections:

The maximum number of concurrent connections to the database server

max_connections = 100

2) Resource Management

shared_buffers:

The amount of memory the database server uses for shared memory buffers.

shared_buffers = 128MB

work_mem:

The amount of memory used by internal sort operations and hash tables before writing to temporary disk files.

work_mem = 4MB

maintenance_work_mem:

specifies the maximum amount of memory that can be used by maintenance operations such as VACUUM, CREATE INDEX, ALTER TABLE, and CLUSTER.

maintenance_work_mem = 64MB

3) Performance Tuning

effective_cache_size:

An estimate of the memory available for disk caching by the operating system and within the database itself.

effective_cache_size = 4GB

random_page_cost:It estimates the cost of a non-sequential page fetch from disk. This cost influences the query planner’s decision-making process regarding whether to use an index scan or a sequential scan.

random_page_cost = 4.0

4) Logging

logging_collector: Enables logging to files. Default is off.

logging_collector = on

log_directory: Directory where log files will be stored.

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

wal_level = replica

checkpoint_timeout: specifies the maximum time between automatic WAL checkpoints

checkpoint_timeout = 5min

max_wal_size: defines the maximum size that the Write-Ahead Log (WAL) can grow to between automatic checkpoints.

max_wal_size = 1GB

6) Replication

max_wal_senders: specifies the maximum number of concurrent connections that can be used for streaming replication.

max_wal_senders = 10

wal_keep_segments:specifies the number of WAL (Write-Ahead Logging) segment files that the server should keep in the pg_wal directory for replication and recovery purposes.

wal_keep_segments = 64

primary_conninfo = 'host=primary_host port=5432 user=replication_user password=replication_password'

postgresql.auto.conf 

`postgresql.auto.conf` is a supplementary configuration file that is automatically managed by PostgreSQL. It is used to store configuration changes made via the `ALTER SYSTEM` command. The settings in `postgresql.auto.conf` override those in `postgresql.conf`, allowing for dynamic changes without directly editing the main configuration file. This file ensures that changes made via `ALTER SYSTEM` are persistent across server restarts. 

Changing Parameters 

Example:

1)  Editing `postgresql.conf`
1) Locate and Open postgresql.conf:
This file is usually in the PostgreSQL data directory. You can find it by checking the data_directory setting in your PostgreSQLconfiguration.

2) Modify a Parameter:
For example, to change the shared_buffers setting to 512MB, you would add or modify this line in postgresql.conf:

shared_buffers = 512MB

3) Save the File:
After making your changes, save the postgresql.conf file.

4) Using the command line:

pg_ctl reload -D /path/to/data_directory

Or from within the database:

SELECT pg_reload_conf();

1) Connect to the Database:

Use psql or any other PostgreSQL client to connect to your database.

2) Execute the ALTER SYSTEM Command:

ALTER SYSTEM SET shared_buffers TO '512MB';

3) Reload the Configuration:
Apply the changes by reloading the configuration:

SELECT pg_reload_conf();

   This will write the change to `postgresql.auto.conf` and apply it immediately.

Example:

Using SET Command (Temporary Change)

1) Connect to the Database:

Use psql or any other PostgreSQL client to connect to your database.

2) Execute the SET Command:

For example, to change the work_mem setting for the current session, run:

SET work_mem TO '64MB';

This change will only be effective for the duration of the current session and will revert to the default or configured value once the session ends.

postgresql.conf`:

The main configuration file for PostgreSQL, where you can manually set a wide range of parameters. 

postgresql.auto.conf`

A supplementary configuration file managed by PostgreSQL to store persistent changes made via the `ALTER SYSTEM` command. 

Changing Parameters:

Parameters can be changed by editing `postgresql.conf` and reloading the configuration, using the `ALTER SYSTEM` command (which updates `postgresql.auto.conf`), or temporarily with the `SET` command for session-specific changes.