Upgrade PostgreSQL from version 9 to 10 on Ubuntu
Determine actual version
$ sudo -u postgres psql
First install new version
To install the new version, I want especially version 10, I will first add the PosgreSQL Apt Repository to my Ubuntu installation as follows.
# Create the file repository configuration: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' # Import the repository signing key: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - # Update the package lists: sudo apt-get update # Install the latest version of PostgreSQL. # If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql': sudo apt-get -y install postgresql
Therefore I will first add the repository and signing key, run the apt update command and to install the specific version 10, I will run
$ sudo apt-get -y install postgresql-10
After that we want to check what versions now are running on our system, therefore you can run one of the following commands.
$ dpkg -l | grep postgresql $ dpkg --get-selections | grep postgresql
Further we want to check which clusters are online at the moment, so run the following command.
I don’t want to hear anything about why I am logged in as root 🙂
Upgrade to the new version
Now we need to stop PostgreSQL in order to upgrade to the new cluster version.
$ sudo systemctl stop postgresql
We also want to check if both cluster now down with
As the installation of the new version by default creates a cluster for us with the same name main as the old version, we need to rename the new PostgreSQL cluster in order to not run into an names conflict between the two versions.
$ sudo pg_renamecluster 10 main main_ver10
Now we can upgrade my old version 9.5 to the new version 10 as follows.
$ sudo pg_upgradecluster 9.5 main
Depending on the size and numbers of databases running on the server, the upgrade process can take several minutes, so grab a coffee ☕ and relax.
As mentioned in the console at the end of the upgrade process in the above screenshot, we can now start the PostgreSQL Server and check that everything works fine.
$ sudo systemctl start postgresql
Here you can see three clusters, the first one is the old cluster which is down. The second is the one we are interested in, it is the upgraded cluster from 9.5 to 10. And the last one is the newly by default created and renamed cluster from the installation of the new PostgreSQL sever version 10.
So the next step is to delete the first and last cluster.
In my case I have running databases for Atlassians Confluence and Jira Systems, so I can further check in the backend to which cluster the systems are connected.
In Confluence I can check this under General configuration – Administration – System Information
Regarding the port number 5432 this is as mentioned the second cluster in our list.
In Jira you can check this under System – System Information
So I can stop and drop the first and last cluster as follow.
$ sudo pg_dropcluster 9.5 main –stop
$ sudo pg_dropcluster 10 main_ver10 –stop
Jira and Confluence are still running so we are finish 🙂