2

I tryed to restore my old PostGIS dumps (Postgis 2.1, PostgreSQL 9.1) at my new system (PostGIS 2.2 and PostgreSQL 9.5)

I was using pgadmin3 to dump the databases and was now trying to restore it with pgadmin3 but got this error:

pg_restore: [archiver (db)] could not execute query: ERROR:  could not access file "$libdir/postgis-2.1": No such file or directory

Do I have to downgrade my PostGIS-Version back to 2.1? Or is there a more elegant way?

Im working on a Ubuntu 16.04 system.

Improve this question
1
  • 1
    I used on the psql console "\i path-to-file" to restore a db that does not wanted to be restored with the pgadmin itself. No idea why, but you could try.
    – Matte
    Commented Jul 21, 2016 at 11:01

1 Answer 1

Reset to default
4

You seem to have a PostGIS installation that was created by loading the postgis.sql file, rather than using the CREATE EXTENSION postgis command. So when you dumped your database, you got not only the data, but also all the function definitions, which includes references to the 2.1 PostGIS library.

  • Install PostGIS 2.1.8 on your new system.
  • Create a blank database, do not add PostGIS.
  • Do your restore. The function definitions in your dump file should find the PostGIS 2.1 library you've installed.
  • Turn your unpackaged PostGIS into a packaged extension by running CREATE EXTENSION postgis VERSION 2.1.8 FROM unpackaged
  • Install PostGIS 2.2.2 on your new system.
  • Upgrade your PostGIS using the extension mechanism: ALTER EXTENSION postgis UPDATE TO 2.2.2

Or you could do the package-to-extension-and-update steps on your old database pre-dump, either way. The idea though is to get away from having the function definitions mixed in with the data by using the extension system, so you get clean dumps.

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.