TheLazy.Dev
Portfolio GitHub

Blog

postgres |openbsd |database |backend

PostgreSQL 14.5 on OpenBSD 7.2: Install

nabbisen

nabbisen

Posted on November 13, 2022

PostgreSQL 14.5 on OpenBSD 7.2: Install

Summary

In OpenBSD 7.2 release, PostgreSQL was updated to 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.

$ 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
Enter fullscreen mode Exit fullscreen mode

Tutorial

The one-by-one steps are as follows.

1. Install PostgreSQL Server

Get the package to be installed from ports system:

$ doas pkg_add postgresql-server
Enter fullscreen mode Exit fullscreen mode

The output was:

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
Enter fullscreen mode Exit fullscreen mode

2. Init database

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

$ doas su - _postgresql
Enter fullscreen mode Exit fullscreen mode

Run init_db to create a database cluster.
Well, /var/postgresql directory will be created automatically:

$ initdb -D /var/postgresql/data -U postgres \
      -W -A scram-sha-256 -E UTF-8 --locale=xx_XX.UTF-8
Enter fullscreen mode Exit fullscreen mode

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

The output started with:

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 fullscreen mode Exit fullscreen mode

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:

Enter new superuser password: 
Enter it again:
Enter fullscreen mode Exit fullscreen mode

The rest was:

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
Enter fullscreen mode Exit fullscreen mode

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

$ exit
Enter fullscreen mode Exit fullscreen mode

3. Start PostgreSQL server

Activate the daemon and start it:

$ doas rcctl enable postgresql
$ doas rcctl start postgresql
postgresql(ok)
Enter fullscreen mode Exit fullscreen mode

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 was installed.

Configuration files

They are useful to configure the server.

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:

$ psql -U postgres
Password for user postgres:
Enter fullscreen mode Exit fullscreen mode

You will be welcomed :)

psql (14.5)
Type "help" for help.

postgres=#
Enter fullscreen mode Exit fullscreen mode

Reference

💖 💪 🙅 🚩
nabbisen
nabbisen

Posted on November 13, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

PostgreSQL 14.5 on OpenBSD 7.2: Install
postgres PostgreSQL 14.5 on OpenBSD 7.2: Install

November 13, 2022