PostgreSQL configuration

How to tune PostgreSQL config file?

First you need install pgtune by using below code

sudo yum install pgtune

Go to the PostgreSQL data folder

cd /var/lib/pgsql/9.2/data 

The command will output new config file with settings roughly optimized for the server’s hardware.

pgtune -i postgresql.conf -o postgresql.conf.pgtune -T DW -c 1000

PG Tune Options

---

• -i or –input-config : Specifies the current postgresql.conf file.

• -o or –input-config : Specifies the file name for the new postgresql.conf file.

• -M or –memory: Use this parameter to specify total system memory. If not specified, pgtune will attempt to detect memory size.

• -T or –type : Specifies database type. Valid options are: DW, OLTP, Web, Mixed, Desktop

• -c or –connections: Specifies number of maximum connections expected. If not specified, it depends on database type.

• -D or –debug : Enables debugging mode.

• -S or –settings: Directory where settings data files are located at.Defaults to the directory where the script is being run from. The RPM package includes a patch to use the correct location these files were installed into.

---

Backup the current config file

sudo mv postgresql.conf postgresql.conf.backup

And then setup new config file

sudo mv postgresql.conf.pgtune postgresql.conf

Restart the server

service postgresql-9.2 restart