This post shows how to install PostgreSQL on OpenBSD and set it up. I wrote about the same theme last year, and now the version of PostgreSQL in OpenBSD has been upgraded 10.3 -> 11.2 as the OpenBSD has 6.3 -> 6.5. Happily, what I had to do was just to follow the same process.
- OS: OpenBSD 6.5
- DB: PostgreSQL 11.2
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.
# pkg_add postgresql-server
Switch user to
_postgresql which was created at the package installation above:
# # in order to avoid an error about permission: # cd /var/postgresql/ # su _postgresql
init_db to create a database cluster:
$ initdb -D /var/postgresql/data/ -U postgres --auth=md5 --pwprompt --encoding=UTF-8 --locale=xx_XX.UTF-8
--locale is up to your environment.
In my case, it’s
In order not to specify locale, run without
--encoding=UTF-8 --locale=xx_XX.UTF-8 instead:
- --encoding=UTF-8 --locale=xx_XX.UTF-8 + --no-locale
--pwprompt are for the sake of security.
The below will be printed:
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.
You will be asked:
Enter new superuser password: Enter it again:
This is the password for the root user aka
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
OK. Exit from
Start PostgreSQL server
Activate the daemon and start it:
# rcctl enable postgresql # rcctl start postgresql postgresql(ok)
Finished : )
psql is used as a terminal-based front-end to PostgreSQL.
Run as the root user and you will be asked for the password which denied above:
$ psql -U postgres
Here are DDL examples. Create database and role:
CREATE DATABASE %DATABASE%; CREATE ROLE %USER% WITH ENCRYPTED PASSWORD '%PASSWORD%'; GRANT ALL PRIVILEGES ON DATABASE %DATABASE% TO %USER%;
CREATE USER is available instead of
CREATE ROLE, which is just alias.
Another way with configuration about valid period for role and encoding for database:
CREATE ROLE %USER% LOGIN ENCRYPTED PASSWORD '%PASSWORD%' NOINHERIT VALID UNTIL 'infinity'; CREATE DATABASE %DATABASE% WITH ENCODING='UTF8' OWNER=%USER%;
Happy storing :)