0

In my current example I have to migrate ~90 databases between 2 Servers ...

  • source: debian11, postgres-11, postGIS 2.5
  • target: rhel8, postgres-12, postGIS 3.2.2

on PostGIS upgrades i read in the manual

35.1.2.1. Dump/Restore
Dumping and restoring involves converting all the data to a platform neutral format (text representations) on dump, and back to native representations on restore, so it can be time consuming and CPU intensive. However, if you are migrating to a new architecture or operating system, it’s a required process. It’s also a time-tested and well-understood upgrade path, so if your database is not too big, there’s no reason not to stick with it.

  • Dump your data pg_dumpall from the old database.
  • Install the new version of PostgreSQL and the same version of PostGIS you are using in your old database. You need to match the PostGIS version so that the dump file function definitions reference an expected version of the PostGIS library.
  • Initialize the new data area using the initdb program from the new software.
  • Start the new server on the new data area. Restore the dump file using pg_restore.

that is quite clear but I do not get where the upgrade to a new PostGIS Version is happening in here?

based on the above it looks like I was bound to postGIS 2.5 forever. Or is this just the first step and I have to ...

  1. install new PostGIS on that postgres-12 server
  2. run SELECT postgis_extensions_upgrade(); on each database
4
  • I would simply pre-install PostGIS 3.2 in the new database and restore the dump. Most things should work ok, a few things might have to be adjusted. Nov 16, 2022 at 20:16
  • thx ... can those "few things" be listed in a few sentences here, or would that require detailed inspection and understanding of the particular databases and/or PostGIS?
    – vrms
    Nov 17, 2022 at 11:46
  • That would require detailed inspection and understanding of the particular databases and/or PostGIS. Nov 17, 2022 at 12:00
  • then it is not for me atm, thx. I guess I'll have to figure out the other route then.
    – vrms
    Nov 17, 2022 at 12:10

1 Answer 1

1

You read the documentation right. The recommended path is to stay on PostGIS 2.5 while you are upgrading PostgreSQL. Then, in a second step, you upgrade PostGIS. Install the new software and run

ALTER EXTENSION postgis UPDATE;
SELECT postgis_extensions_upgrade();
-- This second call is needed to rebundle postgis_raster extension
SELECT postgis_extensions_upgrade();
4
  • what I dont get is how to upgrade PostGIS works. In my understanding it is always a postgres/PostGIS bundle. So I don't get where this new-postGres/old-PostGIS is getting the new PostGIS version from. I guess ALTER EXTENSION postgis UPDATE; & postgis_extension_upgrade need to have access to the old as well as the new postGIS version. Or ist the bundle idea wrond after all?
    – vrms
    Nov 18, 2022 at 8:10
  • No, PostGIS is different from PostgreSQL, has different versions and release cycles. You may be confused because you get both from the same packager. You can install a new version of PostGIS independently from upgrading PostgreSQL (though it may replace the previous PostGIS software, which is fine). Nov 18, 2022 at 8:14
  • maybe it is worth mentioning (which I forgot so far) that we do not have access to distribution packages but are building this ourselves (due to company regulations). And for now always a postgres/postGIS unit is being build. So based on what you say above we have to firgure out, how to build and unbundled, standalone PostGIS in order to run such a migration.
    – vrms
    Nov 18, 2022 at 8:17
  • Precisely. Which should be easy if you build the software yourself. Nov 18, 2022 at 8:24

Your Answer

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

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