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:
Postgres needs System V IPC available in jails. Enable it.
Make it persistent by adding this line to
Add this line to
Enter your jail and install Postgres.
pkg install postgresql93-server
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
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:
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
Now you can safely spin up the jail again, so long as the Postgres service isn't enabled in the jail's
/usr/local/pgsqlexists, fix the permissions on it (within the jail).
chown -R pgsql /usr/local/pgsql
Now we can get back to configuring Postgres. Initialize the cluster.
service postgresql oneinitdb service postgresql onestart
pgsqluser, create a user for the database -- a superuser, a user your website will use to access it, whatever you need. Look in
man createuserfor 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
/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
has a great page describing these settings and what they should be. Here's what I did.
Add the following lines to
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
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.
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.