How to Upgrade the PostgreSQL Server Version Without Losing Any Data

  2. HowTo
  3. PostgreSQL Howtos
  4. How to Upgrade the PostgreSQL Server …
Bilal Shahid Feb 02, 2024
PostgreSQL PostgreSQL Update
  1. Importance of Upgrading the PostgreSQL Server Version
  2. Different Approaches to Update the PostgreSQL Server Version
How to Upgrade the PostgreSQL Server Version Without Losing Any Data

Usually, many users do not upgrade the PostgreSQL server to the latest version because of the fear of data loss in the process. Updating the PostgreSQL server to a new version is a little tricky and may result in a complete data loss if the correct procedure is not followed.

Therefore, conducting thorough research is essential before upgrading the PostgreSQL server to the newest version. This tutorial will go through the step-by-step process of upgrading the PostgreSQL server version without losing any data.

Importance of Upgrading the PostgreSQL Server Version

Yes! It is essential to upgrade the PostgreSQL server to the newest version due to numerous reasons that are listed below:

  • The older versions may no longer remain compatible with other applications.
  • Cybercriminals can find vulnerabilities in your system due to the unpatched threats in the older versions and attack the PostgreSQL server.
  • Bugs in the older versions would be patched in the newer version.
  • The new version offers enhanced features that help perform the tasks efficiently.

This article includes the method to update the PostgreSQL server from one version to another successfully.

The instructions apply to all operating systems.

Different Approaches to Update the PostgreSQL Server Version

Multiple methods exist to update the PostgreSQL server from one version to another without losing data. Here, we will demonstrate three of them to upgrade the PostgreSQL server.

You can select any one of the following solutions depending upon your requirements.

Solution 1: The General Solution

It works for any operating system and can update the PostgreSQL server to any version of your choice without manipulating the data. Since this is a general solution, the exact BASH commands are not mentioned.

These commands can be searched for the different operating systems and implemented as instructed:

  1. Stop any running instance of the PostgreSQL server using the BASH command for your specific operating system.
  2. Install the new version of the PostgreSQL server you would like to shift to and start it.
  3. Check if you can connect to the new version of the PostgreSQL server you have just installed.
  4. Change the port number of the old version of PostgreSQL server, postgresql.conf -> port from 5432 to 5433.
  5. Start the PostgreSQL server’s old version of the new port number 5433.
  6. Open the terminal and change the directory using the cd command to the new version’s bin folder.
  7. Run the command pg_dumpall -p 5433 -U <username> | psql -p 5432 -U <username> on the terminal.
  8. Stop the running instance of the old PostgreSQL server.

Implementing the entire procedure using BASH commands designed for your specific operating system will help you upgrade your PostgreSQL server from an old version to a new version.

Note: An issue that you can encounter with this solution is that if you have changed some of the Postgres config files - for example, postgresql.conf or pg_hba.conf - these changes would need to be manually replicated in the new PostgreSQL server’s installation. The pg_upgradecluster command can help copy config files to the new cluster.

Solution 2: Use Assumption Used by Homebrew

This solution focuses on the assumption that Homebrew software has been used to install and upgrade Postgres. Follow the steps below to upgrade the PostgreSQL server from one version to another.

For simplicity, it is assumed that Postgres v9.6 is being updated to Postgres v10.1.

  • Stop the running instance of the current PostgreSQL server:
    launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
  • Install and initialize a new 10.1 database using the initdb command:
    initdb /usr/local/var/postgres10.1 -E utf8
  • Run the pg_upgrade command. This command is used with different extensions, some of which have been mentioned below:
    pg_upgrade -v \
-d /usr/local/var/postgres \
-D /usr/local/var/postgres10.1 \
-b /usr/local/Cellar/postgresql/9.6/bin/ \
-B /usr/local/Cellar/postgresql/10.1/bin/

# The different extensions of the `pg_upgrade` command have the following significance:
# The `-v` extension enables verbose internal logging.
# The `-d` extension is used to specify the configuration directory of the old database.
# The `-D` extension is used to specify the configuration directory of the new database.
# The `-b` extension specifies the executable directory of the old database.
# The `-B` extension specifies the executable directory of the new database.
  • Shift the data from the old database to a new place using the following commands:
    cd /usr/local/var
mv postgres postgres9.6
mv postgres10.1 postgres
  • After successfully performing all four steps, restart the PostgreSQL server. We can use the following command for this purpose:
    launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

    After restarting Postgres, check /usr/local/var/postgres/server.log for the details of the new server. Ensure that the new server has appropriately started and is running correctly.

  • The rails pg gem should be reinstalled using the following commands:
    gem uninstall pg
gem install pg

Revisions to the Second Solution

Homebrew now allows users to use brew services start postgresql and brew services stop postgresql commands rather than the old launchctl load and launchctl unload commands.

The commands added to the brew services are highlighted below for reference:

brew services stop postgresql
brew upgrade postgresql
brew postgresql-upgrade-database
brew services start postgresql

Solution 3: For Ubuntu Users

This solution is specifically for Ubuntu users. The general procedure is the same as described earlier in the article; however, this solution contains terminal commands used in Ubuntu.

The steps are mentioned below:

  • Stop the old version of Postgres using the following command:
    sudo /etc/init.d/postgresql stop
  • A new file needs to be created /etc/apt/sources.list.d/pgdg.list and edited with the addition of the following line:
    deb http://apt.postgresql.org/pub/repos/apt/ utopic-pgdg main

    Note: We can use the trusty-pgdb command for Ubuntu 14.04 instead of the utopic-pgdb command.

  • Once the line has been added to the newly created file, execute the commands mentioned below:
    wget -q -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add-
sudo apt-getupdate
sudo apt-get install postgresql-10.1
sudo pg_dropcluster --stop 10.1 main
sudo /etc/init.d/postgresql start
  • Upgrade the PostgreSQL server:
    sudo pg_upgradecluster 9.6 main
sudo pg_dropcluster 9.6 main

    The upgraded cluster is supposed to run on port 5433. So, check the port to ensure a successful upgradation of the PostgreSQL server using the sudo pg_lsclusters command.

It is essential to follow a correct process for upgrading the PostgreSQL server from one version to another; otherwise, an incorrect upgradation process can lead to data loss. Therefore, one should remain careful.

Author: Bilal Shahid
Bilal Shahid avatar Bilal Shahid avatar

Hello, I am Bilal, a research enthusiast who tends to break and make code from scratch. I dwell deep into the latest issues faced by the developer community and provide answers and different solutions. Apart from that, I am just another normal developer with a laptop, a mug of coffee, some biscuits and a thick spectacle!

GitHub

Related Article - PostgreSQL Update