PostgreSQL

Relational database server, PostgreSQL 18 from the FreeBSD packages.

DatabaseTested on FreeBSD18.6PostgreSQL

jailops app install postgresql

Add --dry-run to see every step first. From the console: open Apps, pick PostgreSQL and press Install; the dialog takes the jail name, the network and the storage, and Preview steps shows the plan before anything runs. See the console.

About

PostgreSQL is a relational database with transactions, replication and a large extension ecosystem. This definition installs the server package of the current major release, initialises a cluster under /var/db/postgres/data18 with UTF-8 and the C collation (the port's defaults) and starts it.

The cluster listens on localhost only until listen_addresses in postgresql.conf and the client rules in pg_hba.conf are changed. The port logs to syslog.

What you get

Ports

  • 5432/tcp postgresThe SQL port; listens on localhost until listen_addresses is set.

Data on its own datasets

  • /var/db/postgres volume var-db-postgresThe cluster (data18) and the home of the postgres user.

Configuration

  • /var/db/postgres/data18/postgresql.confServer settings, including listen_addresses and logging. Restart: service postgresql restart.
  • /var/db/postgres/data18/pg_hba.confWho may connect from where, and how they authenticate. Restart: service postgresql restart.

Logs

  • /var/log/messagesThe port logs to syslog by default (log_destination in postgresql.conf).

Health check

Command: pg_isready -U postgres succeeds in the jail, retried for up to 60 seconds.

Services

  • postgresql: enabled and started
  • System V IPC of the jail's own (sysvipc), which the server needs.

Setup, once, before the first start

Two commands, run in the jail as argv, without a shell:

  1. /usr/sbin/chown postgres:postgres /var/db/postgres
  2. /usr/sbin/service postgresql initdb

After the installation

The notes of the definition, shown after the installation too.

Connect from inside the jail with: jailops exec NAME -- su -l postgres -c psql
The jail accepts no connection from outside until you allow one:
jailops app allow NAME --from jail:web makes a role and a database for the jail web, lets its address in and prints the connection string once.

How it connects

jailops app allow postgresql from @web

This makes a role and a database named web in the jail, adds a line for the jail web to pg_hba.conf, opens port 5432/tcp to it and prints the connection string once. jailops app deny postgresql from @web takes it back; --db NAME picks another database name and --rotate makes a new password.

Used by isms

isms keeps its data here. One command makes its side:

jailops app allow postgresql --from @isms --db isms

An installed app is closed: nothing reaches it, not even its own network, until you allow a source. A source is a jail (@web), a jail of another project (@project/web), an address set, an address or prefix, or host. jailops app access postgresql lists who may connect. The names here are jail names: jailops app install names the jail after the app unless --name says otherwise.