Missing RHEL8 RPMS

[dev dyskolos]




Hello,

some RPMS, like python3-ydiff, are available for rhel7 and rhel9 but are 
missing in the rhel8 repo:
https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-8-x86_64/

The rpms an are a prerequisite of patroni that thus fails to install  on rhel8.

Which is the proper way to install patroni? Where do I have to source the 
required RPMs from?

Regards,
Bernardo

Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

[Laurenz Albe]

I may have been one of the respondents who showed some annoyance, and I am sorry
for that.  I understand that you ask questions to gain deeper understanding.

On Mon, 2023-03-20 at 13:46 -0700, Bryn Llewellyn wrote:
> Oracle Database doesn't expose scrollability for PL/SQL's equivalent of 
> "refcursor".
> So I had never come across use cases where this was beneficial. I wanted, 
> therefore,
> to hear about some. I thought that insights here would help me understand the 
> mechanics.
> But I didn't get anything beyond "Scrollability is what it is. If you don't 
> need it,
> don't use it."

I recently used cursor scrollability, so I can show you a use case:
https://github.com/cybertec-postgresql/db_migrator/blob/master/db_migrator--1.0.0.sql#L49

The goal is to get the query result count right away, without having to run
a second query for it: you declare the cursor, move to the end of the result 
set,
fetch the ROW_COUNT, then move back to the beginning of the result set and start
fetching the result rows.


About your description of the difficulties with the terms "cursor", "portal",
"bound" vs. "unbound" cursors in PL/pgSQL etc: I understand your confusion, and 
I
believe that the documentation could be improved.

The way I understand it, "portal" is PostgreSQL jargon.  A portal is a cursor.
The documentation tries to avoid "portal" as an implementation detail.
PL/pgSQL cursors and "refcursor"s are not the same as SQL cursors: they are
variables that hold a cursor name.  That is confusing.

I personally find that reading the PostgreSQL documentation gets you far, but 
only
so far: for deep understanding, you have to read the code.  It is usually well
documented and readable, and I have come to see it as an extension of the
documentation that covers the details.

Yours,
Laurenz Albe

Re: Re[2]: Getting error while upgrading postgres from version 12 to 13

[shashidhar Reddy]

Actually I was using the below command to check the compatibility, it
worked without any issue with 12.6 but it is not working with 1version 2.14
time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir
/usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin
--old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2 --link
--check

I have used a different directory for postgres13 from postgres 12.

On Mon, Mar 20, 2023 at 11:07 AM  wrote:

> Hello,
>
> As per error it seems you have initiated source data with PG13 binaries.
>
> Could you please connect to both source and target psql and share out of
> select versions();
>
> Also share pg_upgrade complete command which you are using.
>
>
>
> Regards,
> Abdul Sayeed
>
>
>
> Sent from myMail for iOS
>
>
> Sunday, 19 March 2023 at 11:29 AM +0530 from david.g.johns...@gmail.com <
> david.g.johns...@gmail.com>:
>
> On Sat, Mar 18, 2023 at 7:27PM shashidhar Reddy <
> shashidharreddy...@gmail.com> wrote:
>
> Can someone please help me with this?
>
>
>
> You haven't given others enough information to help you.  Present to the
> fullest extent possible your current environment and the commands you are
> running.  Showing that psql itself works on both running clusters would be
> a good start.  Show inputs, not just outputs those inputs creates.
>
> David J.
>
>

-- 
Shashidhar

Re: Oracle to PostgreSQL Migration

[Umair Shahid]

Could oracle_fdw  help in your use
case? You could consider setting up a job to pull data into PostgreSQL live
and then cut over when you are ready.

- Umair

On Tue, Mar 21, 2023 at 10:36 AM Inzamam Shafiq 
wrote:

> Hi Thomas,
>
> We have tried kafka, but in that we stuck in CDC part, update/delete was
> not working due to some NULL value issue. Do you have any helping material
> for Oracle to PostgreSQL data migration using debezium?
>
> Regards,
>
> *Inzamam Shafiq*
> *Sr. DBA*
> --
> *From:* Thomas Kellerer 
> *Sent:* Monday, March 20, 2023 7:21 PM
> *To:* pgsql-general@lists.postgresql.org <
> pgsql-general@lists.postgresql.org>
> *Subject:* Re: Oracle to PostgreSQL Migration
>
> Inzamam Shafiq schrieb am 20.03.2023 um 13:57:
> > We have an Oracle DB which is around 1TB and we want to migrate to
> > PostgreSQL that have a new table structure, so we want to perform
> > data transformation and real time CDC from Oracle to PostgreSQL. Do
> > we have any good open source tool to achieve this with No Coding
> > involved.??
>
>
> You could try debezium, but I don't know how good it works and if it
> qualifies as "no coding involved"
>
>
>
>

Re: Getting error while upgrading postgres from version 12 to 13

[Adrian Klaver]

On 3/21/23 03:29, shashidhar Reddy wrote:
Actually I was using the below command to check the compatibility, it 
worked without any issue with 12.6 but it is not working with 1version 2.14
time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir 
/usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin 
--old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2 
--link --check


I have used a different directory for postgres13 from postgres 12.


The issue is not that they are the same directory it is:

FATAL:  database files are incompatible with server
 DETAIL:  The data directory was initialized by PostgreSQL version 13
, which is not compatible with this version 12.14 (Ubuntu
12.14-1.pgdg18.04+1)

Check the value that is the PG_VERSION file in each data directory. It 
should be 12 in /usr/pgdata/pg_data  and 13 in /usr/pgdata/pg_data2.




On Mon, Mar 20, 2023 at 11:07 AM > wrote:


Hello,

As per error it seems you have initiated source data with PG13
binaries.

Could you please connect to both source and target psql and share
out of select versions();

Also share pg_upgrade complete command which you are using.



Regards,
Abdul Sayeed



Sent from myMail for iOS


Sunday, 19 March 2023 at 11:29 AM +0530 from
david.g.johns...@gmail.com 
mailto:david.g.johns...@gmail.com>>:

On Sat, Mar 18, 2023 at 7:27PM shashidhar Reddy
mailto:shashidharreddy...@gmail.com>> wrote:

Can someone please help me with this?



You haven't given others enough information to help you. 
Present to the fullest extent possible your current environment

and the commands you are running.  Showing that psql itself
works on both running clusters would be a good start.  Show
inputs, not just outputs those inputs creates.

David J.



--
Shashidhar


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

Re: Getting error while upgrading postgres from version 12 to 13

[shashidhar Reddy]

Both shows different versions as 13 shows version 13.10 and 12 shows 12.14

On Tue, 21 Mar, 2023, 8:09 pm Adrian Klaver, 
wrote:

> On 3/21/23 03:29, shashidhar Reddy wrote:
> > Actually I was using the below command to check the compatibility, it
> > worked without any issue with 12.6 but it is not working with 1version
> 2.14
> > time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir
> > /usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin
> > --old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2
> > --link --check
> >
> > I have used a different directory for postgres13 from postgres 12.
>
> The issue is not that they are the same directory it is:
>
> FATAL:  database files are incompatible with server
>   DETAIL:  The data directory was initialized by PostgreSQL version 13
> , which is not compatible with this version 12.14 (Ubuntu
> 12.14-1.pgdg18.04+1)
>
> Check the value that is the PG_VERSION file in each data directory. It
> should be 12 in /usr/pgdata/pg_data  and 13 in /usr/pgdata/pg_data2.
>
> >
> > On Mon, Mar 20, 2023 at 11:07 AM  > > wrote:
> >
> > Hello,
> >
> > As per error it seems you have initiated source data with PG13
> > binaries.
> >
> > Could you please connect to both source and target psql and share
> > out of select versions();
> >
> > Also share pg_upgrade complete command which you are using.
> >
> >
> >
> > Regards,
> > Abdul Sayeed
> >
> >
> >
> > Sent from myMail for iOS
> >
> >
> > Sunday, 19 March 2023 at 11:29 AM +0530 from
> > david.g.johns...@gmail.com 
> > mailto:david.g.johns...@gmail.com>>:
> >
> > On Sat, Mar 18, 2023 at 7:27PM shashidhar Reddy
> >  > > wrote:
> >
> > Can someone please help me with this?
> >
> >
> >
> > You haven't given others enough information to help you.
> > Present to the fullest extent possible your current environment
> > and the commands you are running.  Showing that psql itself
> > works on both running clusters would be a good start.  Show
> > inputs, not just outputs those inputs creates.
> >
> > David J.
> >
> >
> >
> > --
> > Shashidhar
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Oracle to PostgreSQL Migration

[Marc Millas]

EDB do have a replication server which can be used to transfer real time
data from oracle to postgres.
don't know if it can be used to get to "no downtime"
BTW what do you call "no downtime" as anyway a switch, as fast as it can be
do take ""some"" time ?


Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Mon, Mar 20, 2023 at 1:58 PM Inzamam Shafiq 
wrote:

> Hi,
>
> Hope everyone is fine.
>
> Can someone help or guide regarding Open Source tools for Oracle to
> PostgreSQL migration with real time CDC. along with this is there any
> possibility to change the structure of the database? Let me explain a
> little more,
>
> We have an Oracle DB which is around 1TB and we want to migrate to
> PostgreSQL that have a new table structure, so we want to perform data
> transformation and real time CDC from Oracle to PostgreSQL. Do we have any
> good open source tool to achieve this with No Coding involved.??
>
> Thanks.
>
> Regards,
>
> *Inzamam Shafiq*
> *Sr. DBA*
>

Re: Getting error while upgrading postgres from version 12 to 13

[Adrian Klaver]

On 3/21/23 08:13, shashidhar Reddy wrote:

Both shows different versions as 13 shows version 13.10 and 12 shows 12.

The question asked was:

"Check the value that is the PG_VERSION file in each data directory. It
should be 12 in /usr/pgdata/pg_data  and 13 in /usr/pgdata/pg_data2."

So indicate which directory path has which PG_VERSION value.

While you are at it do:

/usr/lib/postgresql/13/bin/pg_upgrade -V

and post the version returned.



On Tue, 21 Mar, 2023, 8:09 pm Adrian Klaver, > wrote:


On 3/21/23 03:29, shashidhar Reddy wrote:
 > Actually I was using the below command to check the
compatibility, it
 > worked without any issue with 12.6 but it is not working with
1version 2.14
 > time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir
 > /usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin
 > --old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2
 > --link --check
 >
 > I have used a different directory for postgres13 from postgres 12.

The issue is not that they are the same directory it is:

FATAL:  database files are incompatible with server
   DETAIL:  The data directory was initialized by PostgreSQL version 13
, which is not compatible with this version 12.14 (Ubuntu
12.14-1.pgdg18.04+1)

Check the value that is the PG_VERSION file in each data directory. It
should be 12 in /usr/pgdata/pg_data  and 13 in /usr/pgdata/pg_data2.

 >
 > On Mon, Mar 20, 2023 at 11:07 AM mailto:abdulsayee...@gmail.com>
 > >> wrote:
 >
 >     Hello,
 >
 >     As per error it seems you have initiated source data with PG13
 >     binaries.
 >
 >     Could you please connect to both source and target psql and share
 >     out of select versions();
 >
 >     Also share pg_upgrade complete command which you are using.
 >
 >
 >
 >     Regards,
 >     Abdul Sayeed
 >
 >
 >
 >     Sent from myMail for iOS
 >
 >
 >     Sunday, 19 March 2023 at 11:29 AM +0530 from
 > david.g.johns...@gmail.com 
>
 >     mailto:david.g.johns...@gmail.com>
>>:
 >
 >         On Sat, Mar 18, 2023 at 7:27PM shashidhar Reddy
 >         mailto:shashidharreddy...@gmail.com>
 >         >> wrote:
 >
 >             Can someone please help me with this?
 >
 >
 >
 >         You haven't given others enough information to help you.
 >         Present to the fullest extent possible your current
environment
 >         and the commands you are running.  Showing that psql itself
 >         works on both running clusters would be a good start.  Show
 >         inputs, not just outputs those inputs creates.
 >
 >         David J.
 >
 >
 >
 > --
 > Shashidhar

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



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

Re: Re[2]: Getting error while upgrading postgres from version 12 to 13

[Geoff Winkless]

On Tue, 21 Mar 2023 at 10:29, shashidhar Reddy 
wrote:

> Actually I was using the below command to check the compatibility, it
> worked without any issue with 12.6 but it is not working with 1version 2.14
> time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir
> /usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin
> --old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2 --link
> --check
>

Just in case I'm _not_ misreading this... are you saying you already ran
this command once with postgres v12.6?

If so, since you've used --link the original folder will no longer be
version12 data.

Geoff

>

Re: Re[2]: Getting error while upgrading postgres from version 12 to 13

[Geoff Winkless]

On Tue, 21 Mar 2023 at 16:06, Geoff Winkless  wrote:

> On Tue, 21 Mar 2023 at 10:29, shashidhar Reddy <
> shashidharreddy...@gmail.com> wrote:
>
>> Actually I was using the below command to check the compatibility, it
>> worked without any issue with 12.6 but it is not working with 1version 2.14
>> time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir
>> /usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin
>> --old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2 --link
>> --check
>>
>
> Just in case I'm _not_ misreading this... are you saying you already ran
> this command once with postgres v12.6?
>
> If so, since you've used --link the original folder will no longer be
> version12 data.
>

No, I'm an idiot. Ignore me, I missed "--check".

Geoff

Is there psql cluster or replication

[Vince McMahon]

Is there cluster on postgres?  Or, there is replication, not cluster.

If there is active/passive cluster or active/active , may I have the steps
to configure them?

Re: Is there psql cluster or replication

[Ian Lawrence Barwick]

2023年3月22日(水) 8:47 Vince McMahon :
>
> Is there cluster on postgres?  Or, there is replication, not cluster.
>
> If there is active/passive cluster or active/active , may I have the steps to 
> configure them?

See here for details about PostgreSQL's built-in streaming replication.

https://www.postgresql.org/docs/current/high-availability.html

Regards

Ian Barwick

RE: Logical replication fails when adding multiple replicas

[houzj.f...@fujitsu.com]

On Monday, March 20, 2023 8:46 PM Will Roper   
wrote:

Hi,

> We’re having some issues with Postgresql’s logical replication. Specifically
> trying to add several replicas at once. Essentially we can add replicas one at
> a time, but when we try and add two or more together some of the table
> subscriptions (as described in pg_subscription_rel) fail to get to
> ‘STATE_READY’ (‘r’). Here’s a bit more detail.
> 
> How the problem manifests/what we observe When we try to add several replicas
> at the same time most of them have at least some tables/subscriptions (in the
> pg_subscription_rel) that get stuck in STATE_FINISHEDCOPY and never reach
> STATE_READY. The tables have the right number of rows, but the logs show
> postgres is in a constant cycle of errors like: 
> 
> 2023-03-18 16:00:19.974 UTC [4207] LOG:  logical replication table 
> synchronization worker for subscription "polling_stations_01c7ee00f17021f94", 
> table "uk_geo_utils_onspd" has started
> 2023-03-18 16:00:19.999 UTC [4207] ERROR:  could not start WAL streaming: 
> ERROR:  replication slot "pg_37982_sync_37918_7210774007126708177" does not 
> exist

Are there some other kind of ERROR messages on subscriber before the first 
"slot ... does
not exist" ? I think it's possible that some other ERROR happened when first 
time
setting the state to READY, and then the table sync keeps retrying and failing.

Best Regards,
Hou zj

Re: Oracle to PostgreSQL Migration

[paramjib baruah]

EDB has migration toolkit , which is very helpful in migration from Oracle
to EDB.

Thanks and Regards
Paramjib Baruah

On Tue, Mar 21, 2023 at 9:12 PM Marc Millas  wrote:

> EDB do have a replication server which can be used to transfer real time
> data from oracle to postgres.
> don't know if it can be used to get to "no downtime"
> BTW what do you call "no downtime" as anyway a switch, as fast as it can
> be do take ""some"" time ?
>
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
>
>
> On Mon, Mar 20, 2023 at 1:58 PM Inzamam Shafiq 
> wrote:
>
>> Hi,
>>
>> Hope everyone is fine.
>>
>> Can someone help or guide regarding Open Source tools for Oracle to
>> PostgreSQL migration with real time CDC. along with this is there any
>> possibility to change the structure of the database? Let me explain a
>> little more,
>>
>> We have an Oracle DB which is around 1TB and we want to migrate to
>> PostgreSQL that have a new table structure, so we want to perform data
>> transformation and real time CDC from Oracle to PostgreSQL. Do we have any
>> good open source tool to achieve this with No Coding involved.??
>>
>> Thanks.
>>
>> Regards,
>>
>> *Inzamam Shafiq*
>> *Sr. DBA*
>>
>