PostgreSQL install

PostgreSQL|PostgreSQL

Install
PostgreSQL 9.1 + PostGIS 1.5

PostgreSQL 9.4 + PostGIS 2.1

Config

 * PostgreSQL when it is not your job
 * Tuning Your PostgreSQL Server

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


 * 1) CONNECTIONS AND AUTHENTICATION
 * 1) CONNECTIONS AND AUTHENTICATION


 * 1) - Connection Settings -

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


 * 1) ERROR REPORTING AND LOGGING
 * 1) 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

-- 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

kernel.shmmax =	104857600

Save and set the new value

Check again

-- 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


 * 1) RESOURCE USAGE (except WAL)
 * 1) RESOURCE USAGE (except WAL)

shared_buffers = 100MB work_mem = 16MB maintenance_work_mem = 256MB effective_cache_size = 1GB


 * 1) WRITE AHEAD LOG
 * 1) WRITE AHEAD LOG

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


 * 1) QUERY TUNING
 * 1) QUERY TUNING

random_page_cost = 2.0

Restart postgresql