PostgreSQL 10.3 on OpenBSD 6.3: Install

This post shows how to install PostgreSQL on OpenBSD and set it up.

✿ ✿ ✿

Environment

• OS: OpenBSD 6.3
• DB: PostgreSQL 10.3

✿ ✿ ✿

Procedure

* note: 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

# # change user
# su _postgresql

$ # Both `--auth=md5` and `--pwprompt` are for the sake of security. 
$ initdb -D /var/postgresql/data/ -U postgres --auth=md5 --pwprompt --encoding=UTF-8 --locale=ja_JP.UTF-8
$ # or without locale: `initdb -D /var/postgresql/data/ -U postgres --auth=md5 --pwprompt --no-locale`

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.
postgresql
Enter new superuser password: 
Enter it again: 

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

$ exit

Start PostgreSQL server

# rcctl enable postgresql
# rcctl start postgresql
postgresql(ok)

(Finished!) Now DDL and DML are available

"psql is a terminal-based front-end to PostgreSQL" (from doc):

$ psql -U postgres

DDL examples:

CREATE DATABASE {% database-name %};
CREATE USER {% user-name %} WITH PASSWORD '{% user-password %}';
GRANT ALL PRIVILEGES ON DATABASE {% database-name %} TO {% user-name %};

-- exit
\q

✿ ✿ ✿

Thank you very much for your reading.
I'm happy if this post helps someone in some way :)

Comments

[tux0r]

I like seeing fellow OpenBSD users here. Thank you! (Haha, you wrote "dabatase"...)

FWIW, for NetBSD, Solaris and other pkgsrc-relevant systems, the main difference should be to replace "pkg_add" by "pkgin in", on FreeBSD it would be "pkg install".

[nabbisen]

tux0r, thank you so much for your kind comments!
This post is one of my preparation to write a post about my personal big challenge: creating OpenBSD vm on GCP : )
Oh, I modified my typo. Thank you, again!

I know little about "pkgsrc-relevant systems" and its history, so I'll have time to study them : )
I have been fond of using OpenBSD and FreeBSD for 3-4 years, because, in my feelings, their filesystems seem pretty simple to beauty.
Although I am still far from matured on them, I've been trying to get more familiar with them recently!

[tux0r]

in my feelings, their filesystems seem pretty simple to beauty.

Depends. FreeBSD has (some kind of) ZFS - which is really complicated. But they also have a recent version of the Berkeley filesystem.

[nabbisen]

I see. Depends.
You taught me and I knew better.
Thank you😉
I just remembered I have used DragonFlyBSD but didn't understand HAMMER file system at all😅

[tux0r]

There are manual pages for it! ;-)

[nabbisen]

Yeah, I'll be diligent😆