Install and configure PostgreSQL 13.2 on OpenBSD 6.9

In this section we will install and configure PostgreSQL 13.2 database server on OpenBSD 6.9.

"PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. "

A description and more information about the database server is available on the project's web site, https://www.postgresql.org/.

PostgreSQL is not 100% but largely SQL compliant, with additional features. If you are familiar with MySQL, be aware that database server is much less SQL Compliant, so you may have a short learning curve to "break bad learned habits".

OpenBSD 6.9 has version 13.2 available as a package. You should keep in mind that upgrading between minor versions of PostgreSQL (for example from 13.1 to 13.2) can be done in place, however if you are upgrading between major versions (for example from 12.6 to 13.2) then you need to do a complete database dump to an SQL file, upgrade the system to the major version, then re-import the data. You can also export the data to another server, upgrade, and re-import the data after the update.

You should always keep a backup of your database in your system plan.

When you install the server, it will also install the database client.

# doas pkg_add postgresql-server 

quirks-3.632 signed on 2021-05-14T14:40:43Z
postgresql-server-13.2p1:libexecinfo-0.3p2v0: ok
postgresql-server-13.2p1:libxml-2.9.10p3: ok
postgresql-server-13.2p1:postgresql-client-13.2p1: ok
useradd: Warning: home directory `/var/postgresql' doesn't exist, and -m was not specified
postgresql-server-13.2p1: ok
Running tags: ok
The following new rcscripts were installed: /etc/rc.d/postgresql
See rcctl(8) for details.
New and changed readme(s):
	/usr/local/share/doc/pkg-readmes/postgresql-server

Read the documentation:

# cat /usr/local/share/doc/pkg-readmes/postgresql-server
$OpenBSD: README-server,v 1.33 2021/03/01 13:40:08 sthen Exp $

+-----------------------------------------------------------------------
| Running postgresql-server on OpenBSD
+-----------------------------------------------------------------------

At least two different accounts are involved when working with PostgreSQL:
One is an OpenBSD userid, '_postgresql', which is used as the userid of files
that are part of PostgreSQL.  The other, usually named 'postgres', is not an
OpenBSD userid, i.e. you will not find it in /etc/passwd, but an account
internal to the database system.  The 'postgres' account is called the dba
account (database administrator) and is created when a new database is
initialized using the initdb command.

If you are installing PostgreSQL for the first time, you have to create
a default database first.  In the following example we install a database
in /var/postgresql/data with a dba account 'postgres' and scram-sha-256
authentication. We will be prompted for a password to protect the dba account:

       # su - _postgresql
       $ mkdir /var/postgresql/data
       $ initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W

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.

Please consult the PostgreSQL website for more information, especially when
you are upgrading an existing database installation.


Network Connections
===================
To allow connections over TCP (and other options) edit the file:

	/var/postgresql/data/postgresql.conf

and also edit the pg_hba.conf (in the same directory) making the
appropriate changes to allow connection from your network.

To allow SSL connections, edit postgresql.conf and enable the
'ssl' keyword, and create keys and certificates:

       # su - _postgresql
       $ cd /var/postgresql/data
       $ umask 077
       $ openssl genrsa -out server.key 2048
       $ openssl req -new -key server.key -out server.csr

Either take the CSR to a Certifying Authority (CA) to sign your
certificate, or self-sign it:

       $ openssl x509 -req -days 365 -in server.csr \
         -signkey server.key -out server.crt

Restart PostgreSQL to allow these changes to take effect.

Tuning for busy servers
=======================
The default sizes in the GENERIC kernel for SysV semaphores are only
just large enough for a database with the default configuration
(max_connections 40) if no other running processes use semaphores.
In other cases you will need to increase the limits. Adding the
following in /etc/sysctl.conf will be reasonable for many systems:

	kern.seminfo.semmni=60
	kern.seminfo.semmns=1024

To serve a large number of connections (>250), you may need higher
values for the above.

You may also want to tune the max_connect value in the
postgresql.conf file to increase the number of connections to the
backend.

By default, the _postgresql user, and so the postmaster and backend
processes run in the login(1) class of "daemon". On a busy server,
it may be advisable to put the _postgresql user and processes in
their own login(1) class with tuned resources, such as more open
file descriptors (used for network connections as well as files),
possibly more memory, etc.

For example, add this to the login.conf(5) file:

	postgresql:\
		:openfiles=768:\
		:tc=daemon:

Rebuild the login.conf.db file if necessary:

	# [ -f /etc/login.conf.db ] && cap_mkdb /etc/login.conf

For more than about 250 connections, these numbers should be
increased. Please report any changes and experiences to the package
maintainers so that we can update this file for future versions.

Upgrade Howto (for a major upgrade)
===================================
If you didn't install PostgreSQL by following this README,
you must adapt these instructions to your setup.

Option 1: Dump and Restore
--------------------------

This will work for any upgrade from any major version of PostgreSQL
to the current version.

1) Backup all your data:
# su _postgresql -c "cd /var/postgresql && \
    pg_dumpall -U postgres > /var/postgresql/full.sqldump"

2) Shutdown the server:
# rcctl stop postgresql

3) Upgrade your PostgreSQL package with pkg_add.
# pkg_add -ui postgresql-server

4) Backup your old data directory and rename:
# cd /var/postgresql && tar cf - data | gzip -1 > data.tar.gz
# mv /var/postgresql/data /var/postgresql/data-12

5) Create a new data directory:
# su _postgresql -c "mkdir /var/postgresql/data"
# su _postgresql -c "cd /var/postgresql && \
    initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W"

6) Restore your old pg_hba.conf and (if used) SSL certificates
# su _postgresql -c \
    "cp /var/postgresql/data-12/pg_hba.conf /var/postgresql/data/"
# su _postgresql -c \
    "cp /var/postgresql/data-12/server.{crt,key} /var/postgresql/data/"

Some postgresql.conf settings changed or disappeared in this version.
Examine your old file for local changes and apply them to the new version
(/var/postgresql/data/postgresql.conf). The following command may help
identify them:

# diff -wu /usr/local/share/postgresql-12/postgresql.conf.sample \
    /var/postgresql/data-12/postgresql.conf

7) Start PostgreSQL:
# rcctl start postgresql

8) Restore your data:
# su _postgresql -c "cd /var/postgresql && \
    psql -U postgres < /var/postgresql/full.sqldump"

Option 2: pg_upgrade
--------------------

This will work for an upgrade from the previous major version of
PostgreSQL supported by OpenBSD to the current version, and should be
faster than a dump and reload, especially for large databases.

1) Shutdown the server:
# rcctl stop postgresql

2) Upgrade your PostgreSQL package with pkg_add.
# pkg_add postgresql-pg_upgrade

3) Backup your old data directory:
# mv /var/postgresql/data /var/postgresql/data-12

4) Create a new data directory:
# su _postgresql -c "mkdir /var/postgresql/data && cd /var/postgresql && \
    initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W"

(The database environment defaults to UTF-8 if your terminal is already
in a UTF-8 locale; if that is the case and you require an ASCII database
environment, use "initdb --locale=C -D /var/postgresql/data [...]").

5) Temporarily support connecting without a password for local users by
   editing pg_hba.conf to include "local all postgres trust"
# vi /var/postgresql/data-12/pg_hba.conf

6) Restore your old pg_hba.conf and (if used) SSL certificates
# cp -p /var/postgresql/data-12/pg_hba.conf /var/postgresql/data/
# cp -p /var/postgresql/data-12/server.{crt,key} /var/postgresql/data/

Some postgresql.conf settings changed or disappeared in this version.
Examine your old file for local changes and apply them to the new version
(/var/postgresql/data/postgresql.conf). The following command may help
identify them:

# diff -wu /usr/local/share/postgresql-12/postgresql.conf.sample \
    /var/postgresql/data-12/postgresql.conf

7) Run pg_upgrade:
# su _postgresql -c "cd /var/postgresql && \
    pg_upgrade -b /usr/local/bin/postgresql-12/ -B /usr/local/bin \
    -U postgres -d /var/postgresql/data-12/ -D /var/postgresql/data"

8) Remove "local all postgres trust" line from pg_hba.conf
# vi /var/postgresql/data/pg_hba.conf

9) Start PostgreSQL:
# rcctl start postgresql

Clients/Frontends
=================
Many applications can use the PostgreSQL database right away.  To facilitate
administration of a PostgreSQL database, two clients are notable:

www/phppgadmin		A web based user interface that uses PHP
databases/pgadmin3	A graphical user interface that uses wxWidgets

We will not need to modify TCP networking in PostgreSQL config for this project. By default PostgreSQL will listen on 127.0.0.1 port 5432 and if you have IPv6 enabled, ::1 port 5432.

Keep in mind for other projects that the PostgreSQL has many configuration options and it is possible to set up a cluster of servers running on multiple hosts with real-time streaming updates. As a distributed/cluster data it excels over other database servers.

First-time installation of database:
# su - _postgresql
dvi$ mkdir /var/postgresql/data
dvi$ initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W
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 "C".
The default text search configuration will be set to "english".

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 ... UTC
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

To start the database you can use rcctl. (first exit from user _postgresql)

$ exit
dvi# doas rcctl start postgresql

*If you want to modify the configuration files see /var/postgresql/data/pg_hba.conf and /var/postgresql/data/postgresql.conf

Now we will log into the database server and create our Asterisk user and CDR table. CDR is "Call Detail Record", a log of calls placed and received.

The PostgreSQL client is 'psql'. We log into the superuser account we created in the install step.

# psql -U postgres    
Password for user postgres: 
psql (13.2)
Type "help" for help.

postgres=# 

Now we enter the following commands to create a database and user and give that user full permissions on the database. Make note of the password you supply, as you will need it when you configure Asterisk 16.

Note that each line ends with a semicolon. If you accidentally press 'Enter' without the semicolon, it is OK to type the semicolon on the line by itself and press Enter. If you try to enter another command without the semicolon, it will consider both lines as one command and cause an error.

postgres=# create database asterisk;
CREATE DATABASE
postgres=# create user asterisk with encrypted password 'THEPASSWORD';
CREATE ROLE
postgres=# grant all privileges on database asterisk to asterisk;
GRANT
postgres=# 

Next we will create the CDR table. (You can download the file here).

postgres=# CREATE TABLE cdr ( 
postgres(#         calldate timestamp NOT NULL , 
postgres(#         clid varchar (80) NOT NULL , 
postgres(#         src varchar (80) NOT NULL , 
postgres(#         dst varchar (80) NOT NULL , 
postgres(#         dcontext varchar (80) NOT NULL , 
postgres(#         channel varchar (80) NOT NULL , 
postgres(#         dstchannel varchar (80) NOT NULL , 
postgres(#         lastapp varchar (80) NOT NULL , 
postgres(#         lastdata varchar (80) NOT NULL , 
postgres(#         duration int NOT NULL , 
postgres(#         billsec int NOT NULL , 
postgres(#         disposition varchar (45) NOT NULL , 
postgres(#         amaflags int NOT NULL , 
postgres(#         accountcode varchar (20) NOT NULL , 
postgres(#         uniqueid varchar (150) NOT NULL , 
postgres(#         userfield varchar (255) NOT NULL ,
postgres(#         peeraccount varchar(20) NOT NULL ,
postgres(#         linkedid varchar(150) NOT NULL ,
postgres(#         sequence int NOT NULL
postgres(# );
CREATE TABLE