Postgresql13-devel fails to install on centos 7

[Hemil Ruparel]

I am trying to install postgres13-devel on a centos 7 server. But I am
getting this error message:
Error: Package: postgresql13-devel-13.1-1PGDG.rhel7.x86_64 (pgdg13)
   Requires: llvm5.0-devel >= 5.0

After a bit of googling, and following these instructions

:

$ sudo yum install centos-release-scl
$ sudo yum install llvm-toolset-7
$ scl enable llvm-toolset-7 bash

But I am still getting the same error message.

Re: create type with %type or %rowtype

[Post Gresql]

On 2020-11-18 04:37, David G. Johnston wrote:

(resending to include the list)

On Tue, Nov 17, 2020 at 3:12 PM Post Gresql > wrote:


create type my_type as my_table%rowtype;


This would be redundant with existing behavior - all tables have a 
corresponding type already


create type my_type as my_table.my_column%type;


What does the indirection get us?

Correct? It seems to be a feature for plpgsql programing only, right?


Correct


But wouldn't that be a good thing to be able to do?


You are the one proposing it - why would it be a good thing to do?


My idea, that I did not explain properly, sorry for that, is that when I 
write plpgsql functions I sometime need to have a certain column type as 
return value, or even a complete table row as return type.


Then it would be great if I could just refer to the column or row type 
when delcaring the return type.


It would also be handy if I could reference types when declaring other 
types,


for example

create type my_type (a int, b my_table.my_column%type);


The real reason: you will be sure you are using the same type 
everywhere.  And it is easier to change type later on, then only one 
column has to be changed, not many and in a lot of different places.


I hope that explains my idea.



David J.


On Tue, Nov 17, 2020 at 3:12 PM Post Gresql > wrote:


Hello.

It seems that I can not create a type with

create type my_type as my_table%rowtype;

or

create type my_type as my_table.my_column%type;

Correct? It seems to be a feature for plpgsql programing only, right?

But wouldn't that be a good thing to be able to do? Or would it cause
too many problems?


Best regards

Re: pg_upgrade from 12 to 13 failes with plpython2

[Marcin Giedz]

but my question still remains the same - what causes pg_upgrade failure - are 
functions the reason? what I did was to delete these 2 rows from pg_pltemplate 
as I thought this may help: 

postgres=# delete from pg_pltemplate where tmplname = 'plpython2u'; 
DELETE 1 
postgres=# delete from pg_pltemplate where tmplname = 'plpythonu'; 
DELETE 1 


but pg_upgrade still complains about plpython2: 

cat loadable_libraries.txt 
could not load library "$libdir/plpython2": ERROR: could not access file 
"$libdir/plpython2": No such file or directory 
In database: alaxx 
In database: template1 



marcin@carbon2:~$ psql -U pgsql -h 192.168.89.64 alaxx 
psql (12.4 (Ubuntu 12.4-1)) 
Type "help" for help. 

argosrm=# select * from pg_pltemplate ; 
tmplname | tmpltrusted | tmpldbacreate | tmplhandler | tmplinline | 
tmplvalidator | tmpllibrary | tmplacl 
+-+---++--+-+---+-
 
plpgsql | t | t | plpgsql_call_handler | plpgsql_inline_handler | 
plpgsql_validator | $libdir/plpgsql | 
pltcl | t | t | pltcl_call_handler | | | $libdir/pltcl | 
pltclu | f | f | pltclu_call_handler | | | $libdir/pltcl | 
plperl | t | t | plperl_call_handler | plperl_inline_handler | plperl_validator 
| $libdir/plperl | 
plperlu | f | f | plperlu_call_handler | plperlu_inline_handler | 
plperlu_validator | $libdir/plperl | 
plpython3u | f | f | plpython3_call_handler | plpython3_inline_handler | 
plpython3_validator | $libdir/plpython3 | 
(6 rows) 

argosrm=# \q 
marcin@carbon2:~$ psql -U pgsql -h 192.168.89.64 template1 
psql (12.4 (Ubuntu 12.4-1)) 
Type "help" for help. 

template1=# select * from pg_pltemplate ; 
tmplname | tmpltrusted | tmpldbacreate | tmplhandler | tmplinline | 
tmplvalidator | tmpllibrary | tmplacl 
+-+---++--+-+---+-
 
plpgsql | t | t | plpgsql_call_handler | plpgsql_inline_handler | 
plpgsql_validator | $libdir/plpgsql | 
pltcl | t | t | pltcl_call_handler | | | $libdir/pltcl | 
pltclu | f | f | pltclu_call_handler | | | $libdir/pltcl | 
plperl | t | t | plperl_call_handler | plperl_inline_handler | plperl_validator 
| $libdir/plperl | 
plperlu | f | f | plperlu_call_handler | plperlu_inline_handler | 
plperlu_validator | $libdir/plperl | 
plpython3u | f | f | plpython3_call_handler | plpython3_inline_handler | 
plpython3_validator | $libdir/plpython3 | 
(6 rows) 


what can I do next ? 


Thx 
Marcin 


Od: "Adrian Klaver"  
Do: "Devrim Gündüz" , "Tom Lane"  
DW: "Marcin Giedz" , "pgsql-general" 
 
Wysłane: wtorek, 17 listopad 2020 23:30:44 
Temat: Re: pg_upgrade from 12 to 13 failes with plpython2 

On 11/17/20 2:17 PM, Devrim Gündüz wrote: 
> 
> Hi, 
> 
> On Tue, 2020-11-17 at 16:23 -0500, Tom Lane wrote: 
>> You're confusing what the source code can do (which is what the 
>> manual documents) versus what individual packagers choose to support. 
>> The packagers frequently don't have a lot of choice in the matter; 
>> once their platform drops python2, they can't support plpython2. 
> 

First let me say: 

1) I don't use Python 2 anymore. 

2) I have converted my plpython(2)u functions to plpythonu3u 

3) I don't use RH family distros. 

> Well, CentOS 8 "supports" PY2, however given that Christoph also 
> dropped PY2 support in Debian packages as of PostgreSQL 12 [1], and 
> given that *Python 2 is unsupported* anyway, I just wanted to drop 
> support at this point. 

Read that sentence again and see the inherent disconnect between the 
beginning and end. 

As a packager you are in charge of how the packaging is done. Still 
announcing a change that effectively nullifies the documentation would 
to me be something that should be announced somewhere else than a list 
that I'm guessing 99% of the users don't read. 

> 
> FWIW, older PostgreSQL major versions still have PY 2 support. This is 
> for PostgreSQL 13 only. 

> 
> [1] : 
> https://www.postgresql.org/message-id/20200522080114.GA449430%40msg.df7cb.de 
> 
> Regards, 
> 


-- 
Adrian Klaver 
adrian.kla...@aklaver.com 

-- 
Pozdrawiam 
Marcin Giedz 
Wiceprezes Zarządu 

ARISE Sp. z o.o. 
mob. +48 606 673 977 
mail: marcin.gi...@arise.pl 

Al. Solidarności 117 
00-140 Warszawa 
tel. +48 (22) 440 56 20 
fax +48 (22) 440 56 22 
http://www.arise.pl 

Grupa ARISE: ARISE Sp. z o.o. (podmiot dominujący), ARISE Services S.A., Al. 
Solidarności 117, 00-140 Warszawa, Sąd Rejonowy dla m. st. Warszawy w Warszawie 
XII Wydział Gospodarczy Krajowego Rejestru Sądowego, KRS 316860, REGON 
141595449, NIP 5272590610, kapitał zakładowy wpłacony 250,000.00 zł. 

received immediate shutdown request caused cluster failover

[Yi Sun]

Hi all

There are 3 nodes in our prd db in patroni cluster, vm01 is leader, vm02
and vm03 are standby, vm01 received immediate shutdown request  caused
failover to vm02, after that vm03 received fast shutdown request

As vm03 not in cluster so have to reinit vm03

What's the possible root caused vm01 received immediate shutdown request
please?

and What's the possible root caused vm03 received fast shutdown request

Below are detail info, please check, if need any other info, I will
provide, thanks

postgresql version: 11.4
OS: centos 7
HA: patroni upgraded from 1.6.3 to 2.0.1 these days

--vm01 pg log

[2020-11-16 12:03:56.592 UTC] p=31485:3@ c=
pgwatch2@127.0.0.1/eu4_baas_bckp_epmgr:pgwatch2 LOG:  disconnection:
session time: 0:00:00.037 user=pgwatch2 database=eu4_baas_bckp_epmgr
host=127.0.0.1 port=36916
[2020-11-16 12:03:57.005 UTC] p=32103:14@ c=@/: LOG:  received immediate
shutdown request
[2020-11-16 12:03:57.017 UTC] p=31189:3@314/0 c=
atp_si_user@10.253.85.203/eu4_baas_software_inventory:[unknown] WARNING:
 terminating connection because of crash of another server process
[2020-11-16 12:03:57.017 UTC] p=31189:4@314/0 c=
atp_si_user@10.253.85.203/eu4_baas_software_inventory:[unknown] 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.
[2020-11-16 12:03:57.017 UTC] p=31189:5@314/0 c=
atp_si_user@10.253.85.203/eu4_baas_software_inventory:[unknown] HINT:  In a
moment you should be able to reconnect to the database and repeat your
command.
[2020-11-16 12:03:57.017 UTC] p=31183:3@313/0 c=
atp_pm@10.253.86.62/eu4_baas_atp_pm:[unknown] WARNING:  terminating
connection because of crash of another server process
[2020-11-16 12:03:57.017 UTC] p=31183:4@313/0 c=
atp_pm@10.253.86.62/eu4_baas_atp_pm:[unknown] 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.
[2020-11-16 12:03:57.017 UTC] p=31183:5@313/0 c=
atp_pm@10.253.86.62/eu4_baas_atp_pm:[unknown] HINT:  In a moment you should
be able to reconnect to the database and repeat your command.
[2020-11-16 12:03:57.017 UTC] p=31182:3@310/281059 c=
bckp_epmgr@10.253.85.202/eu4_baas_bckp_epmgr:[unknown] WARNING:
 terminating connection because of crash of another server process
[2020-11-16 12:03:57.017 UTC] p=31182:4@310/281059 c=
bckp_epmgr@10.253.85.202/eu4_baas_bckp_epmgr:[unknown] 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.
[2020-11-16 12:03:57.017 UTC] p=31182:5@310/281059 c=
bckp_epmgr@10.253.85.202/eu4_baas_bckp_epmgr:[unknown] HINT:  In a moment
you should be able to reconnect to the database and repeat your command.

--vm01 patroni log
Nov 16 12:03:56 eu4-baas-patroni-cluster-vm01 patroni[32085]: {"time":
"2020-11-16 12:03:56,922", "name": "patroni.dcs.consul", "process": 32085,
"thread": 140350381303616, "level": "WARNING", "message": "Could not
register service: unknown role type promoted"}
Nov 16 12:03:56 eu4-baas-patroni-cluster-vm01 patroni[32085]: {"time":
"2020-11-16 12:03:56,923", "name": "patroni.ha", "process": 32085,
"thread": 140350381303616, "level": "INFO", "message": "Lock owner:
eu4-baas-patroni-cluster-vm02; I am eu4-baas-patroni-cluster-vm01"}
Nov 16 12:03:56 eu4-baas-patroni-cluster-vm01 patroni[32085]: {"time":
"2020-11-16 12:03:56,923", "name": "patroni.ha", "process": 32085,
"thread": 140350381303616, "level": "INFO", "message": "does not have lock"}
Nov 16 12:03:59 eu4-baas-patroni-cluster-vm01 patroni[32085]: {"time":
"2020-11-16 12:03:59,158", "name": "patroni.dcs.consul", "process": 32085,
"thread": 140350381303616, "level": "INFO", "message": "Deregister service
pgcluster11/eu4-baas-patroni-cluster-vm01"}
Nov 16 12:03:59 eu4-baas-patroni-cluster-vm01 patroni[32085]: {"time":
"2020-11-16 12:03:59,180", "name": "patroni", "process": 32085, "thread":
140350381303616, "level": "INFO", "message": "demoting self because i do
not have the lock and i was a leader"}
Nov 16 12:03:59 eu4-baas-patroni-cluster-vm01 patroni[32085]: {"time":
"2020-11-16 12:03:59,181", "name": "patroni", "process": 32085, "thread":
140350381303616, "level": "WARNING", "message": "Loop time exceeded,
rescheduling immediately."}
Nov 16 12:03:59 eu4-baas-patroni-cluster-vm01 patroni[32085]: {"time":
"2020-11-16 12:03:59,184", "name": "patroni.ha", "process": 32085,
"thread": 140350381303616, "level": "INFO", "message": "Lock owner:
eu4-baas-patroni-cluster-vm02; I am eu4-baas-patroni-cluster-vm01"}
Nov 16 12:03:59 eu4-baas-patroni-cluster-vm01 patroni[32085]: {"time":
"2020-11-16 12:03:59,185", "name": "patroni", "process": 32085, "thread":
140350381303616, "level": "INFO", "message": "starting after demotion in
progress"}
Nov 16 12:03:59 

Re: create type with %type or %rowtype

[David G. Johnston]

(resending to include the list)

On Tue, Nov 17, 2020 at 3:12 PM Post Gresql  wrote:

> create type my_type as my_table%rowtype;
>

This would be redundant with existing behavior - all tables have a
corresponding type already

create type my_type as my_table.my_column%type;
>

What does the indirection get us?


> Correct? It seems to be a feature for plpgsql programing only, right?
>

Correct

>
> But wouldn't that be a good thing to be able to do?


You are the one proposing it - why would it be a good thing to do?

Or would it cause
> too many problems?
>

If it doesn't have value it wouldn't matter whether it would be problematic.

David J.


On Tue, Nov 17, 2020 at 3:12 PM Post Gresql  wrote:

> Hello.
>
> It seems that I can not create a type with
>
> create type my_type as my_table%rowtype;
>
> or
>
> create type my_type as my_table.my_column%type;
>
> Correct? It seems to be a feature for plpgsql programing only, right?
>
> But wouldn't that be a good thing to be able to do? Or would it cause
> too many problems?
>
>
> Best regards
>
>
>
>
>

Re: PK issue: serial sequence needs updating [RESOLVED]

[Rich Shepard]

On Tue, 17 Nov 2020, Adrian Klaver wrote:


https://www.postgresql.org/docs/12/sql-altersequence.html
ALTER SEQUENCE people_person_nbr_seq RESTART 485;


Thanks, Adrian. I missed that page.

Regards,

Rich

Re: PK issue: serial sequence needs updating

[Adrian Klaver]

On 11/17/20 3:46 PM, Rich Shepard wrote:

Running postgresql-12.2-x86_64-1_SBo on Slackware-14.2/x86_64.

My restructed business database has populated tables, but the primary keys
are not aware of the current maximum number since the table rows were added
external to postgres and read in using psql.

For example, I'm trying to add a new person to the people table which
currently has 484 rows. The insert into statement provides all columns but
the first, the PK (person_nbr) which is an int with an associated
people_person_nbr_seq. Passing the insert statement to psql is rejected
because there's already a person_nbr=1.

How do I inform the sequence that nextval should be 485? Or, do I drop that
sequence and restore it so it reads all existing table rows and their PKs?

If I didn't clearly explain myself, ask and I'll try again. :-)


https://www.postgresql.org/docs/12/sql-altersequence.html


ALTER SEQUENCE people_person_nbr_seq RESTART 485;



TIA,

Rich







--
Adrian Klaver
adrian.kla...@aklaver.com

PK issue: serial sequence needs updating

[Rich Shepard]

Running postgresql-12.2-x86_64-1_SBo on Slackware-14.2/x86_64.

My restructed business database has populated tables, but the primary keys
are not aware of the current maximum number since the table rows were added
external to postgres and read in using psql.

For example, I'm trying to add a new person to the people table which
currently has 484 rows. The insert into statement provides all columns but
the first, the PK (person_nbr) which is an int with an associated
people_person_nbr_seq. Passing the insert statement to psql is rejected
because there's already a person_nbr=1.

How do I inform the sequence that nextval should be 485? Or, do I drop that
sequence and restore it so it reads all existing table rows and their PKs?

If I didn't clearly explain myself, ask and I'll try again. :-)

TIA,

Rich

Re: create type with %type or %rowtype

[Adrian Klaver]

On 11/17/20 2:12 PM, Post Gresql wrote:

Hello.

It seems that I can not create a type with

create type my_type as my_table%rowtype;


To me that is redundant as a table has a composite type already.



or

create type my_type as my_table.my_column%type;


In plpgsql %type is effectively a placeholder, not sure how well that 
would work in a created type.




Correct? It seems to be a feature for plpgsql programing only, right?

But wouldn't that be a good thing to be able to do? Or would it cause 
too many problems?



Best regards







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pg_upgrade from 12 to 13 failes with plpython2

[Adrian Klaver]

On 11/17/20 2:17 PM, Devrim Gündüz wrote:


Hi,

On Tue, 2020-11-17 at 16:23 -0500, Tom Lane wrote:

You're confusing what the source code can do (which is what the
manual documents) versus what individual packagers choose to support.
The packagers frequently don't have a lot of choice in the matter;
once their platform drops python2, they can't support plpython2.




First let me say:

1) I don't use Python 2 anymore.

2) I have converted my plpython(2)u functions to plpythonu3u

3) I don't use RH family distros.


Well, CentOS 8 "supports" PY2, however given that Christoph also
dropped PY2 support in Debian packages as of PostgreSQL 12 [1], and
given that *Python 2 is unsupported* anyway, I just wanted to drop
support at this point.


Read that sentence again and see the inherent disconnect between the 
beginning and end.


As a packager you are in charge of how the packaging is done. Still 
announcing a change that effectively nullifies the documentation would 
to me be something that should be announced somewhere else than a list 
that I'm guessing 99% of the users don't read.




FWIW, older PostgreSQL major versions still have PY 2 support. This is
for PostgreSQL 13 only.




[1] : 
https://www.postgresql.org/message-id/20200522080114.GA449430%40msg.df7cb.de

Regards,




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pg_upgrade from 12 to 13 failes with plpython2

[Devrim Gündüz]

Hi,

On Tue, 2020-11-17 at 16:23 -0500, Tom Lane wrote:
> You're confusing what the source code can do (which is what the
> manual documents) versus what individual packagers choose to support.
> The packagers frequently don't have a lot of choice in the matter;
> once their platform drops python2, they can't support plpython2.

Well, CentOS 8 "supports" PY2, however given that Christoph also
dropped PY2 support in Debian packages as of PostgreSQL 12 [1], and
given that *Python 2 is unsupported* anyway, I just wanted to drop
support at this point.

FWIW, older PostgreSQL major versions still have PY 2 support. This is
for PostgreSQL 13 only.

[1] : 
https://www.postgresql.org/message-id/20200522080114.GA449430%40msg.df7cb.de

Regards,
-- 
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

create type with %type or %rowtype

[Post Gresql]

Hello.

It seems that I can not create a type with

create type my_type as my_table%rowtype;

or

create type my_type as my_table.my_column%type;

Correct? It seems to be a feature for plpgsql programing only, right?

But wouldn't that be a good thing to be able to do? Or would it cause 
too many problems?



Best regards

Re: autovacuum recommendations for Large tables

[Olivier Gautherot]

Hi Rob,

On Tue, Nov 17, 2020 at 10:24 PM Rob Sargent  wrote:

> If it helps, I put together a few thoughts and own experience on a blog:
> https://sites.google.com/gautherot.net/postgresql/vacuum
>
> Hope you find it useful.
> --
>
> That URL does not work for me (not even
> https://sites.google.com/gautherot.net)
>
>
Thanks  for the heads up - it looks like Google won't publish the site
outside of my domain.

I moved it to the old sites:
https://sites.google.com/a/gautherot.net/postgresql-tips/vacuum

Let's hope it works this time.

Cheers
Olivier


Libre
de virus. www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: pg_upgrade from 12 to 13 failes with plpython2

[Adrian Klaver]

On 11/17/20 1:23 PM, Tom Lane wrote:

Adrian Klaver  writes:

It would be nice to mention this on --announce and here as this still
exists:
https://www.postgresql.org/docs/13/plpython-python23.html


You're confusing what the source code can do (which is what the
manual documents) versus what individual packagers choose to support.
The packagers frequently don't have a lot of choice in the matter;
once their platform drops python2, they can't support plpython2.


But CentOS/RH have not dropped Python 2, they have just made Python 3 
the default:


https://www.liquidweb.com/kb/how-to-install-python-on-centos-8/



regards, tom lane




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: autovacuum recommendations for Large tables

[Rob Sargent]

> 
> If it helps, I put together a few thoughts and own experience on a blog:
> https://sites.google.com/gautherot.net/postgresql/vacuum 
>  
> 
> Hope you find it useful.
> --

That URL does not work for me (not even https://sites.google.com/gautherot.net 
)

Re: pg_upgrade from 12 to 13 failes with plpython2

[Tom Lane]

Adrian Klaver  writes:
> It would be nice to mention this on --announce and here as this still 
> exists:
> https://www.postgresql.org/docs/13/plpython-python23.html

You're confusing what the source code can do (which is what the
manual documents) versus what individual packagers choose to support.
The packagers frequently don't have a lot of choice in the matter;
once their platform drops python2, they can't support plpython2.

regards, tom lane

Re: pg_upgrade from 12 to 13 failes with plpython2

[Devrim Gündüz]

Hi,

On Tue, 2020-11-17 at 13:18 -0800, Adrian Klaver wrote:
> > https://www.postgresql.org/message-id/333f3aa334ba93019c75fffaec373f2bf4275d28.camel%40gunduz.org
> 
> So to be clear what was dropped was plpythonu, which means
> plpython2u. plpython3u still exists, correct?

Right.

Regards,
-- 
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: pg_upgrade from 12 to 13 failes with plpython2

[Adrian Klaver]

On 11/17/20 12:49 PM, Devrim Gündüz wrote:


Hi,

On Tue, 2020-11-17 at 12:18 -0800, Adrian Klaver wrote:

This was announced where and when?


https://www.postgresql.org/message-id/333f3aa334ba93019c75fffaec373f2bf4275d28.camel%40gunduz.org


So to be clear what was dropped was plpythonu, which means plpython2u. 
plpython3u still exists, correct?


It would be nice to mention this on --announce and here as this still 
exists:


https://www.postgresql.org/docs/13/plpython-python23.html

"

Existing users and users who are currently not interested in Python 3 
use the language name plpythonu and don't have to change anything for 
the foreseeable future. It is recommended to gradually “future-proof” 
the code via migration to Python 2.6/2.7 to simplify the eventual 
migration to Python 3.


In practice, many PL/Python functions will migrate to Python 3 with few 
or no changes.

"



Regards,




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: autovacuum recommendations for Large tables

[Olivier Gautherot]

Hi Atul,

On Tue, Nov 17, 2020 at 12:05 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Nov 16, 2020 at 3:57 PM Atul Kumar  wrote:
>
>> I only have this one big table in the database of size 3113 GB with rows
>> 7661353111.
>>
>> Right Now the autovacuum setting for that table is set to
>>
>> {autovacuum_enabled=true,autovacuum_vacuum_scale_factor=0.2,autovacuum_analyze_scale_factor=0.2}
>>
>>>

> auto-vacuum doesn't care directly about absolute size, it cares about
> change (relative to absolute size in many cases, hence the scale factors).
>
> David J.
>

David is correct.

If it helps, I put together a few thoughts and own experience on a blog:
https://sites.google.com/gautherot.net/postgresql/vacuum

Hope you find it useful.
--
Olivier Gautherot



Libre
de virus. www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: pg_upgrade from 12 to 13 failes with plpython2

[Devrim Gündüz]

Hi,

On Tue, 2020-11-17 at 12:18 -0800, Adrian Klaver wrote:
> This was announced where and when?

https://www.postgresql.org/message-id/333f3aa334ba93019c75fffaec373f2bf4275d28.camel%40gunduz.org

Regards,
-- 
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: pg_upgrade from 12 to 13 failes with plpython2

[Adrian Klaver]

On 11/17/20 12:06 PM, Devrim Gündüz wrote:


Hi,

On Tue, 2020-11-17 at 21:00 +0100, Marcin Giedz wrote:

Hi all, trying to performe upgrade from 12 to 13 installed from
Centos8 repo gives such error:

cat loadable_libraries.txt
could not load library "$libdir/plpython2": ERROR: could not access
file "$libdir/plpython2": No such file or directory


I dropped PL/Python support along with PostgreSQL 13 RPMs.


This was announced where and when?



Regards,




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pg_upgrade from 12 to 13 failes with plpython2

[Tom Lane]

Devrim =?ISO-8859-1?Q?G=FCnd=FCz?=  writes:
> On Tue, 2020-11-17 at 21:00 +0100, Marcin Giedz wrote:
>> Hi all, trying to performe upgrade from 12 to 13 installed from
>> Centos8 repo gives such error: 
>> 
>> cat loadable_libraries.txt 
>> could not load library "$libdir/plpython2": ERROR: could not access
>> file "$libdir/plpython2": No such file or directory 

> I dropped PL/Python support along with PostgreSQL 13 RPMs.

Yeah, I tried to stir up some interest in having a cleaner python 2
to python 3 migration path, but there wasn't any.  Best bet probably
is to convert your plpython[2] functions to plpython3 before you
upgrade.

regards, tom lane

Re: pg_upgrade from 12 to 13 failes with plpython2

[Devrim Gündüz]

Hi,

On Tue, 2020-11-17 at 21:00 +0100, Marcin Giedz wrote:
> Hi all, trying to performe upgrade from 12 to 13 installed from
> Centos8 repo gives such error: 
> 
> cat loadable_libraries.txt 
> could not load library "$libdir/plpython2": ERROR: could not access
> file "$libdir/plpython2": No such file or directory 

I dropped PL/Python support along with PostgreSQL 13 RPMs.

Regards,
-- 
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

pg_upgrade from 12 to 13 failes with plpython2

[Marcin Giedz]

Hi all, trying to performe upgrade from 12 to 13 installed from Centos8 repo 
gives such error: 

cat loadable_libraries.txt 
could not load library "$libdir/plpython2": ERROR: could not access file 
"$libdir/plpython2": No such file or directory 

digging around: 
1. 
drop extension plpythonu; 
ERROR: extension "plpythonu" does not exist 

2. 
postgres=# select * from pg_pltemplate ; 
tmplname | tmpltrusted | tmpldbacreate | tmplhandler | tmplinline | 
tmplvalidator | tmpllibrary | tmplacl 
+-+---++--+-+---+-
 
plpgsql | t | t | plpgsql_call_handler | plpgsql_inline_handler | 
plpgsql_validator | $libdir/plpgsql | 
pltcl | t | t | pltcl_call_handler | | | $libdir/pltcl | 
pltclu | f | f | pltclu_call_handler | | | $libdir/pltcl | 
plperl | t | t | plperl_call_handler | plperl_inline_handler | plperl_validator 
| $libdir/plperl | 
plperlu | f | f | plperlu_call_handler | plperlu_inline_handler | 
plperlu_validator | $libdir/plperl | 
plpythonu | f | f | plpython_call_handler | plpython_inline_handler | 
plpython_validator | $libdir/plpython2 | 
plpython2u | f | f | plpython2_call_handler | plpython2_inline_handler | 
plpython2_validator | $libdir/plpython2 | 
plpython3u | f | f | plpython3_call_handler | plpython3_inline_handler | 
plpython3_validator | $libdir/plpython3 | 
(8 rows) 

3. 
postgres=# select * from pg_language ; 
oid | lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | 
lanvalidator | lanacl 
---+--+--+-+--+---+---+--+
 
12 | internal | 10 | f | f | 0 | 0 | 2246 | 
13 | c | 10 | f | f | 0 | 0 | 2247 | 
14 | sql | 10 | f | t | 0 | 0 | 2248 | 
14177 | plpgsql | 10 | t | t | 14174 | 14175 | 14176 | 
(4 rows) 


4. 
postgres=# select DISTINCT l.lanname as function_language 
postgres-# from pg_proc p 
postgres-# left join pg_language l on p.prolang = l.oid; 
function_language 
--- 
c 
sql 
internal 
(3 rows) 

5. 

postgres=# SELECT oid::regprocedure 
postgres-# FROM pg_catalog.pg_proc 
postgres-# WHERE probin = '$libdir/plpython2'; 
oid 
- 
(0 rows) 



Question: where is plpython2 comming from during pg_upgrade? Is this pg_upgrade 
bug or something else? 


Thx 
Marcin 

Re: pg_upgrade of 11 -> 13: free(): invalid pointer

[Paul Ramsey]

> On Nov 17, 2020, at 11:44 AM, Bruce Momjian  wrote:
> 
> On Tue, Nov 17, 2020 at 11:59:10AM -0500, Jeremy Wilson wrote:
>> pg_restore: 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.
>> pg_restore: creating COMMENT "public.FUNCTION "st_isempty"("rast" 
>> "public"."raster")"
>> pg_restore: while PROCESSING TOC:
>> pg_restore: from TOC entry 5338; 0 0 COMMENT FUNCTION "st_isempty"("rast" 
>> "public"."raster") postgres
>> pg_restore: error: could not execute query: server closed the connection 
>> unexpectedly
>>This probably means the server terminated abnormally
>>before or while processing the request.
>> Command was: COMMENT ON FUNCTION "public"."st_isempty"("rast" 
>> "public"."raster") IS 'args: rast - Returns true if the raster is empty 
>> (width = 0 and height = 0). Otherwise, returns false.’;
> 
> My guess is that this is a crash in the PostGIS shared library.  I would
> ask the PostGIS team if they know of any crash cases, and if not, I
> think you need to do a pg_dump of the database and test-load it into a
> new database to see what query makes it fail, and then load debug
> symbols and do a backtrace of the stack at the point of the crash. 
> Yeah, not fun.

These kinds of problems have been almost always due to multiple versions of 
dependencies installed simultaneously. So packaging fun. You'll get some 
version of postgis compiled against one train of dependencies and another 
against another train, and for upgrade both trains will end up installed 
simultaneously, and things will break. 
P

> 
> -- 
>  Bruce Momjian  https://momjian.us
>  EnterpriseDB https://enterprisedb.com
> 
>  The usefulness of a cup is in its emptiness, Bruce Lee
> 
> 
> 

Re: pg_upgrade of 11 -> 13: free(): invalid pointer

[Bruce Momjian]

On Tue, Nov 17, 2020 at 02:44:47PM -0500, Bruce Momjian wrote:
> On Tue, Nov 17, 2020 at 11:59:10AM -0500, Jeremy Wilson wrote:
> > pg_restore: 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.
> > pg_restore: creating COMMENT "public.FUNCTION "st_isempty"("rast" 
> > "public"."raster")"
> > pg_restore: while PROCESSING TOC:
> > pg_restore: from TOC entry 5338; 0 0 COMMENT FUNCTION "st_isempty"("rast" 
> > "public"."raster") postgres
> > pg_restore: error: could not execute query: server closed the connection 
> > unexpectedly
> > This probably means the server terminated abnormally
> > before or while processing the request.
> > Command was: COMMENT ON FUNCTION "public"."st_isempty"("rast" 
> > "public"."raster") IS 'args: rast - Returns true if the raster is empty 
> > (width = 0 and height = 0). Otherwise, returns false.’;
> 
> My guess is that this is a crash in the PostGIS shared library.  I would
> ask the PostGIS team if they know of any crash cases, and if not, I
> think you need to do a pg_dump of the database and test-load it into a
> new database to see what query makes it fail, and then load debug
> symbols and do a backtrace of the stack at the point of the crash. 
> Yeah, not fun.

Actually pg_dump --schema-only is what you want to dump and load into a
separate databsae.   No need to dump the data.

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Re: pg_upgrade of 11 -> 13: free(): invalid pointer

[Bruce Momjian]

On Tue, Nov 17, 2020 at 11:59:10AM -0500, Jeremy Wilson wrote:
> pg_restore: 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.
> pg_restore: creating COMMENT "public.FUNCTION "st_isempty"("rast" 
> "public"."raster")"
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 5338; 0 0 COMMENT FUNCTION "st_isempty"("rast" 
> "public"."raster") postgres
> pg_restore: error: could not execute query: server closed the connection 
> unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> Command was: COMMENT ON FUNCTION "public"."st_isempty"("rast" 
> "public"."raster") IS 'args: rast - Returns true if the raster is empty 
> (width = 0 and height = 0). Otherwise, returns false.’;

My guess is that this is a crash in the PostGIS shared library.  I would
ask the PostGIS team if they know of any crash cases, and if not, I
think you need to do a pg_dump of the database and test-load it into a
new database to see what query makes it fail, and then load debug
symbols and do a backtrace of the stack at the point of the crash. 
Yeah, not fun.

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Re: pg_upgrade of 11 -> 13: free(): invalid pointer

[Jeremy Wilson]

> On Nov 17, 2020, at 12:18 PM, Adrian Klaver  wrote:
> 
> On 11/17/20 8:59 AM, Jeremy Wilson wrote:
> 
> Was this after a clean install of the corrected RPM’s?

Yes, this is a fresh install of CentOS 8 and installed using the updated repo 
and RPMs.

Re: pg_upgrade of 11 -> 13: free(): invalid pointer

[Adrian Klaver]

On 11/17/20 8:59 AM, Jeremy Wilson wrote:

I’m continuing my upgrade journey, this time from 11 to 13, and the process is 
dying in the copy phase, always on the same DB:

—
Performing Upgrade
--
Analyzing all rows in the new cluster   ok
Freezing all rows in the new clusterok
Deleting files from new pg_xact ok
Copying old pg_xact to new server   ok
Setting next transaction ID and epoch for new cluster   ok
Deleting files from new pg_multixact/offsetsok
Copying old pg_multixact/offsets to new server  ok
Deleting files from new pg_multixact/membersok
Copying old pg_multixact/members to new server  ok
Setting next multixact ID and offset for new clusterok
Resetting WAL archives  ok
Setting frozenxid and minmxid counters in new cluster   ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
   messages
*failure*

Consult the last few lines of "pg_upgrade_dump_16387.log" for
the probable cause of the failure.
Failure, exiting
—

The log contains (which is different each time):

—
pg_restore: 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.
pg_restore: creating COMMENT "public.FUNCTION "st_isempty"("rast" 
"public"."raster")"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 5338; 0 0 COMMENT FUNCTION "st_isempty"("rast" 
"public"."raster") postgres
pg_restore: error: could not execute query: server closed the connection 
unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.
Command was: COMMENT ON FUNCTION "public"."st_isempty"("rast" 
"public"."raster") IS 'args: rast - Returns true if the raster is empty (width = 0 and height = 0). 
Otherwise, returns false.’;
—

And the pgsql13 server log contains:

—
2020-11-17 11:51:40.953 EST [96545] LOG:  database system is ready to accept 
connections
free(): invalid pointer
2020-11-17 11:51:42.880 EST [96545] LOG:  server process (PID 96575) was 
terminated by signal 6: Aborted
2020-11-17 11:51:42.880 EST [96545] LOG:  terminating any other active server 
processes
2020-11-17 11:51:42.880 EST [96582] WARNING:  terminating connection because of 
crash of another server process
2020-11-17 11:51:42.880 EST [96582] 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.
2020-11-17 11:51:42.880 EST [96582] HINT:  In a moment you should be able to 
reconnect to the database and repeat your command.
2020-11-17 11:51:42.884 EST [96545] LOG:  all server processes terminated; 
reinitializing
2020-11-17 11:51:42.904 EST [96545] LOG:  received fast shutdown request
2020-11-17 11:51:42.905 EST [96585] LOG:  database system was interrupted; last 
known up at 2020-11-17 11:51:42 EST
2020-11-17 11:51:42.906 EST [96585] LOG:  database system was not properly shut 
down; automatic recovery in progress
2020-11-17 11:51:42.906 EST [96585] LOG:  redo starts at E0/DB6B2960
2020-11-17 11:51:42.907 EST [96545] LOG:  abnormal database system shutdown
2020-11-17 11:51:42.909 EST [96545] LOG:  database system is shut down
—

So I’m assuming it’s that free() call.  Servers have PostGIS 3.0 on them, all 
installed from repo, and running CentOS 8.


Was this after a clean install of the corrected RPM's?


--
Adrian Klaver
adrian.kla...@aklaver.com

pg_upgrade of 11 -> 13: free(): invalid pointer

[Jeremy Wilson]

I’m continuing my upgrade journey, this time from 11 to 13, and the process is 
dying in the copy phase, always on the same DB:

—
Performing Upgrade
--
Analyzing all rows in the new cluster   ok
Freezing all rows in the new clusterok
Deleting files from new pg_xact ok
Copying old pg_xact to new server   ok
Setting next transaction ID and epoch for new cluster   ok
Deleting files from new pg_multixact/offsetsok
Copying old pg_multixact/offsets to new server  ok
Deleting files from new pg_multixact/membersok
Copying old pg_multixact/members to new server  ok
Setting next multixact ID and offset for new clusterok
Resetting WAL archives  ok
Setting frozenxid and minmxid counters in new cluster   ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
  messages
*failure*

Consult the last few lines of "pg_upgrade_dump_16387.log" for
the probable cause of the failure.
Failure, exiting
—

The log contains (which is different each time):

—
pg_restore: 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.
pg_restore: creating COMMENT "public.FUNCTION "st_isempty"("rast" 
"public"."raster")"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 5338; 0 0 COMMENT FUNCTION "st_isempty"("rast" 
"public"."raster") postgres
pg_restore: error: could not execute query: server closed the connection 
unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Command was: COMMENT ON FUNCTION "public"."st_isempty"("rast" 
"public"."raster") IS 'args: rast - Returns true if the raster is empty (width 
= 0 and height = 0). Otherwise, returns false.’;
—

And the pgsql13 server log contains: 

—
2020-11-17 11:51:40.953 EST [96545] LOG:  database system is ready to accept 
connections
free(): invalid pointer
2020-11-17 11:51:42.880 EST [96545] LOG:  server process (PID 96575) was 
terminated by signal 6: Aborted
2020-11-17 11:51:42.880 EST [96545] LOG:  terminating any other active server 
processes
2020-11-17 11:51:42.880 EST [96582] WARNING:  terminating connection because of 
crash of another server process
2020-11-17 11:51:42.880 EST [96582] 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.
2020-11-17 11:51:42.880 EST [96582] HINT:  In a moment you should be able to 
reconnect to the database and repeat your command.
2020-11-17 11:51:42.884 EST [96545] LOG:  all server processes terminated; 
reinitializing
2020-11-17 11:51:42.904 EST [96545] LOG:  received fast shutdown request
2020-11-17 11:51:42.905 EST [96585] LOG:  database system was interrupted; last 
known up at 2020-11-17 11:51:42 EST
2020-11-17 11:51:42.906 EST [96585] LOG:  database system was not properly shut 
down; automatic recovery in progress
2020-11-17 11:51:42.906 EST [96585] LOG:  redo starts at E0/DB6B2960
2020-11-17 11:51:42.907 EST [96545] LOG:  abnormal database system shutdown
2020-11-17 11:51:42.909 EST [96545] LOG:  database system is shut down
—

So I’m assuming it’s that free() call.  Servers have PostGIS 3.0 on them, all 
installed from repo, and running CentOS 8.

Re: Unable to compile postgres 13.1 on Slackware current x64

[Peter Eisentraut]

On 2020-11-16 18:30, Tom Lane wrote:

In this case, I see one use of the constant TRUE in collationcmds.c,
but I wonder how come that's there given that we deprecated upper-case
TRUE some time ago.


In 2eb4a831e5fb5d8fc17e13aea56e04af3efe27b4, I intentionally left that 
there because it was the documented ICU API.  Now that that's changed, 
we should just do s/TRUE/true/ there as suggested.

Re: I have just downloaded Postgre SQL and "pgadmin 4" doesn't open.

[Ecenur Corlu]

Thank you so much for your time and help  Mr Klaver.  I will try to
investigate it as I keep learning about machines.
With sincere thanks,
Ece

Adrian Klaver , 17 Kas 2020 Sal, 18:22 tarihinde
şunu yazdı:

> On 11/16/20 9:30 PM, Ecenur Corlu wrote:
> >It is Windows 10  too.
>
> Given that this is the same OS and essentially the same hardware as the
> other machine, I would be investigating the state of the other machine
> in general.
>
> >
> > Ecenur Corlu mailto:corluece...@gmail.com>>, 17
> > Kas 2020 Sal, 08:26 tarihinde şunu yazdı:
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

RE: Race condition with restore_command on streaming replica

[Brad Nicholson]

Dilip Kumar  wrote on 2020/11/15 04:47:12 AM:
> I am not sure how Patroni does it internally,  can you explain the
> scenario in more detail?  Suppose you are executing the promote on m-1
> and if the promotion is successful it will switch the timeline and it
> will create the timeline history file.  Now, once the promotion is
> successful if we change the primary_conninfo on the m-2 then it will
> restart the walsender and look for the latest .history file which it
> should find either from direct streaming or through the
> restore_command.  If you are saying that m-2 tried to look for the
> history file before m-1 created it then it seems like you change the
> primary_conninfo on m-2 before the m-1 promotion got completed.


Patroni first issues a fast shutdown to m-0.  Once that completes, it
issues a promote on m-1.

In this case, m-2 is not connecting directly to to m-0 or m-1 via a
hostname, it is connecting to a virtual layer that routes the connectivity
to the current primary.  This does not change.

Brad.

Re: I have just downloaded Postgre SQL and "pgadmin 4" doesn't open.

[Adrian Klaver]

On 11/16/20 9:30 PM, Ecenur Corlu wrote:

   It is Windows 10  too.


Given that this is the same OS and essentially the same hardware as the 
other machine, I would be investigating the state of the other machine 
in general.




Ecenur Corlu mailto:corluece...@gmail.com>>, 17 
Kas 2020 Sal, 08:26 tarihinde şunu yazdı:




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Unable to compile postgres 13.1 on Slackware current x64

[Condor]

On 17-11-2020 11:00, Thomas Munro wrote:

On Tue, Nov 17, 2020 at 8:02 PM Condor  wrote:

I try to compile postgres again with (cd src/backend/commands; sed
's/TRUE/true/' collationcmds.c > collationcmds.c; ) and it's compiled
but get new error on linking:


Doesn't that produce an empty file collationcmds.c? I think you want:

sed 's/TRUE/true/' collationcmds.c > collationcmds.c.tmp && mv
collationcmds.c.tmp collationcmds.c


Yes,
you are right. Changing script to (cd src/backend/commands; sed 
's/TRUE/true/' collationcmds.c > collationcmds.c.tmp; mv 
collationcmds.c.tmp collationcmds.c )

and PostgreSQL is compiled without any errors and working like a charm.

Best Regards,
Hristo Simeonov

Re: Unable to compile postgres 13.1 on Slackware current x64

[Thomas Munro]

On Tue, Nov 17, 2020 at 8:02 PM Condor  wrote:
> I try to compile postgres again with (cd src/backend/commands; sed
> 's/TRUE/true/' collationcmds.c > collationcmds.c; ) and it's compiled
> but get new error on linking:

Doesn't that produce an empty file collationcmds.c? I think you want:

sed 's/TRUE/true/' collationcmds.c > collationcmds.c.tmp && mv
collationcmds.c.tmp collationcmds.c