PostgreSQL 13.5 on OpenBSD 7.0: Install
Tags: postgres, openbsd, database, tutorial
Published: 2022-02-15 00:13:29

## Summary

[OpenBSD](https://www.openbsd.org/) [7.0](https://www.openbsd.org/70.html) was released and [PostgreSQL](https://www.postgresql.org/) was upgraded to 13.4 then. In addition, it is now 13.5.
The installation process of PostgreSQL server in OBSD 7.0 is almost the same to that in the earlier.

### Environment

- OS: OpenBSD 7.0
- DB: PostgreSQL 13.5

## Tutorial

### The overall

Each step will be described at the rest of this post.

```console
$ doas pkg_add postgresql-server

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

$ # changed: `--auth=md5` -> `--auth=scram-sha-256`
$ initdb -D /var/postgresql/data/ -U postgres -A scram-sha-256 -W -E UTF-8 --locale=xx_XX.UTF-8
$ # password of superuser (= "postgres") will be asked

$ exit

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

### One-by-one steps

#### Install

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

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

The output was:

```console
quirks-4.54 signed on 2022-02-12T18:54:43Z
postgresql-server-13.5:libxml-2.9.12: ok
postgresql-server-13.5:postgresql-client-13.5: ok
useradd: Warning: home directory `/var/postgresql' doesn't exist, and -m was not specified
postgresql-server-13.5: ok
Running tags: 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
```

#### Init database

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

```console
$ doas su - _postgresql
$ cd /var/postgresql/
$ mkdir /var/postgresql/data
```

Run [`init_db`](https://www.postgresql.org/docs/current/app-initdb.html) to create a database cluster:

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

Here, `-U postgres` (= `--user=...`) 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.

Besides, 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 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:

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

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, success. 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)
```

Finished.

## Conclusion

Through the installation above, the postgresql server daemon was activated and started. It works as RDBMS and listens to requests from clients.

Moreover, configuration files, such as postgresql.conf and pg_hba.conf, were created, 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 (13.5)
Type "help" for help.

postgres=#
```

Thank you for your reading :)