Subject: Re: Option on `postgres` CLI to shutdown when there are no more active connections?

From: Rob Sargent <[email-masked]>
Date: Sun, 12 Oct 2025 20:00:46 -0600

On 10/12/25 16:10, David Barsky wrote:
> > stop mode shuts down the server that is running in the specified data >
> > directory. Three different shutdown methods can be selected with the 
> -m >
> > option. “Smart” mode disallows new connections, then waits for all > 
> existing
> > clients to disconnect. If the server is in hot standby, > recovery and
> > streaming replication will be terminated once all clients > have 
> disconnected.
> > “Fast” mode (the default) does not wait for clients > to disconnect. All
> > active transactions are rolled back and clients are > forcibly 
> disconnected,
> > then the server is shut down. “Immediate” mode > will abort all server
> > processes immediately, without a clean shutdown. > This choice will 
> lead to a
> > crash-recovery cycle during the next server > start.
>
> Ah, I missed this, thanks! I'm still new to this and unsure when I 
> should use
> `postgres` vs. `pg_ctl`. I can probably hack something together with this!
>
> > Postgres is not an embedded database, if you want that experience then
> > use a database that is designed to be embedded.
>
> That's fair, especially from an operational standpoint. However, I _think_
> Postgres can get really close to an embedded database's development 
> experience
> by doing a few tricks that I'll elaborate on later on in this email.
>
> > > I think OP is looking for AUTO_CLOSE, like SQL Server (and Rdb/VMS 
> before
> > > it). Its only real utility is OP's use-case: a Windows desktop 
> running local
> > > testing.
> > > We in the shell scripting daemon world don't think like that.
> > > From the original post:
>
> > "Is there any interest in adding a command line option to the `postgres`
> > CLI"
> > Which I took to mean:
> >
> > https://www.postgresql.org/docs/current/app-postgres.html
>
> I think Ron's interpretation is correct, but I also don't mind using 
> `pg_ctl`!
> And yes, the thing I'm looking for looks pretty similar to SQL Server's
> `AUTO_CLOSE`.
>
> More concretely, the desiderata are (some are more flexible then others):
>
> 1. Our test runner runs each test as a standalone process. While it 
> can _setup_
>    a test environment atomically, it can't tear down a test environment
>    atomically. I think this is reasonable stance on the part of the 
> test runner
>    to encourage reliable test suites.
> 2. We started by using SQLite, which has the _really nice_ property of 
> being
>    able to function entirely in-memory. This means that when the test 
> completes,
>    cleanup of the entire database occurs due to the operating system
>    deallocating the test process' memory; no orphaned processes to 
> think about.
> 3. After someone installs all the tools that they need for their 
> development
>    environment (language toolchains, editor, database), they shouldn’t 
> need to
>    do any additional, ongoing maintenance. Having experienced a 
> workflow where
>    the entire build/test process is almost entirely self-contained, the
>    productivity benefits are massive and I really don’t want to go back.
>    1. There's an additional benefit here: we're able to unit test 
> against the
>       actual database we're running against in production with 
> complete fidelity
>       (some people might say that that these are really integration 
> tests, but
>       if each test completes in 0.02 milliseconds and scales to use 
> all cores on
>       my machine, I consider them to be _morally_ unit tests)
By "against the actual database..in production" do you mean the server 
type (e.g. postgres) or a verbatim data set?  I am assuming the former.  
Also assuming this isn't the application code hitting the server directly.
>
> I'm pretty sure I want the following behavior from Postgres (this is 
> the part I
> referred to above that would get Postgres pretty close to the development
> experience of an embedded database!):
>
> 1. On test, create or connect to an existing Postgres instance. Since 
> each test
>    is its own standalone process, I think something shaped like optimistic
>    locking to launch Postgres at a given port suffices. The operating 
> system
>    will complain if two processes are launched the same port and the 
> OS holding
>    the lock on the port should prevent any TOCTOU bugs.
> 2. Each test runs their own set of test transactions, which are 
> automatically
>    rolled back at the end of each test.
> 3. Postgres does some sort of connection-based reference counting 
> after the
>    first connection. Once all connections close and a short timeout window
>    passes (e.g., 100ms, but it should probably be configurable?) 
> Postgres shuts
>    down and cleans up any on-disk data.

"Testing" db interaction in a faked, circumscribed 
only-my-stuff-is-there world is folly.  Certainly each db developer 
needs their own instance of the database (on their own box or a 
server).  And it needs to be kept current with both DDL and domain meta 
data changes (see things like flyway) as regularly as is the source 
code.  It should have a decent representation of a production dataset 
else reads and writes will always be fast.  All the tests reading and 
writing all the columns of all the tables generates a lot of "green 
lights" but near zero practicable information in the developers' workflow.

> Best,
> David

Were I a betting man, I would bet heavily against this community, which 
prides itself on NOT losing data, allowing an option that would do just 
that.

---

From: Tom Lane <[email-masked]>
Date: Sun, 12 Oct 2025 22:16:09 -0400

> prides itself on NOT losing data, allowing an option that would do just 

Well, mumble ... we have any number of options that can be read that
way.  One obvious one is that we don't try to prevent you from putting
$PGDATA on a RAM disk.  Turning off fsync is another popular way to
trade away durability for speed.

But I concur with the point that we're not here to pretend to be an
embedded database, as there are other projects that do that better
(for example, our good friends at SQLite).

The advice I'd give the OP is to take a look at our TAP-test
infrastructure.  We've put a good deal of effort, and are continuing
to do so, into letting those tests spin up transitory testing
databases pretty cheaply.

			regards, tom lane

---

From: Dominique Devienne <[email-masked]>
Date: Mon, 13 Oct 2025 10:47:22 +0200

On Mon, Oct 13, 2025 at 4:16 AM Tom Lane <[email-masked]> wrote:
>>> experience by doing a few tricks that I'll elaborate on later on in this email.
>
>> prides itself on NOT losing data, allowing an option that would do just that.
>

Heavy user of SQLite here, and I have to disagree Tom.

The main reason PostgreSQL can't be embedded is because
of its process-based model with globals. And perhaps locking
as well. But otherwise it would a dream-come-true for single-user
mode of our app, and for testing as well, I have to agree with David.

Even a localhost-only mode that still functions as a normal cluster
except auth is entirely OS-based and it uses a random port
(or bypasses TCP entirely in a *CROSS*-platform way), based on
which pgdata-directory is used, would be OK.

There's apparently no way to abstract the "transport" between libpq
and the server, must be TCP (or *nix only socket files), cannot be an
in-memory channel (for the embedded non-shared case),
nor shared-memory (for the shared-case across localhost processes).

SQLite is fantastic, but it's type-system and lock-model are too restrictive,
for a general DB. Similar to David, I think PostgreSQL is close to my ideal
above, yet still far-enough (and perhaps unwilling enough, as a community)
to venture into embedded and localhost use-cases, that it's frustrating.

My $0.02. --DD

---

From: Ron Johnson <[email-masked]>
Date: Mon, 13 Oct 2025 11:07:53 -0400

On Mon, Oct 13, 2025 at 4:47 AM Dominique Devienne <[email-masked]>
wrote:
[snip]

> in-memory channel (for the embedded non-shared case),

I'd bet a nickel that local socket transfers all stay in memory.

> nor shared-memory (for the shared-case across localhost processes).

Shared memory means that I can stomp all over you, and you can't stop me.
That's the antithesis of ACID.

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

---

From: Dominique Devienne <[email-masked]>
Date: Mon, 13 Oct 2025 17:15:20 +0200

On Mon, Oct 13, 2025 at 5:08 PM Ron Johnson <[email-masked]> wrote:
> On Mon, Oct 13, 2025 at 4:47 AM Dominique Devienne <[email-masked]> wrote:
> [snip]

But that's not cross-platform... Needs to work the same on Windows.

> Shared memory means that I can stomp all over you, and you can't stop me.  That's the antithesis of ACID.

SHM is how SQLite in WAL mode coordinates access to the same DB from
several connections. So if it's good enough for SQLite, I don't see
what it would be wrong for PostgreSQL too. SQLite is also ACID.

---

From: Tom Lane <[email-masked]>
Date: Mon, 13 Oct 2025 11:37:09 -0400

> what it would be wrong for PostgreSQL too.

SQLite has accepted the cost that comes with being embedded, which is
that application-side memory-stomping bugs can destroy the database.
Postgres is not willing to make that tradeoff.  From a pure
developer's perspective, every time we got a bug report we'd have to
ask "did you observe this while running embedded?" and then demand a
repro that uses a non-embedded database.  We are not going to help
application authors debug their own bugs, especially not when we have
no visibility into what those are.

> SQLite is also ACID.

I guess they have a different set of assumptions about what that
buzzword means.

			regards, tom lane

---

From: Dominique Devienne <[email-masked]>
Date: Mon, 13 Oct 2025 18:43:15 +0200

> no visibility into what those are.

That's true for the embedded case, true.

There are some of those, on the ML (custom SQLite-based forum in
fact), but not that many in fact, far from it. So that concern does
exist, but maybe not to the extent one fears.

But not for the localhost case, which remains "client-server"
(multi-process). And SHM is then one of the options for the
"transport" between the libpq-based client, and the backends (running
on localhost). Unix Socket on Linux is almost perfect for the
localhost case, but again, is not portable. And I'd need simplified
authN, on the fly start if necessary, that kind of thing. Our apps are
multi-process themselves too, and each process can also be
multi-connection. In the localhost case, the data is private to you,
but can still be accessed concurrently across connections (from one or
more processes). And in that case, we shouldn't have to deal with
passwords, and everything should run as the OS user.

> I guess they have a different set of assumptions about what that

As you wrote, there are existing footguns one can turn on to weaken
ACID already. PostgreSQL is superior to SQLite in many ways. I get
that the embedded use-case is a step too far, for a long time, but the
localhost case, for testing but also for localhost private-data
serving (a possibly cache of a larger remote server) is much more
attainable. And valuable IMHO.

---

From: David Barsky <[email-masked]>
Date: Mon, 13 Oct 2025 12:19:04 -0700

> lights" but near zero practicable information in the developers' workflow.

This is not the extent of the testing we plan on using. In this email
thread,
I'm only referring to fast unit tests that ensure the correctness of the
application's logic and behavior. These are paired with the proper, hygienic
things you called out.

> Well, mumble ... we have any number of options that can be read that way.
One
> obvious one is that we don't try to prevent you from putting $PGDATA on a
RAM
> disk. Turning off fsync is another popular way to trade away durability
for
> speed.

We already do both for unit tests, which helps a bunch :).

> But I concur with the point that we're not here to pretend to be an
embedded
> database, as there are other projects that do that better (for example,
our
> good friends at SQLite).

> The advice I'd give the OP is to take a look at our TAP-test
infrastructure.
> We've put a good deal of effort, and are continuing to do so, into letting
> those tests spin up transitory testing databases pretty cheaply.

Thanks! I'll dig in. I'm guessing you're referring to these?
https://www.postgresql.org/docs/current/regress-tap.html

For what it's worth, I don't think Postgres _should_ be an embedded
database,
but I think there are some qualities of embedded databases that I would
love to see in Postgres: namely, the self-contained cleanup. Regardless,
I worry that me introducing SQLite into this discussion was a mistake and
hurt
the coherency of my request, so I apologize for that.

> to venture into embedded and localhost use-cases, that it's frustrating.

Yup, Dominique understands what I want: Postgres' type system, query
planner,
and locking model, but _shades_ of SQLite's operational properties during
local
development. However, I don't really need Postgres to function like an
embedded
database; I just want the self-contained process cleanup. Connecting to
Postgres
over TCP over localhost is perfect for my use-case: the difference in
performance for an in-process database (à la SQLite) vs. connecting over
localhost is the difference between single-digit microseconds and
single-digit
milliseconds. That difference matters in some cases, but not here: as far
as a
human running tests is concerned, both are instant. Here's someone at
CrunchyData/Snowflake providing an experience report of this exact workflow:

https://www.crunchydata.com/blog/dont-mock-the-database-data-fixtures-are-parallel-safe-and-plenty-fast

Anyways, I'll try to get at what motivated this whole discussion: would
there be
community opposition to adding a CLI flag that'd exit/shutdown all Postgres
processes once all pending connections close? E.g., something similar to SQL
Server's `auto_close` in the vein of `postgres
-c "auto_close_after=100"` or `pg-ctl start --exit-mode=smart`?

---

From: Dominique Devienne <[email-masked]>
Date: Wed, 15 Oct 2025 11:51:32 +0200

On Mon, Oct 13, 2025 at 9:19 PM David Barsky <[email-masked]> wrote:
> Yup, Dominique understands what I want: Postgres' type system, query planner,
> and locking model, but _shades_ of SQLite's operational properties during local
> development. However, I don't really need Postgres to function like an embedded
> database; I just want the self-contained process cleanup. Connecting to Postgres
> over TCP over localhost is perfect for my use-case

Except postgres is actively hostile to localhost
testing by not supporting ephemeral TCP ports...

Why oh why???
My own servers are unit tested that way.
Why would postgres.exe prevent the use of port 0?

Any work-around that picks a "free" port is racy and ugly.
There's a clean idiom for that, and that's port 0. Why disable it?

I don't get it... --DD

D:\pdgm\trunk\psc2\pgdata>where postgres.exe
D:\pdgm\kits\trunk\postgresql\17.6\Win_x64_10_v17\bin\postgres.exe

D:\pdgm\trunk\psc2\pgdata>where initdb.exe
D:\pdgm\kits\trunk\postgresql\17.6\Win_x64_10_v17\bin\initdb.exe

D:\pdgm\trunk\psc2\pgdata>initdb -D .\test1 -U postgres -A trust
The files belonging to this database system will be owned by user "ddevienne".
...
Success. You can now start the database server using:  pg_ctl -D
^"^.^\test1^" -l logfile start

D:\pdgm\trunk\psc2\pgdata>postgres -D .\test1 -p 0
2025-10-15 09:43:59.293 GMT [42288] FATAL:  0 is outside the valid
range for parameter "port" (1 .. 65535)

---

From: Greg Sabino Mullane <[email-masked]>
Date: Thu, 16 Oct 2025 18:18:57 -0400

On Mon, Oct 13, 2025 at 3:19 PM David Barsky <[email-masked]> wrote:

> there be community opposition to adding a CLI flag that'd exit/shutdown all
> Postgres
> processes once all pending connections close?
>

I don't know about opposition, per se, but the onus is on you to provide a
strong use case not already covered by existing tools. I don't think that
bar has been met yet.

--
Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

---

From: Tom Lane <[email-masked]>
Date: Thu, 16 Oct 2025 18:22:07 -0400

Greg Sabino Mullane <[email-masked]> writes:
>> Postgres processes once all pending connections close?

> strong use case not already covered by existing tools.

In particular, it's not terribly clear why the existing "smart"
shutdown mode isn't sufficient.

			regards, tom lane