I am writing this with respect to the use of FreeBSD packages used to install PostgreSQL. When upgrading FreeBSD from one major release to another, the version of PostgreSQL client and server are often moved upwards in a number of other packages that you may be using E.g. php84-pdo_pgsql, that will insist on installing the latest PostgreSQL recommended for that FreeBSD version.
The upgrade process is simple and covers the following steps:
- Backup the database
- Install the new PostgreSQL version
- Reboot
- Restore the database into the new PostgreSQL
- Check that your application runs as expected and fix any other pkg problems that are discovered.
In the following example PostgreSQL is being upgraded from version 17 to version 18
1. Backup the database
cd /var/db/postgres/data17
pg_dumpall -U postgres > backup.sql
2. Install the new PostgreSQL
pkg install postgresql18-server
service postgresql initdb
service postgresql start
The database initialisation will automatically create /var/db/data18. Don’t create this subdirectory manually as you will have to delete it to enable the initialisation to be successful.
I am assuming that PostgreSQL is already enabled in /etc/rc.conf
3. Reboot
Reboot the server or jail that has this new PostgreSQL installation. Then check that PostgreSQL is running.
service -l | grep ^postgres
4. Restore the database
cd /var/db/postgres/data18
cp ../data17/backup.sql ./
psql -U postgres < backup.sql
5. Check your application
It is important to check that all parts of your application are working OK after the upgrade. Thoroughly check web browser sessions and client applications.
Remember to check cron jobs. These can often have scripts written in other development languages that may also require dependencies to be updated. These scripts should be run manually using the appropriate user ID that cron would use. Check the script output and system error logs for problems, correct as necessary.