Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v84Gv-00CDZU-Ms for pgsql-general@arkaria.postgresql.org; Sun, 12 Oct 2025 22:10:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v84Gr-002WYL-Ns for pgsql-general@arkaria.postgresql.org; Sun, 12 Oct 2025 22:10:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v84Gr-002WYC-2Y for pgsql-general@lists.postgresql.org; Sun, 12 Oct 2025 22:10:30 +0000 Received: from mail-vs1-xe42.google.com ([2607:f8b0:4864:20::e42]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v84Go-001PV9-1w for pgsql-general@lists.postgresql.org; Sun, 12 Oct 2025 22:10:28 +0000 Received: by mail-vs1-xe42.google.com with SMTP id ada2fe7eead31-5a46c3b3a5bso1671162137.0 for ; Sun, 12 Oct 2025 15:10:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=davidbarsky.com; s=google; t=1760307025; x=1760911825; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=u1cOgqPI3ogDjFhjWh48OtWKrh4Sl2OeBerhqC1B05U=; b=em6zhlT5Fnv6wO5+pdrAw6IfLjiZ30yOS0kMbkYkJLapUjQJVH7f+2ZflnUVS6+5gn U/vLe5GXoKNVOgYbmw7Nzsk2wsAlY6+eIinKQp2lrvFjt8RsLUbEpb3HWaGcLGH5uInA JaTrKB9uy0DUWHv5bq3sd4IC4zcr/C99R3c7ZsKbSjSvRRr/a176Cest2I0lOiSiMcth txSJTh59/5OUKvWHM8Xa0zEPTIwIBe7w2rS3QdSSsfPjZsQsRpe/NfHQuPpVeVBT3sfq trabscgezt/FGvsrFlHKOtJpOCP+T4jaWIPGtli+1Ms78LO3NOUmrzc5YPNlulNyR7Gu KBaw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1760307025; x=1760911825; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=u1cOgqPI3ogDjFhjWh48OtWKrh4Sl2OeBerhqC1B05U=; b=W2BQBfwZCrg6rdxvExDWRZ/kiR+cpYHf7YYw44YaKF/tZkgXsmBcC8OR2THdD+KJWf xXuBVBpm0PELTlSPTElpYfUuudPhDMOVdhmPW+wQjhlnwsvz+UqquvNVe32Jg+hasnSt 2TtmFXl2ZgP/5ibqmBsPIDj1eh4wo50uQ6K4uxtQ+BGJdZLJsYaCLRxZpqSUKIFK3Vek LJeIanSb/OFftvE2Ofe4jQmD1Jq87jk+aT8163AvMx+1/S6t7Xnh9jGus9mbtPlIGnrz QLFgtN4kaiIukmADRt8XBS5qU3TGnITKurS5wKLE3kC0lh8atBte47PDorizrKSVClvy eIZQ== X-Gm-Message-State: AOJu0YxKQX2DgHbhlLxfokZvs6kI3/Z+SRCFV0hQMndRONLY5GJJjOua LRgh6RKYbTJTVmptVDzhDsYe88beLkjcx0C0bTl0UZtmwiFsRunOowt47sV2Qmva73t9+a/5zNS 87kX03mSctgos9BErtMm8AlWhVRhNIYJFCdWE7kXxWg== X-Gm-Gg: ASbGnctTnPpIVcw9H9j6u7hPRyNHZNh6MmtWCt6a58m19E+k2sZJNNp2/5bONqLL6kU hECLXBBSMB3BncIq+aa+FwCXngAijMYWAz/G8dRECfNcrGMtDcZEKuLzSIFduy2LUo7WUAZFdCS T0qekFQ7EPrdlEE3A1jQm95FUMeYz6h7pbuDscR6YLGzKafxEHz2QRUgH8rC07S59yFXoASq6fo Ipa+eVJ0mTsglNVwY7u+nrBOb2ZTQ2TiqLn02io43TpH3cStkF4F5Qlgw== X-Google-Smtp-Source: AGHT+IH/k3Mx+TlRr4+H1VQjoazRaFvr12MR1Q7hjz0C2SKQt9QXsJgmwoG22rMZ6rp5uG6eNLqkrz+KpjIlHfi6pN8= X-Received: by 2002:a05:6102:6c3:b0:5d5:f6ae:38c2 with SMTP id ada2fe7eead31-5d5f6ae3c57mr4679138137.43.1760307024818; Sun, 12 Oct 2025 15:10:24 -0700 (PDT) Received: from 1064022179695 named unknown by gmailapi.google.com with HTTPREST; Sun, 12 Oct 2025 15:10:24 -0700 Received: from 1064022179695 named unknown by gmailapi.google.com with HTTPREST; Sun, 12 Oct 2025 15:10:21 -0700 MIME-Version: 1.0 (Mimestream 1.8.3) References: In-Reply-To: From: David Barsky Date: Sun, 12 Oct 2025 15:10:24 -0700 X-Gm-Features: AS18NWDuDbyGkv84BjC1Yq4ocUVbWwoRg-HrDEXTCPYwfN0hhL3droWR-nfq_9A Message-ID: Subject: Re: Option on `postgres` CLI to shutdown when there are no more active connections? To: Adrian Klaver , ronljohnsonjr@gmail.com Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000e905dd0640fd6a75" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e905dd0640fd6a75 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > 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. =E2=80=9CSmart=E2=80=9D mode disallows new connections, then wait= s 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. > =E2=80=9CFast=E2=80=9D 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. =E2=80=9CImmediate=E2=80=9D mode > will abo= rt 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 bein= g 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 developmen= t environment (language toolchains, editor, database), they shouldn=E2=80= =99t 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=E2=80=99t 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 syste= m 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=E2=80=99m not a database expert (I=E2=80=99m 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=3Don" An API surface like that would be nice, but doesn=E2=80=99t need to be `pos= tgres`. I=E2=80=99m not picky. Best, David On Oct 11, 2025 at 4:11:18=E2=80=AFPM, Adrian Klaver 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. =E2=80=9CSmart=E2=80=9D mode disallows new connections, then wait= s 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. =E2=80=9CFast=E2=80=9D mode (the default) does not wai= t for clients > to disconnect. All active transactions are rolled back and clients are > forcibly disconnected, then the server is shut down. =E2=80=9CImmediate= =E2=80=9D 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 th= e > > 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 purel= y > > 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 > --000000000000e905dd0640fd6a75 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
> stop mode shuts down the server that is running i= n the specified data >
> directory. Three diffe= rent shutdown methods can be selected with the -m >
> option. =E2=80=9CSmart=E2=80=9D mode disallows new connections, then= waits for all > existing
> clients to disconne= ct. If the server is in hot standby, > recovery and
> streaming replication will be terminated once all clients > have = disconnected.
> =E2=80=9CFast=E2=80=9D mode (the d= efault) does not wait for clients > to disconnect. All
> active transactions are rolled back and clients are > forcibly= disconnected,
> then the server is shut down. =E2= =80=9CImmediate=E2=80=9D mode > will abort all server
> processes immediately, without a clean shutdown. > This choice = will lead to a
> crash-recovery cycle during the n= ext 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 some= thing 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, especial= ly from an operational standpoint. However, I _think_
Postgres can get really close to an embedded database's development ex= perience
by doing a few tricks that I'll elaborat= e 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 O= P's use-case: a Windows desktop running local
>= ; > testing.
> > We in the shell scripting d= aemon world don't think like that.
> > From= the original post:

> &= quot;Is there any interest in adding a command line option to the `postgres= `
> CLI"
> Which I t= ook to mean:
>
I think Ron's interpretation is correct, but I = also don't mind using `pg_ctl`!
And yes, the thin= g I'm looking for looks pretty similar to SQL Server's
`AUTO_CLOSE`.

Mo= re 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_
= =C2=A0 =C2=A0a test environment atomically, it can't tear down a test e= nvironment
=C2=A0 =C2=A0atomically. I think this is r= easonable stance on the part of the test runner
=C2= =A0 =C2=A0to encourage reliable test suites.
2. We st= arted by using SQLite, which has the _really nice_ property of being
<= div dir=3D"ltr">=C2=A0 =C2=A0able to function entirely in-memory. This mean= s that when the test completes,
=C2=A0 =C2=A0cleanup = of the entire database occurs due to the operating system
=C2=A0 =C2=A0deallocating the test process' memory; no orphaned pr= ocesses to think about.
3. After someone installs all= the tools that they need for their development
=C2= =A0 =C2=A0environment (language toolchains, editor, database), they shouldn= =E2=80=99t need to
=C2=A0 =C2=A0do any additional, on= going maintenance. Having experienced a workflow where
=C2=A0 =C2=A0the entire build/test process is almost entirely self-contai= ned, the
=C2=A0 =C2=A0productivity benefits are massi= ve and I really don=E2=80=99t want to go back.
=C2=A0= =C2=A01. There's an additional benefit here: we're able to unit te= st against the
=C2=A0 =C2=A0 =C2=A0 actual database w= e're running against in production with complete fidelity
=C2=A0 =C2=A0 =C2=A0 (some people might say that that these are re= ally integration tests, but
=C2=A0 =C2=A0 =C2=A0 if e= ach test completes in 0.02 milliseconds and scales to use all cores on
=C2=A0 =C2=A0 =C2=A0 my machine, I consider them to be _m= orally_ 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 c= lose to the development
experience of an embedded dat= abase!):

1. On test, creat= e or connect to an existing Postgres instance. Since each test
=C2=A0 =C2=A0is its own standalone process, I think something sha= ped like optimistic
=C2=A0 =C2=A0locking to launch Po= stgres at a given port suffices. The operating system
=C2=A0 =C2=A0will complain if two processes are launched the same port and= the OS holding
=C2=A0 =C2=A0the lock on the port sho= uld prevent any TOCTOU bugs.
2. Each test runs their = own set of test transactions, which are automatically
=C2=A0 =C2=A0rolled back at the end of each test.
3.= Postgres does some sort of connection-based reference counting after the
=C2=A0 =C2=A0first connection. Once all connections cl= ose and a short timeout window
=C2=A0 =C2=A0passes (e= .g., 100ms, but it should probably be configurable?) Postgres shuts
=C2=A0 =C2=A0down and cleans up any on-disk data.

I can probably write a proxy that do= es what I described above or do something
with `pg_ct= l`'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=E2=80=99m not a da= tabase expert (I=E2=80=99m more of a compilers person), but=C2=A0
I think what I described above should work, I hope?

> Yeah. Something like this, whic= h we don't have:

> = postgres -c "auto_close=3Don"

An API surface like that would be nice, but doesn=E2=80=99t n= eed to be `postgres`.=C2=A0
I=E2=80=99m not picky.

Best,
David

On Oct 11, 2025 at 4:11:18=E2=80= =AFPM, Adrian Klaver <adria= n.klaver@aklaver.com> wrote:
=20
On 10/8/25 12:39, David Barsky wrote:
Hiy= a folks,

I'm a bit of a newcomer when it comes to PostgreSQ= L, so I apologize if
this is
the wrong mailing list. Anyways, m= y two questions:

1. Is there any interest in adding a command l= ine option to the
`postgres` CL= I
=C2=A0=C2=A0 =C2=A0that shuts = down the PostgreSQL instance once (and optionally cleans
<= blockquote type=3D"cite"> up the
= =C2=A0=C2=A0 =C2=A0data 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 spec= ified data
directory. Three different shutdown methods can be selected = with the -m
option. =E2=80=9CSmart=E2=80=9D mode disallows new connecti= ons, then waits for all
existing clients to disconnect. If the server i= s in hot standby,
recovery and streaming replication will be terminated= once all clients
have disconnected. =E2=80=9CFast=E2=80=9D mode (the d= efault) does not wait for clients
to disconnect. All active transaction= s are rolled back and clients are
forcibly disconnected, then the serve= r is shut down. =E2=80=9CImmediate=E2=80=9D 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.
"

=C2=A0=C2=A0 =C2=A0a. Alternatively, I wou= ldn't mind accomplishing this via the single-user
=C2=A0=C2=A0 =C2=A0 =C2=A0 mode if it could accept th= e binary/wire protocol in addition to the
=C2=A0=C2=A0 =C2=A0 =C2=A0 current text protocol.
2. Are there plans for having any additional t= able access methods beyond
`HEA= P`
=C2=A0=C2=A0 =C2=A0shipping a= s part of Postgres? I'd love to have something that's purely
=C2=A0=C2=A0 =C2=A0in-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, actu= al PostgreSQL instance and make the experience feel a lot like
sqlite's embedded/in-memory workflow. To= day, we've gotten really great

Postgres is not an e= mbedded 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 with= out
requiring
<= blockquote type=3D"cite"> the user to start an instance of Postgres prior t= o running the tests.

Warmest regards,
David


--
Adrian Klaver
adrian.klaver@aklaver.com
--000000000000e905dd0640fd6a75--