[postgis-users] Help to upgrade postgresql10 with postgis 2.5
pham lan
phamlankt at gmail.com
Fri Mar 17 22:21:01 PDT 2023
Hi Regina,
So I tried to pg_upgrade at least from postgresql 10 (postgis 2.5) to
postgresql 12 (postgis 2.5) and get the following error. Do you have any
idea why? (I tried this several times, I even pg_basebackup to another
server with postgresql 10 and pg_upgrade to postgres12 but got the same
error)
> /usr/pgsql-12/bin/pg_upgrade -b /usr/pgsql-10/bin/ -B /usr/pgsql-12/bin/
-d /var/lib/pgsql/10/data/ -D /var/lib/pgsql/12/data/ -p 5432 -P 5433
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster
*failure*
Consult the last few lines of "pg_upgrade_utility.log" for
the probable cause of the failure.
Failure, exiting
1 postgres at host:~> cat pg_upgrade_utility.log
-----------------------------------------------------------------
pg_upgrade run on Sat Mar 18 06:16:00 2023
-----------------------------------------------------------------
command: "/usr/pgsql-12/bin/pg_dumpall" --host /var/lib/pgsql --port 5432
--username postgres --globals-only --quote-all-identifiers --binary-upgrade
-f pg_upgrade_dump_globals.sql >> "pg_upgrade_utility.log" 2>&1
command: "/usr/pgsql-12/bin/vacuumdb" --host /var/lib/pgsql --port 5433
--username postgres --all --analyze >> "pg_upgrade_utility.log" 2>&1
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
WARNING: terminating connection because of crash of another server process
DETAIL: 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.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
vacuumdb: error: vacuuming of table "pg_catalog.pg_proc" in database
"template1" failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Thanks,
Lan
On Fri, Mar 17, 2023 at 10:27 AM pham lan <phamlankt at gmail.com> wrote:
> Yes, actually I followed your instructions.
> After drop extension postgis_sfcgal whichs required me to drop cascade 13
> materialized views, the upgrade to postgres15 went well.
> Afterward, I wanted to update the postgis extension:
>
> ALTER EXTENSION postgis UPDATE;
>
> it requests me to drop cascade around 200 materialized views. For the
> test DB, I did that, the update to postgis 3.3 went well but afterward, the
> vacuum step failed.
> In the end, I talked to the teams and they can not afford to have those
> materialized views deleted and then being created again by them.
> That's why I wanna ask if there is a simple way of migrating the
> materialized views? I am not familiar with this.
>
> Thanks,
> Lan
>
> On Fri, Mar 17, 2023 at 9:26 AM Regina Obe <lr at pcorp.us> wrote:
>
>> You’d probably want to upgrade PostgreSQL too since PostgreSQL 10 is
>> EOL’d.
>>
>>
>>
>> Below are some instructions I had written for Centos. Should be more or
>> less the same for Redhat.
>>
>>
>>
>>
>> https://www.bostongis.com/blog/index.php?/archives/278-Using-pg_upgrade-to-upgrade-PostgreSQL-9.6-PostGIS-2.4-to-PostgreSQL-15-3.3-on-Yum.html
>>
>>
>>
>>
>>
>>
>>
>> *From:* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] *On
>> Behalf Of *pham lan
>> *Sent:* Friday, March 17, 2023 3:56 AM
>> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
>> *Subject:* Re: [postgis-users] Help to upgrade postgresql10 with postgis
>> 2.5
>>
>>
>>
>> Hi Regina,
>>
>>
>>
>> Thank you for your reply. Below is the output of the command. Do you know
>> a good way to migrate all the definitions of materialized views because we
>> have a lot of them and they are quite complex?
>>
>> # SELECT postgis_full_version(), version();
>>
>> postgis_full_version
>> |
>> version
>>
>> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>
>> -----------------------+----------------------------------------------------------------------------------------------------------
>> POSTGIS="2.5.5" [EXTENSION] PGSQL="100" GEOS="3.9.2-CAPI-1.14.3"
>> SFCGAL="1.4.1" PROJ="Rel. 7.2.1, January 1st, 2021" GDAL="GDAL 3.2.3,
>> released 2021/04/27" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="
>> 1.3.0" TOPOLOGY RASTER | PostgreSQL 10.23 on x86_64-pc-linux-gnu,
>> compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit
>>
>> Thanks,
>>
>> Lan
>>
>>
>>
>>
>>
>> On Thu, Mar 16, 2023 at 2:15 PM Regina Obe <lr at pcorp.us> wrote:
>>
>> It depends on what platform you are on.
>>
>>
>>
>> Can you output the following from your postgis enabled databases:
>>
>>
>>
>> SELECT postgis_full_version(), version();
>>
>>
>>
>> And send us the output of what each says. That will give a clue of at
>> least what platform you are running.
>>
>>
>>
>> You’ll need to run this on each database you have with postgis installed
>> on, as it is possible to have 2 versions of postgis installed in separate
>> databases.
>>
>>
>>
>> As to whether you’d need to drop and recreate any materialized views, it
>> would depend on what functions they are using. If they are using
>> deprecated or removed functions, then eventually you will need to drop and
>> recreate, but you could do that at a later time. The PostGIS 3+ upgrade
>> will notify you of those issues, but generally can just rename the
>> functions in use, so you can drop and recreate at a more convenient time.
>>
>>
>>
>> For materialized views that take a long time to build, I generally build
>> them under a new name, and do a swap after the new one has been built.
>> That would reduce the downtime from hours to 1-2 minutes.
>>
>>
>>
>> Thanks,
>>
>> Regina
>>
>>
>>
>> *From:* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] *On
>> Behalf Of *pham lan
>> *Sent:* Wednesday, March 15, 2023 5:00 PM
>> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
>> *Subject:* [postgis-users] Help to upgrade postgresql10 with postgis 2.5
>>
>>
>>
>> Hello,
>>
>>
>>
>> I have very less experience with postgres and postgis. However i receive
>> a task to upgrade an old database which has postgresql10 with postgis 2.5.
>> The database has some extensions: postgis, postgis_sfcgal,
>> postgis_topology, raster and have a lot of materialized views which depends
>> on functions on postgis and sfcgal libraries. Could someone please instruct
>> me to upgrade my postgres DB to a newer postgres and postgis version
>> without having to drop all those materialized views? Idealy to postgis 3.x?
>>
>>
>>
>> Thanks in advance.
>>
>>
>>
>> Best regards
>>
>> Lan Pham
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230318/ad979a61/attachment.htm>
More information about the postgis-users
mailing list