Installing PostgreSQL 15 on FreeBSD 13
Posted on Apr 20, 2023 by Daring Akalat in FreeBSD and PostgreSQL
$ uname -a
FreeBSD freebsd 13.1-RELEASE-p6 FreeBSD 13.1-RELEASE-p6 GENERIC amd64
$ pwd
/home/akalat
$ pkg search postgresql
postgresql15-contrib-15.2 The contrib utilities from the PostgreSQL distribution
postgresql15-client-15.2 PostgreSQL database (client)
postgresql15-docs-15.2 The PostgreSQL documentation set
postgresql15-server-15.2 PostgreSQL is the most advanced open-source database available anywhere
=========== BACKUP YOUR DATA! =============
As always, backup your data before
upgrading. If the upgrade leads to a higher
major revision (e.g. 9.6 -> 10), a dump
and restore of all databases is
required. This is *NOT* done by the port!
See https://www.postgresql.org/docs/current/upgrading.html
$ doas pkg install postgresql15-server postgresql15-client postgresql15-contrib postgresql15-docs
Message from postgresql15-server-15.2:
--
For procedural languages and postgresql functions, please note that
you might have to update them when updating the server.
If you have many tables and many clients running, consider raising
kern.maxfiles using sysctl(8), or reconfigure your kernel
appropriately.
The port is set up to use autovacuum for new databases, but you might
also want to vacuum and perhaps backup your database regularly. There
is a periodic script, /usr/local/etc/periodic/daily/502.pgsql, that
you may find useful. You can use it to backup and perform vacuum on all
databases nightly. Per default, it performs `vacuum analyze'. See the
script for instructions. For autovacuum settings, please review
~postgres/data/postgresql.conf.
If you plan to access your PostgreSQL server using ODBC, please
consider running the SQL script /usr/local/share/postgresql/odbc.sql
to get the functions required for ODBC compliance.
Please note that if you use the rc script,
/usr/local/etc/rc.d/postgresql, to initialize the database, unicode
(UTF-8) will be used to store character data by default. Set
postgresql_initdb_flags or use login.conf settings described below to
alter this behaviour. See the start rc script for more info.
To set limits, environment stuff like locale and collation and other
things, you can set up a class in /etc/login.conf before initializing
the database. Add something similar to this to /etc/login.conf:
---
postgres:\
:lang=en_US.UTF-8:\
:setenv=LC_COLLATE=C:\
:tc=default:
---
and run `cap_mkdb /etc/login.conf'.
Then add 'postgresql_class="postgres"' to /etc/rc.conf.
======================================================================
To initialize the database, run
/usr/local/etc/rc.d/postgresql initdb
You can then start PostgreSQL by running:
/usr/local/etc/rc.d/postgresql start
For postmaster settings, see ~postgres/data/postgresql.conf
NB. FreeBSD's PostgreSQL port logs to syslog by default
See ~postgres/data/postgresql.conf for more info
NB. If you're not using a checksumming filesystem like ZFS, you might
wish to enable data checksumming. It can be enabled during
the initdb phase, by adding the "--data-checksums" flag to
the postgresql_initdb_flags rcvar. Otherwise you can enable it later by
pg_checksums. Check the initdb(1) manpage for more info
and make sure you understand the performance implications.
======================================================================
To run PostgreSQL at startup, add
'postgresql_enable="YES"' to /etc/rc.conf
$ doas sysrc postgresql_enable="YES"
postgresql_enable: -> YES
$ doas /usr/local/etc/rc.d/postgresql initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with this locale configuration:
provider: libc
LC_COLLATE: C
LC_CTYPE: C.UTF-8
LC_MESSAGES: C.UTF-8
LC_MONETARY: C.UTF-8
LC_NUMERIC: C.UTF-8
LC_TIME: C.UTF-8
The default text search configuration will be set to "english".
Data page checksums are disabled.
--snip --
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/local/bin/pg_ctl -D /var/db/postgres/data15 -l logfile start
$ doas service postgresql status
pg_ctl: no server running
$ doas service postgresql start
[67409] LOG: ending log output to stderr
[67409] HINT: Future log output will go to log destination "syslog".
$ doas service postgresql status
pg_ctl: server is running (PID: 67409)
/usr/local/bin/postgres "-D" "/var/db/postgres/data15"
$ doas ps -U postgres
67409 - Is 0:00.06 /usr/local/bin/postgres -D /var/db/postgres/data15
67496 - Is 0:00.01 postgres: checkpointer (postgres)
67509 - Ss 0:00.03 postgres: background writer (postgres)
67825 - Ss 0:00.02 postgres: walwriter (postgres)
68253 - Is 0:00.01 postgres: autovacuum launcher (postgres)
69139 - Is 0:00.00 postgres: logical replication launcher (postgres)
$ doas sockstat
postgres postgres 69139 5 dgram -> /var/run/log
postgres postgres 68253 5 dgram -> /var/run/log
postgres postgres 67825 5 dgram -> /var/run/log
postgres postgres 67509 5 dgram -> /var/run/log
postgres postgres 67496 5 dgram -> /var/run/log
postgres postgres 67409 5 dgram -> /var/run/log
postgres postgres 67409 6 tcp6 ::1:5432 *:*
postgres postgres 67409 7 tcp4 127.0.0.1:5432 *:*
postgres postgres 67409 8 stream /tmp/.s.PGSQL.5432
$ doas ee /var/db/postgres/data15/pg_hba.conf
# /var/db/postgres/data15/pg_hba.conf
-- snip --
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
-- snip --
# /var/db/postgres/data15/pg_hba.conf
-- snip --
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
-- snip --
$ doas service postgresql restart
[75086] LOG: ending log output to stderr
[75086] HINT: Future log output will go to log destination "syslog".
$ su
$ su postgres
$ cd
$ whoami && pwd
postgres
/var/db/postgres
$ psql
postgres=# \password postgres
postgres=# CREATE DATABASE daring;
CREATE DATABASE
postgres=# CREATE ROLE akalat WITH ENCRYPTED PASSWORD 'daring1';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE daring TO akalat;
GRANT
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
akalat | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# ALTER ROLE akalat WITH LOGIN;
ALTER ROLE
postgres=# \q
$ psql -U akalat -d daring -W
daring=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
daring | postgres | UTF8 | C | C.UTF-8 | | libc | =Tc/postgres +
| | | | | | | postgres=CTc/postgres+
| | | | | | | akalat=CTc/postgres
postgres | postgres | UTF8 | C | C.UTF-8 | | libc |
template0 | postgres | UTF8 | C | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
daring => \q
$ psql -U postgres
postgres=# DROP DATABASE daring;
DROP DATABASE
postgres=# DROP ROLE akalat;
DROP ROLE
postgres=# \q
$ service postgresql stop
$ exit
$ whoami && pwd
root
/usr/home/akalat
$ exit