Changes between Version 18 and Version 19 of InstallationGuidelines/PostgreSQL


Ignore:
Timestamp:
09/19/10 18:02:36 (11 years ago)
Author:
Fran Boon
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • InstallationGuidelines/PostgreSQL

    v18 v19  
    2121psql -d postgres -f /usr/share/postgresql/8.4/contrib/adminpack.sql
    2222}}}
     23
     24Adjust postgresql.conf for performance relative to resources available (http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server)
     25{{{
     26vim /etc/sysctl.conf
     27# Increase Shared Memory available for PostgreSQL
     28# 512Mb
     29kernel.shmmax = 279134208
     30# 1024Mb (may need more)
     31#kernel.shmmax = 536870912
     32kernel.shmall = 2097152
     33
     34sysctl -w kernel.shmmax=58720256
     35sysctl -w kernel.shmall=2097152
     36
     37vim /etc/postgresql/8.4/main/postgresql.conf
     38
     39# This default is probably ok
     40# adjust upward _or_ add a connection pooling layer like pgpool if running out of connections for clients
     41max_connections = 100
     42
     43# shared_buffers = 24MB
     44# Ideal is if all frequently accessed indexes and table rows can fit in here - this is often unrealistic
     45# setting too high relative to system resources can be detrimental to the system.
     46# At the high end this should be no more than 1/4 to 1/3 the available memory (ie that which is not being used by the OS or other processes).
     47# Values suggested are quite conservative, so you can push them up a bit if there are resources available for it
     48# but absolutely no more than 1/3 available memory.
     49# 512Mb system suggest starting at:
     50shared_buffers = 56MB
     51# 1024Mb system suggest starting at:
     52#shared_buffers = 160MB
     53
     54# effective_cache_size = 128MB
     55# 512Mb system suggest starting at:
     56effective_cache_size = 256MB
     57# 1024Mb system suggest starting at:
     58#effective_cache_size = 512MB
     59
     60#work_mem = 1MB
     61# This is a per sort setting, so setting this too high with a high number of max_connections can be bad.
     62# If the database load turns out to have a lot of sorting and not a lot of separate connections, you should increase this
     63# and drop the max_connections.
     64# 512Mb system suggest starting at:
     65work_mem = 2MB
     66# 1024Mb system suggest starting at:
     67#work_mem = 4MB
     68
     69# As you get more memory in the system, moving this up can be quite useful if you notice vacuum processes taking forever.
     70maintenance_work_mem = 16MB
     71
     72/etc/init.d/postgresql restart
     73}}}
     74
     75Allow remote access:
     76{{{
     77passwd postgres
     78
     79vim /etc/postgresql/8.4/main/postgresql.conf
     80listen_addresses = '*'
     81
     82vim /etc/postgresql/8.4/main/pg_hba.conf
     83host    all         all         my.ip.add.ress/24     md5
     84
     85/etc/init.d/postgresql restart
     86
     87vim /etc/iptables.rules
     88-A INPUT -p tcp --dport 5432 -j ACCEPT
     89
     90reboot
     91}}}
     92(Don't do remote access as 'postgres' - to do so means can't simply access psql via 'su postgres')
    2393
    2494It is possible to install [http://phppgadmin.sourceforge.net PHPPgAdmin] if you want a web-based management tool: