PostgreSQL 10 On OpenBSD 6.3: Install

@nabbisen

Series


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 or feedbacks are welcome and appreciated.