Postgresql 9.2 pg_dump version mismatch

Question

I encountered this while using Heroku on Ubuntu, and here's how I fixed it:

1. Add the PostgreSQL apt repository as described at "Linux downloads (Ubuntu) ". (There are similar pages for other operating systems.)

2. Upgrade to the latest version (9.3 for me) with:

   sudo apt-get install postgresql
   

3. Recreate the symbolic link in /usr/bin with:

   sudo ln -s /usr/lib/postgresql/9.3/bin/pg_dump /usr/bin/pg_dump --force
   

   The version number in the /usr/lib/postgresql/... path above should match the server version number in the error you received. So if your error says, pg_dump: server version: 9.9, then link to /usr/lib/postgresql/9.9/....

1. Check the installed version(s) of pg_dump:

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

2. My output was:

   /usr/pgsql-9.3/bin/pg_dump
   /usr/bin/pg_dump
   

3. There are two versions installed. To update pg_dump with the newer version:

   sudo ln -s /usr/pgsql-9.3/bin/pg_dump /usr/bin/pg_dump --force
   

This will create the symlink to the newer version.

Macs have a builtin /usr/bin/pg_dump command that is used as default.

With the postgresql install you get another binary at /Library/PostgreSQL/<version>/bin/pg_dump

You can just locate pg_dump and use the full path in command

locate pg_dump

/usr/bin/pg_dump
/usr/bin/pg_dumpall
/usr/lib/postgresql/9.3/bin/pg_dump
/usr/lib/postgresql/9.3/bin/pg_dumpall
/usr/lib/postgresql/9.6/bin/pg_dump
/usr/lib/postgresql/9.6/bin/pg_dumpall

Now just use the path of the desired version in the command

/usr/lib/postgresql/9.6/bin/pg_dump books > books.out

You can either install PostgreSQL 9.2.1 in the pg_dump client machine or just copy the $PGHOME from the PostgreSQL server machine to the client machine. Note that there is no need to initdb a new cluster in the client machine.

After you have finished installing the 9.2.1 software, remember to edit some environment variables in your .bash_profile file.

If you're on Ubuntu, you might have an old version of postgresql-client installed. Based on the versions in your error message, the solution would be the following:

sudo apt-get remove postgresql-client-9.1
sudo apt-get install postgresql-client-9.2

Every time you upgrade or re install a new version of PostgreSQL, a latest version of pg_dump is installed.

There must be a PostgreSQL/bin directory somewhere on your system, under the latest version of PostgreSQL that you've installed ( 9.2.1 is latest) and try running the pg_dump from in there.