In the last post we’ve seen that it is quite easy to get started with PostgreSQL on OpenBSD when you install from the provided packages. The downside of this was, that not the latest version of PostgreSQL was provided, 14.2 instead of 14.5, which is the current version as of today. If you want to stay more up to date, and you really should, then you can always install PostgreSQL from source code for your own.
We’ll start again from a fresh installation but this time, the latest patches are already applied. As we need a group and a user to run PostgreSQL let’s create those:
openbsd-latest$ groupadd -g 1000 postgres
openbsd-latest$ useradd -g postgres -d /home/postgres/ -m -u 1000 postgres
As there is no sudo in OpenBSD we need to configure doas, that makes life a bit easier because we do not need to switch accounts for root related tasks:
openbsd-latest$ cat /etc/doas.conf
permit postgres as root
permit nopass keepenv postgres
Once we have that we need to download and extract the PostgreSQL source code. I’ll use wget for that:
openbsd-latest$ su - postgres
openbsd-latest$ doas pkg_add wget
quirks-5.5 signed on 2022-10-08T12:25:54Z
wget-1.21.3: ok
openbsd-latest$ wget https://ftp.postgresql.org/pub/source/v15rc2/postgresql-15rc2.tar.bz2
openbsd-latest$ bunzip2 postgresql-15rc2.tar.bz2
openbsd-latest$ tar xf postgresql-15rc2.tar
openbsd-latest$ cd postgresql-15rc2
Our Linux, the default configuration we use for the PostgreSQL source code looks like this:
./configure --prefix=${PGHOME} \
--exec-prefix=${PGHOME} \
--bindir=${PGHOME}/bin \
--libdir=${PGHOME}/lib \
--sysconfdir=${PGHOME}/etc \
--includedir=${PGHOME}/include \
--datarootdir=${PGHOME}/share \
--datadir=${PGHOME}/share \
--with-pgport=5432 \
--with-perl \
--with-python \
--with-openssl \
--with-pam \
--with-ldap \
--with-libxml \
--with-libxslt \
--with-segsize=${SEGSIZE} \
--with-blocksize=${BLOCKSIZE} \
--with-llvm LLVM_CONFIG='/usr/bin/llvm-config' \
--with-uuid=ossp \
--with-lz4 \
--with-zstd \
--with-gssapi \
--with-systemd \
--with-icu \
--with-system-tzdata=/usr/share/zoneinfo
What OS packages do we need to get a comparable build on OpenBSD? First of all, there is no PAM authentication on OpenBSD, so we can replace that with “–with-bsd-auth” and there is no systemd, no gssapi, this will give us this:
./configure --prefix=${PGHOME} \
--exec-prefix=${PGHOME} \
--bindir=${PGHOME}/bin \
--libdir=${PGHOME}/lib \
--sysconfdir=${PGHOME}/etc \
--includedir=${PGHOME}/include \
--datarootdir=${PGHOME}/share \
--datadir=${PGHOME}/share \
--with-pgport=5432 \
--with-perl \
--with-python \
--with-openssl \
--with-bsd-auth \
--with-ldap \
--with-libxml \
--with-libxslt \
--with-segsize=${SEGSIZE} \
--with-blocksize=${BLOCKSIZE} \
--with-llvm LLVM_CONFIG='/usr/bin/llvm-config' \
--with-uuid=ossp \
--with-lz4 \
--with-zstd \
--with-icu \
--with-system-tzdata=/usr/share/zoneinfo
Getting this to configure properly, we need these packages:
openbsd-latest$ doas pkg_add icu4c lz4 zstd python3 libxml libxslt openldap-client p5-ossp-UUID
Having that installed, configuration goes fine:
openbsd-latest$ PGHOME=/u01/app/postgres/product/15/db_0/
openbsd-latest$ SEGSIZE=2
openbsd-latest$ BLOCKSIZE=8
./configure --prefix=${PGHOME} \
--exec-prefix=${PGHOME} \
--bindir=${PGHOME}/bin \
--libdir=${PGHOME}/lib \
--sysconfdir=${PGHOME}/etc \
--includedir=${PGHOME}/include \
--datarootdir=${PGHOME}/share \
--datadir=${PGHOME}/share \
--with-pgport=5432 \
--with-perl \
--with-python \
--with-openssl \
--with-bsd-auth \
--with-ldap \
--with-libxml \
--with-libxslt \
--with-segsize=${SEGSIZE} \
--with-blocksize=${BLOCKSIZE} \
--with-llvm LLVM_CONFIG='/usr/bin/llvm-config' \
--with-uuid=ossp \
--with-lz4 \
--with-zstd \
--with-icu \
--with-system-tzdata=/usr/share/zoneinfo
I’ll not paste the whole output of this, but the end of the output should look like this:
checking build system type... x86_64-unknown-openbsd7.1
checking host system type... x86_64-unknown-openbsd7.1
checking which template to use... openbsd
...
config.status: linking src/backend/port/sysv_sema.c to src/backend/port/pg_sema.c
config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking src/include/port/openbsd.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.openbsd to src/Makefile.port
Ready to compile:
openbsd-latest$ make all
You must use GNU make to build PostgreSQL.
*** Error 1 in /home/postgres/postgresql-15rc2 (Makefile:47 'all': @IFS=':' ; for dir in $PATH; do for prog in gmake gnumake make; do if ...)
OK, this seems to be obvious, we need GNU Make for this:
openbsd-latest$ doas pkg_add gmake
quirks-5.5 signed on 2022-10-08T12:25:54Z
gmake-4.3: ok
openbsd-latest$ gmake all
...
gmake[1]: Entering directory '/home/postgres/postgresql-15rc2/config'
gmake[1]: Nothing to be done for 'all'.
gmake[1]: Leaving directory '/home/postgres/postgresql-15rc2/config'
Done, so install and add the extensions, but first create the directory structure. Here you have the choice, we’ll usually do it like this to make it easier for our service desk to work on both, Oracle and PostgreSQL:
openbsd-latest$ doas mkdir -p /u01/app/postgres/local
openbsd-latest$ doas mkdir /u02
openbsd-latest$ doas mkdir /u99
openbsd-latest$ doas chown -R postgres:postgres /u01/
openbsd-latest$ doas chown postgres:postgres /u02
openbsd-latest$ doas chown postgres:postgres /u99
openbsd-latest$ doas chmod 770 /u01/app/postgres
openbsd-latest$ doas chmod 770 /u02
openbsd-latest$ doas chmod 770 /u99
The last steps to do for bringing the PostgreSQL server and the extensions onto the system:
openbsd-latest$ doas ln -s /usr/local/bin/llvm-lto /usr/bin/llvm-lto
openbsd-latest$ gmake install
Using GNU make found at /usr/local/bin/gmake
/usr/local/bin/gmake -C ./src/backend generated-headers
gmake[1]: Entering directory '/home/postgres/postgresql-15rc2/src/backend'
/usr/local/bin/gmake -C catalog distprep generated-header-symlinks
openbsd-latest$ cd contrib/
openbsd-latest$ gmake install
gmake -C ../src/backend generated-headers
gmake[1]: Entering directory '/home/postgres/postgresql-15rc2/src/backend'
gmake -C catalog distprep generated-header-symlinks
gmake[2]: Entering directory '/home/postgres/postgresql-15rc2/src/backend/catalog'
...
cd '/u01/app/postgres/product/15/db_0//lib/bitcode' && /usr/bin/llvm-lto -thinlto -thinlto-action=thinlink -o ltree_plpython3.index.bc ltree_plpython3/ltree_plpython.bc
gmake[1]: Leaving directory '/home/postgres/postgresql-15rc2/contrib/ltree_plpython'
Done. Initializing the cluster is nothing special:
openbsd-latest$ /u01/app/postgres/product/15/db_0/bin/initdb -D /u02/pgdata/15/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".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /u02/pgdata/15/PG1 ... 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
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:
/u01/app/postgres/product/15/db_0/bin/pg_ctl -D /u02/pgdata/15/PG1 -l logfile start
The last step is to create a service, so PostgreSQL will start automatically when the system comes up. How does that work on OpenBSD. We’ve already seen in the last post that OpenBSD uses rc to manage system daemons. All we need to do is to create a script like this in /etc/rc.d (this is stolen from the PostgreSQL packages):
openbsd-latest$ cat /etc/rc.d/postgres
#!/bin/ksh
daemon="/u01/app/postgres/product/15/db_0/bin/pg_ctl"
daemon_flags="-D /u02/pgdata/15/PG1/ -w -l /u02/pgdata/15/PG1/log/postgresql.log"
daemon_user="postgres"
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
Enable and start the service, and that’s it:
openbsd-latest# chmod 755 /etc/rc.d/postgres
openbsd-latest# rcctl enable postgres
openbsd-latest# rcctl start postgres
openbsd-latest# /u01/app/postgres/product/15/db_0/bin/psql -U postgres postgres
psql (15rc2)
Type "help" for help.
postgres=#
Conclusion: We anyway prefer to install PostgreSQL from source code, because in that case we are independent from the package manager, whatever it is. In the case of OpenBSD it seems to be even more important, because the packages provided by the OS do not reflect the latest PostgreSQL minor version. Doing it that way is quite simple, once you figured out the required dependencies and how the init systems works.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)