6

I have several installs of Postgres / PostGIS, version 9.4 up to and including 9.6. I would like to upgrade all the databases (maybe 20 total) in the various installs to a single install of the current version.

How do I migrate the databases to the current version? Do I just backup each database in situ, restore in the new PG instance and then upgrade the PostGIS extension (like ALTER EXTENSION postgis UPDATE TO "2.3.1";)? Any other steps?

4

As long as you have latest PostGIS 2.3.1 on your PostgreSQL, then when you restore, it should just upgrade to the latest.

The pg_dump just has a line CREATE EXTENSION postgis;

So would naturally pick up the latest on pg_restore.

If you are doing upgrade pg_upgrade, then that's a bit more complicated since you need to make sure the PostGIS version you are running is installed in both Postgres instances and then you'd need to do:

 ALTER EXTENSION postgis UPDATE;

If you leave the version number out, it will upgrade to the one listed in postgis.control file (which is usually the newest).

2

When I used pg_upgrade to upgrade the databases without a full dump and restore, it complained with:

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    loadable_libraries.txt

In loadable_libraries.txt the Postgis lib was mentioned. To fix this, one can simply copy the new postgis and rtpostgis libraries to the old versions. E.g.:

cp /usr/lib/postgresql/10/lib/postgis-2.4.so /usr/lib/postgresql/10/lib/postgis-2.2.so  
cp /usr/lib/postgresql/10/lib/rtpostgis-2.4.so /usr/lib/postgresql/10/lib/rtpostgis-2.2.so

After that, the pg_upgrade command should run without errors. Then, as the next step, run:

ALTER EXTENSION postgis UPDATE;

Then verify that the version is current:

SELECT postgis_full_version();

There should be no mention of "need upgrade for use with" in the output. After that you can delete the copied libraries.

Credit: http://www.bostongis.com/blog/index.php?/archives/268-Using-pg_upgrade-to-upgrade-PostGIS-without-installing-an-older-version-of-PostGIS.html

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.