25

I installed two PostgreSQL servers in my computer. One is 9.1 and the other is 9.3. I need both servers installed.

When I run pg_dump, however, I get a version mismatch error:

server version: 9.3.6; pg_dump version: 9.1.15
pg_dump: aborting because of server version mismatch

How can I solve it? (Cannot uninstall either version - I installed the 9.1 first and the 9.3 many months later - I need both database servers installed).

4
  • Explicitly run the 9.3 version of pg_dump. Both are installed, but only one is the default. See update-alternatives for the paths, or dpkg -L the postgresql client package to see where they are. Don't have immediate access to an Ubuntu box to show you myself. Jul 9, 2015 at 23:44
  • Cannot. The pg_dump command is being run from another application. Jul 9, 2015 at 23:55
  • You'll have to change the PATH environment variable the app runs with, then. Jul 10, 2015 at 0:05
  • I had the same problem. I had to install via the PostgreSQL apt repository: askubuntu.com/a/831293/448857
    – neoneye
    Mar 11, 2018 at 1:11

3 Answers 3

23

TL;DR: if both PostgreSQL instances are managed by the Ubuntu packages (as they should), just use the --cluster option to select the PostgreSQL instance to backup, and it will automatically choose the corresponding version of pg_dump:

pg_dump --cluster 9.1/main [other pg_dump options]

or

pg_dump --cluster 9.3/main [other pg_dump options].

main is just a default value, run pg_lsclusters to see your actual names in the Cluster column.

How it works: as installed by the Ubuntu packages, /usr/bin/pg_dump is actually a soft-link to /usr/share/postgresql-common/pg_wrapper, whose purpose is precisely to select the correct instance and run the corresponding binary. --cluster does not exist in the stock PostgreSQL commands, it's a Debian/Ubuntu addition that is meant to address this multiple versions/multiple paths problem.

This is the same for psql, createdb, createuser, etc. about 18 postgres commands in /usr/bin are actually managed by pg_wrapper.

See man pg_wrapper for more.

2
  • what is the version/main for? what is main? Aug 8, 2019 at 23:33
  • 1
    @AlexanderMills: version=major version of postgresql and main=default name of the cluster. See the manpages of pg_lsclusters and pg_wrapper. Aug 9, 2019 at 14:39
10

You can use:

sudo find / -name pg_dump

to find your versions of pg_dump in my case: /usr/pgsql-9.6/bin/pg_dump

so next we can do:

sudo ln -sfn /usr/pgsql-9.6/bin/pg_dump /usr/bin/pg_dump

in order to update to the one we need

3

If you're running docker, you can run any version of pg_dump without having to manage multiple installations. This will write a file to /path/to/dump/to/myfilename.dump using postgres 12.5:

docker run --rm --network=host -v "/path/to/dump/to:/tmp/dump" \
  postgres:12.5 pg_dump -Fc -v --dbname="my_db_bame" -f "/tmp/dump/myfilename.dump"
  • --rm removes the container when the command is done
  • --network=host connects the container to the host local network
  • -v ... mounts your host directory (/path/to/dump/to) to a temp directory inside the container

You just have to make sure that the directory you mount to (/tmp/dump) is the same as the directory that pg_dump is writing to.

Then you can change 12.5 to whatever version you want.

1
  • Nice! This is quick and flexible.
    – f01
    Apr 14, 2021 at 0:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .