DEV Community

loading...

Installing PostgreSQL 11.2 Server On OpenBSD 6.5

Heddi Nabbisen
An ICT designer/developer and a security monk with "a cool brain and a warm heart". OpenBSD/Rust etc. Interested in social issues and relationships. Founder of Scqr Inc.
Updated on ・2 min read

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

Tutorial

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

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

CREATEUSER is available instead of 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 : )

Discussion (2)

Collapse
cloud69420 profile image
cloud69420

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

Collapse
nabbisen profile image
Heddi Nabbisen Author

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😃