Re: pg_upgrade from Postgresql-12 to Postgresql-13 fails with "Creating dump of database schemas postgres *failure*"

Hi Liam,

You mentioned one of the issues I have been facing with PostGIS.

PostGIS used to be working fine on a version I vaguely recalled as 3.1.1.
It was until I accidentally did a `*SELECT* PostGIS_Extensions_Upgrade();`
that the nightmare began.

Currently the `*SELECT* *PostGIS_Full_Version*();` returns POSTGIS="3.2.1
5fae8e5" [EXTENSION] PGSQL="120" GEOS="3.10.1-CAPI-1.16.0" PROJ="6.3.1"
GDAL="GDAL 3.0.4, released 2020/01/28 GDAL_DATA not found" LIBXML="2.9.10"
LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" RASTER. Note
the "GDAL 3.0.4, released 2020/01/28 GDAL_DATA not found".

Locally on Ubuntu 20.04, the installed libraries are actually newer
versions. They are somehow not detected by PostGIS within Postgresql:
- `sudo apt-cache policy gdal-bin`: 3.4.0+dfsg-1~focal0 500
- `sudo apt-cache policy proj-bin`: 8.2.0-1~focal0 500
- `sudo apt-cache policy geos-bin`: 3.10.1-1~focal0 500

There are many variations of libxml, libjson, libprotobuf that I wouldn't
really verify.

I have tried reinstalling, downgrading then upgrading, but it has little
effect on the result of which version is actually being linked/used on
Postgresql/PostGIS. Sadly I couldn't find the used-be-working PostGIS
3.1.1 version. I posted a question on the PostGIS user group and have yet
to receive any response. :(

At this point, I am pretty much lost as to what I could do with this.

Best,
Yoong Shin

On Sun, Feb 20, 2022 at 10:33 PM liam saffioti <liam(dot)saffiotti(at)gmail(dot)com>
wrote:

> Thank you for the information.
>
> Also, I am wondering about your libraries related to the PostGIS /
> postgis_raster extension. So, what are the versions of linked GDAL,
> PROJ, SFCGAL, SPATIALITE libraries? If you updated the PostGIS extension a
> while ago, old versions of the libraries may be left. Especially, if there
> is an incompatible version of the proj, it could be the cause. Please, be
> sure all libraries linked to the posgis_raster and PostGIS extensions are
> up to date.
>
> I suppose that the problem always occurred on the second database. For
> instance, if you run the pg_dump command for the kinta database first and
> then for the lugano database, the problem will occur for the lugana
> database, the second database. However, the kinta database will be
> completed successfully. So, something is happening on the system /* I think
> because of the bugs of the extensions /* when you run the pg_dump command
> for a database that has the PostGIS or postgis_raster extension.
>
> Best,
> Liam
>
> Yoong S. Chow <chowy1026(at)gmail(dot)com>, 20 Şub 2022 Paz, 15:13 tarihinde
> şunu yazdı:
>
>> Hi Liam,
>>
>> Here are the answers:
>> - on my Postgresql-12, the default version of pgaudit is 1.4.2 but the
>> installed version is 1.4.1. Is this causing a problem?
>> - for postgis: both default and installed versions are 3.2.1; so are
>> postgis_raster.
>> - The postgis and postgis_raster extensions are installed in all other
>> user databases except for postgres, postgis_raster and template1
>> - TIL about postgresql.auto.conf. Didn't know this file existed.
>> Attached.
>>
>> Thanks a lot for your help.
>>
>> Best,
>> Yoong Shin
>>
>> On Sun, Feb 20, 2022 at 7:52 PM liam saffioti <liam(dot)saffiotti(at)gmail(dot)com>
>> wrote:
>>
>>> Hi Chow,
>>>
>>> Can you answer the below questions, please?
>>>
>>> - What is the pgaudit extension version?
>>> - What is the PostGIS and postgis_raster extensions version?
>>> - In which databases are PostGIS or postgis_raster extensions installed?
>>> - Can you add postgresql.auto.conf file?
>>>
>>> I suppose that the problem is caused by the extensions bugs.
>>>
>>> Best,
>>> Liam
>>>
>>> Yoong S. Chow <chowy1026(at)gmail(dot)com>, 19 Şub 2022 Cmt, 19:02 tarihinde
>>> şunu yazdı:
>>>
>>>> Hi Jeff,
>>>>
>>>> Could you please clarify what core files are? I am truly a newbie to
>>>> postgresql.
>>>>
>>>> The pg_dump on both `postgres` and `postgis_raster` databases ran
>>>> without error. Attached is the posgtresql log.
>>>>
>>>> Thank you,
>>>> Chow
>>>>
>>>>
>>>> On Sat, Feb 19, 2022 at 12:07 AM Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>>
>>>>>
>>>>> On Fri, Feb 18, 2022 at 8:35 AM Yoong S. Chow <chowy1026(at)gmail(dot)com>
>>>>> wrote:
>>>>>
>>>>>>
>>>>>> Here are the logs: pg_upgrade_server.log
>>>>>> <https://pastebin.com/ePDpmheE>, pg_upgrade_dump_13427.log
>>>>>> <https://pastebin.com/YTsU4hYh> and
>>>>>> tail_postgresql-12-2022-02-16_190344.log
>>>>>> <https://pastebin.com/KP8ePVct>.
>>>>>>
>>>>>
>>>>> From that last log file we have:
>>>>>
>>>>>
>>>>> 1. 2022-02-16 19:04:35 +08 [303982]: [4957-1] session=620cda42.4
>>>>> a36e,user=postgres,db=postgis_raster,app=pg_dump,client=[local] |
>>>>> LOG: disconnection: session time: 0:00:01.101 user=postgres
>>>>> database=postgis_raster host=[local]
>>>>> 2. free(): invalid pointer
>>>>>
>>>>>
>>>>> and then a little later:
>>>>>
>>>>>
>>>>> 1. 2022-02-16 19:04:35 +08 [303791]: [7-1] session=620cda0f.4a2af,
>>>>> user=,db=,app=,client= | LOG: server process (PID 303982) was
>>>>> terminated by signal 6: Aborted
>>>>>
>>>>>
>>>>> Do you get any core files for 303982? Do you have core dumps
>>>>> enabled? What do you get if you run a manual pg_dump -s on db
>>>>> postgis_raster?
>>>>>
>>>>> Cheers,
>>>>>
>>>>> Jeff
>>>>>
>>>>>>