PostgreSQL 14.5 on OpenBSD 7.2: Install
Tags: postgres, openbsd, database, backend
Published: 2022-11-13 12:33:55

## Summary

In [OpenBSD](https://www.openbsd.org/) [7.2](https://www.openbsd.org/72.html) release, [PostgreSQL](https://www.postgresql.org/) was updated to [14.5](https://www.postgresql.org/docs/release/14.5/).
This post shows how to install it on the latest OpenBSD.

### Environment

- OS: OpenBSD 7.2
- Database: PostgreSQL 14.5

### Overview

Each step is described later.

```console
$ doas pkg_add postgresql-server

$ doas su - _postgresql

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

$ exit

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

## Tutorial

The one-by-one steps are as follows.

### 1. Install PostgreSQL Server

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

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

The output was:

```console
quirks-6.42 signed on 2022-10-30T18:56:25Z
postgresql-server-14.5:postgresql-client-14.5: ok
useradd: Warning: home directory `/var/postgresql' doesn't exist, and -m was not specified
postgresql-server-14.5: ok
The following new rcscripts were installed: /etc/rc.d/postgresql
See rcctl(8) for details.
New and changed readme(s):
	/usr/local/share/doc/pkg-readmes/postgresql-server
```

### 2. Init database

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

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

Run [`init_db`](https://www.postgresql.org/docs/current/app-initdb.html) to create a database cluster.
Well, `/var/postgresql` directory will be created automatically:

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

`-U postgres` (= `--user=...`) above is the superuser's name.

The output started with:

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

Then, when `-W` (= `--pwprompt`) is set, password will be asked. `-W` and `-A scram-sha-256` (= `--auth=...`) are for the sake of security.

As a reference, 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.

Well, `--locale=...` is optional for one but en_US.UTF-8.

Enter the password:

```console
Enter new superuser password: 
Enter it again: 
```

The rest was:

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

### 3. Start PostgreSQL server

Activate the daemon and start it:

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

### 4. Work with the server

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.5)
Type "help" for help.

postgres=#
```

## Reference

- [How to upgrade OpenBSD 7.1 to 7.2](https://obsd.solutions/en/blog/2022/10/21/openbsd-upgrade-71-to-72/)