Documentation of Postgresql.conf file

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

Documentation of Postgresql.conf file

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.