loading...

Installing PostgreSQL 10.3 Server On OpenBSD 6.3

nabbisen profile image Heddi Nabbisen Updated on ・2 min read

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

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

pic
Editor guide
 

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

 

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!

 

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.

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😅

There are manual pages for it! ;-)

Yeah, I'll be diligent😆