Installing Postgres

This page outlines the steps I use to install and configure Postgres on a FreeBSD server or in a FreeBSD jail. At the time of writing this, the latest version of Postgres in FreeBSD ports, which I am using, is 8.4.3.

Install the ports

Install the ports with default options:

portmaster /usr/ports/databases/postgresql84-server/

And as always, remember to enable Postgres in rc.conf:

[tykling@wackbox163 ~]$ grep postgres /etc/rc.conf
postgresql_enable="YES"

/etc/periodic.conf

I also add the following to /etc/periodic.conf:

#for postgres
daily_pgsql_backup_enable="YES" # do backup
daily_pgsql_vacuum_enable="YES" # do vacuum

This makes Postgres run a full vacuum daily, as well as dump a daily backup (into /usr/local/pgsql/backups by default).

Initializing Postgres

When you install the Postgres server, the post-install message says (among other things):

To initialize the database, run

  /usr/local/etc/rc.d/postgresql initdb

You can then start PostgreSQL by running:

  /usr/local/etc/rc.d/postgresql start

For postmaster settings, see ~pgsql/data/postgresql.conf

NB. FreeBSD's PostgreSQL port logs to syslog by default
    See ~pgsql/data/postgresql.conf for more info

When I tried calling the rc.d script with the "initdb" argument, I had some problems which I later found out was due to the fact that Postgres is running in a jail. I was getting some errors about shared memory:

creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL:  could not create shared memory segment: Function not implemented

I found the solution on the always great The FreeBSD Diary. I needed to change a sysctl on the jail host - not in the jail itself ofcourse - to allow the shared memory operation. Use:

[tykling@wackbox ~]$ sudo sysctl security.jail.sysvipc_allowed=1

to change the current value, and add:

security.jail.sysvipc_allowed=1

to /etc/sysctl.conf to ensure it stays that way after the next reboot. I didn't need to restart the jail or anything after this change, it just immediately worked when I tried going back to rerun the initdb command.

Update: Later I have found that when running multiple Postgres servers in different jails on the same physical FreeBSD machine, I needed some additional tweaking to make it work. First of all, again from the same article on The FreeBSD Diary, the uid # of the pgsql user in the jails needs to be different, or the different Postgres processes will corrupt each others memory. So I use vipw to change the uid so they are different in the different jails, like so:

$ cat /usr/jails/*/etc/passwd | grep pgsql
pgsql:*:2070:2070:PostgreSQL Daemon:/usr/local/pgsql:/bin/sh
pgsql:*:1070:1070:PostgreSQL Daemon:/usr/local/pgsql:/bin/sh
pgsql:*:70:70:PostgreSQL Daemon:/usr/local/pgsql:/bin/sh

Here I have three different jails on the same machine, all running Postgres without affecting each other, because the uids are different.

More shared memory problems: When running multiple instances of Postgres on the same server in jails, the default amount of shared memory in FreeBSD isn't enough it seems. I found a solution which I am documenting here for future reference. I am not a fan of blindly copy/pasting stuff from around the web into config files. I generally try to graps the meaning of each line before I use it. But in this particular case, I have absolutely no idea if these values are correct or not, or even what they do (other than the fact that they are all related to shared memory). Some of them are from The FreeBSD Diary, others are from mailing lists and blogs around the web:

$ cat /etc/sysctl.conf | egrep -i "ipc|postgres"
# for more shared memory for jails/PostgreSQL
kern.ipc.shmall=65536
kern.ipc.shmmax=134217728
kern.ipc.semmap=4096
$ cat /boot/loader.conf | egrep -i "ipc|postgres"
#for shared memory for postgresql
kern.ipc.shmmni=2048
kern.ipc.shmseg=2048
kern.ipc.semaem=32767
kern.ipc.semvmx=65534
kern.ipc.semusz=184
kern.ipc.semume=80
kern.ipc.semopm=200
kern.ipc.semmsl=120
kern.ipc.semmnu=4096
kern.ipc.semmns=8192
kern.ipc.semmni=32767
kern.ipc.semmap=60

These are all on the host machine ofcourse, not in the jails. After all this I am usually able to run initdb with no further problems. After the database is initialized, I start Postgres using the rc.d script, and I am ready to go.