dbi Blog

This post builds on top of the previous post in which we’ve created a simple thin Jail. The goal of this post is, to put a bit of automation around this and to provide a simple PostgreSQL as a service platform. We want to do this by creating one PostgreSQL instance per Jail (without any restrictions on resource usage for now, no monitoring and so on). Before we start with this, let’s remove the sample thinjail1 we’ve created in the last post:

root@freebsd14:~ $ jls
   JID  IP Address      Hostname                      Path
     1  192.168.122.160 thinjail1                     /usr/local/jails/containers/thinjail1
root@freebsd14:~ $ service jail stop thinjail1
Stopping jails: thinjail1.
root@freebsd14:~ $ chflags -R 0 /usr/local/jails/containers/thinjail1
root@freebsd14:~ $ rm -rf /usr/local/jails/containers/thinjail1/*
root@freebsd14:~ $ jls
   JID  IP Address      Hostname                      Path
root@freebsd14:~ $ zfs destroy zroot/jails/containers/thinjail1

We’ll also remove the ZFS snapshot because we’ll be creating a new one containing additional packages and configuration:

root@freebsd14:~ $ zfs destroy zroot/jails/templates/14.1-RELEASE@base
root@freebsd14:~ $ zfs list -t snapshot
NAME                                       USED  AVAIL  REFER  MOUNTPOINT
zroot/ROOT/default@2024-11-26-10:56:43-0   394M      -  1.46G  -
zroot/ROOT/default@2024-12-04-08:41:41-0  2.23M      -  2.49G  -
zroot/ROOT/default@2024-12-04-08:42:46-0  1.86M      -  2.58G  -

Remember that this was our base for the Jail:

root@freebsd14:~ $ ls -al /usr/local/jails/templates/14.1-RELEASE/
total 95
drwxr-xr-x  18 root wheel   22 Dec  2 13:35 .
drwxr-xr-x   3 root wheel    3 Dec  2 13:29 ..
-rw-r--r--   2 root wheel 1011 May 31  2024 .cshrc
-rw-r--r--   2 root wheel  495 May 31  2024 .profile
-r--r--r--   1 root wheel 6109 May 31  2024 COPYRIGHT
drwxr-xr-x   2 root wheel   49 Dec  2 13:40 bin
drwxr-xr-x  15 root wheel   69 Dec  2 13:40 boot
dr-xr-xr-x   2 root wheel    2 May 31  2024 dev
drwxr-xr-x  30 root wheel  105 Dec  2 13:40 etc
drwxr-xr-x   4 root wheel   78 Dec  2 13:40 lib
drwxr-xr-x   3 root wheel    5 May 31  2024 libexec
drwxr-xr-x   2 root wheel    2 May 31  2024 media
drwxr-xr-x   2 root wheel    2 May 31  2024 mnt
drwxr-xr-x   2 root wheel    2 May 31  2024 net
dr-xr-xr-x   2 root wheel    2 May 31  2024 proc
drwxr-xr-x   2 root wheel  150 Dec  2 13:40 rescue
drwxr-x---   2 root wheel    7 May 31  2024 root
drwxr-xr-x   2 root wheel  150 Dec  2 13:40 sbin
lrwxr-xr-x   1 root wheel   11 May 31  2024 sys -> usr/src/sys
drwxrwxrwt   2 root wheel    2 May 31  2024 tmp
drwxr-xr-x  14 root wheel   14 May 31  2024 usr
drwxr-xr-x  24 root wheel   24 May 31  2024 var

What we’re doing now is to install PostgreSQL into this base so we’ll have that available immediately when we create new Jails afterwards. To do this we can ask pkg to perform it’s actions against a chroot environment instead of the host system by using the “-c” switch:

root@freebsd14:~ $ pkg -c /usr/local/jails/templates/14.1-RELEASE/ install postgresql17-server-17.2 postgresql17-contrib-17.2
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 17 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
        gettext-runtime: 0.22.5
        icu: 74.2_1,1
        indexinfo: 0.3.1
        libedit: 3.1.20240808,1
        libffi: 3.4.6
        liblz4: 1.10.0,1
        libxml2: 2.11.9
        llvm15: 15.0.7_10
        lua53: 5.3.6_1
        mpdecimal: 4.0.0
        perl5: 5.36.3_2
        postgresql17-client: 17.2
        postgresql17-contrib: 17.2
        postgresql17-server: 17.2
        python311: 3.11.10
        readline: 8.2.13_1
        zstd: 1.5.6

Number of packages to be installed: 17

The process will require 1 GiB more space.
231 MiB to be downloaded.

Proceed with this action? [y/N]: y
[1/17] Fetching indexinfo-0.3.1.pkg: 100%    6 KiB   5.9kB/s    00:01    
[2/17] Fetching libxml2-2.11.9.pkg: 100%  873 KiB 893.6kB/s    00:01    
...
To use PostgreSQL, enable it in rc.conf using

  sysrc postgresql_enable=yes

To initialize the database, run

  service postgresql initdb

You can then start PostgreSQL by running:

  service postgresql start

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

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

NB. If you're not using a checksumming filesystem like ZFS, you might
    wish to enable data checksumming. It can be enabled during
    the initdb phase, by adding the "--data-checksums" flag to
    the postgresql_initdb_flags rcvar. Otherwise you can enable it later by
    using pg_checksums.  Check the initdb(1) manpage for more info
    and make sure you understand the performance implications.

We can easily verify this by looking at /usr/local/bin of our base:

root@freebsd14:~ $ ls -al /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg*
-rwxr-xr-x  1 root wheel  99224 Dec  3 02:06 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_amcheck
-rwxr-xr-x  1 root wheel  43616 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_archivecleanup
-rwxr-xr-x  1 root wheel 161128 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_basebackup
-rwxr-xr-x  1 root wheel  78336 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_checksums
-rwxr-xr-x  1 root wheel  53784 Dec  3 02:06 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_config
-rwxr-xr-x  1 root wheel  57272 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_controldata
-rwxr-xr-x  1 root wheel 101656 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_createsubscriber
-rwxr-xr-x  1 root wheel  72136 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_ctl
-rwxr-xr-x  1 root wheel 422632 Dec  3 02:06 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_dump
-rwxr-xr-x  1 root wheel 121896 Dec  3 02:06 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_dumpall
-rwxr-xr-x  1 root wheel  67904 Dec  3 02:06 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_isready
-rwxr-xr-x  1 root wheel 103840 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_receivewal
-rwxr-xr-x  1 root wheel 101768 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_recvlogical
-rwxr-xr-x  1 root wheel  68312 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_resetwal
-rwxr-xr-x  1 root wheel 207752 Dec  3 02:06 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_restore
-rwxr-xr-x  1 root wheel 153560 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_rewind
-rwxr-xr-x  1 root wheel  49024 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_test_fsync
-rwxr-xr-x  1 root wheel  39088 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_test_timing
-rwxr-xr-x  1 root wheel 178256 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_upgrade
-rwxr-xr-x  1 root wheel 110984 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pg_waldump
-rwxr-xr-x  1 root wheel 207416 Dec  3 02:06 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pgbench
root@freebsd14:~ $ ls -al /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/po*
-rwxr-xr-x  1 root wheel    4149 Oct 31 02:09 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pod2html
-rwxr-xr-x  1 root wheel   15046 Oct 31 02:09 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pod2man
-rwxr-xr-x  1 root wheel   10815 Oct 31 02:09 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pod2text
-rwxr-xr-x  1 root wheel    4113 Oct 31 02:09 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/pod2usage
-rwxr-xr-x  1 root wheel    3664 Oct 31 02:09 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/podchecker
-rwxr-xr-x  1 root wheel 9548040 Dec  3 02:18 /usr/local/jails/templates/14.1-RELEASE/usr/local/bin/postgres

In the same way we can ask pkg to operate in a chroot, we can also ask sysrc to operate in a chroot and directly enable the PostgreSQL service:

root@freebsd14:~ $ sysrc -R /usr/local/jails/templates/14.1-RELEASE/ postgresql_enable=yes
postgresql_enable:  -> yes
root@freebsd14:~ $ cat /usr/local/jails/templates/14.1-RELEASE/etc/rc.conf
postgresql_enable="yes"

Now we have everything we need and can create a new ZFS snapshot:

root@freebsd14:~ $ zfs snapshot zroot/jails/templates/14.1-RELEASE@postgresql17
root@freebsd14:~ $ zfs list -t snapshot
NAME                                              USED  AVAIL  REFER  MOUNTPOINT
zroot/ROOT/default@2024-11-26-10:56:43-0          394M      -  1.46G  -
zroot/ROOT/default@2024-12-04-08:41:41-0         2.23M      -  2.49G  -
zroot/ROOT/default@2024-12-04-08:42:46-0         1.86M      -  2.58G  -
zroot/jails/templates/14.1-RELEASE@postgresql17     0B      -  1.34G  -

Looking at the configuration for the Jail in the last post, it looks like this:

root@freebsd14:~ $ cat /etc/jail.conf.d/thinjail1.conf 
thinjail1 {
  # STARTUP/LOGGING
  exec.start = "/bin/sh /etc/rc";
  exec.stop = "/bin/sh /etc/rc.shutdown";
  exec.consolelog = "/var/log/jail_console_${name}.log";

  # PERMISSIONS
  allow.raw_sockets;
  exec.clean;
  mount.devfs;

  # HOSTNAME/PATH
  host.hostname = "${name}";
  path = "/usr/local/jails/containers/${name}";

  # NETWORK
  ip4.addr = 192.168.122.160;
  interface = vtnet0;
}

The dynamic parts in that configuration are the IP address, the path, the hostname and the name of the Jail. Everything else remains static for additional Jails. So we could create a simple script which creates a new Jail configuration based on a few parameters (there is no error handling, no sanity checks, and everything else which makes a good script a good script, this really is just for demonstration purposes):

#!/usr/local/bin/bash

# The parameters for a new Jail
JAILNAME="$1"
IPADDR="$2"

# This is where all the Jails go to
BASEPATH="/usr/local/jails/containers/"

cat << EOF > /etc/jail.conf.d/${JAILNAME}.conf
${JAILNAME} {
  # STARTUP/LOGGING
  exec.start = "/bin/sh /etc/rc";
  exec.stop = "/bin/sh /etc/rc.shutdown";
  exec.consolelog = "/var/log/jail_console_${name}.log";
  # PERMISSIONS
  allow.raw_sockets;
  allow.sysvipc = 1;
  exec.clean;
  mount.devfs;
  # HOSTNAME/PATH
  host.hostname = "${JAILNAME}.it.dbi-services.com";
  path = "${BASEPATH}/${JAILNAME}";
  # NETWORK
  ip4.addr = ${IPADDR};
  interface = vtnet0;
}
EOF

zfs clone zroot/jails/templates/14.1-RELEASE@postgresql17 zroot/jails/containers/${JAILNAME}
service jail start ${JAILNAME}
jls
jexec -U postgres ${JAILNAME} /usr/local/bin/initdb --pgdata=/var/db/postgres/data17
jexec -l ${JAILNAME} service postgresql start

What this script is doing:

Create the Jail configuration based on the name and IP address
Clone the ZFS snapshot we’ve created above
Start the Jail
Initialize PostgreSQL
Start up PostgreSQL

If you run that it looks like this:

root@freebsd14:~ $ ./new_jail.sh PG1 192.168.122.130
Starting jails: PG1.
   JID  IP Address      Hostname                      Path
    11  192.168.122.130 PG1.it.dbi-services.com       /usr/local/jails/containers/PG1
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/db/postgres/data17 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Europe/Vaduz
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data17 -l logfile start

2024-12-04 10:28:32.103 CET [4809] LOG:  ending log output to stderr
2024-12-04 10:28:32.103 CET [4809] HINT:  Future log output will go to log destination "syslog".

That’s it, PostgreSQL is up and running in the Jail:

root@freebsd14:~ $ psql -h 192.168.122.130
psql: error: connection to server at "192.168.122.130", port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.122.130", user "root", database "root", no encryption

Pretty easy to do. Now additional PostgreSQL Jails can be added easily. What is really impressive is the size of the Jail on disk:

root@freebsd14:~ $ du -sh /usr/local/jails/containers/PG1
1.4G    /usr/local/jails/containers/PG1

A bit more than a GB for a complete PostgreSQL service.

Post Views: 564

FreeBSD basics – 5 – Providing services using Jails

Daniel Westermann

Leave a Reply:

Related blog articles

Daniel Westermann

Leave a Reply:

Related blog articles

PostgreSQL 18: Add OLD/NEW support to RETURNING in DML queries

dbo vs. db_owner in SQL Server – What are the differences between them?

PostgreSQL 18: Change the maximum number of autovacuum workers on the fly

FreeBSD basics – 8 – Running Linux Containers