Обсуждение: Re: pg_upgrade from Postgresql-12 to Postgresql-13 fails with "Creating dump of database schemas postgres *failure*"

Поиск
Список
Период
Сортировка
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@gmail.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.janes@gmail.com> wrote:


On Fri, Feb 18, 2022 at 8:35 AM Yoong S. Chow <chowy1026@gmail.com> wrote:

From that last log file we have:

  1. 2022-02-16 19:04:35 +08 [303982]: [4957-1] session=620cda42.4a36e,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
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.saffiotti@gmail.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@gmail.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.janes@gmail.com> wrote:


On Fri, Feb 18, 2022 at 8:35 AM Yoong S. Chow <chowy1026@gmail.com> wrote:

From that last log file we have:

  1. 2022-02-16 19:04:35 +08 [303982]: [4957-1] session=620cda42.4a36e,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
Вложения
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@gmail.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.saffiotti@gmail.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@gmail.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.janes@gmail.com> wrote:


On Fri, Feb 18, 2022 at 8:35 AM Yoong S. Chow <chowy1026@gmail.com> wrote:

From that last log file we have:

  1. 2022-02-16 19:04:35 +08 [303982]: [4957-1] session=620cda42.4a36e,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
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.saffiotti@gmail.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@gmail.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.saffiotti@gmail.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@gmail.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.janes@gmail.com> wrote:


On Fri, Feb 18, 2022 at 8:35 AM Yoong S. Chow <chowy1026@gmail.com> wrote:

From that last log file we have:

  1. 2022-02-16 19:04:35 +08 [303982]: [4957-1] session=620cda42.4a36e,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