Updated Fedora 40 and Fedora 41 RPM packages of Pgpool-II 4.5.5 in the repo

[Gerhard Wiesinger]

Hello,

Any chance to get Updated Fedora 40 and Fedora 41 RPM packages of 
Pgpool-II 4.5.5 in the repo quickly?


Thnx.

Ciao,
Gerhard

Qsn on Setting replication using " set session_replication_role = "

[Bharani SV-forum]

  
 TeamNeed help on clarification on using" set 
session_replication_role "

Assume i am loggging intopsql and issue\o output.lstset 
session_replication_role=defaultupdate coming out of \psql command and 
verifying log fileand assume i come out of the psql session without doing set 
session_replication_role=replica.
what will be the impact.How to find out if the session replicat role is in 
Replica stage or local ?  

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Bharani SV-forum]

 Team /Ron/Adrian
Wann to reconfirmwe have an setup with 
new server will be with 
will be following the following suggestion
On old VM [ existing server with OS "Amazon Linux release 2 (Karoo) " present 
in aws "us-east-1 region" and along with postgresql ver 13.16.2  - community 
edn ]

 - "take offline full backup (PG_DATA folder alone)  using OS command"

On new VM [OS "Amazon Linux 2023 " in aws region=us-east-1 and intended db as 
"postgresql 15.10 - community edn" ] 

 - "Restore offline full backup (PG_DATA folder alone) using OS command"
- create postgres unix userid- install postgresql ver 15.10 binaries- setup 
respective env variable to point correctly for PG_DATA
- will follow "pg_upgrade"

my question is a) is the above said steps is correct with the given existing 
and proposed setupb) is their any known issues using "cross over using 
pg_upgrade " option between the server's having below said operating system - 
source = existing server with OS = Amazon Linux release 2 (Karoo) " present in 
aws "us-east-1 region" and along with postgresql ver 13.16.2  - community edn 
vstarget - different server OS "Amazon Linux 2023 " in aws region=us-east-1 and 
intended db as "postgresql 15.10 - community edn"

On Tuesday, December 3, 2024 at 12:28:58 AM EST, Adrian Klaver 
 wrote:  
 
 On 12/2/24 17:23, Ron Johnson wrote:
> Adrian,
> 
> OP is moving to a new VM when migrating to PG 15.  When was the 
> "cross-server" feature added to pg_upgrade?
> 

Moving to a new VM was not the issue, my mistake was thinking the OS 
version was staying the same.

Then:

On old VM:

"take offline full backup (PG_DATA folder alone)  using OS command"

On new VM:
"Restore offline full backup (PG_DATA folder alone) using OS command"

Followed by installing new Postgres version could be dealt with using 
pg_upgrade. Once I was corrected on what was actually going on then 
doing a dump/restore or logical replication became better choices.


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



  

Re: Qsn on Setting replication using " set session_replication_role = "

[David G. Johnston]

On Wednesday, December 4, 2024, Bharani SV-forum 
wrote:

>
>
> Team
> Need help on clarification on using
> " set session_replication_role "
>
> Assume i am loggging into
> psql
> and issue
> \o output.lst
> set session_replication_role=default
> update 
>

This seems like a bad idea.  Why would you do such a thing?


> coming out of \psql command and verifying log file
> and assume i come out of the psql session
> without
> doing set session_replication_role=replica.
>

If you logged out, the session no longer exists so anything you did to
configure the session environment is no longer relevant.


>
> what will be the impact.
> How to find out if the session replicat role is in Replica stage or local ?
>

https://www.postgresql.org/docs/current/config-setting.html

David J.

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Ron Johnson]

On Wed, Dec 4, 2024 at 7:42 AM Bharani SV-forum 
wrote:

> Team /Ron/Adrian
>
> Wann to reconfirm
> we have an setup with
>
> new server will be with
>
> will be following the following suggestion
>
> *On old VM* [ existing server with OS "Amazon Linux release 2 (Karoo) "
> present in aws "us-east-1 region" and along with postgresql ver 13.16.2  -
> community edn ]
>
>  - "take offline full backup (PG_DATA folder alone)  using OS command"
>
> *On new VM [OS "Amazon Linux 2023 " in aws region=us-east-1 and intended
> db as "postgresql 15.10 - community edn" ] *
>
>  - "Restore offline full backup (PG_DATA folder alone) using OS command"
> - create postgres unix userid
> - install postgresql ver 15.10 binaries
> - setup respective env variable to point correctly for PG_DATA
> - will follow "pg_upgrade"
>
>
> my question is
> a) is the above said steps is correct with the given existing and proposed
> setup
>

No.  You're vastly overcomplicating things.


> b) is their any known issues using "cross over using pg_upgrade " option
> between the server's having below said operating system
>

There is no "cross over using pg_upgrade" because it does not exist.

When migrating from OldServer to NewServer, your options are:
A) pg_dump/pg_restore
B) Logical Replication


> *- source = existing server with OS = *Amazon Linux release 2 (Karoo) "
> present in aws "us-east-1 region" and along with postgresql ver 13.16.2  -
> community edn
> vs
> target - different server *OS "Amazon Linux 2023 " in aws
> region=us-east-1 and intended db as "postgresql 15.10 - community edn"*
>


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

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Greg Sabino Mullane]

On Wed, Dec 4, 2024 at 7:42 AM Bharani SV-forum 
wrote:

> a) is the above said steps is correct with the given existing and proposed
> setup
>

No. Here are some steps:

* Install Postgres on the new VM
However you get it, use the newest version you can. As of this writing, it
is Postgres 17.2. Version 15 is okay, but going to 17 now means a better
Postgres today, and no worrying about replacing v15 in three years.

* Create a new Postgres cluster
On the new VM, use the initdb command to create a new data directory.
Use the --data-checksums option

* Start it up
Adjust your postgresql.conf as needed
Adjust your pg_hba.conf as needed
Install any extensions used on the old VM
Start the cluster using the pg_ctl command (or systemctl)

* Test connection to the old vm from the new vm
On the new vm, see if you can connect to the old one:
psql -h oldvm -p 5432 --list
You may need to adjust firewalls and pg_hba.conf on the old vm.

* Copy the data
Run this on the new VM, adjusting ports as needed:
time pg_dumpall -h oldvm -p 5432 | psql -p 5432

Bonus points for doing this via screen/tmux to prevent interruptions

* Generate new statistics and vacuum
On the new vm, run:
psql -c 'vacuum freeze'
psql -c 'analyze'

* Test your application

* Setup all the other stuff (systemd integration, logrotate, cronjobs,
etc.) as needed

As Peter mentioned earlier, this can be done without disrupting anything,
and is easy to test and debug. The exact steps may vary a little, as I'm
not familiar with how Amazon Linux packages Postgres, but the basics are
the same.

Take it slow. Go through each of these steps one by one. If you get stuck
or run into an issue, stop and solve it, reaching out to this list as
necessary.

Cheers,
Greg

Seamless age (xid) replacement

[Ivan Shershnev]

Hello!

I need to use the 'age (xid)' function, but I have noticed that it is
deprecated without a clear alternative. I know that xid is also kinda
deprecated, so it makes sense not to use it. I can get xid8 from
'pg_current_xact_id()', which replaced 'txid_current()', but cannot use it
right away with 'age'.

I can cast xid8 that I've got to xid and pass to 'age', but 1) I have no
idea if it's the right way, i.e. it's promised to work or will work anyway
by accident, 2) 'age' is anyway deprecated.

I can re-implement 'age' by myself. It's (mostly) a subtraction after all.
But it would mean that I inline implementation in place of "api" function
call which is not always a great idea.

Could anyone advise, please?

Kind regards,
Ivan

Clarification of behaviour when dropping partitions

[Bolaji Wahab]

Hi team,

I have these two partitioned tables, with referential integrity. The tables
are structured in such a way that we have 1 to 1 mapping between their
partitions. This is achieved with a foreign key.

```
CREATE TABLE parent (
partition_date date NOT NULL,
id uuid NOT NULL,
external_transaction_id uuid NOT NULL,

CONSTRAINT parent_pkey
PRIMARY KEY (id, partition_date),

CONSTRAINT parent_external_transaction_id_key
UNIQUE (external_transaction_id, partition_date)
) PARTITION BY RANGE (partition_date);

CREATE TABLE parent_2024_12_01
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');

CREATE TABLE parent_2024_12_02
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');

CREATE TABLE parent_2024_12_03
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-03') TO ('2024-12-04');

CREATE TABLE parent_2024_12_04
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-04') TO ('2024-12-05');

CREATE TABLE parent_2024_12_05
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-05') TO ('2024-12-06');

CREATE TABLE child (
partition_date date NOT NULL,
transaction_id uuid NOT NULL,
keytext NOT NULL,
value  text NOT NULL,

CONSTRAINT child_pkey
PRIMARY KEY (transaction_id, key, partition_date),

CONSTRAINT child_transaction_id_fkey
FOREIGN KEY (transaction_id, partition_date)
REFERENCES parent (id, partition_date)
) PARTITION BY RANGE (partition_date);

CREATE TABLE child_2024_12_01
PARTITION OF child
FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');

CREATE TABLE child_2024_12_02
PARTITION OF public.child
FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');

CREATE TABLE child_2024_12_03
PARTITION OF public.child
FOR VALUES FROM ('2024-12-03') TO ('2024-12-04');

CREATE TABLE child_2024_12_04
PARTITION OF public.child
FOR VALUES FROM ('2024-12-04') TO ('2024-12-05');

CREATE TABLE child_2024_12_05
PARTITION OF public.child
FOR VALUES FROM ('2024-12-05') TO ('2024-12-06');
```

I have a scheduled job that removes old partitions with simply `DROP
TABLE`. It processes the child table first, then the parent table. For
example

First transaction (works fine and quick):

```
DROP TABLE child_2024_12_01;
```

Second transaction (slow, degrading performance):
Here, I had to detach the partition first because of the inherited
references

```
ALTER TABLE parent DETACH PARTITION parent_2024_12_01;
DROP TABLE parent_2024_12_01;
```

I noticed the job was taking a long time and affecting the performance of
the database.

After debugging, I found this query used internally by Postgres.
```
SELECT fk."transaction_id", fk."partition_date"
FROM "public"."child" fk
JOIN "public"."parent_2024_12_01" pk ON
(pk."id" OPERATOR(pg_catalog.=) fk."transaction_id" AND pk."partition_date"
OPERATOR(pg_catalog.=) fk."partition_date")
WHERE ((pk.partition_date IS NOT NULL)
AND (pk.partition_date >= '2024-12-01'::date) AND (pk.partition_date <
'2024-12-02'::date))
AND (fk."transaction_id" IS NOT NULL AND fk."partition_date" IS NOT NULL)
```
Which of course is not able to do partition pruning on the child table.
Wondering if this is somehow the expectation or an edge case. One would
have expected the optimiser to target the child partition with the
available foreign key.

Postgres Version: *14.10*

Thanks.

Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X

[Adrian Klaver]

On 12/4/24 04:42, Bharani SV-forum wrote:

Team /Ron/Adrian

Wann to reconfirm
we have an setup with

new server will be with

will be following the following suggestion

*On old VM* [ existing server with OS "Amazon Linux release 2 (Karoo) " 
present in aws "us-east-1 region" and along with postgresql ver 13.16.2  
- community edn ]


  - "take offline full backup (PG_DATA folder alone)  using OS command"

*On new VM [OS "Amazon Linux 2023 " in aws region=us-east-1 and intended 
db as "postgresql 15.10 - community edn" ] *


  - "Restore offline full backup (PG_DATA folder alone) using OS command"
- create postgres unix userid
- install postgresql ver 15.10 binaries
- setup respective env variable to point correctly for PG_DATA
- will follow "pg_upgrade"


That will not work as you would need an install of Postgres 13 on the 
new machine as well. And then there is the issue that the OS version 
changed as well. That would cause issues. Follow the process Greg Sabino 
Mullane posted.





my question is
a) is the above said steps is correct with the given existing and 
proposed setup
b) is their any known issues using "cross over using pg_upgrade " option 
between the server's having below said operating system
*- source = existing server with OS = *Amazon Linux release 2 (Karoo) " 
present in aws "us-east-1 region" and along with postgresql ver 13.16.2  
- community edn

vs
target - different server *OS "Amazon Linux 2023 " in aws 
region=us-east-1 and intended db as "postgresql 15.10 - community edn"*

*
*
*
*
On Tuesday, December 3, 2024 at 12:28:58 AM EST, Adrian Klaver 
 wrote:



On 12/2/24 17:23, Ron Johnson wrote:
 > Adrian,
 >
 > OP is moving to a new VM when migrating to PG 15.  When was the
 > "cross-server" feature added to pg_upgrade?
 >

Moving to a new VM was not the issue, my mistake was thinking the OS
version was staying the same.

Then:

On old VM:

"take offline full backup (PG_DATA folder alone)  using OS command"

On new VM:
"Restore offline full backup (PG_DATA folder alone) using OS command"

Followed by installing new Postgres version could be dealt with using
pg_upgrade. Once I was corrected on what was actually going on then
doing a dump/restore or logical replication became better choices.



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





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

Re: Clarification of behaviour when dropping partitions

[Laurenz Albe]

On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote:
> I have these two partitioned tables, with referential integrity. The tables
> are structured in such a way that we have 1 to 1 mapping between their
> partitions. This is achieved with a foreign key.
> 
> CREATE TABLE parent (
>     partition_date date NOT NULL,
>     id uuid NOT NULL,
>     external_transaction_id uuid NOT NULL,
> 
>     CONSTRAINT parent_pkey
>         PRIMARY KEY (id, partition_date),
> 
>     CONSTRAINT parent_external_transaction_id_key
>         UNIQUE (external_transaction_id, partition_date)
> ) PARTITION BY RANGE (partition_date);
> 
> CREATE TABLE parent_2024_12_01
>     PARTITION OF public.parent
>     FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
> 
> CREATE TABLE parent_2024_12_02
>     PARTITION OF public.parent
>     FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
> 
> [...]
> 
> CREATE TABLE child (
>     partition_date date NOT NULL,
>     transaction_id uuid NOT NULL,
>     key            text NOT NULL,
>     value          text NOT NULL,
> 
>     CONSTRAINT child_pkey
>         PRIMARY KEY (transaction_id, key, partition_date),
> 
>     CONSTRAINT child_transaction_id_fkey
>         FOREIGN KEY (transaction_id, partition_date)
>         REFERENCES parent (id, partition_date)
> ) PARTITION BY RANGE (partition_date);
> 
> CREATE TABLE child_2024_12_01
>     PARTITION OF child
>     FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
> 
> CREATE TABLE child_2024_12_02
>     PARTITION OF public.child
>     FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');

I recommend that you don't create the foreign key constraint between the
partitioned tables, but between the individual partitions.

That will make detaching and dropping partitions easier, and you will have
the same integrity guarantees.

Yours,
Laurenz Albe

Re: Updated Fedora 40 and Fedora 41 RPM packages of Pgpool-II 4.5.5 in the repo

[Christiano Anderson]

Hi,

I'm Fedora package maintainer (but not of the pgpool package). It's the 
package maintainer's responsibility to update the package in different 
Fedora versions (40, 41 and Rawhide).


There is already a ticket for this update: 
https://bugzilla.redhat.com/show_bug.cgi?id=2305049


Maybe it's a matter of time until the new version is available, but you 
can also contact the developer directly: 
https://src.fedoraproject.org/rpms/postgresql-pgpool-II


Best,

Christiano

On 05/12/2024 07:16, Gerhard Wiesinger wrote:

Hello,

Any chance to get Updated Fedora 40 and Fedora 41 RPM packages of 
Pgpool-II 4.5.5 in the repo quickly?


Thnx.

Ciao,
Gerhard

Re: Clarification of behaviour when dropping partitions

[Laurenz Albe]

On Wed, 2024-12-04 at 23:00 +0100, Bolaji Wahab wrote:
> On Wed, Dec 4, 2024 at 6:20 PM Laurenz Albe  wrote:
> > On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote:
> > > I have these two partitioned tables, with referential integrity. The 
> > > tables
> > > are structured in such a way that we have 1 to 1 mapping between their
> > > partitions. This is achieved with a foreign key.
> > 
> > I recommend that you don't create the foreign key constraint between the
> > partitioned tables, but between the individual partitions.
> > 
> > That will make detaching and dropping partitions easier, and you will have
> > the same integrity guarantees.
> 
> Yes, this is what I have done.
> But the whole point of declaring the foreign key constraint on the partitioned
> table is to have it automatically created on subsequent/future partitions.

Sure, but then you have to accept the disadvantage that it becomes more
difficult to detach partitions.  I think it is less pain to create the
constraint on the partition level.

Yours,
Laurenz Albe