Postgresql 9.2 pg_dump version mismatch

Question

I am trying to dump a Postgresql database using the pg_dump tool.

$ pg_dump books > books.out

How ever i am getting this error.

pg_dump: server version: 9.2.1; pg_dump version: 9.1.6
pg_dump: aborting because of server version mismatch

The --ignore-version option is now deprecated and really would not be a a solution to my issue even if it had worked.

How can I upgrade pg_dump to resolve this issue?

Solution 1

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.

Solution 2

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.

Solution 3

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/....

Solution 4

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

Solution 5

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

View more solutions

Comments

[Craig Ringer]

Location will vary depending on how PostgreSQL was installed (EnterpriseDB installer, MacPorts, Homebrew, etc), but the gist of the answer - that the user probably has the right version already installed - is certainly right.

[Craig Ringer]

Tip: in Terminal.app, find / -name pg_dump -type f 2>/dev/null

[Craig Ringer]

Nonono, don't just delete package managed files! If it's in /usr/ (rather than /usr/local/, /home/ or /opt/, where deleting things is generally OK) you really need to use the package manager - rpm & yum or dpkg and apt-get or aptitude, distro-depending. Either uninstall the package with rpm or alter your PATH environment variable in your .bash_profile so that the newer version is found first. If you're in doubt about something being under package management, use rpm -qf /path/to/file (RPM) or dpkg -S /path/to/file (dpkg)

[Alex Weber]

In my case, opening .bash_profile and adding "export PATH=/Applications/Postgres.app/contents/macos/bin:$PATH" did the magic.

[Seth]

This only happens when you upgrade PostgreSQL. I just did a fresh install of the OS and didn't have any problems with this.

[IanBussieres]

For anyone wondering, this solution applies to non-Heroku Ubuntu as well.

[Arthur]

According to what has been said in this answer, it's a pretty bad idea as well.

[Seth]

That answer recommends removing software by deleting a folder. Here we're just recreating a symbolic link.

[Yo Ludke]

Perfect! Recreating the symbolic link after postgres upgrade

[Franco]

Worked perfectly for me. I never imagined that the version mismatch was caused by /usr/bin/pg_dump which has nothing to do with my fresh PostgreSQL update. I'm fine with the ln solution.

[VaTo]

I'm using centos and by doing yum postgresql I get that I have the latest postgresql version installed. And I still get that error :( So it doesn't work for me.

[Seth]

@VaTo see the additional paragraph I added to the 3rd step.

[Seth]

@VaTo what is the full text of your error? Since you're on CentOS, replace that path with the path to your installation of PostgreSQL.

[VaTo]

I had to get a pg_dump newer version from another machine and then copy that pg_dump file into this machine and now it works.

[Davidson Lima]

For CentOS users (my version is 6.9 Final): sudo /usr/pgsql-<version>/bin/pg_dump <database-name> -U <username> -h localhost

[Obromios]

It was much quicker for me to find the versions using locate pg_dump

[Poutrathor]

IMO, you should not add 2>/dev/null since that will throw out the ERROR stream rather than printing it to the console. I think one should want to see any errors happening, except some rare cases. For example, in my case, it reminds me that I was not root thus failing to see some directories where the newer pg_dump version was installed. In general, don't remove errors.

[Deepak Mahakale]

@jDub9 what version did you use while taking the dump? Make sure you are using the same

[Andrius]

This is very useful. As I was confused why pg_dump was not working on one of my clusters. Turns out, it was using default Postgresql version, when one of my clusters was upgraded to newer version. Specifying cluster, solved the problem.

[vinh]

This won't replace the existing executable files on your computer, so you'll need to explicitly use the new version of pg_dump, e.g. /usr/lib/postgresql/9.6/bin/pg_dump or make a new shortcut e.g. /usr/bin/pg_dump96

[rado]

I didn't have to explicitly use the new it picked it up.

[rogerdpack]

You can sometimes (easily) get particular versions from it too do brew search postgres to see them all.

[mikebridge]

This is by far the simplest and most flexible solution.

[moodboom]

Agreed, but at least my package manager, apt in Ubuntu 20.04 using the "official" postgres repo, leaves this bad /usr/bin/pg_dump link from an old install and will not refresh it no matter what I ask it to do (and I asked it to do a LOT before giving up).

[thanos.a]

The first time it needs sudo updatedb to create the locate db

[Lahiru Jayaratne]

This worked just fine!

[John Doe]

This command was useful in my case: docker run --net host --rm -i postgres:14.1 pg_dump -h 127.0.0.1 -U postgres dbname > pg.dump.schema.sql

[Zach Milan]

For those using newer Macs (with Zsh instead of Bash), place the above in your .zshrc file.

[Gunar Gessner]

Here's a function to add to your .bashrc: pg_dump-version() { docker run --rm "postgres:${1}-alpine" pg_dump ${@:2} } ­— e.g. pg_dump 14 $PG

[Abhinav Keshri]

You should have docker installed on your system to proceed with this solution.