Running PostgreSQL in FreeBSD Jails

Postgres is my favorite database, and I run multiple sites backed by it on one physical host with FreeBSD's jails. I keep a separate Postgres instance in each webserver jail to keep things partitioned off. Here's how to set it up:

Basic Configuration

Postgres needs System V IPC available in jails. Enable it.
```
sysctl security.jail.sysvipc_allowed=1
```
Make it persistent by adding this line to /etc/sysctl.conf
```
security.jail.sysvipc_allowed=1
```
Add this line to /etc/rc.conf
```
jail_sysvipc_allow="YES"
```
Enter your jail and install Postgres.
```
pkg install postgresql93-server
```

UID Problems

If you plan on having multiple instances of Postgres running on the same host (even of they're in different jails), you must change the pgsql UID such that each one is different. The reason for this is simple once you realize two things: First, the kernel manages shared memory. Second, jails do not duplicate the kernel, so all processes from every jail are running under the same one kernel as the host.

If you have multiple Postgres instances from the same UID, the kernel will give them the same pages of shared memory. This causes them to clobber each other in a bad way. I learned this the hard way.

To fix this, stop your jail. Run vipw -d /usr/jails/myjail/etc/ and change pgsql's UID to something else. For example:
from pgsql:*:70:70:PostgreSQL pseudo-user:/usr/local/pgsql:/bin/sh
to pgsql:*:70203:70:PostgreSQL pseudo-user:/usr/local/pgsql:/bin/sh

Although UIDs are traditionally 65535 or lower, there's no technical reason for that, at least on FreeBSD. To make the number more meaningful to me, I chose the convention of setting the UID to 70 + the last block of the jail's IP. In this example, the IP address of my jail is 192.168.1.203
Now you can safely spin up the jail again, so long as the Postgres service isn't enabled in the jail's /etc/rc.conf

If /usr/local/pgsql exists, fix the permissions on it (within the jail).
```
chown -R pgsql /usr/local/pgsql
```

Starting Postgres

Now we can get back to configuring Postgres. Initialize the cluster.
```
service postgresql oneinitdb
service postgresql onestart
```
As the pgsql user, create a user for the database -- a superuser, a user your website will use to access it, whatever you need. Look in man createuser for options.
```
su pgsql
_> createuser -d webserver
_> exit
```
In my case, I only need a user that can create databases with no password authentication for my webserver to use. No one else will be in this jail, and Postgres won't be allowing connections from other machines on the LAN.
Now you can set postgresql_enable="YES" in /etc/rc.conf, create your tables, and whatever else.

Shared Memory Problems

When trying to start my third Postgres jail, I ran into the following issue.

root@myjail:~ # service postgresql start
LOG:  could not create IPv6 socket: Protocol not supported
FATAL:  could not create semaphores: No space left on device
DETAIL:  Failed system call was semget(5432021, 17, 03600).
HINT:  This error does *not* mean that you have run out of disk space.  It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded.  You need to raise the respective kernel parameter. Alternatively, reduce PostgreSQL's consumption of semaphores by reducing its max_connections parameter.
       The PostgreSQL documentation contains more information about configuring your system for PostgreSQL.
pg_ctl: could not start server
Examine the log output.

This happens because the default values for maximum shared memory in FreeBSD aren't sufficient for three copies of Postgres. Look at sysctl kern.ipc and sysctl -d kern.ipc to see what the current values are. The Postgres documentation has a great page describing these settings and what they should be. Here's what I did.

Add the following lines to /boot/loader.conf
```
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256
```
These values can not be changed while the kernel is running, otherwise they would go in /etc/sysctl.conf
Reboot the server and make sure the values have been updated. You should be able to run several Postgres jails like this, but up them as needed.

Conclusion

It would be simpler to run one copy of Postgres and have each jail use its own database in the cluster, but I like having everything separated (at least for small deployments). If someone compromises my webserver, there's no way they can get at the databases for my other sites. It also means I can use ZFS to move the jail from one host to another and have the whole atomic unit stay together.

Note that configuring Postgres' pg_hba.conf and tuning the system for performance is an art in itself. This is a very basic configuration, focused on jail-related issues.

Textplain

Tutorials