Upgrading to PostgreSQL 14 on FreeBSD 13

In this blog post, I’ll show how to update PostgreSQL 14 on FreeBSD 13. Instead of running a database on my development machine, I run it on a FreeBSD virtual machine on my Intel NUC VMware ESXi homelab, and connect to it from anywhere thanks to Tailscale.

First, I started by making a backup of my data and locating where my current PostgreSQL configuration lives:

1
2
$ pg_dumpall -U pgsql | gzip > backup.sql.gz
$ ls /var/db/postgres/

This is an essential step, as you’ll see because I initialized a new database from scratch and recovered my customized settings and data later.

Beastie PostgreSQL mascot Slonik

The next step was to update package repository catalogues and upgrade packaged software distributions:

1
2
$ pkg update
$ pkg upgrade

Next, I checked if PostgreSQL packages were available:

1
2
3
4
5
6
7
8
9
$ pkg search postgresql14
pgtcl-postgresql14-2.1.1_2     TCL extension for accessing a PostgreSQL server (PGTCL-NG)
postgresql14-client-14.0       PostgreSQL database (client)
postgresql14-contrib-14.0      The contrib utilities from the PostgreSQL distribution
postgresql14-docs-14.0         The PostgreSQL documentation set
postgresql14-plperl-14.0       Write SQL functions for PostgreSQL using Perl5
postgresql14-plpython-14.0     Module for using Python to write SQL functions
postgresql14-pltcl-14.0        Module for using Tcl to write SQL functions
postgresql14-server-14.0       PostgreSQL is the most advanced open-source database available anywhere

Great! They are! Now, let’s find out what version PostgreSQL it’s installed, remove it, and install the newest version:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
$ pkg info | grep postgresql
postgresql12-client-12.8       PostgreSQL database (client)
postgresql12-server-12.8       PostgreSQL is the most advanced open-source database available anywhere

# Shutdown the PostgreSQL database service:
$ service postgresql stop

# Delete the old version:
$ pkg delete postgresql12-client postgresql12-server

# Install version 14:
$ pkg install postgresql14-client postgresql14-server

Settings

Now, remember the /var/db/postgres directory I mentioned previously. There, you’ll find a new /var/db/postgres/data14 directory alongside another directory named data13 if you had version 13, data12 if you had version 12, and so on. Use the diff tool to compare configuration files and see what changes you need to apply to your new database.

Any changes you’ve applied with ALTER SYSTEM will be found on the postgresql.auto.conf file.

In my case, I had:

1
2
3
4
root@bsd:/var/db/postgres/data12 # cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
wal_level = 'logical'

Which I decided to copy to /var/db/postgres/data14/postgresql.conf, along with the following changes:

1
2
3
# Listen to external connections (and let firewall worry about blocking unwanted connections):
# Use :: to allow listening for all IPv6 addresses or * for both IPv4 and IPv6.
listen_addresses = '0.0.0.0'

To allow TLS connections I copied my server.crt and server.key files (along with a server.req file),

1
2
3
4
root@bsd:/var/db/postgres/data12 # ls server.*
server.crt	server.key	server.req
root@bsd:/var/db/postgres/data14 # cp ../data12/server.* .
root@bsd:/var/db/postgres/data14 # chown postgres:postgres server.crt server.key server.req

Finally, I turned on TLS on the postgresql.conf file with:

1
2
# - SSL -
ssl = on

As I said previously, I’m also using the Logical Streaming Replication Protocol to ingest data into OpenSearch using a connector, so I had to tweak this knob for configuring the write-ahead log:

1
2
3
4
5
6
#------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -
wal_level = logical                    # minimal, replica, or logical

Alternatively, I could just run the following command, modifying postgresql.auto.conf (this specific change requires a restart, and it’s unlikely you need it, by the way):

1
ALTER SYSTEM SET wal_level = logical;

I also had to edit the pg_hba.conf file used for client authentication to add a few lines:

1
2
# Checking what are the changes I need to do:
root@bsd:/var/db/postgres/data14 diff pg_hba.conf ../data12/pg_hba.conf

Lines I had to add back:

1
2
3
4
> # Henrique's connections
> host    all             all             100.117.1.1/32     password
> hostssl    all             all             100.117.1.1/32     password
> hostssl    all             all             100.117.1.1/32   cert

Otherwise, I’d have run into this error when trying to connect to the database from my laptop:

1
2
3
4
$ pgcli
could not connect to server: Connection refused
	Is the server running on host "bsd.henvic.dev" (100.125.227.84) and accepting
	TCP/IP connections on port 5432?

Starting PostgreSQL

Now, I initialized a new database, and then started PostgreSQL as a service.

1
2
3
4
5
6
7
8
# Initialize the new PostgreSQL 14 version by running:
$ postgres initdb -D /var/db/postgres/data14

# Make PostgreSQL run automatically on boot time, if you haven't (see /etc/rc.conf):
$ sysrc postgresql_enable=yes

# To start the service manually, run:
$ service postgresql start

Restoring your backup

Go back to the directory where you saved your database dump and run the following command to restore everything on your database:

1
$ gzcat backup.sql.gz | psql -U postgres postgres

Once you restore your backup, you should be done and ready to use PostgreSQL.

You can try to connect to the database and run a few commands to check if everything works. If something goes wrong, the following commands might help you:

1
2
3
4
5
6
7
8
# Check service status:
$ service postgresql status

# List open TCPv4 ports:
$ sockstat -l4 -P tcp

# List open TCPv6 ports
$ sockstat -l6 -P tcp

Running an old version? Read the blog post Upgrading PostgreSQL on FreeBSD (2016) by Filipp Lepalaan.

Logging

While setting up, I ran into this error and needed more information to fix it:

1
2
3
root@bsd:/var/db/postgres/data14 # service postgresql start
pg_ctl: could not start server
Examine the log output.

I started looking for this log file and discovered that I had to set up part of the error reporting and logging infrastructure to gather logs.

To do so, I added the following line to /etc/syslog.conf:

1
local0.*    /var/log/postgresql

You can use LOCAL0 through LOCAL7 as the preceding syslog_facility.

You can set at what syslog facility to log using the following configuration: #syslog_facility = ‘LOCAL0’ Change the logserver facility if you are having a conflict with other applications.

Source: PostgresqlCO.NF’s syslog_facility

I followed this advice and created a log file for PostgreSQL:

1
2
3
$ touch /var/log/postgresql.log
$ chown postgres:wheel /var/log/postgresql.log
$ chmod 640 /var/log/postgresql.log

Next, I sent a SIGHUP signal meant to signal the daemon that its configuration file should be re-read and restarted PostgreSQL:

1
2
3
4
5
6
$ kill -HUP `sudo cat /var/run/syslog.pid `
$ service postgresql start
2021-11-09 21:16:58.545 CET [33755] FATAL:  could not load private key file "server.key": Permission denied
2021-11-09 21:16:58.546 CET [33755] LOG:  database system is shut down
pg_ctl: could not start server
Examine the log output.

Interestingly the service postgresql start command now returns some logs, so I didn’t even need to check the logs files.

You can read the stream of PostgreSQL logs with the following command:

1
2
3
$ tail -f /var/log/postgresql.log
Nov  9 21:16:58 bsd postgres[33755]: [1-1] 2021-11-09 21:16:58.545 CET [33755] FATAL:  could not load private key file "server.key": Permission denied
Nov  9 21:16:58 bsd postgres[33755]: [2-1] 2021-11-09 21:16:58.546 CET [33755] LOG:  database system is shut down

With the context of what was going on, it was pretty easy to spot the problem: the permissions for the certificate files were incorrect.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
root@bsd:/var/db/postgres/data14 # ls -la
total 106
drwx------  19 postgres  postgres     28 Nov  9 21:16 .
drwxr-xr-x   5 postgres  postgres      6 Nov  9 20:24 ..
-rw-------   1 postgres  postgres      3 Nov  9 20:24 PG_VERSION
drwx------  13 postgres  postgres     13 Nov  9 20:25 base
drwx------   2 postgres  postgres     60 Nov  9 21:00 global
drwx------   2 postgres  postgres      2 Nov  9 20:24 pg_commit_ts
drwx------   2 postgres  postgres      2 Nov  9 20:24 pg_dynshmem
-rw-------   1 postgres  postgres   5367 Nov  9 20:59 pg_hba.conf
-rw-------   1 postgres  postgres   1636 Nov  9 20:24 pg_ident.conf
drwx------   4 postgres  postgres      5 Nov  9 21:09 pg_logical
drwx------   4 postgres  postgres      4 Nov  9 20:24 pg_multixact
drwx------   2 postgres  postgres      2 Nov  9 20:24 pg_notify
drwx------   2 postgres  postgres      2 Nov  9 20:24 pg_replslot
drwx------   2 postgres  postgres      2 Nov  9 20:24 pg_serial
drwx------   2 postgres  postgres      2 Nov  9 20:24 pg_snapshots
drwx------   2 postgres  postgres     13 Nov  9 21:09 pg_stat
drwx------   2 postgres  postgres      2 Nov  9 21:09 pg_stat_tmp
drwx------   2 postgres  postgres      3 Nov  9 20:24 pg_subtrans
drwx------   2 postgres  postgres      2 Nov  9 20:24 pg_tblspc
drwx------   2 postgres  postgres      2 Nov  9 20:24 pg_twophase
drwx------   3 postgres  postgres     29 Nov  9 20:34 pg_wal
drwx------   2 postgres  postgres      3 Nov  9 20:24 pg_xact
-rw-------   1 postgres  postgres     88 Nov  9 20:24 postgresql.auto.conf
-rw-------   1 postgres  postgres  28915 Nov  9 21:09 postgresql.conf
-rw-------   1 postgres  postgres     55 Nov  9 20:59 postmaster.opts
-rw-r--r--   1 root      postgres   4631 Nov  9 21:09 server.crt
-rw-------   1 root      postgres   1675 Nov  9 21:09 server.key
-rw-r--r--   1 root      postgres   3569 Nov  9 21:10 server.req

Cause: I had copied the files while logged in as root, but forgot to set their group owner to postgres.

Let’s fix this:

1
2
3
4
5
root@bsd:/var/db/postgres/data14 # chown postgres:postgres server.crt server.key server.req
root@bsd:/var/db/postgres/data14 # ls -la server.*
-rw-r--r--  1 postgres  postgres  4631 Nov  9 21:09 server.crt
-rw-------  1 postgres  postgres  1675 Nov  9 21:09 server.key
-rw-r--r--  1 postgres  postgres  3569 Nov  9 21:10 server.req

Running service postgresql start again worked like a charm!

Next, I’ve checked connectivity from my laptop. Finally, everything worked, including the database replication protocol I use at work for ingesting data into a search engine (but that topic is for an upcoming post, hopefully, in a couple of months).

Once you verify the migration happened successfully, you can get rid of your old /var/db/postgres/data<version> directory.

I hope you find this article useful somehow.

If you click and buy any of these from Amazon after visiting the links above, I might get a commission from their Affiliate program.