DEV Community

nabbisen
nabbisen

Posted on • Updated on • Originally published at obsd.solutions

PostgreSQL 11.2 on OpenBSD 6.5: Install

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

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

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

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

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

You will be asked:

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

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

Enter fullscreen mode Exit fullscreen mode

OK. Exit from _postgersql user:

$ exit
Enter fullscreen mode Exit fullscreen mode

Start PostgreSQL server

Activate the daemon and start it:

# rcctl enable postgresql
# rcctl start postgresql
postgresql(ok)
Enter fullscreen mode Exit fullscreen mode

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

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

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

To exit:

\q
Enter fullscreen mode Exit fullscreen mode
✿ ✿ ✿

Happy storaging : )

Top comments (2)

Collapse
 
bestfriend101 profile image
BestFriend101

OpenBSD and PostgreSQL, two of my favorite technologies :) Great post, thanks for sharing!

Collapse
 
nabbisen profile image
nabbisen

Hi, cloud69420,
thank you for your comments. I'm happy to hear it😊
Well, PostgreSQL was updated to 13 in OpenBSD 6.9 which was released this month😃