Tech:Open Source/Setup/PortgreSQL

From Cyclopath

Jump to: navigation, search
up to: Tech:Open Source > Setup

Contents

[edit] Configure PostgreSQL

[edit] Postgres Storage

Move data directory (/var/lib/postgresql/8.2/main) somewhere that can accumulate a lot of space, and make a symlink from the old location to the new. 

sudo mv /var/lib/postgresql/8.3/main /export/scratch/pgdata-8.3
sudo ln -s /export/scratch/pgdata-8.3 /var/lib/postgresql/8.3/main

[edit] Increase Postgres' Shared Memory Limits 

gvim /etc/sysctl.conf

Add to the top of the file 

# Make postgresql happy by increasing the shared memory limits
#
# segment size 1G
kernel.shmmax = 1073741824

Tell the kernel about the changes 

sudo sysctl -p

[edit] Set up the Postgres User

Create the cycling user within the database 

createuser -U postgres -SDR cycling

[edit] Edit the PostgreSQL Ident Authentication Maps

Rather than each Cyclopath engineer also having their own Postgres login, we use an ident-based authentication system. You need to edit this file on your machine and map your login to the postgres login. 

gvim /etc/postgresql/8.3/main/pg_ident.conf

Add two lines to the file, one to map yourself to the postgres user, and the other to the cycling user. E.g., Landon's ident file would look like this 

# MAPNAME     IDENT-USERNAME    PG-USERNAME
local         postgres          postgres
...
local         landonb           postgres
local         landonb           cycling
...
local         www-data          cycling

THIS DOES NOT WORK

Restart Postgres to pickup the changes 

sudo /etc/init.d/postgresql-8.3 restart

FIXME I found these differences btw. my new installation's postgresql.conf and my old machine's. From /etc/postgresql/8.3/main/postgresql.conf 

ssl = false
shared_buffers = 96MB
work_mem = 8MB
maintenance_work_mem = 64MB
checkpoint_segments = 20
effective_cache_size = 64MB
autovacuum = on                 # Enable autovacuum subprocess?  'on' 
autovacuum_naptime = 30min              # time between autovacuum runs
lc_messages = 'en_US.UTF-8'             # locale for system error message
                                        # strings
lc_monetary = 'en_US.UTF-8'             # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'              # locale for number formatting
lc_time = 'en_US.UTF-8'                 # locale for time formatting

[edit] Postgres can't connect without IPv6

Uncomment the IPv6 line in FIXME What file is this? 

# IPv6 local connections:
host    all         all         ::1/128               md5

[edit] Log Rotate

Really only the production server needs this, but if you'd like to analyze Postgres logs with logcheck, add "create 640 root www-data" to the structure, i.e., 

gvim /etc/logrotate.d/postgresql-common

Add the last line in the block. 

/var/log/postgresql/*.log {
       weekly
       rotate 10
       copytruncate
       delaycompress
       compress
       notifempty
       missingok
       create 640 root www-data
}

You can skip this step for your development machine.

Retrieved from "http://cyclopath.org/wiki/Tech:Open_Source/Setup/PortgreSQL"
Personal tools