How to fix pg_dump: aborting because of server version mismatch

Question

My codebase and database is hosted on different servers with Ubuntu 12.04.

database => web1

codebase => web2

I am trying to take my database backup (web1) through the application(web2).

Now the problem is I have different versions of postgres installed on web1 and web2.

pg_dump: server version: 9.3.12 (web1)

pg_dump version: 9.1.23 (web2)

Is there any way I can resolve this issue without upgrading any package because there are multiple applications hosted and relying on these versions.

Thanks for helping!

Intermarry

Usually pg_dump just does an sql dump. It should be restored without troubles regardless version, witn something like psql dbname < dumpfile. Unless you use some specific instruction, in which case you should look how to fix that. – Retractile

@Retractile No, that is not true. You will often have to modify the SQL file until you can downgrade. – Uncurl

Answer 1

pg_dump will refuse to connect to a server with a later version than itself for the good reason that it cannot guarantee that it will work.

Downgrading a database from 9.3 to 9.1 is definitely not supported, particularly since version 9.1 is out of support.

You'll have to use pg_dump from the 9.3 installation to dump the database, then you can try to load the result into the 9.1 database. Watch out for errors during restore and test well!

Uncurl

Answer 2

Change the client version to match the database version.

If you are using a SQL Client like Dbeaver and have more than one version of Postgres, remember to change the client version in the option. I was getting this error while using the Dbeaver's backup tool.

Forewarn