PostgreSQL on OpenBSD: Upgrade 12 to 13 with pg_dumpall
Tags: postgres, database, migration, openbsd
Published: 2021-05-08 03:49:32

<small>\* The cover image is originally by [Andy_Bay](https://pixabay.com/users/andy_bay-1006510) and edited with great appreciation.</small>

---

## Summary

In upgrading [OpenBSD](https://www.openbsd.org/) from [6.8](https://www.openbsd.org/68.html) to [6.9](https://www.openbsd.org/69.html), I had to upgrade [PostgreSQL](https://www.postgresql.org/) server ([12](https://www.postgresql.org/docs/12/index.html).6 to [13](https://www.postgresql.org/docs/13/index.html).2), [as written in OpenBSD's Upgrade Guide](https://www.openbsd.org/faq/upgrade69.html#SpecialPackages).
Here is how I carried it out due to the official readme of `postgresql-server` package:

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

[Alternatively](https://obsd.solutions/en/blog/2023/05/03/postgresql-on-openbsd-upgrade-14-to-15/index.html), using [`pg_upgrade`](https://www.postgresql.org/docs/12/pgupgrade.html) might make the migration process faster as it is necessary to install the package in addition.

## Tutorial

First, check the server is running.

```console
$ doas rcctl check postgresql
postgresql(ok)
```

Run [`pg_dumpall`](https://www.postgresql.org/docs/12/app-pg-dumpall.html).

```console
$ 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](https://fishshell.com/).

Stop the server.

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

Upgrade the server, the package.

```console
$ doas pkg_add -ui postgresql-server
$ # updated
```

Renew `/var/postgresql/data`.

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

Run [`initdb`](https://www.postgresql.org/docs/13/app-initdb.html).

```console
$ # `--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.

```console
$ # 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:

{% post nabbisen/postgresql-tls-connection-k1h %}

---

Start the new server.

```console
$ 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.

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

Done 🙂