Oh it’s been a year since PostgreSQL 14 was released 1.
According to the PostgreSQL site, starting from version 10, the PostgreSQL Global Development Group (PGDG) releases a new major version approximately once a year 2. They also provide guidelines for the upgrading procedure 3, which includes the use of tools like pg_upgrade 4.
Now, let’s talk about my situation. I need to migrate my PostgreSQL database from version 12 to version 14. It’s a straightforward process since it’s just a single database server, not a cluster.
I started by installing the PostgreSQL 14 package using apt from PGDG. However, after installation, I noticed that both versions were installed 😆, with one running on port 5432 and the other on port 5433.
$ sudo apt update
$ sudo apt install postgresql-14
Of course, before proceeding, I made sure to back up my data using sudo -u postgres pg_dumpall > dumpall.sql.
Before switching the ports, I stopped the PostgreSQL service and initiated the pg_upgrade process.
$ sudo systemctl stop postgresql.service
$ sudo -u postgres /usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/12/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/12/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'
After completing the upgrade, I switched the ports, restarted the server, and check the version.
# comment: Switch to port 5432
$ sudo vim /etc/postgresql/14/main/postgresql.conf
# comment: Switch to port 5433
$ sudo vim /etc/postgresql/12/main/postgresql.conf
$ sudo systemctl start postgresql.service
$ sudo -u postgres psql -c "SELECT version();"
By the way, I came across a similar guideline to my process 5, which is more advanced and involves additional steps using analyze_new_cluster.sh and delete_old_cluster.sh.
One more thing to note is that PostgreSQL v14 comes with a new default password encryption method, scram-sha-256. I reset the password using \password $USER.
sudo -u postgres psql
postgres=# \password $USER
Lastly, I removed the unused old PostgreSQL v12 installation. It’s always satisfying to clean up after an upgrade!
😂