DEV Community

Cover image for PostgreSQL on OpenBSD: Upgrade 12 to 13 with pg_dumpall
nabbisen
nabbisen

Posted on • Updated on • Originally published at obsd.solutions

PostgreSQL on OpenBSD: Upgrade 12 to 13 with pg_dumpall

* The cover image is originally by Andy_Bay and edited with great appreciation.


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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Upgrade the server, the package.

$ doas pkg_add -ui postgresql-server
$ # updated
Enter fullscreen mode Exit fullscreen mode

Renew /var/postgresql/data.

$ doas mv /var/postgresql/data /var/postgresql/data-12
$ doas su _postgresql -c "mkdir /var/postgresql/data"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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/"
Enter fullscreen mode Exit fullscreen mode

It is based on:


Start the new server.

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

Restore databases, objects such as tables and records.

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

Done 🙂

Top comments (2)

Collapse
 
autra profile image
Augustin Trancart

Nice write-up! Some remarks:

  • pg_dumpall is handy but doesn't scale because it does not know how to make custom format backup. That's why I usually prefer to use it only for global objects (--globals-only) when the dbs are big (and I cannot use pg_upgrade for some reason)
  • pg_upgrade is waaaayy faster indeed. Like orders of magnitude faster on some cluster

Btw can you have several versions of postgresql installed together on OpenBSD? It is handy to still allow read in the old cluster while you are migrating.

Collapse
 
nabbisen profile image
nabbisen • Edited

Hi, @autra , merci for your comments and sharing your precious knowledge😃
Actually, I didn't know about the difference between pg_dumpall and pg_upgrade.

can you have several versions of postgresql installed together on OpenBSD?

Unfortunately, I don't think so with OpenBSD Ports packages system. It's because it's unable to choose the version in installing PostgreSQL.
You seemingly have to install the old version by building it from source code. There might be another way but, at least, I don't know. I'm sorry.

Besides, as to php (+ php-fpm), it's able to choose the version which is installed and also install multiple versions at the same time on OpenBSD. (Well, /etc/php-fpm.conf is, however, shared by php-fpm daemons by default.)