loading...

Installing PostgreSQL 11.2 Server On OpenBSD 6.5

nabbisen profile image Heddi Nabbisen Updated on ・2 min read

Installing Postgresql Server On OpenBSD (3 Part Series)

1) Installing PostgreSQL 10.3 Server On OpenBSD 6.3 2) Installing PostgreSQL 11.2 Server On OpenBSD 6.5 3) Installing PostgreSQL 12.2 Server On OpenBSD 6.7

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

Installing Postgresql Server On OpenBSD (3 Part Series)

1) Installing PostgreSQL 10.3 Server On OpenBSD 6.3 2) Installing PostgreSQL 11.2 Server On OpenBSD 6.5 3) Installing PostgreSQL 12.2 Server On OpenBSD 6.7

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