pg_dump version mismatch on Debian

Question

I'm running PostgreSQL on Debian Unstable/Sid. I've got the current versions (9.1) of postgresql, postgresql-client, postgresql-client-common (143) installed.

The problem I'm having is trying to run pg_dump. I'm getting this error:

pg_dump: server version: 9.1.9; pg_dump version: 9.0.6
pg_dump: aborting because of server version mismatch

I can't seem to figure out how to get a newer version of pg_dump. It seems to be coming from the postgresql-client-common package, and I've trying running apt-get --reinstall on it, but that didn't get me a newer version of pg_dump.

How do I work around this? Or where can I find a package with a newer version of pg_dump?

Answer 1

Another option, that may be suitable for you, is to uninstall the old parallel version:

on debian/ubuntu:

sudo apt-get remove postgresql-8.4
sudo apt-get remove postgresql-client-8.4

That preserves the later version (e.g 9.1) removing only the older 8.4 client and server libs.

Answer 2

In my case I had two sets of databases some running on the old version of Postgresql 8.4 and other running on version 9.1. What I did was to locate pg_dump in Linux machine using the locate command below

$ locate pg_dump

/usr/bin/pg_dump
/usr/bin/pg_dumpall
/usr/lib/postgresql/8.4/bin/pg_dump
/usr/lib/postgresql/8.4/bin/pg_dumpall
/usr/lib/postgresql/9.1/bin/pg_dump
/usr/lib/postgresql/9.1/bin/pg_dumpall

Since the default /usr/bin/pg_dump is for Postgresql version 8.4, I just specified /usr/lib/postgresql/9.1/bin/pg_dump from command line when dumping from 9.1 databases which runs on a different port and it worked.

$ /usr/lib/postgresql/9.1/bin/pg_dump -p 5434

Answer 3

Couple of options.

Download .deb 9.1 from the Postgres site

Take a look at this page titled: Linux downloads (Debian) - PostgreSQL. You can either download an updated .deb from the site directly, or re-point to their repository, and do a command like this:

apt-get install postgresql-9.1

Debian backports

You may be able to find specific versions here, http://backports.debian.org/.

Use a generic version

You can download a binary build of PostgreSQL and put install it in your home directory or /opt for example.

Download one of the pre-builds for enterprise use

I don't have much experience with these but you may be able to download one of these similar to a generic version and use the client from it's installation, as is, do dump your database.

Cross distribution packages

You can download packages which have been built so that they're distribution agnostic. I just downloaded the 9.1.9 version and it does include the pg_dump tool.

The software is provided as either .rpm or .deb and installs to /opt/postgres/9.1. Specifically the pg_dump tool is provided here: /opt/postgres/9.1/bin/pg_dump.

Answer 4

In addition to the answers above, you can also tell pg_dump and other commands, which version to use via the cluster option. For instance,

$pg_dump --cluster 9.1/main

will target the 9.1 version.

Note that this makes use of pg_wrapper, which comes with postgresql-common and works on Ubuntu (Debian). Also note that the cluster (main in the example) might be different in your setting.

More information on pg_wrapper can be found in this DBA answer.

Answer 5

I ran into the same problem today and Erick's answer seems to be most accurate. The problem is likely that you have different versions of the postgresql-client and pg_dump is just using the oldest client.

You can resolve this by using the full path as he described, but I found an easier solution to be to remove the postgresql-client-common package (which removes all clients), then reinstall postgresql-client-9.3. This leaves you with only the latest versions of pg_dump which is probably want you want.