Changes between Version 18 and Version 19 of InstallationGuidelines/PostgreSQL

Timestamp:

09/19/10 18:02:36 (15 years ago)

Author:

Fran Boon

Comment:

--

InstallationGuidelines/PostgreSQL

@@ -21 +21 @@
 psql -d postgres -f /usr/share/postgresql/8.4/contrib/adminpack.sql
 }}}
+
+Adjust postgresql.conf for performance relative to resources available (http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server)
+{{{
+vim /etc/sysctl.conf
+# Increase Shared Memory available for PostgreSQL
+# 512Mb
+kernel.shmmax = 279134208
+# 1024Mb (may need more)
+#kernel.shmmax = 536870912
+kernel.shmall = 2097152
+
+sysctl -w kernel.shmmax=58720256
+sysctl -w kernel.shmall=2097152
+
+vim /etc/postgresql/8.4/main/postgresql.conf
+
+# This default is probably ok
+# adjust upward _or_ add a connection pooling layer like pgpool if running out of connections for clients
+max_connections = 100
+
+# shared_buffers = 24MB
+# Ideal is if all frequently accessed indexes and table rows can fit in here - this is often unrealistic
+# setting too high relative to system resources can be detrimental to the system.
+# 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).
+# Values suggested are quite conservative, so you can push them up a bit if there are resources available for it
+# but absolutely no more than 1/3 available memory.
+# 512Mb system suggest starting at:
+shared_buffers = 56MB
+# 1024Mb system suggest starting at:
+#shared_buffers = 160MB
+
+# effective_cache_size = 128MB
+# 512Mb system suggest starting at:
+effective_cache_size = 256MB
+# 1024Mb system suggest starting at:
+#effective_cache_size = 512MB
+
+#work_mem = 1MB
+# This is a per sort setting, so setting this too high with a high number of max_connections can be bad.
+# If the database load turns out to have a lot of sorting and not a lot of separate connections, you should increase this
+# and drop the max_connections.
+# 512Mb system suggest starting at:
+work_mem = 2MB
+# 1024Mb system suggest starting at:
+#work_mem = 4MB
+
+# As you get more memory in the system, moving this up can be quite useful if you notice vacuum processes taking forever.
+maintenance_work_mem = 16MB
+
+/etc/init.d/postgresql restart
+}}}
+
+Allow remote access:
+{{{
+passwd postgres
+
+vim /etc/postgresql/8.4/main/postgresql.conf
+listen_addresses = '*'
+
+vim /etc/postgresql/8.4/main/pg_hba.conf
+host    all         all         my.ip.add.ress/24     md5
+
+/etc/init.d/postgresql restart
+
+vim /etc/iptables.rules
+-A INPUT -p tcp --dport 5432 -j ACCEPT
+
+reboot
+}}}
+(Don't do remote access as 'postgres' - to do so means can't simply access psql via 'su postgres')
 
 It is possible to install [http://phppgadmin.sourceforge.net PHPPgAdmin] if you want a web-based management tool: