loading...

Installing PostgreSQL 11.2 Server On OpenBSD 6.5

nabbisen profile image Heddi Nabbisen 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

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%;

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%;

To exit:

\q
✿ ✿ ✿

Happy storaging : )

Posted on by:

nabbisen profile

Heddi Nabbisen

@nabbisen

An ICT designer/developer and a security monk. "With a cool brain and a warm heart", I am challenging unsolved problems in our society. I use OpenBSD/Rust/etc.

Discussion

markdown guide