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:
frompgsql:*:70:70:PostgreSQL pseudo-user:/usr/local/pgsql:/bin/sh
topgsql:*: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 inman 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.