11309
Reporter: mdoering
Type: Improvement
Summary: Upgrade Postgres on boma to 9.1
Description: Install PostGIS modules and ltree for materialized paths
Priority: Trivial
Resolution: Fixed
Status: Closed
Created: 2012-06-07 16:34:41.039
Updated: 2013-12-05 11:15:06.368
Resolved: 2013-12-05 11:08:06.025
Author: mdoering@gbif.org
Created: 2012-06-07 17:30:28.923
Updated: 2012-06-07 17:30:28.923
if possible also add hstore for experimental purposes
http://www.postgresql.org/docs/9.1/static/hstore.html
Author: mdoering@gbif.org
Comment: ... and extension intarray if possible for experiments
Created: 2012-06-07 18:54:35.294
Updated: 2012-06-07 18:54:35.294
Author: mdoering@gbif.org
Created: 2012-06-18 11:31:29.304
Updated: 2012-06-18 11:31:29.304
PGTUNE a python script to tune the postgres config settings comes up with the following settings in contrast to our existing one for 250 connections (as we had before):
http://pgfoundry.org/frs/?group_id=1000416&release_id=1511
{noformat}
#EXISITNG
default_statistics_target = 1000 # range 1-10000
maintenance_work_mem = 8GB # min 1MB
#constraint_exclusion = partition # on, off, or partition
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
effective_cache_size = 84GB
work_mem = 128MB # min 64kB
#wal_buffers = 16MB # min 32kB
checkpoint_segments = 32 # in logfile segments, min 1, 16MB each
shared_buffers = 32GB # min 128kB
max_connections = 250 # (change requires restart)
{noformat}
{noformat}
#DB TYPE=MIXED
default_statistics_target = 50
maintenance_work_mem = 1GB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 88GB
work_mem = 256MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 30GB
{noformat}
{noformat}
#DB TYPE=OLTP
default_statistics_target = 10
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
effective_cache_size = 88GB
work_mem = 512MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 30GB
{noformat}
Author: trobertson@gbif.org
Comment: This will be done when actually needed for the application, and taking into account the plans to harmonize the versions across servers.
Created: 2012-08-29 09:48:09.766
Updated: 2012-08-29 09:48:09.766