Introduction

PostgreSQL is a relational database management system (RDBMS) known for its extensibility, SQL standards compliance, robustness and advanced features. PostgreSQL is often preferred for applications that require strict transaction control and data integrity, and FreeBSD provides an excellent base for running a PostgreSQL database using a licence similar to BSD.

Goals

PostgreSQL forms the database server and therefore the basis for all articles based on it.
This can be done either separately as a separate database jail or together with the application without a separate jail. Especially if several PostgreSQL databases are already in use, a central server is worthwhile.

NEW: For the very impatient I have a console only section. There are only commands, no explanations.

Last update:

  • 18.01.2026: Removing the external directories. This is explained here, but it is only for advanced administrators.
  • 16.01.2026: Small corrections
  • 30.11.2025: Update Postgres Version 17, Upgrade of 13 as an example.
  • 26.12.2024: Reworkded and Integration into "FreeBSD as a Server" logic, TrueNAS removed.
  • 28.08.2024: Update PostgreSQL Version 16
  • 03.03.2024: Initial document

Requirements

Diagramm

The setup, including all optional possibilities, looks like this:

                   ┌──────────────────────────┐
                   │  FreeBSD                 │
                   │ ┌──────────────────────┐ │
                   │ │ jail/pgsql           │ │
LAN: 0.0.0.0:5432 ─│─│─► postgresql-server  │ │
                   │ │                      │ │
                   │ └──────────────────────┘ │
                   └──────────────────────────┘

Create jail

A separate jail is recommended if PostgreSQL should also serve as a central database server for other applications. Here we take pgsql as the jail name and the option allow_sysvipc is required in any case, even though PostgreSQL is installed inside another jail.

Set up jail

Either use bastile console pgsql to start a console in the created jail, or log in via SSH (if activated) with ssh USERNAME@IP or ssh USERNAME@HOSTNAME to then gain root rights with su.

Customise package source

Package sources should be customised, see separate article.

Install packages & activate services

Now update the package source with pkg update and then install the required packages: pkg install -y postgresql17-server. Activate services and start them automatically when starting the jail: service postgresql enable.

PostgreSQL

Initialise PostgreSQL with service postgresql initdb and start with service postgresql start.

Regular maintenance

Databases should be maintained regularly and preferably automatically:

  • vacuumdb is a utility for cleaning up a PostgreSQL database
  • reindexdb is a utility for rebuilding indices in a PostgreSQL database

Use mkdir /var/db/postgres/bin to create a directory for the following script:

cat > /var/db/postgres/bin/vacuum.sh <<'EOF'
#! /bin/sh
/usr/local/bin/vacuumdb -az 1> /dev/null 2> /dev/null
/usr/local/bin/reindexdb -a 1> /dev/null 2> /dev/null
EOF

and then run it every evening at midnight:

echo "# PostgreSQL house keeping" >> /etc/crontab
echo "0       0       *       *       *       postgres /var/db/postgres/bin/vacuum.sh" >> /etc/crontab

Network access

If it is a separate PostgreSQL jail, the other jails can use it to access the database(s), but must be permitted first:

echo "# WITH authentication, not trusting all others" >> /var/db/postgres/data17/pg_hba.conf
echo "host    all             all             all                     scram-sha-256" >> /var/db/postgres/data17/pg_hba.conf

This also means that the service must not be bound only to localhost, otherwise it cannot be reached via the network IP address.

sed -i '' "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" /var/db/postgres/data17/postgresql.conf

After that PostgreSQL needs to be restarted with service postgresql restart.

Create databases and users

su -m postgres -c "createuser -s DATENBANKBENUTZER --pwprompt" # Creates the user
su -m postgres -c "createdb -O DATENBANKBENUTZER -E Unicode -T template1 DATENBANKNAME" # Creates the database for this user

Backup

pg_dump -Fc -h "DATABASEHOST" -p "DATABASEPORT" -d "DATABASENAME" -U "DATABASEUSER" -f "DESTPATH"

Console

Link to the code on Github, I'm always happy about suggestions for improvement!

Voilá