postgresql-server-18.6

This README contains unresolved ports variables; replace them before using its commands.

+-----------------------------------------------------------------------
| 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 not
large enough for PostgreSQL.  Adding the following in /etc/sysctl.conf
should allow PostgreSQL to start:

	kern.seminfo.semmni=256
	kern.seminfo.semmns=2048

While you can start PostgreSQL with smaller limits, it's probably
not wise unless you are very resource constrained.

You may also want to tune the max_connections value in the
postgresql.conf file to increase the number of connections to the
backend.  However, for busy servers with many connections, these
limits may need to be increased further.

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

The capability database file is located at /etc/login.conf.d/postgresql
 
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) Determine local PostgreSQL configuration changes: 
# diff -wu ${LOCALBASE}/share/postgresql/postgresql.conf.sample \
    /var/postgresql/data/postgresql.conf | \
    tee /var/postgresql/data/postgresql.conf.diff

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

3) Shutdown the server:
# rcctl stop postgresql

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

5) 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-${PREV_MAJOR}

6) 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"

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

8) Apply configuration changes using the diff file created in step 1:
# $EDITOR /var/postgresql/data/postgresql.conf

9) Start PostgreSQL:
# rcctl start postgresql

10) 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) Enable data checksums if not already enabled (data checksums were not
   enabled by default until PostgreSQL 18):
# su _postgresql -c '/usr/local/bin/pg_checksums -e -D /var/postgresql/data'

3) Install the PostgreSQL pg_upgrade package with pkg_add.
# pkg_add postgresql-pg_upgrade

4) Backup your old data directory:
# mv /var/postgresql/data /var/postgresql/data-${PREV_MAJOR}

5) 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 [...]").

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

7) Restore your old pg_hba.conf and (if used) SSL certificates
# cp -p /var/postgresql/data-${PREV_MAJOR}/pg_hba.conf /var/postgresql/data/
# cp -p /var/postgresql/data-${PREV_MAJOR}/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 ${LOCALBASE}/share/postgresql-${PREV_MAJOR}/postgresql.conf.sample \
    /var/postgresql/data-${PREV_MAJOR}/postgresql.conf

8) Run pg_upgrade:
# su _postgresql -c "cd /var/postgresql && \
    pg_upgrade -b /usr/local/bin/postgresql-${PREV_MAJOR}/ -B /usr/local/bin \
    -U postgres -d /var/postgresql/data-${PREV_MAJOR}/ -D /var/postgresql/data"

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

10) Start PostgreSQL:
# rcctl start postgresql

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

www/phppgadmin		A web based user interface that uses PHP