Initialize the PostgreSQL service under OpenBSD

How to initialize the PostgreSQL service to run under OpenBSD for the first time

The PostgreSQL service needs further setup (initialization) before running it for the first time under OpenBSD

Before starting the PostgreSQL service for the first time on OpenBSD you need to initialize the PostgreSQL database. Read this article to find out how.

Steps to initialize the PostgreSQL service under the OpenBSD operating system

1. Create the required directories and apply privileges: run the following commands as `root`
PostgreSQL needs the /var/postgresql directory. Change the ownership to admin and the mode to 0755 of this directory after creation in order to be able to initialize the database from step 2.

$ doas mkdir /var/postgresql

$ doas chown -R admin:wheel /var/postgresql

$ doas chmod 0775 /var/postgresql

2. Initialize the PostgreSQL Database: run the following command as `admin` user
The below command (run as admin, not using doas) will create the /var/postgresql/data directory and will populate it with the initial database structure. A prompt will appear which will ask for the default password of the postgres user account in PostgreSQL. You can enter postgres as the password, by example ...

$ initdb --pgdata=/var/postgresql/data --no-locale --username=postgres --pwprompt

3. Re-apply privileges, enable the postgresql service under rc.d and start it: run the following commands as `root`
This is the final step which needs to re-apply and correct the ownership of the PostgreSQL directory as required: username = _postgresql ; group = _postgresql. After fixing the privileges the postgresql service can be enabled in the rc.d and then started. After this step the PostgreSQL service will start automatically on each boot.

$ doas chown -R _postgresql:_postgresql /var/postgresql

$ doas rcctl enable postgresql
$ doas rcctl start postgresql