PostgreSQL 11.2 on OpenBSD 6.5: Install
Tags: postgres, database, openbsd
Published: 2019-05-07 01:51:53

---
title: PostgreSQL 11.2 on OpenBSD 6.5: Install
published: true
description: This post shows how to install PostgreSQL 11.2 on OpenBSD 6.5 and set it up.
tags: postgresql, database, openbsd
series: Postgres on OpenBSD
canonical_url: https://obsd.solutions/en/blog/2019/05/07/postgresql-11-on-openbsd-65-install/index.html
---

This post shows how to install [PostgreSQL](https://www.postgresql.org/) on [OpenBSD](https://www.openbsd.org/) and set it up.
I wrote about the same theme last year, and now the version of PostgreSQL in OpenBSD has been upgraded 10.3 -> 11.2 as the OpenBSD has 6.3 -> 6.5.
Happily, what I had to do was just to follow the same process.

<center>✿ ✿ ✿</center>

## Environment

- OS: OpenBSD 6.5
- DB: PostgreSQL 11.2

## \* legend

In code areas, the leading `#` means execution by superuser; It is equal to using `doas` command (as root), while the leading `$` means by general users.

## Tutorial

### Install package

```console
# pkg_add postgresql-server
```

### Init database

Switch user to `_postgresql` which was created at the package installation above:

```console
# # in order to avoid an error about permission:
# cd /var/postgresql/

# su _postgresql
```

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

```console
$ initdb -D /var/postgresql/data/ -U postgres --auth=md5 --pwprompt --encoding=UTF-8 --locale=xx_XX.UTF-8
```

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

Besides, both `--auth=md5` and `--pwprompt` are for the sake of security.

The below will be printed:

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

You will be asked:

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

This is the password for the root user aka `postgres`.

Then printed:

```console
creating directory /var/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 30
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
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

```

OK. Exit from `_postgersql` user:

```console
$ exit
```

### Start PostgreSQL server

Activate the daemon and start it:

```console
# rcctl enable postgresql
# rcctl start postgresql
postgresql(ok)
```

Finished : )

### After Installation

[`psql`](https://www.postgresql.org/docs/11/static/app-psql.html) is used as a terminal-based front-end to PostgreSQL.
Run as the root user and you will be asked for the password which denied above:

```console
$ psql -U postgres
```

Here are DDL examples.
Create [database](https://www.postgresql.org/docs/11/sql-createdatabase.html) and [role](https://www.postgresql.org/docs/11/sql-createrole.html):

```sql
CREATE DATABASE %DATABASE%;
CREATE ROLE %USER% WITH ENCRYPTED PASSWORD '%PASSWORD%';
GRANT ALL PRIVILEGES ON DATABASE %DATABASE% TO %USER%;
```

[`CREATE USER`](https://www.postgresql.org/docs/11/sql-createuser.html) is available instead of `CREATE ROLE`, which is just alias.

Another way with configuration about valid period for role and encoding for database:

```sql
CREATE ROLE %USER% LOGIN ENCRYPTED PASSWORD '%PASSWORD%' NOINHERIT VALID UNTIL 'infinity';
CREATE DATABASE %DATABASE% WITH ENCODING='UTF8' OWNER=%USER%;
```

To exit:

```sql
\q
```

<center>✿ ✿ ✿</center>

Happy storaging : )