nabbisen

Posted on May 7, 2019 • Edited on Jan 14, 2023 • Originally published at obsd.solutions

PostgreSQL 11.2 on OpenBSD 6.5: Install

#postgres #database #openbsd

This post shows how to install PostgreSQL on OpenBSD 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.

✿ ✿ ✿

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

# pkg_add postgresql-server

Init database

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

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

# su _postgresql

Then run init_db to create a database cluster:

$ 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:

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

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:

Enter new superuser password: 
Enter it again:

This is the password for the root user aka postgres.

Then printed:

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:

$ exit

Start PostgreSQL server

Activate the daemon and start it:

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

Finished : )

After Installation

psql 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:

$ psql -U postgres

Here are DDL examples.
Create database and role:

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

CREATE USER is available instead of CREATE ROLE, which is just alias.

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

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