This content originally appeared on DEV Community and was authored by Heddi Nabbisen
Summary
In OpenBSD 7.1 release, PostgreSQL was upgraded to 14.2.
This post shows how to install it on the latest OpenBSD.
Environment
- OS: OpenBSD 7.1
- DB: PostgreSQL 14.2
Tutorial
The overall
Each step is described as "One-by-one steps" later.
$ doas pkg_add postgresql-server
$ doas su - _postgresql
$ mkdir /var/postgresql/data
$ # --locale below is optional
$ initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF-8 -W --locale=xx_XX.UTF-8
$ # will be asked password of the superuser, "postgres" above
$ exit
$ doas rcctl enable postgresql
$ doas rcctl start postgresql
One-by-one steps
Install
Get the package from ports system:
$ doas pkg_add postgresql-server
Init database
In order to avoid permissions error, switch user to _postgresql which was created above:
$ doas su - _postgresql
Create the directory for init_db:
$ mkdir /var/postgresql/data
Run initdb to create a database cluster:
$ initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF-8 -W --locale=xx_XX.UTF-8
Well, -U postgres (= --user=...) above is the superuser's name. The password will be asked when -W (= --pwprompt) is set. -W and -A scram-sha-256 (= --auth=...) are for the sake of security. --locale=... is optional for one but en_US.UTF-8.
Also, the documentation (/usr/local/share/doc/pkg-readmes/postgresql-server) says:
It is strongly advised that you do not work with the postgres dba account
other than creating more users and/or databases or for administrative tasks.
Use the PostgreSQL permission system to make sure that a database is only
accessed by programs/users that have the right to do so.
As a result, the whole output was:
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.
Enter new superuser password:
Enter it again:
fixing permissions on existing directory /var/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 20
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Tokyo
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
Yay, successful. Let's exit from _postgersql user:
$ exit
Start PostgreSQL server
Activate the daemon and start it:
$ doas rcctl enable postgresql
$ doas rcctl start postgresql
postgresql(ok)
Conclusion
The postgresql server daemon is now activated and started. It works as RDBMS and listens to requests from clients.
The configuration files such as postgresql.conf and pg_hba.conf were generated automatically, and also psql was installed.
Configuration files
They are useful to configure the server.
psql
It is used as a terminal-based front-end to PostgreSQL. By using the password asked above, it's able to connect to the server:
$ psql -U postgres
Password for user postgres:
You will be welcomed :)
psql (14.2)
Type "help" for help.
postgres=#
Reference
- PostgreSQL 14 Release Notes
- How to upgrade OpenBSD 7.0 to 7.1
This content originally appeared on DEV Community and was authored by Heddi Nabbisen
Heddi Nabbisen | Sciencx (2022-04-24T00:54:05+00:00) PostgreSQL 14.2 On OpenBSD 7.1: Install. Retrieved from https://www.scien.cx/2022/04/24/postgresql-14-2-on-openbsd-7-1-install/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.