Re: Upgrade from PostGIS 2.3 to 2.5.5 in PostgreSQL 11 error

> On 12/02/2023 21:56 CET ghadeerdarwesh58(at)gmail(dot)com wrote:
>
> I'm facing an issue while upgrading from PostGIS 2.3 to 2.5.5 in
> PostgreSQL 11.

Use at least PostGIS 2.5.9. It's the final release of the 2.5 branch which
reached EOL in November 2022.

> I used the following command to upgrade the extension:
> ALTER EXTENSION postgis UPDATE;
>
> However, after executing the command and exiting the psql terminal, the
> database went down and the logs showed the following error:
> The postmaster has commanded this server process to roll back the current
> transaction and exit, because another server process exited abnormally
> and possibly corrupted shared memory

Does the log say anything about the other server process and the error it
triggered?

> I've tried to restore the database from a recent backup, but the issue
> persists. I'm seeking help from the community to resolve this issue.

Was the backup created with pg_dump? Those dumps do not include a specific
extension versions in the CREATE EXTENSION statement. Postgres will install
the extension's default version which is usually the latest available
version on the system. So you end up with PostGIS 2.5.5 in the restored
database.

What you can try:

Downgrade to PostGIS 2.3 to get the previous library versions and restore
your backup. I suspect the error in the libraries instead of the extension
script.

If downgrade is not possible:

1. Restore just the schema from your backup:

pg_restore --schema-only --single-transaction -f schema.sql

2. Edit schema.sql to change the CREATE EXTENSION statement to include
your particular 2.3 version:

CREATE EXTENSION postgis VERSION 'x.y.z'

3. Edit other CREATE EXTENSION statements as necessary. Other PostGIS
extension are possibly installed as well such as postgis_raster.

4. Create a new database from schema.sql.

5. Restore just the data in the new database:

pg_restore --data-only --single-transaction --disable-triggers

This may still trigger the error because the PostGIS 2.5 libraries are
still installed.

> I would be grateful if someone could assist me with resolving this issue.
> I'm available to provide any additional information or logs that may be
> helpful.

PostGIS-specific issues are best addressed to their mailing list:
https://lists.osgeo.org/mailman/listinfo/postgis-users.

--
Erik