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
: Log file name pattern.
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
5) Write-Ahead Logging (WAL)
wal_level
: determines the level of information written to the Write-Ahead Log (WAL).
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
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();
Example:
Using `ALTER SYSTEM`
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.