Re: is pg_stat_activity "transactional"? How fast does it update?

[Dominique Devienne]

On Tue, Jun 10, 2025 at 5:46 PM Dominique Devienne  wrote:
> On Tue, Jun 10, 2025 at 4:27 PM Tom Lane  wrote:
> > PQfinish is asynchronous: [...]
> > [...] pg_stat_activity() takes a snapshot of the view's contents
> Many thanks Tom. Didn't know either of those facts.

Hi. I ended up polling pg_stat_activity to wait for the disappearance
of the backend, to avoid the race in my unit tests. Initially
pg_stat_clear_snapshot() seemed to help, but then it didn't. Not
ideal, but I don't see any solution (work-around, really). There's a
new LOGIN event trigger, but no symmetrical FINISH one, and those are
restricted to privileged users, so useless in my case. Oh well. --DD

Re: Logical Replication Memory Allocation Error - "invalid memory alloc request size"

[Max Madden]

Hi Hayato,

Thank you for your reply.

We have rewritten as many of our transactions as possible to avoid using
temporary tables, and so far, that seems to have resolved the problem.

Thank you for your help.

Many thanks,

Max

On Wed, Jun 11, 2025 at 3:31 AM Hayato Kuroda (Fujitsu) <
kuroda.hay...@fujitsu.com> wrote:

> Dear Max,
>
> Thanks for the report.
>
> > The initial snapshot and data copy complete successfully for all tables.
> However, anywhere from 5
> > minutes to 2 hours after the initial sync, the subscription consistently
> fails with memory allocation errors like:
> >
> > ```
> > 2025-06-10 14:14:56.800 UTC [299] ERROR: could not receive data from WAL
> stream: ERROR: invalid memory alloc request size 1238451248
> > 2025-06-10 14:14:56.805 UTC [1] LOG: background worker "logical
> replication worker" (PID 299) exited with exit code 1
> > ```
>
> I think this is a known postgres bug which has been also reported at [1].
> We are discussing
> how we fix. Typically this can happen when there are lots of concurrent
> transactions
> and they have DDLs. IIUC there are no good workaround for now - any
> parameters can't
> avoid the failure. Only you can reduce them.
>
> I'm happy if you apply the patch posted at [1] and confirms the issue can
> be solved, but...
> seems difficult because you are in the managed env.
>
> [1]:
> https://www.postgresql.org/message-id/CALDaNm0TaTPuza7Fa%2BDRMzL%2BmqK3%2B7RVEvFiRoDJbU2vkJESwg%40mail.gmail.com
>
> Best regards,
> Hayato Kuroda
> FUJITSU LIMITED
>
>

Re: pg_upgradecluster version 10 to 16 question

[Adrian Klaver]

On 6/11/25 12:53, Bruce Momjian wrote:

On Wed, Jun 11, 2025 at 11:59:24AM -0700, Jim Cunning wrote:

I configured the PGDG repository and tried to install postgresql-10 on my
kubuntu 24.04 LTS desktop system, but apt refused to install it because of
unsatisfied dependencies.  I then installed kubuntu 22.04 LTS on a backup
system and was able to install postgresql-10 there.  I was able to use pg_dump
to recover all the data and move it to the replacement server running ubuntu
24.04 LTS with postgresql-16.  All should be good.  Thanks for the help.


Uh, newer vesions of pg_dump should be able to dump from a PG 10
cluster.



The issue is the OP just had the $DATADIR and no Postgres 10 binaries to 
run an instance.


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

Re: pg_upgradecluster version 10 to 16 question

[Adrian Klaver]

On 6/11/25 11:59, Jim Cunning wrote:
I configured the PGDG repository and tried to install postgresql-10 on 
my kubuntu 24.04 LTS desktop system, but apt refused to install it 
because of unsatisfied dependencies.  I then installed kubuntu 22.04 LTS 


Out of curiosity what where the unsatisfied dependencies?


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

Re: pg_upgradecluster version 10 to 16 question

[Jim Cunning]

  
  
I have lost the console output of the installation attempt, but I
believe there were two unsatisfied dependencies.  One I think was a
glibc version mismatch, but I don't recall the version numbers.  I
was in a hurry to find an installation solution, so I didn't spend
much time investigating the problem, and just moved on to an earlier
linux version that met my needs.

On 6/11/25 12:10, Adrian Klaver wrote:

On
  6/11/25 11:59, Jim Cunning wrote:
  
  I configured the PGDG repository and tried
to install postgresql-10 on my kubuntu 24.04 LTS desktop system,
but apt refused to install it because of unsatisfied
dependencies.  I then installed kubuntu 22.04 LTS 
  
  Out of curiosity what where the unsatisfied dependencies?
  
  
  


  

Re: pg_upgradecluster version 10 to 16 question

[Adrian Klaver]

On 6/11/25 13:07, David G. Johnston wrote:
On Wednesday, June 11, 2025, Bruce Momjian > wrote:


On Wed, Jun 11, 2025 at 11:59:24AM -0700, Jim Cunning wrote:
 > I configured the PGDG repository and tried to install
postgresql-10 on my
 > kubuntu 24.04 LTS desktop system, but apt refused to install it
because of
 > unsatisfied dependencies.  I then installed kubuntu 22.04 LTS on
a backup
 > system and was able to install postgresql-10 there.  I was able
to use pg_dump
 > to recover all the data and move it to the replacement server
running ubuntu
 > 24.04 LTS with postgresql-16.  All should be good.  Thanks for
the help.

Uh, newer vesions of pg_dump should be able to dump from a PG 10
cluster.


A running cluster, yes.  Apparently the PGDG packaging for PG10 is 
incompatible with Kubuntu 24.04 thus no running cluster.  It worked in 
22.04.  Given that PG10 was obsolete when 24.04 came out this isn’t a 
shock to me.


That is what is confusing me.

This:

https://apt.postgresql.org/pub/repos/apt/dists/noble-pgdg/10/

indicates it is at least expected to install.



David J.



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

Re: pg_upgradecluster version 10 to 16 question

[Jim Cunning]

  
  
I configured the PGDG repository and tried to install postgresql-10
on my kubuntu 24.04 LTS desktop system, but apt refused to install
it because of unsatisfied dependencies.  I then installed kubuntu
22.04 LTS on a backup system and was able to install postgresql-10
there.  I was able to use pg_dump to recover all the data and move
it to the replacement server running ubuntu 24.04 LTS with
postgresql-16.  All should be good.  Thanks for the help.

On 6/10/25 13:17, Adrian Klaver wrote:

On
  6/10/25 13:14, Adrian Klaver wrote:
  
  On 6/10/25 13:01, Jim Cunning wrote:

I had a Ubuntu 20.04 LTS system with a
  postgresql version 10 database, and the system became
  unbootable.  The database was stored in
  /var/lib/postgresql/10/main and I was able to recover the
  entire contents of the data in that directory and below.  I
  now have a system running ubuntu 24.04 LTS with postgresql
  version 16.
  
  
  I obviously no longer have a running version 10 server, so I
  cannot use pg_dump to retrieve the data.  I have placed the
  recovered version files in /var/lib/postgresql/10/main on my
  new system, alongside the /var/lib/postgresql/16/main that was
  created when I installed postgresql.  I have tried several
  iterations of the pg_upgradecluster command, a Python script
  provided by Ubuntu, but it says it cannot find the database
  files.
  
  
  I also see from the postgresql wiki pages that there is a
  pg_upgrade utility program, but cannot find in any of the
  Ubuntu-provided installation files.  Can anyone provide some
  guidance on how I might proceed?
  


1) Do:


man pg_upgradecluster


2) Assuming you used the PGDG repos to install Postgres you can
do:


sudo apt install postgresql-10


to get a Postgres 10 install. I would move the
/var/lib/postgresql/10/main you created out of the way and then
move the contents back in to the installed version.

  
  
  Forgot to say, be sure and retain a copy of the original 10
  $DATADIR somewhere where you can fetch it should the above not
  work as planned.
  
  
  


  
  Thanks in advance.
  
  


  
  


  

Re: pg_upgradecluster version 10 to 16 question

[David G. Johnston]

On Wednesday, June 11, 2025, Bruce Momjian  wrote:

> On Wed, Jun 11, 2025 at 11:59:24AM -0700, Jim Cunning wrote:
> > I configured the PGDG repository and tried to install postgresql-10 on my
> > kubuntu 24.04 LTS desktop system, but apt refused to install it because
> of
> > unsatisfied dependencies.  I then installed kubuntu 22.04 LTS on a backup
> > system and was able to install postgresql-10 there.  I was able to use
> pg_dump
> > to recover all the data and move it to the replacement server running
> ubuntu
> > 24.04 LTS with postgresql-16.  All should be good.  Thanks for the help.
>
> Uh, newer vesions of pg_dump should be able to dump from a PG 10
> cluster.
>
>
A running cluster, yes.  Apparently the PGDG packaging for PG10 is
incompatible with Kubuntu 24.04 thus no running cluster.  It worked in
22.04.  Given that PG10 was obsolete when 24.04 came out this isn’t a shock
to me.

David J.

Re: pg_upgradecluster version 10 to 16 question

[Bruce Momjian]

On Wed, Jun 11, 2025 at 11:59:24AM -0700, Jim Cunning wrote:
> I configured the PGDG repository and tried to install postgresql-10 on my
> kubuntu 24.04 LTS desktop system, but apt refused to install it because of
> unsatisfied dependencies.  I then installed kubuntu 22.04 LTS on a backup
> system and was able to install postgresql-10 there.  I was able to use pg_dump
> to recover all the data and move it to the replacement server running ubuntu
> 24.04 LTS with postgresql-16.  All should be good.  Thanks for the help.

Uh, newer vesions of pg_dump should be able to dump from a PG 10
cluster.

---

> 
> On 6/10/25 13:17, Adrian Klaver wrote:
> 
> On 6/10/25 13:14, Adrian Klaver wrote:
> 
> On 6/10/25 13:01, Jim Cunning wrote:
> 
> I had a Ubuntu 20.04 LTS system with a postgresql version 10
> database, and the system became unbootable.  The database was
> stored in /var/lib/postgresql/10/main and I was able to recover 
> the
> entire contents of the data in that directory and below.  I now
> have a system running ubuntu 24.04 LTS with postgresql version 16.
> 
> I obviously no longer have a running version 10 server, so I 
> cannot
> use pg_dump to retrieve the data.  I have placed the recovered
> version files in /var/lib/postgresql/10/main on my new system,
> alongside the /var/lib/postgresql/16/main that was created when I
> installed postgresql.  I have tried several iterations of the
> pg_upgradecluster command, a Python script provided by Ubuntu, but
> it says it cannot find the database files.
> 
> I also see from the postgresql wiki pages that there is a
> pg_upgrade utility program, but cannot find in any of the
> Ubuntu-provided installation files.  Can anyone provide some
> guidance on how I might proceed?
> 
> 
> 1) Do:
> 
> man pg_upgradecluster
> 
> 2) Assuming you used the PGDG repos to install Postgres you can do:
> 
> sudo apt install postgresql-10
> 
> to get a Postgres 10 install. I would move the /var/lib/postgresql/10/
> main you created out of the way and then move the contents back in to
> the installed version.
> 
> 
> Forgot to say, be sure and retain a copy of the original 10 $DATADIR
> somewhere where you can fetch it should the above not work as planned.
> 
> 
> 
> 
> 
> Thanks in advance.
> 
> 
> 
> 
> 
> 
> 

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

  Do not let urgent matters crowd out time for investment in the future.

Re: pg_upgradecluster version 10 to 16 question

[Bruce Momjian]

On Wed, Jun 11, 2025 at 01:07:11PM -0700, David G. Johnston wrote:
> On Wednesday, June 11, 2025, Bruce Momjian  wrote:
> 
> On Wed, Jun 11, 2025 at 11:59:24AM -0700, Jim Cunning wrote:
> > I configured the PGDG repository and tried to install postgresql-10 on 
> my
> > kubuntu 24.04 LTS desktop system, but apt refused to install it because
> of
> > unsatisfied dependencies.  I then installed kubuntu 22.04 LTS on a 
> backup
> > system and was able to install postgresql-10 there.  I was able to use
> pg_dump
> > to recover all the data and move it to the replacement server running
> ubuntu
> > 24.04 LTS with postgresql-16.  All should be good.  Thanks for the help.
> 
> Uh, newer vesions of pg_dump should be able to dump from a PG 10
> cluster.
> 
> 
> 
> A running cluster, yes.  Apparently the PGDG packaging for PG10 is 
> incompatible
> with Kubuntu 24.04 thus no running cluster.  It worked in 22.04.  Given that
> PG10 was obsolete when 24.04 came out this isn’t a shock to me.

Oh, wow, yeah, good point.

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

  Do not let urgent matters crowd out time for investment in the future.

RE: Logical Replication Memory Allocation Error - "invalid memory alloc request size"

[Hayato Kuroda (Fujitsu)]

Dear Max,

> We have rewritten as many of our transactions as possible to avoid using
> temporary tables, and so far, that seems to have resolved the problem.

Good to know. We try to fix as soon as possible.

Sorry for inconvenience.

Best regards,
Hayato Kuroda
FUJITSU LIMITED