[postgresql] Postgresql 9.2 pg_dump version mismatch

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?

On my scenario the production version was 12, and my development version was 11, upgrading the package postgresql-client-xx was enough to solve my incident.

Reference web page : https://www.postgresql.org/download/linux/ubuntu/

sudo apt-get update && sudo apt-get -y upgrade postgresql-client

One interest thing to point out is that after the upgrade the previous version kept installed :

mlazo@mlazo-pc:~$ dpkg -l |grep -i postgresql-client
ii  postgresql-client-11                                             11.8-1.pgdg18.04+1                                  amd64        front-end programs for PostgreSQL 11
ii  postgresql-client-12                                             12.4-1.pgdg18.04+1                                  amd64        front-end programs for PostgreSQL 12

Hope my experience would be helpful to someone.

Greetings,

Well, I had the same issue as I have two postgress versions installed.

Just use the proper pg_dump and you don't need to change anything, in your case:

 $> /usr/lib/postgresql/9.2/bin/pg_dump books > books.out

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

An alternative answer that I don't think anyone else has covered.

If you have multiple PG clusters installed (as I do), then you can view those using pg_lsclusters.

You should be able to see the version and cluster from the list displayed.

From there, you can then do this:

pg_dump --cluster=9.6/main books > books.out

Obviously, replace the version and cluster name with the appropriate one for your circumstances from what is returned by pg_lsclusters separating the version and cluster with a /. This targets the specific cluster you wish to run against.

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.

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.

The answer sounds silly but if you get the above error and wanna run the pg_dump for earlier version go to bin directory of postgres and type

./pg_dump servername > out.sql ./ ignores the root and looks for pg_dump in current directory

I experienced a similar problem on my Fedora 17 installation. This is what I did to get around the issue

• Delete the builtin pg_dump at /usr/bin/pg_dump (as root: "rm /usr/bin/pg_dump")

• Now make a symbolic link of the postgresql installation

  Again as root ln -s /usr/pgsql-9.2/bin/pg_dump /usr/bin/pg_dump

That should do the trick

First step: see if postgres has a repository with prebuilt binaries for the version you want for your OS: https://www.postgresql.org/download/

If that doesn't work (for instance if your distro is there but is no longer supported, so correct binaries aren't provided for it), or if you just want to go straight or the source and not have to worry about adding remote repo's, etc.

What I did is download the raw source of postgres for the desired version.

Untar it, cd into it, build it ./configure && make, then:

postgresql-12.3 $ find . -name pg_dump
./src/bin/pg_dump/pg_dump
$ ./src/bin/pg_dump/pg_dump


unable to load libpg.so.5 # if it says this...
$ find . -name libpg.so.5
$ export LD_LIBRARY_PATH=/your/path/to/the/shared/dir/of/above/file

$ ./src/bin/pg_dump/pg_dump # works now

Now you have access to any version that builds on your box. Which should be any.

For macs, use find / -name pg_dump -type f 2>/dev/null find the location of pg_dump

For me, I have following results:

Applications/Postgres.app/Contents/Versions/9.5/bin/pg_dump
/usr/local/Cellar/postgresql/9.4.5_2/bin/pg_dump

If you don't want to use sudo ln -s new_pg_dump old_pg_dump --force, just use:

Applications/Postgres.app/Contents/Versions/9.5/bin/pg_dump to replace with pg_dump in your terminal

For example:

Applications/Postgres.app/Contents/Versions/9.5/bin/pg_dump books > books.out

It works for me!

As explained, this is because your postgresql is in old version -> update it For Mac via homebrew:

brew tap petere/postgresql,

brew install <formula> (eg: brew install petere/postgresql/postgresql-9.6)

Remove old postgre:

brew unlink postgresql

brew link -f postgresql-9.6

If any error happen, don't forget to read and follow brew instruction in each step.

Check this out for more: https://github.com/petere/homebrew-postgresql

On Ubuntu you can simply add the most recent Apt repository and then run:

sudo apt-get install postgresql-client-11

If you're using Heroku's Postgres.app the pg_dump (along with all the other binaries) is in /Applications/Postgres.app/Contents/MacOS/bin/

so in that case it's

ln -s /Applications/Postgres.app/Contents/MacOS/bin/pg_dump /usr/local/bin/pg_dump

or

ln -s /Applications/Postgres.app/Contents/MacOS/bin/* /usr/local/bin/.

to just grab them all

For me the issue was updating psql apt-get wasn't resolving newer versions, even after update. The following worked.

Ubuntu

Start with the import of the GPG key for PostgreSQL packages.

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Now add the repository to your system.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Install PostgreSQL on Ubuntu

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

https://www.postgresql.org/download/linux/ubuntu/

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

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

If the database is installed on a different machine it has probably correct version of pg_dump installed. This means that you can execute pg_dump command remotely with SSH: ssh username@dbserver pg_dump books > books.out

You can also use public key authentication for passwordless execution. Steps to achieve that:

1. Generate (if not yet done) a pair of keys with ssh-keygen command.
2. Copy the public key to the database server, usually ~/.ssh/authorized_keys.
3. Test if the connection works with ssh command.

For those running Postgres.app:

1. Add the following code to your .bash_profile:

   export PATH=/Applications/Postgres.app/Contents/Versions/latest/bin:$PATH
   

2. Restart terminal.

For Macs with Homebrew. I had this problem when fetching the db from Heroku. I've fixed it just running:

brew upgrade postgresql

For mac users put to the top of .profile file.

export PATH="/Applications/Postgres.app/Contents/MacOS/bin:$PATH"

then run

. ~/.profile

Download the appropriate postgres version here:

https://www.postgresql.org/download/

Make sure to run the following commands (the postgresql.org/download URL will generate the specific URL for you to use; the one I use below is just an example for centos 7) as sudo:

sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install postgresql11-server

your pg_dump version should now be updated, verify with pg_dump -V

** after install postgres version is match(9.2) Create a symbolic link or new shortcut

**- on '/usr/bin'

syntag is = sudo ln -s [path for use] [new shortcut name]

example

sudo ln -s /usr/lib/postgresql/9.2/bin/pg_dump new_pg_dump

-- how to call : new_pg_dump -h 192.168.9.88 -U postgres database

I had same error and this is how I solved it in my case. This means your postgresql version is 9.2.1 but you have started postgresql service of 9.1.6.

If you run psql postgres you will see:

psql (9.2.1, server 9.1.6)

What I did to solve this problem is:

1. brew services stop [email protected]
2. brew services restart [email protected]

Now run psql postgres and you should have: psql (9.2.1)

You can also run brew services list to see the status of your postgres.

Try that:

export PATH=/usr/local/bin:$PATH

If you have docker installed you can do something like:

$ docker run postgres:9.2 pg_dump books > books.out

That will download the Docker container with Postgres 9.2 in it, run pg_dump inside of the container, and write the output.