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

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

I can probably write a proxy that does what I described above or do
something
with `pg_ctl`'s smart mode, but depending on the lift required for Postgres
to
implement this feature, it'd be nice to not have an additional moving part.
Anyways, I’m not a database expert (I’m more of a compilers person), but
I think what I described above should work, I hope?

> Yeah. Something like this, which we don't have:

> postgres -c "auto_close=on"

An API surface like that would be nice, but doesn’t need to be `postgres`.
I’m not picky.

Best,
David

On Oct 11, 2025 at 4:11:18 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(dot)klaver(at)aklaver(dot)com
>