Thread

1. Option on `postgres` CLI to shutdown when there are no more active connections?

   David Barsky <me@davidbarsky.com> — 2025-10-08T19:39:57Z

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

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

   Adrian Klaver <adrian.klaver@aklaver.com> — 2025-10-11T23:11:18Z

   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.klaver@aklaver.com
   
   
   
   

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

   Ron Johnson <ronljohnsonjr@gmail.com> — 2025-10-12T02:56:31Z

   On Sat, Oct 11, 2025 at 7:11 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 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.
   > "
   >
   
   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.
   
   -- 
   Death to <Redacted>, and butter sauce.
   Don't boil me, I'm still alive.
   <Redacted> lobster!
   

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

   Adrian Klaver <adrian.klaver@aklaver.com> — 2025-10-12T03:21:06Z

   On 10/11/25 19:56, Ron Johnson wrote:
   > On Sat, Oct 11, 2025 at 7:11 PM Adrian Klaver <adrian.klaver@aklaver.com 
   > <mailto:adrian.klaver@aklaver.com>> wrote:
   > 
   
   > 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.
   
    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
   
   > 
   > We in the shell scripting daemon world don't think like that.
   > 
   > -- 
   > Death to <Redacted>, and butter sauce.
   > Don't boil me, I'm still alive.
   > <Redacted> lobster!
   
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com
   
   
   
   

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

   Ron Johnson <ronljohnsonjr@gmail.com> — 2025-10-12T03:38:25Z

   On Sat, Oct 11, 2025 at 11:21 PM Adrian Klaver <adrian.klaver@aklaver.com>
   wrote:
   
   > On 10/11/25 19:56, Ron Johnson wrote:
   > > On Sat, Oct 11, 2025 at 7:11 PM Adrian Klaver <adrian.klaver@aklaver.com
   > > <mailto:adrian.klaver@aklaver.com>> wrote:
   > >
   >
   > > 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.
   >
   >  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
   
   
   Yeah.  Something like this, which we don't have:
   postgres -c "auto_close=on"
   
   -- 
   Death to <Redacted>, and butter sauce.
   Don't boil me, I'm still alive.
   <Redacted> lobster!
   

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

   David Barsky <me@davidbarsky.com> — 2025-10-12T22:10:24Z

    > 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.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 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.klaver@aklaver.com
   >
   

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

   Rob Sargent <robjsargent@gmail.com> — 2025-10-13T02:00:46Z

   
   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.
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-13T02:16:09Z

   Rob Sargent <robjsargent@gmail.com> writes:
   > On 10/12/25 16:10, David Barsky wrote:
   >>> 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.
   
   > 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.
   
   Well, mumble ... we have any number of options that can be read that
   way.  One obvious one is that we don't try to prevent you from putting
   $PGDATA on a RAM disk.  Turning off fsync is another popular way to
   trade away durability for speed.
   
   But I concur with the point that we're not here to pretend to be an
   embedded database, as there are other projects that do that better
   (for example, our good friends at SQLite).
   
   The advice I'd give the OP is to take a look at our TAP-test
   infrastructure.  We've put a good deal of effort, and are continuing
   to do so, into letting those tests spin up transitory testing
   databases pretty cheaply.
   
   			regards, tom lane
   
   
   
   

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

   Dominique Devienne <ddevienne@gmail.com> — 2025-10-13T08:47:22Z

   On Mon, Oct 13, 2025 at 4:16 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
   > Rob Sargent <robjsargent@gmail.com> writes:
   > > On 10/12/25 16:10, David Barsky wrote:
   > >>> 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.
   >
   >> 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.
   >
   > But I concur with the point that we're not here to pretend to be an
   > embedded database, as there are other projects that do that better
   > (for example, our good friends at SQLite).
   
   Heavy user of SQLite here, and I have to disagree Tom.
   
   The main reason PostgreSQL can't be embedded is because
   of its process-based model with globals. And perhaps locking
   as well. But otherwise it would a dream-come-true for single-user
   mode of our app, and for testing as well, I have to agree with David.
   
   Even a localhost-only mode that still functions as a normal cluster
   except auth is entirely OS-based and it uses a random port
   (or bypasses TCP entirely in a *CROSS*-platform way), based on
   which pgdata-directory is used, would be OK.
   
   There's apparently no way to abstract the "transport" between libpq
   and the server, must be TCP (or *nix only socket files), cannot be an
   in-memory channel (for the embedded non-shared case),
   nor shared-memory (for the shared-case across localhost processes).
   
   SQLite is fantastic, but it's type-system and lock-model are too restrictive,
   for a general DB. Similar to David, I think PostgreSQL is close to my ideal
   above, yet still far-enough (and perhaps unwilling enough, as a community)
   to venture into embedded and localhost use-cases, that it's frustrating.
   
   My $0.02. --DD
   
   
   
   

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

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-10-13T15:07:53Z

    On Mon, Oct 13, 2025 at 4:47 AM Dominique Devienne <ddevienne@gmail.com>
    wrote:
    [snip]
    
    > There's apparently no way to abstract the "transport" between libpq
    > and the server, must be TCP (or *nix only socket files), cannot be an
    > in-memory channel (for the embedded non-shared case),
    >
    
    I'd bet a nickel that local socket transfers all stay in memory.
    
    
    > nor shared-memory (for the shared-case across localhost processes).
    >
    
    Shared memory means that I can stomp all over you, and you can't stop me.
    That's the antithesis of ACID.
    
    -- 
    Death to <Redacted>, and butter sauce.
    Don't boil me, I'm still alive.
    <Redacted> lobster!
    

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

    Dominique Devienne <ddevienne@gmail.com> — 2025-10-13T15:15:20Z

    On Mon, Oct 13, 2025 at 5:08 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
    > On Mon, Oct 13, 2025 at 4:47 AM Dominique Devienne <ddevienne@gmail.com> wrote:
    > [snip]
    >> There's apparently no way to abstract the "transport" between libpq
    >> and the server, must be TCP (or *nix only socket files), cannot be an
    >> in-memory channel (for the embedded non-shared case),
    > I'd bet a nickel that local socket transfers all stay in memory.
    
    But that's not cross-platform... Needs to work the same on Windows.
    
    >> nor shared-memory (for the shared-case across localhost processes).
    > Shared memory means that I can stomp all over you, and you can't stop me.  That's the antithesis of ACID.
    
    SHM is how SQLite in WAL mode coordinates access to the same DB from
    several connections. So if it's good enough for SQLite, I don't see
    what it would be wrong for PostgreSQL too. SQLite is also ACID.
    
    
    
    

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-13T15:37:09Z

    Dominique Devienne <ddevienne@gmail.com> writes:
    > On Mon, Oct 13, 2025 at 5:08 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
    >> Shared memory means that I can stomp all over you, and you can't stop me.  That's the antithesis of ACID.
    
    > SHM is how SQLite in WAL mode coordinates access to the same DB from
    > several connections. So if it's good enough for SQLite, I don't see
    > what it would be wrong for PostgreSQL too.
    
    SQLite has accepted the cost that comes with being embedded, which is
    that application-side memory-stomping bugs can destroy the database.
    Postgres is not willing to make that tradeoff.  From a pure
    developer's perspective, every time we got a bug report we'd have to
    ask "did you observe this while running embedded?" and then demand a
    repro that uses a non-embedded database.  We are not going to help
    application authors debug their own bugs, especially not when we have
    no visibility into what those are.
    
    > SQLite is also ACID.
    
    I guess they have a different set of assumptions about what that
    buzzword means.
    
    			regards, tom lane
    
    
    
    

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

    Dominique Devienne <ddevienne@gmail.com> — 2025-10-13T16:43:15Z

    On Mon, Oct 13, 2025 at 5:37 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > Dominique Devienne <ddevienne@gmail.com> writes:
    > > On Mon, Oct 13, 2025 at 5:08 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
    > >> Shared memory means that I can stomp all over you, and you can't stop me.  That's the antithesis of ACID.
    >
    > > SHM is how SQLite in WAL mode coordinates access to the same DB from
    > > several connections. So if it's good enough for SQLite, I don't see
    > > what it would be wrong for PostgreSQL too.
    >
    > SQLite has accepted the cost that comes with being embedded, which is
    > that application-side memory-stomping bugs can destroy the database.
    > Postgres is not willing to make that tradeoff.  From a pure
    > developer's perspective, every time we got a bug report we'd have to
    > ask "did you observe this while running embedded?" and then demand a
    > repro that uses a non-embedded database.  We are not going to help
    > application authors debug their own bugs, especially not when we have
    > no visibility into what those are.
    
    That's true for the embedded case, true.
    
    There are some of those, on the ML (custom SQLite-based forum in
    fact), but not that many in fact, far from it. So that concern does
    exist, but maybe not to the extent one fears.
    
    But not for the localhost case, which remains "client-server"
    (multi-process). And SHM is then one of the options for the
    "transport" between the libpq-based client, and the backends (running
    on localhost). Unix Socket on Linux is almost perfect for the
    localhost case, but again, is not portable. And I'd need simplified
    authN, on the fly start if necessary, that kind of thing. Our apps are
    multi-process themselves too, and each process can also be
    multi-connection. In the localhost case, the data is private to you,
    but can still be accessed concurrently across connections (from one or
    more processes). And in that case, we shouldn't have to deal with
    passwords, and everything should run as the OS user.
    
    > > SQLite is also ACID.
    >
    > I guess they have a different set of assumptions about what that
    > buzzword means.
    
    As you wrote, there are existing footguns one can turn on to weaken
    ACID already. PostgreSQL is superior to SQLite in many ways. I get
    that the embedded use-case is a step too far, for a long time, but the
    localhost case, for testing but also for localhost private-data
    serving (a possibly cache of a larger remote server) is much more
    attainable. And valuable IMHO.
    
    
    
    

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

    David Barsky <me@davidbarsky.com> — 2025-10-13T19:19:04Z

    > "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.
    
    This is not the extent of the testing we plan on using. In this email
    thread,
    I'm only referring to fast unit tests that ensure the correctness of the
    application's logic and behavior. These are paired with the proper, hygienic
    things you called out.
    
    > > 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.
    > Well, mumble ... we have any number of options that can be read that way.
    One
    > obvious one is that we don't try to prevent you from putting $PGDATA on a
    RAM
    > disk. Turning off fsync is another popular way to trade away durability
    for
    > speed.
    
    We already do both for unit tests, which helps a bunch :).
    
    > But I concur with the point that we're not here to pretend to be an
    embedded
    > database, as there are other projects that do that better (for example,
    our
    > good friends at SQLite).
    
    > The advice I'd give the OP is to take a look at our TAP-test
    infrastructure.
    > We've put a good deal of effort, and are continuing to do so, into letting
    > those tests spin up transitory testing databases pretty cheaply.
    
    Thanks! I'll dig in. I'm guessing you're referring to these?
    https://www.postgresql.org/docs/current/regress-tap.html
    
    For what it's worth, I don't think Postgres _should_ be an embedded
    database,
    but I think there are some qualities of embedded databases that I would
    love to see in Postgres: namely, the self-contained cleanup. Regardless,
    I worry that me introducing SQLite into this discussion was a mistake and
    hurt
    the coherency of my request, so I apologize for that.
    
    > SQLite is fantastic, but it's type-system and lock-model are too
    restrictive,
    > for a general DB. Similar to David, I think PostgreSQL is close to my
    ideal
    > above, yet still far-enough (and perhaps unwilling enough, as a community)
    > to venture into embedded and localhost use-cases, that it's frustrating.
    
    Yup, Dominique understands what I want: Postgres' type system, query
    planner,
    and locking model, but _shades_ of SQLite's operational properties during
    local
    development. However, I don't really need Postgres to function like an
    embedded
    database; I just want the self-contained process cleanup. Connecting to
    Postgres
    over TCP over localhost is perfect for my use-case: the difference in
    performance for an in-process database (à la SQLite) vs. connecting over
    localhost is the difference between single-digit microseconds and
    single-digit
    milliseconds. That difference matters in some cases, but not here: as far
    as a
    human running tests is concerned, both are instant. Here's someone at
    CrunchyData/Snowflake providing an experience report of this exact workflow:
    
    https://www.crunchydata.com/blog/dont-mock-the-database-data-fixtures-are-parallel-safe-and-plenty-fast
    
    Anyways, I'll try to get at what motivated this whole discussion: would
    there be
    community opposition to adding a CLI flag that'd exit/shutdown all Postgres
    processes once all pending connections close? E.g., something similar to SQL
    Server's `auto_close` in the vein of `postgres
    -c "auto_close_after=100"` or `pg-ctl start --exit-mode=smart`?
    
    On Oct 13, 2025 at 9:43:15 AM, Dominique Devienne <ddevienne@gmail.com>
    wrote:
    
    > On Mon, Oct 13, 2025 at 5:37 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
    >
    > Dominique Devienne <ddevienne@gmail.com> writes:
    >
    > > On Mon, Oct 13, 2025 at 5:08 PM Ron Johnson <ronljohnsonjr@gmail.com>
    > wrote:
    >
    > >> Shared memory means that I can stomp all over you, and you can't stop
    > me.  That's the antithesis of ACID.
    >
    >
    > > SHM is how SQLite in WAL mode coordinates access to the same DB from
    >
    > > several connections. So if it's good enough for SQLite, I don't see
    >
    > > what it would be wrong for PostgreSQL too.
    >
    >
    > SQLite has accepted the cost that comes with being embedded, which is
    >
    > that application-side memory-stomping bugs can destroy the database.
    >
    > Postgres is not willing to make that tradeoff.  From a pure
    >
    > developer's perspective, every time we got a bug report we'd have to
    >
    > ask "did you observe this while running embedded?" and then demand a
    >
    > repro that uses a non-embedded database.  We are not going to help
    >
    > application authors debug their own bugs, especially not when we have
    >
    > no visibility into what those are.
    >
    >
    > That's true for the embedded case, true.
    >
    > There are some of those, on the ML (custom SQLite-based forum in
    > fact), but not that many in fact, far from it. So that concern does
    > exist, but maybe not to the extent one fears.
    >
    > But not for the localhost case, which remains "client-server"
    > (multi-process). And SHM is then one of the options for the
    > "transport" between the libpq-based client, and the backends (running
    > on localhost). Unix Socket on Linux is almost perfect for the
    > localhost case, but again, is not portable. And I'd need simplified
    > authN, on the fly start if necessary, that kind of thing. Our apps are
    > multi-process themselves too, and each process can also be
    > multi-connection. In the localhost case, the data is private to you,
    > but can still be accessed concurrently across connections (from one or
    > more processes). And in that case, we shouldn't have to deal with
    > passwords, and everything should run as the OS user.
    >
    > > SQLite is also ACID.
    >
    >
    > I guess they have a different set of assumptions about what that
    >
    > buzzword means.
    >
    >
    > As you wrote, there are existing footguns one can turn on to weaken
    > ACID already. PostgreSQL is superior to SQLite in many ways. I get
    > that the embedded use-case is a step too far, for a long time, but the
    > localhost case, for testing but also for localhost private-data
    > serving (a possibly cache of a larger remote server) is much more
    > attainable. And valuable IMHO.
    >
    >
    >
    

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

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-10-13T20:27:17Z

    On Mon, Oct 13, 2025 at 3:19 PM David Barsky <me@davidbarsky.com> wrote:
    [snip]
    
    > Anyways, I'll try to get at what motivated this whole discussion: would
    > there be
    > community opposition to adding a CLI flag that'd exit/shutdown all Postgres
    > processes once all pending connections close? E.g., something similar to
    > SQL
    > Server's `auto_close` in the vein of `postgres
    > -c "auto_close_after=100"` or `pg-ctl start --exit-mode=smart`?
    >
    
    If testing is all scripted, then why not put "pg_ctl stop" at the end of
    the script?
    
    -- 
    Death to <Redacted>, and butter sauce.
    Don't boil me, I'm still alive.
    <Redacted> lobster!
    

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

    David Barsky <me@davidbarsky.com> — 2025-10-14T21:40:45Z

    > If testing is all scripted, then why not put "pg_ctl stop" at the end of
    the script?
    
    Sorry for the delay.
    
    It’s _mostly_ scripted, but two major reasons:
    
    1. If that script is cancelled or interrupted for any reason, it’s possible
    that
       `pg_ctl stop` won't be called and I'd have a leaked process. I could
    mitigate
       this by calling `pg_ctl stop` at the *start* of the script, but that
    adds a
       bit of latency I'd prefer to avoid.
    2. It's a pain to hook that script up to our IDEs in a semi-centralized
    manner
       (I extended rust-analyzer's test runner to support non-standard build
    systems
       and I never really got non-standard build systems working with
    debuggers).
       Even if we eat the pain, the aforementioned latency coming from `pg_ctl
    stop`
       is a bit annoying.
       1. For context, rust-analyzer has a nice "runnables" feature that makes
    it
          possible to run a test directly from within an IDE. Other language
          servers/IDEs have similar functionality, but they're heavily biased
    to use
          language-idiomatic tools and make it a pain to override build
          tools/runnables. Besides, in my experience working on IDEs for a large
          tech company (and collecting a _lot_ of logs/telemetry...), it's
          _extremely_ rare for people to configure their editors: they're
          overwhelmingly stock.
    
    I'm also of the mind that there's some elegance to `pg_ctl start
    --exit-mode=smart`: no matter how the test script is interrupted or
    cancelled, I
    can run it again and guarantee that there will be *no* leaked processes or
    non-idempotency because the postmaster handles it. That said, looking over
    the
    `postmaster.c` source, it seems like there isn't any bookkeeping of children
    starting/exiting, so there isn't really any reference counting of
    connections
    there. I'd be happy to add it, but I _do not_ trust myself to write correct
    C!
    
    Anyways, I think this relatively small tweak can make a pretty meaningful
    impact
    in the end-developer experience of programming against Postgres, especially
    in
    setups that would like to minimize cross-language scripting/dependencies.
    However, I also understand that this isn't exactly how Postgres might be
    commonly used on these mailing lists.
    
    —David
    
    On Oct 13, 2025 at 1:27:17 PM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
    
    > On Mon, Oct 13, 2025 at 3:19 PM David Barsky <me@davidbarsky.com> wrote:
    > [snip]
    >
    >> Anyways, I'll try to get at what motivated this whole discussion: would
    >> there be
    >> community opposition to adding a CLI flag that'd exit/shutdown all
    >> Postgres
    >> processes once all pending connections close? E.g., something similar to
    >> SQL
    >> Server's `auto_close` in the vein of `postgres
    >> -c "auto_close_after=100"` or `pg-ctl start --exit-mode=smart`?
    >>
    >
    > If testing is all scripted, then why not put "pg_ctl stop" at the end of
    > the script?
    >
    > --
    > Death to <Redacted>, and butter sauce.
    > Don't boil me, I'm still alive.
    > <Redacted> lobster!
    >
    

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

    Bird <birdsite@airmail.cc> — 2025-10-15T02:42:01Z

    
    On October 14, 2025 9:40:45 PM UTC, David Barsky <me@davidbarsky.com> wrote:
    >> If testing is all scripted, then why not put "pg_ctl stop" at the end of
    >the script?
    >
    >Sorry for the delay.
    >
    >It’s _mostly_ scripted, but two major reasons:
    >
    >1. If that script is cancelled or interrupted for any reason, it’s possible
    >that
    >   `pg_ctl stop` won't be called and I'd have a leaked process.
    
    bash has EXIT trap you can use to run functions even in the case of interrupts; You can create a wrapper script if its not written in bash.
    
     I could
    >mitigate
    >   this by calling `pg_ctl stop` at the *start* of the script, but that
    >adds a
    >   bit of latency I'd prefer to avoid.
    
    You could also run pg_ctl stop in the background (i.e. in another process). Again, if using bash, you just add & at the end. It should be possible to create processes in any scripting language.
    
    
    
    

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

    Thiemo Kellner <thiemo@gelassene-pferde.biz> — 2025-10-15T05:47:34Z

    14.10.2025 23:49:21 David Barsky <me@davidbarsky.com>:
    
    > 1. If that script is cancelled or interrupted for any reason, it’s possible that
    >    `pg_ctl stop` won't be called and I'd have a leaked process.
    
    As others stated, some shells have the trapping functionality.
    
    > I could mitigate
    >    this by calling `pg_ctl stop` at the *start* of the script, but that adds a
    >    bit of latency I'd prefer to avoid.
    
    I wonder what the reason is to avoid that latency in the script. Spinning down the DB will take time anyway.
    
    Would it be possible to make a procedure run at the start of the DB, like a daemon, checking for the last connection to quit if there has been one, and to shutdown the DB in an ordered manner?
    
    Just my two Rappen
    
    
    
    

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

    Dominique Devienne <ddevienne@gmail.com> — 2025-10-15T09:51:32Z

    On Mon, Oct 13, 2025 at 9:19 PM David Barsky <me@davidbarsky.com> wrote:
    > > SQLite is fantastic, but it's type-system and lock-model are too restrictive,
    > > for a general DB. Similar to David, I think PostgreSQL is close to my ideal
    > > above, yet still far-enough (and perhaps unwilling enough, as a community)
    > > to venture into embedded and localhost use-cases, that it's frustrating.
    >
    > Yup, Dominique understands what I want: Postgres' type system, query planner,
    > and locking model, but _shades_ of SQLite's operational properties during local
    > development. However, I don't really need Postgres to function like an embedded
    > database; I just want the self-contained process cleanup. Connecting to Postgres
    > over TCP over localhost is perfect for my use-case
    
    Except postgres is actively hostile to localhost
    testing by not supporting ephemeral TCP ports...
    
    Why oh why???
    My own servers are unit tested that way.
    Why would postgres.exe prevent the use of port 0?
    
    Any work-around that picks a "free" port is racy and ugly.
    There's a clean idiom for that, and that's port 0. Why disable it?
    
    I don't get it... --DD
    
    D:\pdgm\trunk\psc2\pgdata>where postgres.exe
    D:\pdgm\kits\trunk\postgresql\17.6\Win_x64_10_v17\bin\postgres.exe
    
    D:\pdgm\trunk\psc2\pgdata>where initdb.exe
    D:\pdgm\kits\trunk\postgresql\17.6\Win_x64_10_v17\bin\initdb.exe
    
    D:\pdgm\trunk\psc2\pgdata>initdb -D .\test1 -U postgres -A trust
    The files belonging to this database system will be owned by user "ddevienne".
    ...
    Success. You can now start the database server using:  pg_ctl -D
    ^"^.^\test1^" -l logfile start
    
    D:\pdgm\trunk\psc2\pgdata>postgres -D .\test1 -p 0
    2025-10-15 09:43:59.293 GMT [42288] FATAL:  0 is outside the valid
    range for parameter "port" (1 .. 65535)
    
    
    
    

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

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-10-16T22:18:57Z

    On Mon, Oct 13, 2025 at 3:19 PM David Barsky <me@davidbarsky.com> wrote:
    
    > Anyways, I'll try to get at what motivated this whole discussion: would
    > there be community opposition to adding a CLI flag that'd exit/shutdown all
    > Postgres
    > processes once all pending connections close?
    >
    
    I don't know about opposition, per se, but the onus is on you to provide a
    strong use case not already covered by existing tools. I don't think that
    bar has been met yet.
    
    --
    Cheers,
    Greg
    
    --
    Crunchy Data - https://www.crunchydata.com
    Enterprise Postgres Software Products & Tech Support
    

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-16T22:22:07Z

    Greg Sabino Mullane <htamfids@gmail.com> writes:
    > On Mon, Oct 13, 2025 at 3:19 PM David Barsky <me@davidbarsky.com> wrote:
    >> Anyways, I'll try to get at what motivated this whole discussion: would
    >> there be community opposition to adding a CLI flag that'd exit/shutdown all
    >> Postgres processes once all pending connections close?
    
    > I don't know about opposition, per se, but the onus is on you to provide a
    > strong use case not already covered by existing tools.
    
    In particular, it's not terribly clear why the existing "smart"
    shutdown mode isn't sufficient.
    
    			regards, tom lane
    
    
    
    

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

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-10-17T04:53:45Z

    On Thu, Oct 16, 2025 at 6:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
    
    > Greg Sabino Mullane <htamfids@gmail.com> writes:
    > > On Mon, Oct 13, 2025 at 3:19 PM David Barsky <me@davidbarsky.com> wrote:
    > >> Anyways, I'll try to get at what motivated this whole discussion: would
    > >> there be community opposition to adding a CLI flag that'd exit/shutdown
    > all
    > >> Postgres processes once all pending connections close?
    >
    > > I don't know about opposition, per se, but the onus is on you to provide
    > a
    > > strong use case not already covered by existing tools.
    >
    > In particular, it's not terribly clear why the existing "smart"
    > shutdown mode isn't sufficient.
    >
    
    I think OP is saying "we don't run the tests from a shell prompt, so can't
    run pg_ctl stop".
    
    I think he might be able to, though, by having his IDE run a PowerShell
    command that runs "pg_ctl stop -wt9999", and also create an error trap that
    also runs that PS command.
    
    -- 
    Death to <Redacted>, and butter sauce.
    Don't boil me, I'm still alive.
    <Redacted> lobster!