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:
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.
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),
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:
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
ALTERSYSTEMSET 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
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 startpg_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.
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.
root@bsd:/var/db/postgres/data14 # ls -latotal 106drwx------ 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.reqroot@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.
