How to handle PostgreSQL pg_dump version mismatch error.

2023-04-18

The other day I had to connect to PostgreSQL instance on GCP Cloud SQL. This is best done using Cloud SQL Auth Proxy. I wanted to make a copy of the production database for my local development.

Once you have Cloud SQL Auth Proxy up and running, you would want to run the following command:

pg_dump -h localhost -d backend_database -U some_user \
--no-owner --no-acl -Fc -f prod.dmp

You will be asked for your database password.

At this point, you may get the following error:

pg_dump: error: server version: 14.4; pg_dump version: 13.2
pg_dump: error: aborting because of server version mismatch

What worked for me was to check first how many different pg_dump instances you have with:

find / -name pg_dump -type f 2>/dev/null

You may get something like this:

/usr/local/Cellar/postgresql@14/13.2_2/bin/pg_dump
/usr/local/Cellar/libpq/13.2/bin/pg_dump
/Library/PostgreSQL/14/bin/pg_dump

All you need to do is to symlink with:

sudo ln -sf /Library/PostgreSQL/14/bin/pg_dump \
/usr/local/bin/pg_dump

and voila, it should work now.