PostgreSQL 14.2 on OpenBSD 7.1: Install
Tags: postgres, openbsd, database, tutorial
Published: 2022-04-24 00:54:05

## Summary

In [OpenBSD](https://www.openbsd.org/) [7.1](https://www.openbsd.org/71.html) release, [PostgreSQL](https://www.postgresql.org/) was upgraded to 14.2.
This post shows how to install it on the latest OpenBSD.

### Environment

- OS: OpenBSD 7.1
- DB: PostgreSQL 14.2

## Tutorial

### The overall

Each step is described as "One-by-one steps" later.

```console
$ doas pkg_add postgresql-server

$ doas su - _postgresql
$ mkdir /var/postgresql/data

$ # --locale below is optional
$ initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF-8 -W --locale=xx_XX.UTF-8
$ # will be asked password of the superuser, "postgres" above

$ exit

$ doas rcctl enable postgresql
$ doas rcctl start postgresql
```

### One-by-one steps

#### Install

Get the package from [ports](https://www.openbsd.org/faq/faq15.html) system:

```console
$ doas pkg_add postgresql-server
```

#### Init database

In order to avoid permissions error, switch user to `_postgresql` which was created above:

```console
$ doas su - _postgresql
```

Create the directory for [`init_db`](https://www.postgresql.org/docs/current/app-initdb.html):

```console
$ mkdir /var/postgresql/data
```

Run `initdb` to create a database cluster:

```console
$ initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF-8 -W --locale=xx_XX.UTF-8
```

Well, `-U postgres` (= `--user=...`) above is the superuser's name. The password will be asked when `-W` (= `--pwprompt`) is set. `-W` and `-A scram-sha-256` (= `--auth=...`) are for the sake of security. `--locale=...` is optional for one but en_US.UTF-8.

Also, the documentation (`/usr/local/share/doc/pkg-readmes/postgresql-server`) says:

> It is strongly advised that you do not work with the postgres dba account
> other than creating more users and/or databases or for administrative tasks.
> Use the PostgreSQL permission system to make sure that a database is only
> accessed by programs/users that have the right to do so.

As a result, the whole output was:

```console
The files belonging to this database system will be owned by user "_postgresql".
This user must also own the server process.

The database cluster will be initialized with locale "ja_JP.UTF-8".
initdb: could not find suitable text search configuration for locale "ja_JP.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

Enter new superuser password: 
Enter it again: 

fixing permissions on existing directory /var/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 20
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Tokyo
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/postgresql/data -l logfile start

```

Yay, successful. Let's `exit` from `_postgersql` user:

```console
$ exit
```

#### Start PostgreSQL server

Activate the daemon and start it:

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

## Conclusion

The postgresql server daemon is now activated and started. It works as RDBMS and listens to requests from clients.

The configuration files such as postgresql.conf and pg_hba.conf were generated automatically, and also [`psql`](https://www.postgresql.org/docs/current/static/app-psql.html) was installed.

### Configuration files

They are useful to [configure the server](https://www.postgresql.org/docs/current/runtime-config.html).

### psql

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:

```console
$ psql -U postgres
Password for user postgres:
```

You will be welcomed :)

```sql
psql (14.2)
Type "help" for help.

postgres=#
```

## Reference

- PostgreSQL 14 [Release Notes](https://www.postgresql.org/docs/release/14.0/)
- [How to upgrade OpenBSD 7.0 to 7.1](https://obsd.solutions/en/blog/2022/04/22/openbsd-upgrade-70-to-71/index.html)