public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rob Sargent <[email protected]>
To: [email protected]
Subject: Re: Option on `postgres` CLI to shutdown when there are no more active connections?
Date: Sun, 12 Oct 2025 20:00:46 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPR5_5q2oCkw19rHb7oy-M0M0purWFM5Jc8ub5xDy_NdhUmRTw@mail.gmail.com>
References: <CAPR5_5psNqCm1OQr5LL4v6z02Sh=2YHYUg8NY+3xMzfGHfKs+w@mail.gmail.com>
<[email protected]>
<CAPR5_5q2oCkw19rHb7oy-M0M0purWFM5Jc8ub5xDy_NdhUmRTw@mail.gmail.com>
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.
view thread (11+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Option on `postgres` CLI to shutdown when there are no more active connections?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox