I’m moving from PostgreSQL 16 to PostgreSQL 18 at home in the basement. I’ve done the dev and dvl databases. Now it’s time for test and stage. Since they are closer (in configuration) to production, I thought I would document how I’m doing that.
I’m likely to jump back and forth between test and stage in my examples. All commands are run on both sets.
Downtime
Downtime was set like this:
[22:37 r720-02-proxy01 dvl ~] % ~/bin/offline test.freshports.org "$(date -R -v +3H)" "Offline for database migration"
<html>
<head>
<title>Error 503 Service Unavailable</title>
<style>
<style>
body { text-align: center; padding: 20px; font: 25px Helvetica, sans-serif; color: #efe8e8; background-color:#2e2929}
@media (min-width: 768px){
body{ padding-top: 150px; }
}
h1 { font-size: 50px; }
h2 { font-size: 35px; }
h3 { font-size: 28px; }
article { display: block; text-align: left; max-width: 650px; margin: 0 auto; }
td { font-size: 25px; line-height: 1.5; margin: 20px 0; }
</style>
</style>
</head>
<body>
<h1>Server is offline for maintenance</h1>
<h2>503 Service Unavailable</h2>
<h3>Offline for database migration</h3>
<table>
<tr>
<td>Started at:</td><td>Mon, 23 Mar 2026 22:38:06 +0000</td>
</tr>
<tr>
<td>Please retry after:</td><td> Tue, 24 Mar 2026 01:38:06 +0000</td>
</tr>
</table>
</body>
</html>
[22:38 r720-02-proxy01 dvl ~] % ~/bin/offline stage.freshports.org "$(date -R -v +3H)" "Offline for database migration"
<html>
<head>
<title>Error 503 Service Unavailable</title>
<style>
<style>
body { text-align: center; padding: 20px; font: 25px Helvetica, sans-serif; color: #efe8e8; background-color:#2e2929}
@media (min-width: 768px){
body{ padding-top: 150px; }
}
h1 { font-size: 50px; }
h2 { font-size: 35px; }
h3 { font-size: 28px; }
article { display: block; text-align: left; max-width: 650px; margin: 0 auto; }
td { font-size: 25px; line-height: 1.5; margin: 20px 0; }
</style>
</style>
</head>
<body>
<h1>Server is offline for maintenance</h1>
<h2>503 Service Unavailable</h2>
<h3>Offline for database migration</h3>
<table>
<tr>
<td>Started at:</td><td>Mon, 23 Mar 2026 22:38:14 +0000</td>
</tr>
<tr>
<td>Please retry after:</td><td> Tue, 24 Mar 2026 01:38:14 +0000</td>
</tr>
</table>
</body>
</html>
[22:38 r720-02-proxy01 dvl ~] %
For more in formation on that wonderful script, please see https://dan.langille.org/2026/03/18/maintenance-script-changes/
Stopping the ingress services
This stops stuff on the ingress jail:
[22:39 stage-ingress01 dvl ~] % sudo service ingress stop Stopping ingress. Waiting for PIDS: 27716. [22:39 stage-ingress01 dvl ~] % sudo service freshports stop Stopping freshports. Waiting for PIDS: 27694. [22:41 stage-ingress01 dvl /usr/local/libexec/freshports] % sudo touch /var/db/freshports/signals/OFFLINE
That stops the detection of new commits, the importing of those commits, and tells all the other scripts not to bother running.
I could also disable some cronjobs, periodic scripts, etc but it’s easier to touch that one file.
Stopping stuff on the webserver
Here we go, or rather, stop:
[22:49 test-nginx01 dvl ~] % sudo service nginx stop Stopping nginx. Waiting for PIDS: 32448. [22:49 test-nginx01 dvl ~] % sudo service fp_listen stop Stopping fp_listen. Waiting for PIDS: 26009.
The dump
The dumps have been running since before I started this post. Both are these are in their own tmux session:
[22:42 pg01 dvl ~/tmp] % pg_dump -j 30 -h pg03.int.unixathome.org -U postgres --format=directory -d freshports.test -f freshports.test.dump -Z0 [22:56 pg01 dvl ~/tmp] % pg_dump -j 30 -h pg03.int.unixathome.org -U postgres --format=directory -d freshports.stage -f freshports.stage.dump -Z0
The restores
By the time I got to here in the post, the dumps were nearly done. Here’s the restore:
[23:02 pg01 dvl ~/tmp] % createdb -U postgres -E SQL_ASCII -T template0 -O postgres freshports.stage [23:04 pg01 dvl ~/tmp] % time pg_restore -U postgres -j 34 -d freshports.stage freshports.stage.dump pg_restore -U postgres -j 34 -d freshports.stage freshports.stage.dump 7.10s user 55.95s system 1% cpu 1:27:38.03 total [23:02 pg01 dvl ~/tmp] % createdb -O postgres -E SQL_ASCII -T template0 freshports.test [23:03 pg01 dvl ~/tmp] % time pg_restore -U postgres -j 34 -d freshports.test freshports.test.dump pg_restore -U postgres -j 34 -d freshports.test freshports.test.dump 7.19s user 55.22s system 1% cpu 1:29:27.30 total
Expected log messages
Log entries such as this are expected:
Mar 23 23:00:00 stage-ingress01 FreshPorts[75936]: not testing master port status: system is offline Mar 23 23:00:00 stage-ingress01 missing-port-categories.sh[76440]: exit now because the system is OFFLINE Mar 23 23:00:00 stage-ingress01 check_repos_for_new_stuff.py[76501]: /usr/local/libexec/freshports/./check_repos_for_new_stuff.py is exiting because the system is OFFLINE
Swapping websites over to the other database
For this step, I’m using Ansible and I’m waiting until the next day.
Good morning, from the coffee shop.
The first script I run is:
[11:40 ansible root /usr/local/etc/ansible] # ansible-playbook freshports-configuration-website.yml --limit=test-nginx01.int.unixathome.org,stage-nginx01.int.unixathome.org
That updates these configuration files for the websites:
[11:55 stage-nginx01 dvl /usr/local/etc/freshports] % ls -l database.php fp-listen.ini -rw-r----- 1 root www 671 2026.03.24 11:51 database.php -rw-r----- 1 root freshports 1171 2026.03.24 11:51 fp-listen.ini
At this point, the website will be functional and can be place back online.
Placing websites back online
First, we start the web services:
[12:22 stage-nginx01 dvl ~] % sudo service nginx start Starting fp_listen. [12:22 stage-nginx01 dvl ~] % sudo service fp_listen start Starting fp_listen.
That second service is the cache clearing daemon. It listens to the backend database and clear cache accordingly.
Removing these file puts the websites back online:
[11:57 r720-02-proxy01 dvl /usr/local/www/offline] % ls -l total 14 -rw-r--r-- 1 root wheel 137 2026.03.12 14:11 index.html -rw-r--r-- 1 root wheel 856 2026.03.23 22:38 stage.freshports.org-maintenance.html -rw-r--r-- 1 root wheel 856 2026.03.23 22:38 test.freshports.org-maintenance.html [11:57 r720-02-proxy01 dvl /usr/local/www/offline] % sudo rm stage.freshports.org-maintenance.html test.freshports.org-maintenance.html [11:57 r720-02-proxy01 dvl /usr/local/www/offline] % ls -l total 5 -rw-r--r-- 1 root wheel 137 2026.03.12 14:11 index.html [11:57 r720-02-proxy01 dvl /usr/local/www/offline] %
The backend, don’t forget the backends!
If we don’t move over the ingress (backend) nodes, they will update the old databases, and we can’t have that.
[12:05 ansible root /usr/local/etc/ansible] # ansible-playbook freshports-configuration-ingress.yml --limit=stage-ingress01.int.unixathome.org,test-ingress01.int.unixathome.org
Allow the scripts to run:
[12:11 stage-ingress01 dvl ~] % sudo rm /var/db/freshports/signals/OFFLINE [12:11 stage-ingress01 dvl ~] %
Start the services:
[12:11 test-ingress01 dvl ~] % sudo service ingress start
Starting ingress.
[12:14 test-ingress01 dvl ~] % sudo service freshports start
Starting freshports.
[12:14 test-ingress01 dvl ~] %
Other things to adjust
I know we have to tell Nagios to check the right server. That’s means an update to /var/spool/nagios/.pgpass – I always have trouble remembering that one. Here it is for my future reference:
[12:18 ansible root /usr/local/etc/ansible] # ansible-playbook nrpe.yml --tags=pgpass --limit=stage-ingress01.int.unixathome.org,test-ingress01.int.unixathome.org
I think that’s everything.
Not shown here
I have not shown the changes to the PostgreSQL Host Based Authentication (pg_hba.conf) files. I updated those manually. There is an Ansible script, but I want to rework it to increase the use of template and make them data-driven, not copy/paste. I’ve created myself a ticket to code that up. It should be very satisfying.
