Resolving PostgreSQL pg_dump server version mismatch

Introduction

PostgreSQL, also known as Postgres, is an open-source, object-relational database system renowned for its robust feature set, extensibility, and dependability. PostgreSQL is one of the most advanced and powerful database management systems available today, having been developed over decades with strong community support. In this post we will look into Resolving PostgreSQL pg_dump server version mismatch

Error :

I received this error when I attempted to backup my database running on PostgreSQL server version 15. I also tried it with the –cluster 14/main option, but it did not work. Using the steps below, I was able to resolve this error.

⚡ root@vishalvyas$  pg_dump -h localhost -p 5432 -U my-username -d my-db-name -F c > backup.dump
Password: 
pg_dump: error: server version: 15.3; pg_dump version: 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1)
pg_dump: error: aborting because of server version mismatch

Reason :

This pg_dump: error: aborting because of server version mismatch occurred when your pg_dump version differed between the server and your PC. For example, on your local PC, you are using pg_dump version 14, but on the server, pg_dump is running version 15. This could mismatch the pg_dump version, resulting in an error.

Solution :

To fix this error pg_dump: error: aborting because of server version mismatch. You must upgrade the pg_dump version on your local computer from which you are attempting to connect to the server. In my case, I was using PostgreSQL 14, and my server was running PostgreSQL 15. After upgrading the PostgreSQL version on my local PC, it worked. Here are the steps to upgrade PostgreSQL from 14 to 15.

How to upgrade PostgreSQL from 14 to 15

PostgreSQL 15 is not included in the default package repository. To install PostgreSQL 15 or later, add an additional APT repository to /etc/apt/sources.list.d/.

Add the official PostgreSQL package repository to the APT sources directory.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null

Update the package repository

sudo apt update

Run below command to install postgres 15.

sudo apt install postgresql

Check the PostgreSQL version to ensure the installation was successful.

psql --version

Conclusion

To effectively resolve the ‘pg_dump server version mismatch error’, align the pg_dump version on the local machine with the version running on the PostgreSQL server. Users can ensure seamless database backup operations and maintain compatibility across environments by following the steps outlined in this guide to upgrading PostgreSQL from version 14 to 15.