{"id":2644,"date":"2024-09-02T05:27:03","date_gmt":"2024-09-02T05:27:03","guid":{"rendered":"https:\/\/bynatree.com\/?p=2644"},"modified":"2024-09-02T05:27:03","modified_gmt":"2024-09-02T05:27:03","slug":"upgrading-postgresql-and-postgis-from-9-1-to-12-20-with-legacy-extension-support","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2024\/09\/02\/upgrading-postgresql-and-postgis-from-9-1-to-12-20-with-legacy-extension-support\/","title":{"rendered":"Upgrading PostgreSQL and PostGIS from 9.1 to 12.20 with Legacy Extension Support"},"content":{"rendered":"

Upgrading PostgreSQL and PostGIS<\/h1>\n

Recently we did an upgrade of the legacy PostgreSQL 9.1 database of 1 TB size with PostGIS 1.5 version to PostgreSQL 12 version with PostGIS version 3.4.2.<\/span><\/p><\/blockquote>\n

Upgrading a PostgreSQL database along with its PostGIS extensions can be a daunting task, especially when dealing with legacy versions. In this guide, I\u00a0 will walk you through the process I used to upgrade the PostgreSQL 9.1 database with PostGIS 1.5 to PostgreSQL 12.20, while also upgrading the PostGIS extension to version 3.4.2. This process includes steps to safely transition through intermediate versions of both PostgreSQL and PostGIS to ensure compatibility and maintain the integrity of your spatial data.<\/span><\/p>\n

Here are the high level steps to perform the upgrade. To simulate the existing environment, install and configure the PostgreSQL 9.1 with PostGIS 1.5<\/span><\/p>\n

Install PostgreSQL 9.1<\/h2>\n
sudo apt install curl ca-certificates\nsudo install -d \/usr\/share\/postgresql-common\/pgdg\nsudo curl -o \/usr\/share\/postgresql-common\/pgdg\/apt.postgresql.org.asc --fail https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc\nsudo sh -c 'echo \"deb [signed-by=\/usr\/share\/postgresql-common\/pgdg\/apt.postgresql.org.asc] https:\/\/apt.postgresql.org\/pub\/repos\/apt $(lsb_release -cs)-pgdg main\" > \/etc\/apt\/sources.list.d\/pgdg.list'\nsudo apt update\nsudo apt-get install postgresql-server-dev-9.1 postgresql-9.1<\/pre>\n
Install PostGIS 1.5<\/h2>\n
Next, I installed PostGIS 1.5 by compiling it from source:<\/span><\/p>\n
sudo apt-get install gcc build-essential libproj-dev libjson-c-dev libxml2-dev libxml2-utils xsltproc libgeos*\nwget http:\/\/download.osgeo.org\/postgis\/source\/postgis-1.5.8.tar.gz\ntar xfvz postgis-1.5.8.tar.gz\ncd postgis-1.5.8\n.\/configure CFLAGS=\"-fPIC -DACCEPT_USE_OF_DEPRECATED_PROJ_API_H -I\/usr\/include\/postgresql\/9.1\/server -I\/root\/postgis-1.5.8\/liblwgeom\" --prefix=\/usr\/share\/postgresql\/9.1\/contrib\/postgis-1.5.8\/\nmake\nmake install<\/pre>\n
Create PostGIS extension<\/h3>\n
cd \/usr\/share\/postgresql\/9.1\/contrib\/postgis-1.5\nmars=# i postgis.sql\nSET\npostgres@ip-xxx-xx-x-x:\/usr\/share\/postgresql\/9.1\/contrib\/postgis-1.5$ psql -d mars\npsql (9.1.24)\nType \"help\" for help.\nmars=# SELECT PostGIS_Version();\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 postgis_version\n---------------------------------------\n1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1\n(1 row)\nmars=# q<\/pre>\n
Installation is completed, now we will move to the next steps of actual upgrading.<\/span><\/p>\n
Install and Upgrade PostGIS Extension from 1.5 to 2.2<\/h4>\n
Upgrading the PostgreSQL engine to a new version doesn’t automatically upgrade the PostGIS extension. To ensure compatibility, there will be a series of steps which upgrade PostGIS and PostgreSQL to intermediate versions. Here are the steps we followed to perform the upgrade.\u00a0<\/span><\/p>\n
Install and upgrade to PostGIS 2.2<\/span><\/h4>\n
Upgrade the PostGIS extension to the latest version supported by PostgreSQL 9.1 as outlined in <\/span>https:\/\/trac.osgeo.org\/postgis\/wiki\/PostGISObsoleteVersionsMatrix<\/span><\/a>.<\/span><\/p>\n
The latest available PostGIS package is version 2.2 which supports PostgreSQL 9.1.<\/span><\/p>\n
export LD_LIBRARY_PATH=\/usr\/lib\/postgresql\/9.1\/lib\/:$LD_LIBRARY_PATH\nexport PATH=\/usr\/lib\/postgresql\/9.1\/bin\/:$PATH\n.\/configure CFLAGS=\"-fPIC -DACCEPT_USE_OF_DEPRECATED_PROJ_API_H -I\/usr\/include\/gdal -I\/usr\/include\/postgresql\/9.1\/server -I\/root\/postgis-2.2.0\/liblwgeom -I\/root\/postgis-2.2.0\/raster\/rt_core\/\" --prefix=\/usr\/share\/postgresql\/9.1\/contrib\/postgis-2.2\/\nmake CFLAGS=\"-fPIC -DACCEPT_USE_OF_DEPRECATED_PROJ_API_H -I\/usr\/include\/gdal -I\/usr\/include\/postgresql\/9.1\/server -I\/root\/postgis-2.2.0\/liblwgeom -I\/root\/postgis-2.2.0\/raster\/rt_core\/\"\nmake install<\/pre>\n
Upgrade PostGIS from 1.5 to 2.2<\/span><\/h4>\n
Given the PostGIS version change is a major version change from PostGIS 1.5 to 2.2, a full dump and restore of the database were required to perform the extension upgrade.<\/span><\/p>\n
pg_dump -Fc -b -v -f \"\/tmp\/mars.backup\" mars\nperl postgis_restore.pl \"\/tmp\/mars.backup\" | psql mars 2> \/tmp\/errors.txt<\/pre>\n
Upgrade PostgreSQL to 9.5<\/h3>\n
PostgreSQL 9.5 is the intermediate version of PostgreSQL which supports PostgreGIS 2.2. Install PostgreSQL 9.5 with PostGIS 2.2.<\/span><\/p>\n
sudo apt-get install postgresql-server-dev-9.5 postgresql-9.5\n<\/pre>\n
Install PostGIS 2.2 in PostgreSQL 9.5 extensions<\/span><\/h4>\n
export LD_LIBRARY_PATH=\/usr\/lib\/postgresql\/9.5\/lib\/:$LD_LIBRARY_PATH\nexport PATH=\/usr\/lib\/postgresql\/9.5\/bin\/:$PATH\n.\/configure CFLAGS=\"-fPIC -DACCEPT_USE_OF_DEPRECATED_PROJ_API_H -I\/usr\/include\/gdal -I\/usr\/include\/postgresql\/9.5\/server -I\/root\/postgis-2.2.0\/liblwgeom -I\/root\/postgis-2.2.0\/raster\/rt_core\/\" --prefix=\/usr\/share\/postgresql\/9.5\/contrib\/postgis-2.2\/\nmake\nmake install<\/pre>\n
Upgrade PostgreSQL 9.1 to 9.5<\/span><\/h4>\n
\/usr\/lib\/postgresql\/9.5\/bin\/pg_upgrade -b \/usr\/lib\/postgresql\/9.1\/bin -B \/usr\/lib\/postgresql\/9.5\/bin -d \/var\/lib\/postgresql\/9.1\/main -D \/var\/lib\/postgresql\/9.5\/main --check\n\/usr\/lib\/postgresql\/9.5\/bin\/pg_upgrade -b \/usr\/lib\/postgresql\/9.1\/bin -B \/usr\/lib\/postgresql\/9.5\/bin -d \/var\/lib\/postgresql\/9.1\/main -D \/var\/lib\/postgresql\/9.5\/main<\/pre>\n
Upgrade PostGIS to 2.5 and PostgreSQL to 12.20<\/h3>\n
The PostGIS 2.5 version is the highest supported version by PostgreSQL 9.5 and PostGIS 2.5 is the lowest supported version by PostgreSQL 12. So the first step is to upgrade PostGIS from 2.2 to 2.5 version.<\/span><\/p>\n
PostGIS does not support direct upgrade from 2.2 to 2.5 so need to install 2.3, 2.4 and 2.5 versions and upgrade the extension step by step. Here I am directly jumping to 2.5 installation.<\/span><\/p>\n
sudo apt-get install postgresql-9.5-postgis-2.5<\/pre>\n
Install PostgreSQL 12<\/span><\/h4>\n
sudo apt-get install postgresql-12 postgresql-client-12 postgresql-contrib-12 postgresql-12-postgis-2.5\nsudo apt-get install postgresql-12-postgis-3<\/pre>\n
Upgrade PostgreSQL 9.5 to 12<\/span><\/h4>\n
\/usr\/lib\/postgresql\/12\/bin\/pg_upgrade -b \/usr\/lib\/postgresql\/9.5\/bin -B \/usr\/lib\/postgresql\/12\/bin -d \/var\/lib\/postgresql\/9.5\/main -D \/var\/lib\/postgresql\/12\/main --check\n\/usr\/lib\/postgresql\/12\/bin\/pg_upgrade -b \/usr\/lib\/postgresql\/9.5\/bin -B \/usr\/lib\/postgresql\/12\/bin -d \/var\/lib\/postgresql\/9.5\/main -D \/var\/lib\/postgresql\/12\/main<\/pre>\n
Upgrade PostGIS Extensions to 3.4.2<\/h3>\n
Lastly, I upgraded the PostGIS extensions to the latest version which is compatible with PostgreSQL 12<\/span><\/p>\n
mars=# SELECT postgis_extensions_upgrade();\nmars=# SELECT postgis_full_version();<\/pre>\n
This step completes the upgrade process, and the database is now running on PostgreSQL 12.20 with PostGIS 3.4.2.<\/span><\/p>\n
Conclusion<\/h2>\n
Upgrading a PostgreSQL database with PostGIS extensions is a meticulous process, requiring careful planning and execution. By following the steps outlined above, you can ensure a smooth transition to newer versions while preserving the integrity of your spatial data. Always remember to backup your database before proceeding with major upgrades.<\/span><\/p>\n
Follow our more blog for postgresql<\/a> new features and for increasing performance and database adminstrator services reach out to us https:\/\/divaind.com\/ie1\/contact\/<\/a>.<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"
Upgrading PostgreSQL and PostGIS Recently we did an upgrade of the legacy PostgreSQL 9.1 database of 1 TB size with PostGIS 1.5 version to PostgreSQL 12 version with PostGIS version 3.4.2. Upgrading a PostgreSQL database along with its PostGIS extensions can be a daunting task, especially when dealing with legacy versions. In this guide, I\u00a0…<\/p>\n","protected":false},"author":1,"featured_media":3011,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[254,260,261,266,267,269,288],"class_list":["post-2644","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-pgsql","tag-postgis","tag-postgis-1-5","tag-postgresql","tag-postgresql-12","tag-postgresql-9-1","tag-postgresql12","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2644","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2644"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2644\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/3011"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2644"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2644"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2644"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}