Re: Option on `postgres` CLI to shutdown when there are no more active connections? - Mailing list pgsql-general
| From | David Barsky |
|---|---|
| Subject | Re: Option on `postgres` CLI to shutdown when there are no more active connections? |
| Date | |
| Msg-id | CAPR5_5q2oCkw19rHb7oy-M0M0purWFM5Jc8ub5xDy_NdhUmRTw@mail.gmail.com Whole thread Raw |
| In response to | Re: Option on `postgres` CLI to shutdown when there are no more active connections? (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Option on `postgres` CLI to shutdown when there are no more active connections?
|
| List | pgsql-general |
> 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:
>
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.klaver@aklaver.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 ifthis isthe wrong mailing list. Anyways, my two questions:1. Is there any interest in adding a command line option to the`postgres` CLIthat shuts down the PostgreSQL instance once (and optionally cleansup thedata 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-usermode if it could accept the binary/wire protocol in addition to thecurrent 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 purelyin-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 alive, actual PostgreSQL instance and make the experience feel a lot likesqlite'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 therearen'tany orphaned Postgres processes at the end of a test run or withoutrequiringthe user to start an instance of Postgres prior to running the tests.Warmest regards,David
--
Adrian Klaver
adrian.klaver@aklaver.com
pgsql-general by date: