* 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
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:
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 🙂
Top comments (2)
Nice write-up! Some remarks:
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.
Hi, @autra , merci for your comments and sharing your precious knowledge😃
Actually, I didn't know about the difference between
pg_dumpallandpg_upgrade.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.)