PostgreSQL on OpenBSD: Upgrade 12 to 13 with pg_dumpall

created
( modified )
@nabbisen

Table of contents

Summary

In upgrading OpenBSD from 6.8 to 6.9, I had to upgrade PostgreSQL server (12.6 to 13.2), as written in OpenBSD’s Upgrade Guide. Here is how I carried it out due to the official readme of postgresql-server package:

$ nvim -R /usr/local/share/doc/pkg-readmes/postgresql-server

Alternatively, using pg_upgrade might make the migration process faster as it is necessary to install the package in addition.

Tutorial

First, check the server is running.

$ doas rcctl check postgresql
postgresql(ok)

Run pg_dumpall.

$ doas su _postgresql -c "cd /var/postgresql \
    && pg_dumpall -U postgres > /var/postgresql/full.sqldump.bkYYMMDD"
$ # pg superuser's password is required times of the number of tables

Besides, “YYMMDD” here may be replaced with the actual date which is got, for example, with date +"%y%m%d" in Fish shell.

Stop the server.

$ doas rcctl stop postgresql
postgresql(ok)
$ # check the server is stopped
$ doas rcctl check postgresql
postgresql(failed)

Upgrade the server, the package.

$ doas pkg_add -ui postgresql-server
$ # updated

Renew /var/postgresql/data.

$ doas mv /var/postgresql/data /var/postgresql/data-12
$ doas su _postgresql -c "mkdir /var/postgresql/data"

Run initdb.

$ # `--locale` option here can be omitted
$ doas su _postgresql -c "cd /var/postgresql && \
    initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W \
        --locale=xx_XX.UTF-8"
$ # pg superuser's password is required

Optionally, configure the server again as needed.

$ # in case of using tls/ssl connection
$ # - set `listen_addresses` and `ssl` option
$ doas nvim "/var/postgresql/data/postgresql.conf"
$ # - add `hostssl` authentication
$ doas nvim "/var/postgresql/data/pg_hba.conf"
$ # - create certificate if not exists
$ doas su _postgresql -c \
    "cp /var/postgresql/data-12/server.{crt,key} /var/postgresql/data/"

It is based on this post


Start the new server.

$ doas rcctl start postgresql
postgresql(ok)
$ # check the server is started and running
$ doas rcctl check postgresql
postgresql(ok)

Restore databases, objects such as tables and records.

$ doas su _postgresql -c "cd /var/postgresql && \
    psql -U postgres < /var/postgresql/full.sqldump.bkYYMMDD"
$ # restored

Done 🙂


Comments or feedbacks are welcomed and appreciated.