37

When trying to get local data to Heroku, I am encountering a version mismatch between two different versions of pg_dump.

Specifically, I am getting this message:

pg_dump: server version: 9.2.2; pg_dump version: 9.1.4
pg_dump: aborting because of server version mismatch

I have found others with this problem, but do not know enough to implement the proposed solutions. (I am new to Ruby on Rails, PostgreSQL, Heroku, and the Mac! Very much at the stage of playing around the picking things up as I go.)

I was thinking I might simplify my life if I uninstalled all PostgreSQL on my local machine and started again with a clean install of PostgreSQL 9.2.2 from http://postgresapp.com/, but I don't know how to go about doing the uninstall.

I'm running Mac OS X Mountain Lion 10.8.2.

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
user1147171
  • 1,213
  • 3
  • 14
  • 22
  • I have solved my issue changing a `link` path like noted in this answer http://serverfault.com/a/426510 – Aleks May 31 '16 at 16:00

7 Answers7

38

OS X 10.8 comes with pg_dump version 9.1.4 in the /usr/bin directory, along with psql and other programs that are client-side PostgreSQL tools. It does not mean that PostgreSQL as a server is installed (unless you have OS X Server Edition). So you don't have to uninstall PostgreSQL because it's not installed and it's better not to remove these postgres client tools in /usr/bin because they belong to the system as shipped by Apple. They just need to be side-stepped.

The package provided by postgres.app comprises both the PostgreSQL server and the client-side tools of the same version as this server. These tools get installed in /Applications/Postgres.app/Contents/MacOS/bin

To use these instead of the 9.1 ones from Apple when you work in a Terminal, postgres.app documentation says to do:

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

and put it in your .profile file.

Once you have done that and you run pg_dump, you should no longer get the error that's it's the wrong version, because it would be the one that ships with postgres.app (currently 9.2.2).

I have this setup and it works OK for me.

Community
  • 1
  • 1
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • 1
    Thank you, Daniel! Being new to OS X, I was not able to implement your suggestion right away, but I got a friend to help me. He ended up editing .bash_profile according to your suggestion. I am no longer getting the "version mismatch" error, and the dump file is getting created. So: progress! Thanks again. Mike – user1147171 Jan 09 '13 at 06:17
  • @Daniel, how would I tackle this error if I installed postgres using the official install? (http://www.enterprisedb.com/products-services-training/pgdownload#osx) – Rizon Jan 20 '13 at 22:43
  • 1
    @Rizon: AFAIK there's no _official_ install. See what [postgresql.org says](http://www.postgresql.org/download/macosx/) about the pre-compiled versions for OS X. Other than that, the generic version of the answer above is to just use the client tools (pg_dump,psql...) that come with the server you have installed. – Daniel Vérité Jan 21 '13 at 20:47
  • 4
    If you use the graphical installer (currently v 9.2.4) pg_dump is located at /Library/PostgreSQL/9.2/bin/pg_dump – Michael Wasser Jun 29 '13 at 00:11
  • 3
    Like @MichaelWasser said. If you download the graphical editor be sure to set your path in the .profile or .bash_profile to `PATH=/Library/PostgreSQL/9.2/bin/:$PATH` – keaplogik Aug 12 '13 at 16:00
  • If you're using the version of postgres.app that comes with multiple Postgres versions, you can access the tools for a specific version at `/Applications/Postgres.app/Contents/Versions/X.X/bin` – Charles Davis Jan 06 '21 at 19:28
36

If you only need to upgrade your pg_dump to the latest version and you have homebrew and mac, if the app has the latest version and your local pg doesn't:

brew upgrade postgresql

Joe
  • 1,850
  • 1
  • 13
  • 15
drjorgepolanco
  • 7,479
  • 5
  • 46
  • 47
  • 2
    This is the simplest solution and worth trying first (it worked for me on OS X 10.9) before going deep with other valid approaches. – pjmorse Sep 01 '15 at 15:50
  • 3
    unfortunately it doesn't update the pg_dump. After running this, I still get ```while executing pg_dump: pg_dump: error: server version: 13.1; pg_dump version: 12.2 (Debian 12.2-2.pgdg90+1) ``` – user3325025 Feb 12 '21 at 02:18
6

If you're using postgresapp 9.3.x, the path is different. The following worked for me (courtesy of http://sigmyers.com/blog/2013/3/12/postgres-pgdump-version-mismatch-error-postgresapp-postgresappcom)

export PG_BIN_PATH="/Applications/Postgres.app/Contents/Versions/9.3/bin/"
PATH=$PG_BIN_PATH:$PATH

Check here for the latest path: http://postgresapp.com/documentation/cli-tools.html

Adrien
  • 43
  • 1
  • 6
user1322092
  • 4,020
  • 7
  • 35
  • 52
2

I'm running Mountain Lion Server. My PostgeSQL server is at version 9.2.1 and the default tools are at 9.1.5.

I had to use:

PATH="/Applications/Server.app/Contents/ServerRoot/usr/bin:$PATH"

to make it work.

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
samvax
  • 21
  • 1
1

Yep, sometimes if you run Postgres.app this may happen after upgrade. Make sure you restart your Postgres.app - it will update your PATH.

Lukasz Muzyka
  • 2,783
  • 1
  • 30
  • 41
1

In my case I have postgresql installed via homebrew and the executables are here: /usr/local/opt/postgresql@9.6/bin

Or you copy the dump and restore executions to the /Applications/Postgres.app/Contents/SharedSupport folder

or in PdAgmin you point the PG bin Path (in properties -> binary Path) to the path of the executables of your postgre

Michel Anderson Lütz Teixeira
  • 61
  • 1
  • 6
0

We were having the same (mismatch) issue with PostgreSQL-14 DB, in our Kubernetes cluster. So, we removed the currently installed "postgresql-client-13" and installed the client version-14 with the below code

apt-get install -y postgresql-client-14 in the Docker Image.

And then we were able to connect to the DB and also take a backup dump! So, kindly, fix the mismatch as the error says. Take care!!

Rudhisundar Beura
  • 41
  • 2
  • 5