DEV Community

nabbisen
nabbisen

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

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

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

Start PostgreSQL server

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

(Finished!) Now DDL and DML are available

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

$ psql -U postgres
Enter fullscreen mode Exit fullscreen mode

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

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

Top comments (6)

Collapse
 
tux0r profile image
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".

Collapse
 
nabbisen profile image
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!

Collapse
 
tux0r profile image
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.

Thread Thread
 
nabbisen profile image
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😅

Thread Thread
 
tux0r profile image
tux0r

There are manual pages for it! ;-)

Thread Thread
 
nabbisen profile image
nabbisen

Yeah, I'll be diligent😆