PostgreSQL install

From gnewarchaeology wiki
Jump to: navigation, search
PostgreSQL > PostgreSQL install


Install

PostgreSQL 9.1 + PostGIS 1.5

$ sudo apt-get install postgresql postgresql-client postgresql-9.1-postgis postgis phppgadmin

PostgreSQL 9.4 + PostGIS 2.1

$ sudo apt-get install postgresql-9.4 postgresql-doc-9.4 postgresql-9.4-postgis-2.1 postgis postgis-doc

Config

Edit the file /etc/postgresql/9.1/main/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION                                                
#------------------------------------------------------------------------------

# - Connection Settings -                                                       

#listen_addresses = 'localhost'         # what IP address(es) to listen on;     
                                        # comma-separated list of addresses;    
                                        # defaults to 'localhost', '*' = all
listen_addresses = '*'    
        				# (change requires restart)            
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING                                                   
#------------------------------------------------------------------------------

log_destination = 'csvlog'
log_directory = 'pg_log'
logging_collector = on
log_filename = 'postgres-%Y-%m-%d_%H%M%S'
log_rotation_age = 1d
log_rotation_size = 1GB
log_min_duration_statement = 250ms 
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

Check the system shared memory limits

$ ipcs -l
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 32768 #up from 32MB to 100MB in /etc/sysctl.d/30-postgresql-shm.conf
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1

Improve shared memory settings for PostgreSQL

$ sudo emacs -nw /etc/sysctl.d/30-postgresql-shm.conf
kernel.shmmax =	104857600

Save and set the new value

$ sudo sysctl -p /etc/sysctl.d/30-postgresql-shm.conf

Check again

$ ipcs -l
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 102400 #100MB
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)                                                   
#------------------------------------------------------------------------------

shared_buffers = 100MB
work_mem = 16MB
maintenance_work_mem = 256MB
effective_cache_size = 1GB
#------------------------------------------------------------------------------
# WRITE AHEAD LOG                                                               
#------------------------------------------------------------------------------

wal_buffers = 16MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 10m-30m # Depends on restart time
checkpoint_segments = 32 # To start.
#------------------------------------------------------------------------------
# QUERY TUNING                                                                  
#------------------------------------------------------------------------------

random_page_cost = 2.0

Restart postgresql

$ sudo /etc/init.d/postgresql restart