Обсуждение: Option on `postgres` CLI to shutdown when there are no more active connections?

On 10/8/25 12:39, David Barsky wrote:
> Hiya folks,
> 
> I'm a bit of a newcomer when it comes to PostgreSQL, so I apologize if 
> this is
> the wrong mailing list. Anyways, my two questions:
> 
> 1. Is there any interest in adding a command line option to the 
> `postgres` CLI
>     that shuts down the PostgreSQL instance once (and optionally cleans 
> up the
>     data directory) once all connections have disconnected?

https://www.postgresql.org/docs/current/app-pg-ctl.html

"
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.
"


>     a. Alternatively, I wouldn't mind accomplishing this via the single-user
>        mode if it could accept the binary/wire protocol in addition to the
>        current text protocol.
> 2. Are there plans for having any additional table access methods beyond 
> `HEAP`
>     shipping as part of Postgres? I'd love to have something that's purely
>     in-memory to bypass the tempdir dance that I'm currently doing.
> For context, I'm trying to make it easier to test our application against a
> live, actual PostgreSQL instance and make the experience feel a lot like
> sqlite's embedded/in-memory workflow. Today, we've gotten really great

Postgres is not an embedded database, if you want that experience then 
use a database that is designed to be embedded.

> latencies via test transactions, but I'd also like to ensure that there 
> aren't
> any orphaned Postgres processes at the end of a test run or without 
> requiring
> the user to start an instance of Postgres prior to running the tests.
> 
> Warmest regards,
> David


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/11/25 19:56, Ron Johnson wrote:
> On Sat, Oct 11, 2025 at 7:11 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 

> 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.

 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

> 
> We in the shell scripting daemon world don't think like that.
> 
> -- 
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!


-- 
Adrian Klaver
adrian.klaver@aklaver.com

> 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)

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.

Best,

David

Rob Sargent <robjsargent@gmail.com> writes:
> On 10/12/25 16:10, David Barsky wrote:
>>> 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.

> 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.

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

On Mon, Oct 13, 2025 at 4:16 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rob Sargent <robjsargent@gmail.com> writes:
> > On 10/12/25 16:10, David Barsky wrote:
> >>> 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.
>
>> 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.
>
> 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).

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

On Mon, Oct 13, 2025 at 5:08 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Mon, Oct 13, 2025 at 4:47 AM Dominique Devienne <ddevienne@gmail.com> wrote:
> [snip]
>> 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),
> I'd bet a nickel that local socket transfers all stay in memory.

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

>> 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.

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.

Dominique Devienne <ddevienne@gmail.com> writes:
> On Mon, Oct 13, 2025 at 5:08 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>> 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 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

On Mon, Oct 13, 2025 at 5:37 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dominique Devienne <ddevienne@gmail.com> writes:
> > On Mon, Oct 13, 2025 at 5:08 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> >> 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 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.

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.

> > SQLite is also ACID.
>
> I guess they have a different set of assumptions about what that
> buzzword means.

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.

On October 14, 2025 9:40:45 PM UTC, David Barsky <me@davidbarsky.com> wrote:
>> If testing is all scripted, then why not put "pg_ctl stop" at the end of
>the script?
>
>Sorry for the delay.
>
>It’s _mostly_ scripted, but two major reasons:
>
>1. If that script is cancelled or interrupted for any reason, it’s possible
>that
>   `pg_ctl stop` won't be called and I'd have a leaked process.

bash has EXIT trap you can use to run functions even in the case of interrupts; You can create a wrapper script if its
notwritten in bash. 

 I could
>mitigate
>   this by calling `pg_ctl stop` at the *start* of the script, but that
>adds a
>   bit of latency I'd prefer to avoid.

You could also run pg_ctl stop in the background (i.e. in another process). Again, if using bash, you just add & at the
end.It should be possible to create processes in any scripting language. 

14.10.2025 23:49:21 David Barsky <me@davidbarsky.com>:

> 1. If that script is cancelled or interrupted for any reason, it’s possible that
>    `pg_ctl stop` won't be called and I'd have a leaked process.

As others stated, some shells have the trapping functionality.

> I could mitigate
>    this by calling `pg_ctl stop` at the *start* of the script, but that adds a
>    bit of latency I'd prefer to avoid.

I wonder what the reason is to avoid that latency in the script. Spinning down the DB will take time anyway.

Would it be possible to make a procedure run at the start of the DB, like a daemon, checking for the last connection to
quitif there has been one, and to shutdown the DB in an ordered manner? 

Just my two Rappen

On Mon, Oct 13, 2025 at 9:19 PM David Barsky <me@davidbarsky.com> wrote:
> > 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.
>
> 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)

Greg Sabino Mullane <htamfids@gmail.com> writes:
> On Mon, Oct 13, 2025 at 3:19 PM David Barsky <me@davidbarsky.com> wrote:
>> 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?

> 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.

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

            regards, tom lane