PostgreSQL configuration: Difference between revisions

From DISI
Jump to navigation Jump to search
No edit summary
No edit summary
 
(2 intermediate revisions by the same user not shown)
Line 1: Line 1:
=='''How to tune PostgreSQL config file?'''==
=='''How to tune PostgreSQL config file?'''==


First you need install pgtune by using below code
Written by Chinzorig Dandarchuluun (Sep. 19, 2018).
 
 
To install pgtune by using below code


  sudo yum install pgtune
  sudo yum install pgtune
Line 22: Line 25:
* -M or –memory: Use this parameter to specify total system memory. If not specified, pgtune will attempt to detect memory size.
* -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
* -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.
* -c or –connections: Specifies number of maximum connections expected. If not specified, it depends on database type.

Latest revision as of 19:02, 6 November 2018

How to tune PostgreSQL config file?

Written by Chinzorig Dandarchuluun (Sep. 19, 2018).


To 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