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 :)
Top comments (6)
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!
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😆