PostgreSQL
Relational database server, PostgreSQL 18 from the FreeBSD packages.
jailops app install postgresqlAdd --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/tcppostgresThe SQL port; listens on localhost until listen_addresses is set.
Data on its own datasets
/var/db/postgresvolumevar-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:
/usr/sbin/chown postgres:postgres /var/db/postgres/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 ismsAn 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.