Mac OS

I am trying to upgrade my Postgres version 9.2 to 9.6 and failing. My strategy was to follow the standard advice for Postgres databases and use pg_dumpall. This does not appear to work if you are using PostGIS extensions because the dump contains 1000s of references to the actual PostGIS library on disk. See this.

So how does one move around PostGIS databases without links to actual libraries embedded in them?

  • Have you considered OGR2OGR? I'm assuming both PostGIS installs are working properly you should be able to use OGR2OGR -f PostgreSQL but you must manually create the database(s) the layers are going into see gdal.org/drv_pg.html for limitations. – Michael Stimson Aug 2 '18 at 3:25

pg_dumpall should work. Make sure that you have already installed the new PostGIS extension, and that you are not using the --clean option.

Doing so, it will try to recreate the old PostGIS items but will fail since they already exist, and move on to the next item.

  • It does not work in my case because the tables were created with a version of Postgres older than 9.2. It was at 9.2 that the extension management changed and decoupled the extension from the from the library file on disk. I have accepted this answer as correct as it answers the question as asked. At the time I was unaware of the migration issues with tables created by earlier versions. – Russell Fulton Aug 7 '18 at 1:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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