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

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.