Basic Configuration File Modifications (postgresql.conf, including memory, connection, logging settings)

1. Memory Settings

POSTGRES_MEMSIZE: Sets memory size, affecting system performance and database performance. A common formula is: 16GB (for small applications) to 64GB (for large applications). 

pg_data_size = '8GB'
shared_buffers = '16MB'
effective_cache_size = '32MB'
work_mem = '4MB'
maintenance_work_mem = '12MB'
temp_read_only = 'on'

2. Connection Settings

listen_addresses: Sets the listened address, used by network servers to determine the local IP address for incoming connections. Can be an IP (localhost) or a domain name. 

listen_addresses = 'localhost'

max_connections: Sets the maximum allowed number of connections, used to control database concurrency. A value that is too low will result in high connection latency, while a value that is too high may exhaust system resources. 

max_connections = 100

3. Logging Settings

log_destination: Sets the log output location; options include file (local file), stdout (standard output), or stderr (error output). 

log_destination = 'file'

log_filename: Sets the log filename, including the log path and name. 

log_filename = '/var/log/postgresql/progress.log'

4. Other Settings

wal_sender_timeout: Sets wal_sender_timeout timeout time. A value that is too short will increase WAL latency, while a value that is too long may lead to data loss. 

wal_sender_timeout = '30s'

log_min_messages: Sets the minimum log message level; options range from debug (most detailed) to warning (warning). 

log_min_messages = 'info'

Final Note

When modifying the postgresql.conf file, keep the following points in mind:

  • Restart the database service to apply modifications.
  • Be cautious when making configuration changes so as not to impact system performance and security.
  • Use pg_config or pg_ctl tools to check current database configuration options.