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.
Environment
- OS: OpenBSD 6.5
- DB: PostgreSQL 11.2
* Legend
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.
Tutorial
Install package
# pkg_add postgresql-server
Init database
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
Then run 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 ja_JP.UTF-8.
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
Besides, both --auth=md5 and --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 postgres.
Then printed:
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 _postgersql user:
$ exit
Start PostgreSQL server
Activate the daemon and start it:
# rcctl enable postgresql
# rcctl start postgresql
postgresql(ok)
Finished : )
After Installation
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%;
To exit:
\q
Happy storing :)