After my recent failure to upgrade to version 5.0.0.(which eventually succeeded), where I screwed up my config, I am looking through my server to find any issues. I am planning to upgrade all dependencies to their latest version (node, yarn, postgres, redis-server and ffmpeg).
By running ‹ apt list --installed › I saw I have 2 versions of PostgreSQL installed. Both 12 and 14:
postgresql-12/now 12.12-0ubuntu0.20.04.1 amd64 [installed,local]
postgresql-14/jammy-updates,jammy-security,now 14.5-0ubuntu0.22.04.1 amd64 [installed,automatic]
I can see in my production.yaml that Peertube is using version 12 (by comparing both ports PostgreSQL instances are listening on).
Can I change/migrate Peertube to PostgreSQL version 14 and uninstall version 12?
Any advice is welcome 
When you upgrade pgsql, it always keep the older version until you migrate it manually.
I’m not very familiar with psql, so I don’t know the exact command to execute. But I think it would be something like:
# Stop postgresql
systemctl stop postgresql
# Drop the new cluster. WARNING: only if it is empty. If you have another application that is using it... Don't. In such case, I don't know how to do, check the manual
pg_dropcluster --stop 14 main
# Then migrate data from old cluster
pg_upgradecluster -m upgrade 11 main
# Start and test
systemctl start postgresql
Please note that i’m not sure of these command, you should check for an online tutorial to confirm.
If everything works, you should also drop the data from the old cluster (I don’t know the exact commands)
The commands are quite good.
As @yerunski has PostgreSQL 12,
pg_upgradecluster -m upgrade 11 main
should be
pg_upgradecluster -m upgrade 12 main
You also need to run
sudo -u postgres /var/log/postgresql/pg_upgradecluster-12-14-main.*/analyze_new_cluster.sh
after the
systemctl start postgresql
Please note that during the upgrade, the PostgreSQL 12 data are copied to the new version of PostgreSQL: you’ll need enough disk space to migrate.
You can verify that PostgreSQL 14 is now the default by checking the port it uses:
grep "^port" /etc/postgresql -R
If the port in /etc/postgresql/14/main/postgresql.conf is 5432, then it should be the default PostgreSQL version (unless you made PostgreSQL 12 listening on another port than the default one, which is 5432).
Once you’re sure that the migration went all right, you can remove the old PostgreSQL by doing:
pg_dropcluster 12 main --stop
apt-get autoremove --purge postgresql-12 postgresql-client-12
What is this command doing? I think I never saw it before.
Is it important?
@Framasky Thanks! Since I have a full backup of the server, maybe tonight I will give it a go. If it works, I can probably uninstall version 12.
Also thanks @JohnLivingston
Update: I remember the ‹ postgresql › command does not work on my system. Instead I have to use ‹ psql ›. It probably doesn’t make a difference to the procedure.
It makes PostgreSQL analyze the data in the new version, in order to do statistics… for what, that is something I don’t know, but I sure think it’s to work better.
The script is created by the pg_upgradecluster command, which says at the end that you should run the script. I just added the sudo -u postgres because it’s a script you need to run with the PostgreSQL superuser.
For information, I maintain a documentation on PostgreSQL upgrades in french. It’s updated at every new version of Debian (which brings a new version of PostgreSQL).
That’s the link I was searching for, and I always struggle to find ^^. I will note it somewhere I will not forget. Thanks.
I think it all worked out. But the ‹ analyze_new_cluster.sh › script could not be found. In that directory I only have a ‹ delete_old_cluster.sh › script and a ‹ update_extensions.sql ›. Which I had to run according to the output of the database conversion.
The only output it gave was "You are now connected to database « peertube_prod » as user « postgres ».
ALTER EXTENSION"
So I guess that went fine too, although I don’t know what the correct output should be.
Purged the old (version 12) database and removed server and client packages. Rebooted my server (just to make sure it still works), everything is working fine. No error logs either
Merci beaucoup!
1 « J'aime »
Instead of analyze_new_cluster.sh script, you may have to use the following command:
sudo -u postgres /usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages
That’s an evolution of the upgrade process.