MIME-Version: 1.0
References: <AS8PR08MB829778001E6F10552A6C4CEBC0DF2@AS8PR08MB8297.eurprd08.prod.outlook.com>
 <CAKoxK+4RTRP+phsnJgMjVzFVjmAHdu2wCXG_5YtsFR_fJEY6_Q@mail.gmail.com>
 <7002449259b582723ed7e041a7f0de116b48ad4d.camel@cybertec.at>
 <CANzqJaCZH2KmjOpAWXA=jUb-+He0D68sgY-YTibjk4bmH5Qt0A@mail.gmail.com> <66585a0d3b617bc2cba8ebbe3f0a479bee9c6324.camel@cybertec.at>
In-Reply-To: <66585a0d3b617bc2cba8ebbe3f0a479bee9c6324.camel@cybertec.at>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 17 Mar 2025 16:50:13 -0400
Message-ID: <CANzqJaBL-fnT9S45F_5huoZiASin9aPfyV0_ZVbk_XK3zQN39g@mail.gmail.com>
Subject: Re: Creating a new database on a different file system
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f31f5006308fef7f"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBL-fnT9S45F_5huoZiASin9aPfyV0_ZVbk_XK3zQN39g%40mail.gmail.com>
Precedence: bulk

--000000000000f31f5006308fef7f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Mar 17, 2025 at 4:30=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec=
.at>
wrote:

> On Mon, 2025-03-17 at 14:33 -0400, Ron Johnson wrote:
> > On Mon, Mar 17, 2025 at 11:18=E2=80=AFAM Laurenz Albe <laurenz.albe@cyb=
ertec.at>
> wrote:
> > > On Mon, 2025-03-17 at 15:51 +0100, Luca Ferrari wrote:
> > > > On Mon, Mar 17, 2025 at 3:49=E2=80=AFPM Ian Dauncey <
> Ian.Dauncey@bankzero.co.za> wrote:
> > > > > We have created a few databases on the file system defined in the
> postgresql.conf,
> > > > > but now I would like to create another database within the same
> cluster but on a
> > > > > different file system.
> > > > >
> > > > > Is this possible and if so, how do we go about it.
> > > >
> > > > create a tablespace on the filesystem you want to use, and then
> create
> > > > the database adding the `WITH TABLESPACE` clause.
> > > > See <https://www.postgresql.org/docs/17/sql-createtablespace.html>
> > >
> > > That is an option, but I would recommend to create a new database
> cluster
> > > on the new file system rather than creating a tablespace.
> >
> > That of course requires using another port, which can be tricky in a
> company that by
> > default closes all firewall ports at the network switch level, and wher=
e
> you must
> > enumerate every server/subnet ("Rejected. Subnet range too broad!") tha=
t
> needs access
> > to the new port, it takes time for requests for new port openings to be
> approved
> > ("Rejected. We don't recognize 5433!") and then implemented.
> >
> > Much easier to use a tablespace.
>
> *shrug* Sure, there are entities that think that security and
> professionalism can be
> measured in how difficult you are making everybody's life.  If rules and
> regulations
> are in the way of choosing the best solution, you have to go for the
> second best one.
>

Things are what they are.

A listener (like what SQL Server uses) on port 5432 that looks at a
connection, determines which instance it's asking for, and then redirects
the connection to it. would be useful.

How?  By enabling multiple instances all externally viewable on 5432, one
can run PgBackRest individually for each database instead of for
*every* database.
Role management would become more complicated, but *how much* more
complicated is site-dependent.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--000000000000f31f5006308fef7f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Mar 17, 2025 at 4:30=E2=80=AFPM L=
aurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cy=
bertec.at</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_contain=
er"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex">On Mon, 2025-03-17 at=
 14:33 -0400, Ron Johnson wrote:<br>
&gt; On Mon, Mar 17, 2025 at 11:18=E2=80=AFAM Laurenz Albe &lt;<a href=3D"m=
ailto:laurenz.albe@cybertec.at" target=3D"_blank">laurenz.albe@cybertec.at<=
/a>&gt; wrote:<br>
&gt; &gt; On Mon, 2025-03-17 at 15:51 +0100, Luca Ferrari wrote:<br>
&gt; &gt; &gt; On Mon, Mar 17, 2025 at 3:49=E2=80=AFPM Ian Dauncey &lt;<a h=
ref=3D"mailto:Ian.Dauncey@bankzero.co.za" target=3D"_blank">Ian.Dauncey@ban=
kzero.co.za</a>&gt; wrote:<br>
&gt; &gt; &gt; &gt; We have created a few databases on the file system defi=
ned in the postgresql.conf,<br>
&gt; &gt; &gt; &gt; but now I would like to create another database within =
the same cluster but on a<br>
&gt; &gt; &gt; &gt; different file system.<br>
&gt; &gt; &gt; &gt; <br>
&gt; &gt; &gt; &gt; Is this possible and if so, how do we go about it.<br>
&gt; &gt; &gt; <br>
&gt; &gt; &gt; create a tablespace on the filesystem you want to use, and t=
hen create<br>
&gt; &gt; &gt; the database adding the `WITH TABLESPACE` clause.<br>
&gt; &gt; &gt; See &lt;<a href=3D"https://www.postgresql.org/docs/17/sql-cr=
eatetablespace.html" rel=3D"noreferrer" target=3D"_blank">https://www.postg=
resql.org/docs/17/sql-createtablespace.html</a>&gt;<br>
&gt; &gt; <br>
&gt; &gt; That is an option, but I would recommend to create a new database=
 cluster<br>
&gt; &gt; on the new file system rather than creating a tablespace.<br>
&gt; <br>
&gt; That of course requires using another port, which can be tricky in a c=
ompany that by<br>
&gt; default closes all firewall ports at the network switch level, and whe=
re you must<br>
&gt; enumerate every server/subnet (&quot;Rejected. Subnet range too broad!=
&quot;) that needs access<br>
&gt; to the new port, it takes time for requests for new port openings to b=
e approved<br>
&gt; (&quot;Rejected. We don&#39;t recognize 5433!&quot;) and then implemen=
ted.<br>
&gt; <br>
&gt; Much easier to use a tablespace.<br>
<br>
*shrug* Sure, there are entities that think that security and professionali=
sm can be<br>
measured in how difficult you are making everybody&#39;s life.=C2=A0 If rul=
es and regulations<br>
are in the way of choosing the best solution, you have to go for the second=
 best one.<br></blockquote><div><br></div><div>Things are what they are.</d=
iv><div><br></div><div>A listener (like what SQL Server uses) on port 5432 =
that looks at a connection, determines which instance it&#39;s asking for, =
and then redirects the connection to it. would be useful.</div><div><br></d=
iv><div>How?=C2=A0 By enabling multiple instances all externally viewable o=
n 5432, one can run PgBackRest individually for each database instead of fo=
r <i>every</i>=C2=A0database.=C2=A0 Role management would become more compl=
icated, but <i>how much</i>=C2=A0more complicated is site-dependent.</div><=
div><br></div></div><span class=3D"gmail_signature_prefix">-- </span><br><d=
iv dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Reda=
cted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br>=
<div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--000000000000f31f5006308fef7f--