> 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 <[email protected]> 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 > [email protected] >
