Re: Help in vetting error using "pg_upgrade" - steps for Postgres DB upgrade from Ver 13.X to ver 14.X/15.X and issue with python binaries

[Adrian Klaver]

On 1/28/25 08:23, Bharani SV-forum wrote:

Team
Need your help.
We are trying to use existing VM with underlying OS = Amazon Linux 2 
(AL2)  along with Pgsql ver 13.X community edn.
Trying to upgrade from 13.X to 15.X and had hit the bottleneck for the 
mandate to have python ver 3.X binaries.
We have limitation with the existing VM with AWS and currently AL2 uses 
the |yum| package manager that has a hard dependency on Python 2.7 and 
the pgsql ver 15. needed Ver 3.x python binaries and the package 
community edition (pgsql ver 15.x) "postgresql15-contrib.." is not 
getting installed.


We have limitation , where the application being used is having python 
ver 2.x binaries and we cannot install python 3.x binaries , as  we have 
the underlying OS with AL2 which is having "a hard dependency on Python 2.7"


In lieu of using pgsql ver 15.X. We are OK to upgrade to the next 
version which is 14.X  from existing ver 13.X.


Can anyone re-confirm if the Community edition (pgsql ver14.x)  - 
"postgresql14-contrib .." needed python ver 2.X binaries or Ver 3.X 
binaries.


From here:

https://yum.postgresql.org/14/redhat/rhel-7-x86_64/repoview/postgresql14-contrib.html

postgresql14-contrib-14.15-1PGDG.rhel7.x86_64

hstore_plpython3.so
jsonb_plpython3.so
ltree_plpython3.so

So I'm going to say yes Python 3 is needed.

FYI I don't see those files in postgresql13-contrib.

If you want to move forward you need to either install Python 3 in your 
current distro or use a different distro.




I cross checked and found one of the url " Install Postgres v14 + 
-contrib on AWS Linux 2 | the gabriellephant 
" is quoting pgsql ver 14.x needed python ver 3.x binary


On Friday, January 24, 2025 at 02:14:56 PM EST, Adrian Klaver 
 wrote:







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

Re: Help in vetting measuring egress/ingress w.r.to " pg_dumpall " - old vm to new vm

[Ron Johnson]

On Tue, Jan 28, 2025 at 3:13 PM Bharani SV-forum 
wrote:

> TQ Adrian
> another Question on the measuring egress (out bound traffic) /ingress
> (inbound traffic) w.r.to " pg_dumpall " during usage of
>
> pg_dumpall -h-p 5432 | psql -p 5462
>
> taking data from old_vm and copying to new_vm, as i need to use across the
> network for taking data and i cannot use "pg_upgrade" tool as AWS - AL3
> doesnot support postgresql 13 - community edition.
>
> My existing DB size is  *around 60 GB* (all the DB's) using postgresql
> Ver 13.
>

iotop is *always* useful.

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Help in vetting measuring egress/ingress w.r.to " pg_dumpall " - old vm to new vm

[Adrian Klaver]

On 1/28/25 12:13, Bharani SV-forum wrote:

TQ Adrian
another Question on the measuring egress (out bound traffic) /ingress 
(inbound traffic) w.r.to " pg_dumpall " during usage of


Since you are using AWS EC2(?) instances wouldn't the AWS dashboard show 
you this?




pg_dumpall -h    -p 5432 | psql -p 5462

taking data from old_vm and copying to new_vm, as i need to use across 
the network for taking data and i cannot use "pg_upgrade" tool as AWS - 
AL3 doesnot support postgresql 13 - community edition.


The PGDG repo does:

https://www.postgresql.org/download/linux/redhat/

Have you tried using it?

Also from what I gather there is no Amazon Linux 3.

There is Amazon Linux 2023, is that what you are referring to?


My existing DB size is *around 60 GB* (all the DB's) using postgresql 
Ver 13.




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

Re: Parallel workers via functions?

[Jim Vanns]

Thanks for the reply Laurenz. Inline replies follow...

On Tue, 28 Jan 2025 at 04:47, Laurenz Albe  wrote:
>
> On Mon, 2025-01-27 at 18:08 +, Jim Vanns wrote:
> > If I have a function that is marked 'stable parallel safe' and returns
> > a table, can a calling function or procedure (marked volatile parallel
> > unsafe) still take advantage of the parallel workers from the first
> > function - as the data source. I.e.
> >
> > func_a(); // selects, returns table, parallel safe
> > func_b() {
> >insert into foo
> >select * from func_a(); // Will func_a still execute parallel
> > workers to fetch the data?
> > }
> >
> > Or even if func_b() uses 'create temporary table as select * from
> > func_a()' and then insert?
> >
> > I ask because when I simply call func_a() from a psql shell, I see the
> > parallel workers run and everything is nice and swift. But when called
> > from a data-modifying function like func_b(), no workers are spawned
> > :( Even from the read-part of the code.
> >
> > Are there differences in functions vs. stored procedures that might
> > affect the behaviour of the planner to disregard workers?
>
> See 
> https://www.postgresql.org/docs/current/when-can-parallel-query-be-used.html

Thanks. Yup, read that. Seems easy enough to understand... however...

> The problem here is the INSERT.  Data modifying statements won't use
> parallel query.

OK, that's clear enough.

> There are exceptions: CREATE TABLE ... AS SELECT ... should be able
> to use parallel query.

I've been experimenting with this. The problem deepens... It seems
that actually, it's the function itself - func_a() in my example
above. Even simply calling that from psql doesn't spawn parallel
workers to run as part of the query defined in the funcion body. But
if I copy the body of the function and paste it into a psql shell, it
does parallelise. This function is marked STABLE PARALLEL SAFE though.
Are there limitations or restrictions I'm missing!? I'll try to find
the time to provide a MRP but I'm hoping somebody will just magically
know what the problem is or at least could be!

So... I am still confused! This is PG 15.5 BTW.

Jim

> Yours,
> Laurenz Albe




--
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London

Re[2]: FATAL: could not send data to WAL stream: lost synchronization with server: got message type "0", length 892351284

[Дмитрий]

Colleagues confirmed that the problem is with the network between data centers. 
Thank you!
воскресенье, 26 января 2025г., 20:33 +03:00 от Adrian Klaver  
adrian.kla...@aklaver.com :

>On 1/26/25 03:29, Дмитрий wrote:
> "How was it shut down, on purpose or a hardware/software issue?"
> - I reboot the receiver every 2 minutes on purpose. I determined this 
> time empirically, because replication breaks down approximately every 
> minute and a half. The reboot helps to advance the receiver.
>
> "Also do you have corresponding logs from primary?"
> - Attached to this message.
>
> "Unless, is there cascading replication going on?"
> - No, this is replication from the leader. The leader has its two 
> replicas and they are all in one data center. And the problematic 
> replica is needed to migrate to another data center.
>
> "Was that a manual intervention?"
> - Yes, reboot on schedule, every two minutes.
>
> "Is that what is shown above or have you restarted since the above and
> the server is running?"
> - Sometimes replication works without problems for several hours. But 
> when a breakdown occurs, rebooting every two minutes helps to catch up 
> with this replica.
>1) It would make life easier if the log line entry prefix timestamp was 
>set to same precision on primary and standby. As of now it looks like 
>the primary has %t (Time stamp without milliseconds) and the standby has
>%m (Time stamp with milliseconds)
>
>2) From the logs.
>
>Primary:
>
>2025-01-26 12:21:27 MSK [656]: [11-1] 
>app=v-host-n1,user=replicator,db=[unknown],client=192.168.5.1 STATEMENT: 
>  START_REPLICATION SLOT "slot_migration_to_rcod" 106B6/5200 TIMELINE 61
>
>2025-01-26 12:21:27 MSK [656]: [12-1] 
>app=v-host-n1,user=replicator,db=[unknown],client=192.168.5.1 LOG: 
>disconnection: session time: 0:01:05.329 user=replicator database= 
>host=192.168.5.1 port=58380
>
>
>Standby:
>
>2025-01-26 12:21:27.113 MSK [10824] FATAL:  could not send data to WAL 
>stream: lost synchronization with server: got message type "0", length 
>825373235
>
>
>Do you know what is doing START_REPLICATION SLOT?
>
>
> Another interesting point. In addition to this replication, there are 
> two more, to the same data center. One replication had the same problem, 
> but a one-time restart helped to solve the problem, the replication is 
> still working normally. And the second replication does not have such 
> problems, it has been working since its launch, more than a month ago.
>
> --
>
>
>
>-- 
>Adrian Klaver
>adrian.kla...@aklaver.com

Help in vetting error using "pg_upgrade" - steps for Postgres DB upgrade from Ver 13.X to ver 14.X/15.X and issue with python binaries

[Bharani SV-forum]

 TeamNeed your help.We are trying to use existing VM with underlying OS = 
Amazon Linux 2 (AL2)  along with Pgsql ver 13.X community edn.Trying to upgrade 
from 13.X to 15.X and had hit the bottleneck for the mandate to have python ver 
3.X binaries.We have limitation with the existing VM with AWS and currently AL2 
uses the yum package manager that has a hard dependency on Python 2.7 and the 
pgsql ver 15. needed Ver 3.x python binaries and the package community edition 
(pgsql ver 15.x) "postgresql15-contrib.." is not getting installed.
We have limitation , where the application being used is having python ver 2.x 
binaries and we cannot install python 3.x binaries , as  we have the underlying 
OS with AL2 which is having "a hard dependency on Python 2.7"
In lieu of using pgsql ver 15.X. We are OK to upgrade to the next version which 
is 14.X  from existing ver 13.X.
Can anyone re-confirm if the Community edition (pgsql ver14.x)  - 
"postgresql14-contrib .." needed python ver 2.X binaries or Ver 3.X binaries.
I cross checked and found one of the url " Install Postgres v14 + -contrib on 
AWS Linux 2 | the gabriellephant" is quoting pgsql ver 14.x needed python ver 
3.x binary
On Friday, January 24, 2025 at 02:14:56 PM EST, Adrian Klaver 
 wrote:  
 
 

On 1/24/25 10:01 AM, Bharani SV-forum wrote:
> Adrian
> Thanks
> 
> This is the exact error which the system admin is facing
> 
> 
>     postgresql15-contrib installation on Amazon Linux 2 fails on Python
>  shared lib dependency

Which from your post the admin said was due to:

"His version is "It needs libpython3.6m.so.1.0()(64bit)" "


Note the libpython3.6.


The link I posted previously:

https://developers.redhat.com/blog/install-python3-rhel#installing_python_3_on_rhel_7

Shows how to install Python 3.6


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

Re: Content of pg_publication using a local connection versus network connection?

[Shaheed Haque]

Hi,

Based on the nudge from Adrian, I think I am now trying to connect to the
correct/same database through both the original
login-to-EC2-host-then-use-psql-to-RDS and then
setup-SSHTunnel-then-connect-via-psycopg-over-tunnel.

The connect-via-psycopg-over-tunnel bit currently fails. Obviously, that is
almost certainly a bug in my code, but I am aware that the Postgres' HBA
setup is capable of distinguishing local logins from remote logins, so I
wanted to check if Postgres' login security can similarly distinguish
between a (remote) psql login and a (remote) psycopg login?

Thanks, Shaheed



On Mon, 27 Jan 2025 at 22:20, Shaheed Haque  wrote:

>
>
> On Mon, 27 Jan 2025 at 21:54, Adrian Klaver 
> wrote:
>
>> On 1/27/25 13:34, Shaheed Haque wrote:
>> > Hi Adrian,
>> >
>> > On Mon, 27 Jan 2025 at 20:51, Adrian Klaver > > > wrote:
>> >
>> > On 1/27/25 12:41, Shaheed Haque wrote:
>> >  > Hi,
>> >  >
>> >  > I'm a novice-ish when it comes to Postgres, but I've studied the
>> > docs
>> >  > and not been able to understand why I can see the rows in
>> > pg_publication
>> >  > via a local psql session, but not when I am connected via the
>> > network.
>> >  >
>> >  > Since the network login is (a) successful and (b) can read the
>> > content
>> >  > of other non-system tables, I guessed that my problem is
>> row-level
>> >  > security (RLS)except that from the docs, I was unable to see
>> > how the
>> >  > login type could affect RLS. What am I missing?
>> >  >
>> >  > Here is some context...please do ask if something else needs to
>> be
>> >  > clarified!
>> >  >
>> >  > - System Postgres 16, AWS RDS version.
>> >  > - The pg_publication tabe looks like this:
>> >  >
>> >  > foo=>  \dpS pg_publication
>> >  >Access privileges
>> >  >Schema   |  Name  | Type  | Access privileges
>> |
>> >  > Column privileges | Policies
>> >  >
>> >
>>  
>> ++---+---+---+--
>> >  > pg_catalog | pg_publication | table | rdsadmin=arwdDxt/rdsadmin+|
>> >  >|
>> >  > ||   | =r/rdsadmin
>>   |
>> >  >|
>> >  >
>> >  >
>> >  > - When I am logged in as this user via psql, I  can see:
>> >
>> > This user is rdsadmin or something else?
>> >
>> >
>> > The username is "dbcorexyz". See more  below.
>> >
>> >  >
>> >  > foo=> select * from pg_publication;
>> >  >   oid  |  pubname  | pubowner | puballtables | pubinsert
>> |
>> >  > pubupdate | pubdelete | pubtruncate | pubviaroot
>> >  >
>> >
>>  
>> ---+---+--+--+---+---+---+-+
>> >  > 98923 | vm_db_publication |16478 | t| t
>> | t
>> >  >  | t | t   | f
>> >  >
>> >  >
>> >  > - When I connect via psycog, I can read other tables, but
>> > pg_publication
>> >  > aways seems to return no rows.
>> >
>> > 1) What is your connection string?
>> >  In particular what user are you connecting as?
>> >
>> >
>> > When I use psql, I first have to SSH to an AWS EC2, and then run psql.
>> > Thus, the details in this case are:
>> >
>> >   * ssh -i vm_paiyroll.pem awsuser@18.168.196.169
>> > 
>> >   * foo=> \conninfo
>> >
>> > You are connected to database "foo" as user "dbcorexyz" on host
>> > "live-paiyroll-db-c702180bbf.ci22uuz4wz33.eu-west-2.rds.amazonaws.com
>> > <
>> http://live-paiyroll-db-c702180bbf.ci22uuz4wz33.eu-west-2.rds.amazonaws.com>"
>> (address "172.31.4.93") at port "5432".
>> > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
>> > compression: off)
>> >
>> > When I connect via pscopg, I first set up an SSH tunnel through the EC2
>> > host, and then connect. Thus the details in this case are:
>> >
>> >   *
>> >
>> > bastion_host.ssh_host is '18.168.196.169', bastion_host.ssh_usernme
>> isawsuser
>> >
>> >   * > > database=foo) at 0x7f6bfd554a90>
>> >
>> > I *am* dealing with multiple db connections (am working on some
>> > replication tooling) but AFAICS, both connections are to the same place.
>> >
>>
>> Are you sure?
>>
>>  From psql connection:
>>
>> You are connected to database "foo" as user "dbcorexyz" on host
>> "live-paiyroll-db-c702180bbf.ci22uuz4wz33.eu-west-2.rds.amazonaws.com"
>> (address "172.31.4.93")
>>
>> Note host of 172.31.4.93
>>
>> In psycopg2 case you again connect to 18.168.196.169 for SSH but then:
>>
>> (host=localhost ...)
>>
>> I'm not seeing localhost being equal to 172.31.4.93.
>>
>
> Erk. I think you may have got it. I will go examine my navel...and the
> code. Many thanks for the quick and kind help.

Re: Content of pg_publication using a local connection versus network connection?

[Adrian Klaver]

On 1/28/25 10:02 AM, Shaheed Haque wrote:

Hi,

Based on the nudge from Adrian, I think I am now trying to connect to 
the correct/same database through both the original 
login-to-EC2-host-then-use-psql-to-RDS and then 
setup-SSHTunnel-then-connect-via-psycopg-over-tunnel.


The connect-via-psycopg-over-tunnel bit currently fails. Obviously, that 
is almost certainly a bug in my code, but I am aware that the Postgres' 
HBA setup is capable of distinguishing local logins from remote logins, 
so I wanted to check if Postgres' login security can similarly 
distinguish between a (remote) psql login and a (remote) psycopg login?


They both use libpq so I doubt it.



Thanks, Shaheed



On


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

Re: Content of pg_publication using a local connection versus network connection?

[Shaheed Haque]

Thanks for the confirmation.

On Tue, 28 Jan 2025 at 18:06, Adrian Klaver 
wrote:

>
>
> On 1/28/25 10:02 AM, Shaheed Haque wrote:
> > Hi,
> >
> > Based on the nudge from Adrian, I think I am now trying to connect to
> > the correct/same database through both the original
> > login-to-EC2-host-then-use-psql-to-RDS and then
> > setup-SSHTunnel-then-connect-via-psycopg-over-tunnel.
> >
> > The connect-via-psycopg-over-tunnel bit currently fails. Obviously, that
> > is almost certainly a bug in my code, but I am aware that the Postgres'
> > HBA setup is capable of distinguishing local logins from remote logins,
> > so I wanted to check if Postgres' login security can similarly
> > distinguish between a (remote) psql login and a (remote) psycopg login?
>
> They both use libpq so I doubt it.
>
> >
> > Thanks, Shaheed
> >
> >
> >
> > On
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Content of pg_publication using a local connection versus network connection?

[Adrian Klaver]

On 1/28/25 10:08 AM, Shaheed Haque wrote:


Thanks for the confirmation.


Your login/connection security is going to be handled by:

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

and the auth methods it supports.



On Tue, 28 Jan 2025 at 18:06, Adrian Klaver > wrote:





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

Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.

[Nem Tudom]

On 28/01/2025 00:05, Thomas Munro wrote:


Thanks to you and all the others who took the trouble to reply,



I showed the bones of how you could do this in SQL here:
https://www.postgresql.org/message-id/CA%2BhUKGLU9Don4YHnfdzn0eeWQsUu8GJDaLiUAefLLT6%3DmmeGoQ%40mail.gmail.com



The technical explanation is much appreciated - I'd gathered (more or 
less) as much from my searching and reading the leap second Wiki.


So, the situation is that, basically, leap seconds are "fudged" to use 
the technical term!


I asked the question with a view to having accurate TIMESTAMP 
differences - i.e. to the second. However, since everyone is fudging 
(incl. AFAICS Oracle and SQL Server), this means that accepting the 
status quo will just make my inaccuracies will be the same as everyone 
else's, ergo I'm golden!


At least that's one issue that I can safely ignore - I didn't fancy 
implementing this on my own.


Thanks again and rgs,


E!

Help in vetting measuring egress/ingress w.r.to " pg_dumpall " - old vm to new vm

[Bharani SV-forum]

 TQ Adriananother Question on the measuring egress (out bound traffic) /ingress 
(inbound traffic) w.r.to " pg_dumpall " during usage of 
pg_dumpall-h    -p 5432 | psql -p 5462

taking data from old_vm and copying to new_vm, as i need to use across the 
network for taking data and i cannot use "pg_upgrade" tool as AWS - AL3 doesnot 
support postgresql 13 - community edition. My existing DB size is  around 60 GB 
(all the DB's) using postgresql Ver 13.