RDBMS: PostgreSQL

Table of Contents

Configuration
Upgrading Postgres

Configuration

by ross at 07:13:48 on December 18, 2013

Install

# cd /usr/ports/databases/postgresql93-server
# make install clean
# cd /usr/ports/databases/postgresql93-contrib
# make install clean
# rehash

Add to rc.conf:

postgresql_enable="YES"

Moving data directory to /home

# mv /usr/local/pgsql /home/
# pw usermod pgsql -d /home/pgsql

Initialize postgres

Create databse and assign password to pgsql user:

# service postgresql initdb
# service postgresql start
# psql -U pgsql -d postgres
postgres=# alter user pgsql with password 'the_password';
^D

Modify database access rules:

# su -l pgsql
$ vi data/postgresql.conf
listen_addresses = '*'

$ vi data/pg_hba.conf:
# "local" is for Unix domain socket connections only
local   all         all                               password
# IPv4 local connections:
host    all         all         127.0.0.1/32          password
# IPv6 local connections:
host    all         all         ::1/128               password

host    all         all         192.168.10.0/24       md5
# service postgresql restart

On-server daily backups:

Add to /etc/periodic.conf:

daily_pgsql_backup_enable="YES"
daily_pgsql_vacuum_enable="YES"
daily_pgsql_backupdir="/var/backups/pgsql"
daily_pgsql_savedays="5"

If you changed backupdir to /var/backups/pgsql (default path is in pgsql home), you need to chmod o+rX /var/backups in order for backups to work. Remember: make installworld will restore permissions of /var/backups to 0750. So you will need to chmod it again.

Create in pgsql user home dir file .pgpass:

localhost:*:*:pgsql:the_password
# chown pgsql .pgpass
# chmod 600 .pgpass

Administration

Consider installing databases/phppgadmin - PostgreSQL database design and management system.

 

Comments
Are you sure about the kernel changes? I haven't heard the build custom kernel for postgresql recommendation before - usually increasing some sysctl settings is recommended for a busy server. I don't see any sign of SHMMAXPGS but the others already exist and FreeBSD 9.2 defaults are higher than you have. Personally I still increase kern.ipc.shmseg and kern.ipc.shmmni to 1024 - all others appear to be good defaults these days.
-- Shane
Saturday, December 14, 2013, 1:21:42
Have a look at /usr/ports/databases/postgresql91-server/files/pkg-message-server.in

So the defaults are higher than this? I should probably remove it...
-- ross
Sunday, December 15, 2013, 5:48:16
In a 9.2 src tree I see #define SHMMAXPGS 131072 in sys/kern/sysv_shm.c and SHMALL is set to SHMMAXPGS. shmall and shmmax can be increased in stsctl.conf as described in the pg manual, it also mentions kern.ipc.semmap which doesn't appear to exist now.
sysctl shows my un-adjusted values at -
kern.ipc.semmni: 50
kern.ipc.semmns: 340
kern.ipc.semume: 50
kern.ipc.semmnu: 150
and increasing these can be done in loader.conf - without building a custom kernel.
-- Shane
Sunday, December 15, 2013, 6:22:32
Removed this section. Thank you for the comments.
-- ross
Wednesday, December 18, 2013, 7:15:14