Almost all of our customers run PostgreSQL on one of the distributions of Linux, either installed from packages or from source code. But there is not only Linux, there a various flavors of BSD you can also run PostgreSQL on. So, if you want to have something more UNIX like, OpenBSD can be an option. This flavor of BSD is fully open source, like FreeBSD or NetBSD, and the projects main focus is on security, correctness and cryptography. In fact, OpenSSH is coming from OpenBSD. So why not use it for running PostgreSQL?

I’ll not describe the installation of OpenBSD as it is a really simple text based process. You can mainly go with the default options and after a few minutes the OS is up and running. I’ve done it using KVM and if you also do it virtualized, make sure that you enable the OpenSSH daemon during the installation so you can connect remotely. Working in the the KVM console is not much fun.

As always on a fresh installation, one of the first steps you should do is to install the latest patches. On OpenBSD this is done with syspatch:

openbsd-latest$ syspatch
Get/Verify syspatch71-001_wifi.tgz 100% |***********************************************************************************|  4423 KB    00:02    
Installing patch 001_wifi
syspatch: updated itself, run it again to install missing patches
Relinking to create unique kernel... done; reboot to load the new kernel
openbsd-latest$ syspatch
Get/Verify syspatch71-002_ipsec.tgz 100% |**********************************************************************************| 93708       00:00    
Installing patch 002_ipsec
Get/Verify syspatch71-003_kqueue.tgz 100% |*********************************************************************************|   126 KB    00:00    
Installing patch 003_kqueue
Get/Verify syspatch71-004_asn1.tgz 100% |***********************************************************************************| 15815 KB    00:04    
Installing patch 004_asn1
Get/Verify syspatch71-005_pppoe.tgz 100% |**********************************************************************************| 61252       00:00    
Installing patch 005_pppoe
Get/Verify syspatch71-006_xserver... 100% |*********************************************************************************|  4386 KB    00:02    
Installing patch 006_xserver
Get/Verify syspatch71-007_cron.tgz 100% |***********************************************************************************| 21179       00:00    
Installing patch 007_cron
Get/Verify syspatch71-008_bgpd.tgz 100% |***********************************************************************************|   199 KB    00:00    
Installing patch 008_bgpd
Get/Verify syspatch71-009_zlib.tgz 100% |***********************************************************************************|   491 KB    00:00    
Installing patch 009_zlib
Get/Verify syspatch71-010_expat.tgz 100% |**********************************************************************************|   649 KB    00:00    
Installing patch 010_expat
Get/Verify syspatch71-011_smtpd.tgz 100% |**********************************************************************************|   223 KB    00:00    
Installing patch 011_smtpd
Relinking to create unique kernel... done; reboot to load the new kernel
Errata can be reviewed under /var/syspatch

Here I’ve done it twice because the first execution updated syspatch itself and the second one applied the patches. I’ll skip the reboot mentioned at the end of the output, as it is not important for the scope of this post.

First, let’s check if OpenBSD comes with pre-packed version of PostgreSQL. By default, packages (or ports) are fetched from here:

openbsd-latest$ cat /etc/installurl
https://cdn.openbsd.org/pub/OpenBSD

Searching for PostgreSQL packages like this does not give any result:

openbsd-latest$ pkg_info PostgreSQL
openbsd-latest$ pkg_info postgresql

A more convenient way to search for packages is to use pkglocate, but this is not installed by default:

openbsd-latest$ which pkglocate
which: pkglocate: Command not found.
openbsd-latest$ pkg_add pkglocatedb

Once it is there you get impressive number of results:

openbsd-latest# pkglocate postgres | wc -l 
    7205

Stripping that down a bit gives a good overview on what is available:

openbsd-latest$ pkglocate postgresql | awk -F ':' '{print $1}' | uniq | sort
akonadi-21.12.2
ansible-5.5.0
architect-1.0.6p9
augeas-1.12.0p1
autoconf-archive-2021.02.19
bacula-pgsql-11.0.6
borgmatic-1.5.23
citus-10.2.3
collectd-5.8.1p0
collectd-pgsql-5.8.1p0
dokuwiki-2020.07.29p0
dovecot-postgresql-2.3.18v0
exim-4.95p1-mysql-postgresql-ldap
exim-4.95p1-postgresql
filebeat-7.10.2p0
freebasic-1.05.0p1
freeradius-pgsql-3.0.25
gerbil-0.16p0
google-cloud-sdk-379.0.0
grafana-7.5.11
kamailio-postgresql-5.4.5
kdb-3.2.0p2
kea-2.0.2-postgresql
kexi-3.2.0p4
kibana-7.10.0
librenms-22.3.0p0v0
libreoffice-7.3.2.2v0
logstash-7.10.0v0
lua52dbi-pgsql-0.6p1
lua53dbi-pgsql-0.6p1
luadbi-pgsql-0.6p1
mariadb-tests-10.6.7p0v1
metricbeat-7.10.2p0
moka-icon-theme-5.4.0p1
nedit-5.7p1v0
netbeans-12.6
openfire-4.2.3p1
orthanc-plugin-postgresql-2.0p2
ossec-hids-3.0.0p2
p5-CGI-Session-4.48p0
paper-icon-theme-1.5.0p1
perdition-pgsql-2.2p3
pg_sqlite_fdw-2.1.1
pg_statsinfo-13.0
pgfouine-1.2p11
pgpool-II-3.7.18p0
pgrouting-3.3.0
pgtap-1.1.0
pmacct-1.7.7-postgresql
pointcloud-1.2.1p0
postgis-3.2.1p0
postgresql-client-14.2
postgresql-contrib-14.2
postgresql-docs-14.2
postgresql-odbc-13.02.0000
postgresql-pg_upgrade-14.2
postgresql-pllua-2.0.10
postgresql-plpython-14.2
postgresql-plr-8.4.1
postgresql-previous-13.5p0
postgresql-server-14.2p1
postgresql_autodoc-1.40p1
pure-ftpd-1.0.49p2-postgresql
pure-ftpd-1.0.49p2-postgresql-virtual_chroot
py3-alembic-1.7.7
py3-jedi-0.18.1
py3-peewee-3.14.8
py3-sqlalchemy-1.4.31p0
pygeoapi-0.12.0
qt5-postgresql-5.15.2
qt6-postgresql-6.0.4p4
redland-1.0.17p9
redland-pgsql-1.0.17p9
repmgr-5.2.1
resiprocate-repro-1.12.0p3
rstudio-1.3.959p5
ruby27-pg-1.3.4
ruby27-sequel-5.54.0
ruby30-pg-1.3.4
ruby30-pygments.rb-0.6.3p1v0
ruby30-sequel-5.54.0
ruby31-pg-1.3.4
ruby31-sequel-5.54.0
sogo-5.5.1
sqlmap-1.5.7p0
timescaledb-2.6.0
traccar-4.15p0
trytond-5.0.46p0
trytond-5.2.20p1
unifi-5.14.23p12
unifi-6.0.45p10
unifi-7.0.25
xemacs-21.4.22p32
xemacs-21.4.22p32-mule
xxdiff-4.0.1.20170623
zabbix-proxy-6.0.3-mysql
zabbix-proxy-6.0.3-pgsql
zabbix-proxy-6.0.3-sqlite3
zabbix-server-6.0.3-mysql
zabbix-server-6.0.3-pgsql
zsh-5.8.1

and You’ll see the usual suspects like the server and contrib packages, but also Citus, Timescale, many other packages you might want to install. The PostgreSQL version seems to be 14.2, which is not the latest one, but there is nothing below PostgreSQL 14, which is good as this is the current major version as of today. Let’s install the server package and check how it looks like on the system afterwards:

openbsd-latest$ pkg_add postgresql-server-14.2p1
quirks-5.5 signed on 2022-10-06T09:54:45Z
postgresql-server-14.2p1:xz-5.2.5p1: ok
postgresql-server-14.2p1:libiconv-1.16p0: ok
postgresql-server-14.2p1:libxml-2.9.13p1: ok
postgresql-server-14.2p1:postgresql-client-14.2: ok
useradd: Warning: home directory `/var/postgresql' doesn't exist, and -m was not specified
postgresql-server-14.2p1: ok
Running tags: ok
The following new rcscripts were installed: /etc/rc.d/postgresql
See rcctl(8) for details.
New and changed readme(s):
        /usr/local/share/doc/pkg-readmes/postgresql-server

This also gave us the client package, which makes sense as we want to use psql. The readme, which is mentioned at the end contains all the instructions to get started with PostgreSQL on OpenBSD. There is even a tuning section inside, and also the procedure how to upgrade to a new major version.

For getting PostgreSQL up and running, this is the procedure (notice the “_” at the beginning of the username):

openbsd-latest$ su - _postgresql
openbsd-latest$ id
uid=503(_postgresql) gid=503(_postgresql) groups=503(_postgresql)
openbsd-latest$ mkdir /var/postgresql/data
openbsd-latest$ initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W
The files belonging to this database system will be owned by user "_postgresql".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password: 
Enter it again: 

fixing permissions on existing directory /var/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 20
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Berlin
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/postgresql/data -l logfile start

Back to the initial output of the package installation: The output also mentioned that new rcscripts script were created:

openbsd-latest$ ls -l /etc/rc.d/postgresql
-rwxr-xr-x  1 root  bin  522 Apr  8  2022 /etc/rc.d/postgresql
openbsd-latest$ cat /etc/rc.d/postgresql
#!/bin/ksh

daemon="/usr/local/bin/pg_ctl"
daemon_flags="-D /var/postgresql/data -w -l /var/postgresql/logfile"
daemon_user="_postgresql"
daemon_timeout=300

. /etc/rc.d/rc.subr

rc_usercheck=NO

rc_check() {
        ${rcexec} "${daemon} status ${daemon_flags}"
}

rc_reload() {
        ${rcexec} "${daemon} reload ${daemon_flags}"
}

rc_start() {
        ${rcexec} "${daemon} start ${daemon_flags}"
}

rc_stop() {
        ${rcexec} "${daemon} stop ${daemon_flags} -m fast" || \
                ${rcexec} "${daemon} stop ${daemon_flags} -m immediate"
}

rc_cmd $1

Enabling and starting the service is just a matter of:

openbsd-latest$ rcctl enable postgresql                                                                                                            
openbsd-latest$ rcctl start postgresql  
postgresql(ok)

From now on it is the usual PostgreSQL stuff:

openbsd-latest# su - _postgresql 
openbsd-latest$ psql -U postgres
Password for user postgres: 
psql (14.2)
Type "help" for help.

postgres=#

Conclusion: Getting started with PostgreSQL on OpenBSD is quite easy. The minor version is not the latest one and I don’t know how fast the packages are updated usually. Given that the current minor version for 14 is 14.5, it seems the updates are not too frequently. If you want to avoid that, then you need to go for the installation from source code. This is the topic for the next post.

Post Views: 2,175
Thumbnail [60x60]
by
Daniel Westermann