Configuration file locations:
Where are my postgres *.conf files?
Where is the Postgresql config file: ‘postgresql.conf’ on Windows?
- Windows: C:\Program Files\PostgreSQL\x.x\data\postgresql.conf
- Linux: /etc/postgresql/x.x/main/postgresql.conf
Go to bottom of .conf file, and add this line:
include postgresql.custom.conf
Then create file ‘postgresql.custom.conf’ in the same directory and place your customised configuration settings in it. Any settings set in the custom file will override those in the main config.
Navigate to pgtune and enter the required information, and pgtune will generate custom settings based upon total RAM size and intended use etc:
Copy the generated settings into file ‘postgresql.custom.conf’:
max_connections = 100
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 83886kB
maintenance_work_mem = 2GB
min_wal_size = 2GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
Restart Postgres.
Further reading on Postgres performance: http://www.craigkerstiens.com