PostgreSQL 12.2 on OpenBSD 6.7: Install

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

---

OpenBSD 6.7 was released last month and PostgreSQL was upgraded from 11.7 to 12.2 then.
The installation process of PostgreSQL server in OBSD 6.7 is almost the same to that in 6.5.

Environment

• OS: OpenBSD 6.7
• DB: PostgreSQL 12.2

Summary

The overall steps are below:

$ doas pkg_add postgresql-server

$ doas su _postgresql
$ cd /var/postgresql/
$ # changed: `--auth=md5` -> `--auth=scram-sha-256`
$ initdb -D /var/postgresql/data/ -U postgres --auth=scram-sha-256 --pwprompt --encoding=UTF-8 --locale=xx_XX.UTF-8
$ # password of superuser = postgres is asked
$ exit

$ doas rcctl enable postgresql
$ doas rcctl start postgresql

Each of them will be described at the rest of this post as tutorial.

Tutorial

Install

Get the application package from ports system:

$ doas pkg_add postgresql-server

Init database

Switch user to _postgresql which was created at the package installation above in order to avoid error on permission:

$ doas su _postgresql
$ cd /var/postgresql/

Run init_db to create a database cluster:

$ initdb -D /var/postgresql/data/ -U postgres --auth=scram-sha-256 --pwprompt --encoding=UTF-8 --locale=xx_XX.UTF-8

Here, --auth=scram-sha-256 and --pwprompt are for the sake of security. --auth=scram-sha-256 can be relaced by --auth=md5.
--locale is up to your environment. In my case, it's ja_JP.UTF-8.
In order not to specify locale, run without --encoding=UTF-8 --locale=xx_XX.UTF-8 instead:

- --encoding=UTF-8 --locale=xx_XX.UTF-8
+ --no-locale

The password of the superuser aka postgres is asked:

Enter new superuser password: 
Enter it again: 

After the process is completed, exit from _postgersql user:

$ exit

Start PostgreSQL server

Activate the daemon and start it:

$ doas rcctl enable postgresql
$ doas rcctl start postgresql
postgresql(ok)

Finished.

After Installation

The config files such as postgresql.conf and pg_hba.conf were created.
They are useful to configure your servers.

psql was also installed. It is used as a terminal-based front-end to PostgreSQL.
By using the password asked above, it's able to connect to the server:

$ psql -U postgres

Thank you for your reading :)