Re: PostgreSQL 17.5 - could not map dynamic shared memory segment

[Aleš Zelený]

Hi,
Thanks for the good point:

$ sysctl vm.overcommit_memory
vm.overcommit_memory = 0

That is a difference, the old pg11 running on Ubuntu 18.4 had
disabled overcommit  (vm.overcommit_memory = 2).

Anyway, on a dedicated DB server box with 123GB RAM running only vacuum (14
parallel processes (2GB maintenance workmen)) and shared buffers 22GB seems
to me unlikely to hit available memory.

During Sunday (low load) and Monday so far, it has not reoccurred.

Kind regards Ales Zeleny

ne 22. 6. 2025 v 0:44 odesílatel Tomas Vondra  napsal:

> On 6/21/25 23:09, Aleš Zelený wrote:
> > Hello,
> > ...
> >
> > The application benefits from parallel queries, so despite the first
> > temptation to disable parallel queries (based on log entries correlation
> > only, but is that the root cause?) I did not want to disable parallel
> > queries, if there is another workaround/solution/fix available.
> >
> > Thanks for any hints on how to provide more information if needed, as
> > well as for fix/workaround advice.
> >
>
> Could it be that you simply ran out of memory, or perhaps hit the
> overcommit? What does sysctl say?
>
>   sysctl vm.overcommit_memory
>
> And what's CommitLimit/Committed_AS in /proc/meminfo? IIRC the shmem is
> counted against the limit, and if the system does not have significant
> swap, it's not uncommon to hit that (esp. with overcommit_memory=2).
>
>
> regards
>
> --
> Tomas Vondra
>
>

Re: Extension disappearing act

[Álvaro Herrera]

On 2025-Jun-19, Dominique Devienne wrote:

> Hi. Little mystery we don't understand. v17.
> 
> Create new DB, owned by dedicated new ROLE.
> Create extension (pgcrypto) in our case. Installed in public, owned by
> DB owner role.
> Create schemas and populate them inside the DB.

I would investigate this using an event trigger attached to the sql_drop
event.  That should allow you to identify exactly when the extension is
dropped.  Something like

CREATE OR REPLACE FUNCTION report_dropped()
RETURNS event_trigger
AS $$
DECLARE r record;
BEGIN
  FOR r IN SELECT * from pg_event_trigger_dropped_objects()
  LOOP
RAISE NOTICE
   'orig=% normal=% istemp=% type=% identity=% name=% args=%',
   r.original, r.normal, r.is_temporary, r.object_type,
   r.object_identity, r.address_names, r.address_args;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER svar_regress_event_trigger_report_dropped ON sql_drop
  EXECUTE PROCEDURE report_dropped();

-- 
Álvaro HerreraBreisgau, Deutschland  —  https://www.EnterpriseDB.com/
"En las profundidades de nuestro inconsciente hay una obsesiva necesidad
de un universo lógico y coherente. Pero el universo real se halla siempre
un paso más allá de la lógica" (Irulan)

password rules

[raphi]

Hello all,

I've been lurking for quite a while on the pg lists but now I need some 
help or rather, want to start a discussion:


We can set a password for a role in PG but there is no way to force a 
user to change it, prevent reuse or to enforce some complexity on it. As 
I understand, that's by choice and when I ask about this, the usual 
answer is "that's not the job of a database, use LDAP for it".


To be fair, setting up LDAP is very easy in PG, just one line in 
hba.conf and all is done. But sadly, that's only where the problems 
begin. The difficult part is to embedd this setup into a company, 
especially a large one as I work for with over 1000 PG databases and at 
least that many roles. Someone needs to be able to manage the passwords 
in LDAP and this means someone has to decide who can change which 
passwords, which is usually where some sort of Identity and Access 
Management (IAM) comes into place.


We already have LDAP and IAM in place in our organization for many other 
things, but IAM identities are coupled to a real person, not a team. 
Which means only one person in the team would be able to set a new 
password and when that person leaves the team, IAM rights need to be 
revoked and given to a new person. Doable, but quite a pane in the 
behind, especially when that one person happens to be on holidays. The 
prefered way would be to couple the rights to a dev-team-specific IAM 
role, which is something I am trying to get the okay for from our 
security for the past two years but failed so far (they argue it's a 
PCI/DSS requirement).


What I wish for are two seamingly simple features in PG that would solve 
all our problems without LDAP:


- enforce some password complexity and prevent reuse

- expire a password immediately after creating and prompt the user to 
change it upon first login try. They can connect with the initial 
password but cannot login until they've set a new password.


Background is: our developers can manage their own databases for their 
applications via a self service we've build for them. They can configure 
which databases and roles they need, our self service deployes 
everything, generates a password and sends the info to the dev via 
email. They idea would be, that the dev will change the password 
immediately but we cannot enforce that the dev will change the password, 
ever. And we also can't prevent the dev from setting "1234" as a 
password. With LDAP we could do all this but as stated above, it's not 
easy to implement (our "dev" is usually a team). We've reached a point 
where we (the dba team) are seriously discussing setting up our own LDAP 
server(s) without IAM, solely driven by our self service. But it will be 
tricky to find a setup without being a single point of failure for that 
many databases and get the okay for the resources needed to run and 
manage it, when we already have an "official" LDAP server.


I know there are extensions which are half-way there, like credcheck, 
but they suffer from the same drawback as most extensions, maintained 
only by a very small team or single person who after some years no 
longer has much time for it. Which is why we don't use any extension 
outside the official source code at all.


Sorry for this rather long (first) email on this list but I feel like I 
had to explain our usecase and why LDAP is not always as simple as 
adding a line to hba.conf. I understand the sentiment why some argue 
that this should not be the job of the DB but on the other hand, the DB 
already allows setting a password in the first place, hence why should 
it not be able to enforce some rules?


Is there any chance PG will provide this natively or are there any 
technical limitations I am unaware of? Can I do something to help 
bringing these feature into PG? My C knowledge is very limited so I 
won't be able to provide a patch but I'd be more than happy to test it. 
Also, I'll be at the Swiss PGday this week in Rapperswil if someone 
wants to discuss this in person ;)


have fun,

raphi

IPC/MultixactCreation on the Standby server

[Dmitry]

Hi,

The problem is as follows.
A replication cluster includes a primary server and one hot-standby replica.
The workload on the primary server is represented by multiple requests generating multixact IDs, while the hot-standby replica performs reading requests.

After some time, all requests on the hot-standby are stuck and never get finished.

The `pg_stat_activity` view on the replica reports that processes are stuck waiting for IPC/MultixactCreation,
pg_cancel_backend and pg_terminate_backend cannot cancel the request, SIGQUIT is the only way to stop it.

We tried:
* changing the `autovacuum_multixact_freeze_max_age` parameters,
* increasing `multixact_member_buffers` and `multixact_offset_buffers`,
* disabling `hot_standby_feedback`,
* switching the replica to synchronous and asynchronous mode,
* and much more.
But nothing helped.

We ran the replica in recovery mode from WAL archive, i.e. as warm-standby, the result is the same.

We tried to build from the sources based on REL_17_5 branch with the default configure settings
    ./configure
    make
    make install
But got no luck.

Here is an example with a synthetic workload reproducing the problem.

Test system
===

-   Architecture: x86_64
-   OS: Ubuntu 24.04.2 LTS (Noble Numbat)
-   Tested postgres version(s):
    -   latest 17 (17.5)
    -   latest 18 (18-beta1)

Steps to reproduce
==

    postgres=# create table tbl (
        id int primary key,
        val int
    );
    postgres=# insert into tbl select i, 0 from generate_series(1,5) i;


The first and second scripts execute queries on the master server
-

    pgbench --no-vacuum --report-per-command -M prepared -c 200 -j 200 -T 300 -P 1 --file=/dev/stdin <<'EOF'
    \set id random(1, 5)
    begin;
    select * from tbl where id = :id for key share;
    commit;
    EOF

    pgbench --no-vacuum --report-per-command -M prepared -c 100 -j 100 -T 300 -P 1 --file=/dev/stdin <<'EOF'
    \set id random(1, 5)
    begin;
    update tbl set val = val+1 where id = :id;
    \sleep 10 ms
    commit;
    EOF


The following script is executed on the replica
---

    pgbench --no-vacuum --report-per-command -M prepared -c 100 -j 100 -T 300 -P 1 --file=/dev/stdin <<'EOF'
    begin;
    select sum(val) from tbl;
    \sleep 10 ms
    select sum(val) from tbl;
    \sleep 10 ms
    commit;
    EOF

    pgbench (17.5 (Ubuntu 17.5-1.pgdg24.04+1))
    progress: 1.0 s, 2606.8 tps, lat 33.588 ms stddev 13.316, 0 failed
    progress: 2.0 s, 3315.0 tps, lat 30.174 ms stddev 5.933, 0 failed
    progress: 3.0 s, 3357.0 tps, lat 29.699 ms stddev 5.541, 0 failed
    progress: 4.0 s, 3350.0 tps, lat 29.911 ms stddev 5.311, 0 failed
    progress: 5.0 s, 3206.0 tps, lat 30.999 ms stddev 6.343, 0 failed
    progress: 6.0 s, 3264.0 tps, lat 30.828 ms stddev 6.389, 0 failed
    progress: 7.0 s, 3224.0 tps, lat 31.099 ms stddev 6.197, 0 failed
    progress: 8.0 s, 3168.0 tps, lat 31.486 ms stddev 6.940, 0 failed
    progress: 9.0 s, 3118.0 tps, lat 32.004 ms stddev 6.546, 0 failed
    progress: 10.0 s, 3017.0 tps, lat 33.183 ms stddev 7.971, 0 failed
    progress: 11.0 s, 3157.0 tps, lat 31.697 ms stddev 6.624, 0 failed
    progress: 12.0 s, 3180.0 tps, lat 31.415 ms stddev 6.310, 0 failed
    progress: 13.0 s, 3150.9 tps, lat 31.591 ms stddev 6.280, 0 failed
    progress: 14.0 s, 3329.0 tps, lat 30.189 ms stddev 5.792, 0 failed
    progress: 15.0 s, 3233.6 tps, lat 30.852 ms stddev 5.723, 0 failed
    progress: 16.0 s, 3185.4 tps, lat 31.378 ms stddev 6.383, 0 failed
    progress: 17.0 s, 3035.0 tps, lat 32.920 ms stddev 7.390, 0 failed
    progress: 18.0 s, 3173.0 tps, lat 31.547 ms stddev 6.390, 0 failed
    progress: 19.0 s, 3077.0 tps, lat 32.427 ms stddev 6.634, 0 failed
    progress: 20.0 s, 3266.1 tps, lat 30.740 ms stddev 5.842, 0 failed
    progress: 21.0 s, 2990.9 tps, lat 33.353 ms stddev 7.019, 0 failed
    progress: 22.0 s, 3048.1 tps, lat 32.933 ms stddev 6.951, 0 failed
    progress: 23.0 s, 3148.0 tps, lat 31.769 ms stddev 6.077, 0 failed
    progress: 24.0 s, 1523.2 tps, lat 30.029 ms stddev 5.093, 0 failed
    progress: 25.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
    progress: 26.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
    progress: 27.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
    progress: 28.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
    progress: 29.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
    progress: 30.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
    progress: 31.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
    progress: 32.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
    progress: 33.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
    progress: 34.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
    progress: 35.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed

After some time, all requests on the replica hang 

Re: password rules

[Tom Lane]

raphi  writes:
> We can set a password for a role in PG but there is no way to force a 
> user to change it, prevent reuse or to enforce some complexity on it. As 
> I understand, that's by choice and when I ask about this, the usual 
> answer is "that's not the job of a database, use LDAP for it".
> ...
> Is there any chance PG will provide this natively or are there any 
> technical limitations I am unaware of?

I think we have pretty much decided that that will never be part of
core Postgres.  If you don't like using an extension for it, you're
out of luck.  (The core developers have too much to do already, so
we are never going to be receptive to arguments like "I don't want
to use an extension".  But in this case the space of possible
requirements is so large that it doesn't make sense to try to build
a one-size-fits-all in-core solution.)

Extension or not, there are serious objections to many aspects of
such a feature, namely that they can't be enforced without requiring
clients to send cleartext passwords to the server.  That in itself
is a security problem.  For that matter, the whole business of using
passwords rather than other ID technologies (SSL certificates,
Kerberos/GSS tickets, etc) is feeling pretty twentieth-century.

regards, tom lane

postgres module in msvc

[C.]

Hi there and thx for reading and answering if you can

A few years ago, I wrote a pg module in C on gcc/linux. This module works fine, 
and I'm striving to port on windows postgres edb, I compiled the corresponding 
.dll wirh both mingw54 and msvc 2022, but got postgres crashes.
I also tried the recipe from 
https://www.enterprisedb.com/blog/compiling-postgresql-extensions-visual-studio-windows.
but no way.
Do you have a better available example ?
Thx again for reading

Re: postgres module in msvc

[Adrian Klaver]

On 6/23/25 12:32, C. wrote:

Hi there and thx for reading and answering if you can


A few years ago, I wrote a pg module in C on gcc/linux. This module 
works fine, and I'm striving to port on windows postgres edb, I compiled 
the corresponding .dll wirh both mingw54 and msvc 2022, but got postgres 
crashes.



Crashes with what errors?


I also tried the recipe from
https://www.enterprisedb.com/blog/compiling-postgresql-extensions-visual-studio-windows.
 


but no way.

Do you have a better available example ?

Thx again for reading



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

IPC/MultixactCreation on the Standby server

[Dmitry]

Hi, The problem is as follows.A replication cluster includes a primary server and one hot-standby replica.The workload on the primary server is represented by multiple requests generating multixact IDs, while the hot-standby replica performs reading requests. After some time, all requests on the hot-standby are stuck and never get finished. The `pg_stat_activity` view on the replica reports that processes are stuck waiting for IPC/MultixactCreation,pg_cancel_backend and pg_terminate_backend cannot cancel the request, SIGQUIT is the only way to stop it. We tried:* changing the `autovacuum_multixact_freeze_max_age` parameters,* increasing `multixact_member_buffers` and `multixact_offset_buffers`,* disabling `hot_standby_feedback`,* switching the replica to synchronous and asynchronous mode,* and much more.But nothing helped. We ran the replica in recovery mode from WAL archive, i.e. as warm-standby, the result is the same. We tried to build from the sources based on REL_17_5 branch with the default configure settings    ./configure    make    make installBut got no luck. Here is an example with a synthetic workload reproducing the problem. Test system=== -   Architecture: x86_64-   OS: Ubuntu 24.04.2 LTS (Noble Numbat)-   Tested postgres version(s):    -   latest 17 (17.5)    -   latest 18 (18-beta1) Steps to reproduce==     postgres=# create table tbl (        id int primary key,        val int    );    postgres=# insert into tbl select i, 0 from generate_series(1,5) i;  The first and second scripts execute queries on the master server-     pgbench --no-vacuum --report-per-command -M prepared -c 200 -j 200 -T 300 -P 1 --file=/dev/stdin <<'EOF'    \set id random(1, 5)    begin;    select * from tbl where id = :id for key share;    commit;    EOF     pgbench --no-vacuum --report-per-command -M prepared -c 100 -j 100 -T 300 -P 1 --file=/dev/stdin <<'EOF'    \set id random(1, 5)    begin;    update tbl set val = val+1 where id = :id;    \sleep 10 ms    commit;    EOF  The following script is executed on the replica---     pgbench --no-vacuum --report-per-command -M prepared -c 100 -j 100 -T 300 -P 1 --file=/dev/stdin <<'EOF'    begin;    select sum(val) from tbl;    \sleep 10 ms    select sum(val) from tbl;    \sleep 10 ms    commit;    EOF     pgbench (17.5 (Ubuntu 17.5-1.pgdg24.04+1))    progress: 1.0 s, 2606.8 tps, lat 33.588 ms stddev 13.316, 0 failed    progress: 2.0 s, 3315.0 tps, lat 30.174 ms stddev 5.933, 0 failed    progress: 3.0 s, 3357.0 tps, lat 29.699 ms stddev 5.541, 0 failed    progress: 4.0 s, 3350.0 tps, lat 29.911 ms stddev 5.311, 0 failed    progress: 5.0 s, 3206.0 tps, lat 30.999 ms stddev 6.343, 0 failed    progress: 6.0 s, 3264.0 tps, lat 30.828 ms stddev 6.389, 0 failed    progress: 7.0 s, 3224.0 tps, lat 31.099 ms stddev 6.197, 0 failed    progress: 8.0 s, 3168.0 tps, lat 31.486 ms stddev 6.940, 0 failed    progress: 9.0 s, 3118.0 tps, lat 32.004 ms stddev 6.546, 0 failed    progress: 10.0 s, 3017.0 tps, lat 33.183 ms stddev 7.971, 0 failed    progress: 11.0 s, 3157.0 tps, lat 31.697 ms stddev 6.624, 0 failed    progress: 12.0 s, 3180.0 tps, lat 31.415 ms stddev 6.310, 0 failed    progress: 13.0 s, 3150.9 tps, lat 31.591 ms stddev 6.280, 0 failed    progress: 14.0 s, 3329.0 tps, lat 30.189 ms stddev 5.792, 0 failed    progress: 15.0 s, 3233.6 tps, lat 30.852 ms stddev 5.723, 0 failed    progress: 16.0 s, 3185.4 tps, lat 31.378 ms stddev 6.383, 0 failed    progress: 17.0 s, 3035.0 tps, lat 32.920 ms stddev 7.390, 0 failed    progress: 18.0 s, 3173.0 tps, lat 31.547 ms stddev 6.390, 0 failed    progress: 19.0 s, 3077.0 tps, lat 32.427 ms stddev 6.634, 0 failed    progress: 20.0 s, 3266.1 tps, lat 30.740 ms stddev 5.842, 0 failed    progress: 21.0 s, 2990.9 tps, lat 33.353 ms stddev 7.019, 0 failed    progress: 22.0 s, 3048.1 tps, lat 32.933 ms stddev 6.951, 0 failed    progress: 23.0 s, 3148.0 tps, lat 31.769 ms stddev 6.077, 0 failed    progress: 24.0 s, 1523.2 tps, lat 30.029 ms stddev 5.093, 0 failed    progress: 25.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed    progress: 26.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed    progress: 27.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed    progress: 28.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed    progress: 29.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed    progress: 30.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed    progress: 31.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed    progress: 32.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed    progress: 33.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed    progress: 34.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed    progress: 35.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed After some time, all requests on the replica hang waiting for IPC/MultixactCreation. Output from `pg_stat_activity`-- 

Re: password rules

[raphi]

Am 23.06.2025 um 17:05 schrieb Tom Lane:

raphi  writes:

We can set a password for a role in PG but there is no way to force a
user to change it, prevent reuse or to enforce some complexity on it. As
I understand, that's by choice and when I ask about this, the usual
answer is "that's not the job of a database, use LDAP for it".
...
Is there any chance PG will provide this natively or are there any
technical limitations I am unaware of?

If you don't like using an extension for it, you're
out of luck.  (The core developers have too much to do already, so
we are never going to be receptive to arguments like "I don't want
to use an extension".
I'd be open to use an extension for this if there'd be one that is still 
maintained. The seemingly most popular one, credcheck, has an issue open 
for over a year, the password history is not being replicated to the 
standby so we can not use it. The other one, passwordpolicy, hasn't been 
updated in 6 years.

Extension or not, there are serious objections to many aspects of
such a feature, namely that they can't be enforced without requiring
clients to send cleartext passwords to the server.  That in itself
is a security problem.  For that matter, the whole business of using
passwords rather than other ID technologies (SSL certificates,
Kerberos/GSS tickets, etc) is feeling pretty twentieth-century.
We only allow encrypted connections as (hopefully) most do and don't log 
any passwords but I see your point. As said, it's an ongoing battle 
between what DBAs need and what is possible in our environment. After my 
discussion today with our security officer, LDAP will probably never be 
the solution for us because of IAM (which is why I wrote here). He 
mentioned a project for next year where they want to look into a Vault 
solution. It's still password authentication but with complexity, TTL 
and "hidden" from users.


As of now though we cannot use PG for any PCI/DSS certified application 
because we can't enforce either complexity nor regular password changes, 
which is required in PCI - and they are fine with using passwords per 
se, but with constraints. We can with other DB products, which is a 
pitty, it disqualifies PG automatically from the discussion for certain 
applications even when PG would be the better fit. I would've thought 
that this alone would put password handling back on the todo list, 
providing PCI compliance out-of-the-box without the need of additional 
infrastructure would be something to brag about :D Because 20th or 21th 
century, password authentication will probably be used for a long time, 
especially when it's still allowed by PCI and other industry standards.


One last thing, any chance that "valid until" could get a flag where DBA 
can choose if the user will have a chance to set a new password when it 
expires instead of just locking the account? So when it expires, the 
user can still connect but not login, they can only set a new password, 
idealy with some mechanism preventing the user from setting the same 
password as before (compare the input with the current one, e.g. login 
in the background with the pass and when it succeeds, ask the user to 
give a different password or something like that).


have fun,
raphi

Re: password rules

[Christoph Berg]

Re: raphi
> Sorry for this rather long (first) email on this list but I feel like I had
> to explain our usecase and why LDAP is not always as simple as adding a line
> to hba.conf.

Did you give the "pam" method a try? There are PAM modules for all
sorts of password checks.

Christoph

Re: password rules

[raphi]

Am 23.06.2025 um 22:39 schrieb Christoph Berg:

Re: raphi

Sorry for this rather long (first) email on this list but I feel like I had
to explain our usecase and why LDAP is not always as simple as adding a line
to hba.conf.

Did you give the "pam" method a try? T
Not really because it's a local solution. How do you change passwords or 
keep history on your standby nodes? Besides, the documentation says that 
postgres can't handle /etc/shadow because it runs unprivileged, only 
pam_ldap would work. Or am I missing something?


have fun,
raphi

Re: postgres module in msvc

[Adrian Klaver]

On 6/23/25 13:19, C. wrote:

Reply to list also.
Ccing list.


I don't know. The postgres kernel crashes, not the client
  app.


So does the Postgres log show anything?

On Monday, June 23, 2025 at 09:41:33 PM GMT+2, Adrian Klaver 
 wrote:



On 6/23/25 12:32, C. wrote:
 > Hi there and thx for reading and answering if you can
 >
 >
 > A few years ago, I wrote a pg module in C on gcc/linux. This module
 > works fine, and I'm striving to port on windows postgres edb, I compiled
 > the corresponding .dll wirh both mingw54 and msvc 2022, but got postgres
 > crashes.
 >
Crashes with what errors?

 > I also tried the recipe from
 > 
https://www.enterprisedb.com/blog/compiling-postgresql-extensions-visual-studio-windows. 


 >
 > but no way.
 >
 > Do you have a better available example ?
 >
 > Thx again for reading

 >

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




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