Thread: Creating a new database on a different file system
Hi All
We are running an old version of PostgreSQL on a Linux Server.
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.
In a nutshell:
I have the following statement defined in the postgresql.conf file - data_directory = '/opt/pgdata_postgres'
I have created the following database datadb1, datadb2 (plus all default databases)
Now I want to create a third database datadb3, but it needs to be created on file system “/opt/pgdata1_postgres” and not on ‘/opt/pgdata_postgres'
Is this possible
Regards
Ian
On Mon, Mar 17, 2025 at 3:49 PM 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 anotherdatabase 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> Luca
Hi Ian,
Tablespaces would work... take a look: https://www.postgresql.org/docs/current/manage-ag-tablespaces.html
Tablespaces would work... take a look: https://www.postgresql.org/docs/current/manage-ag-tablespaces.html
Best,
Tim
On Mon, Mar 17, 2025 at 9:49 AM Ian Dauncey <Ian.Dauncey@bankzero.co.za> wrote:
Hi All
We are running an old version of PostgreSQL on a Linux Server.
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.
In a nutshell:
I have the following statement defined in the postgresql.conf file - data_directory = '/opt/pgdata_postgres'
I have created the following database datadb1, datadb2 (plus all default databases)
Now I want to create a third database datadb3, but it needs to be created on file system “/opt/pgdata1_postgres” and not on ‘/opt/pgdata_postgres'
Is this possible
Regards
Ian
On Mon, 2025-03-17 at 15:51 +0100, Luca Ferrari wrote: > On Mon, Mar 17, 2025 at 3:49 PM 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. Yours, Laurenz Albe
On Mon, Mar 17, 2025 at 11:18 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2025-03-17 at 15:51 +0100, Luca Ferrari wrote:
> On Mon, Mar 17, 2025 at 3:49 PM 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 where you must enumerate every server/subnet ("Rejected. Subnet range too broad!") that 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.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On Mon, 2025-03-17 at 14:33 -0400, Ron Johnson wrote: > On Mon, Mar 17, 2025 at 11:18 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > On Mon, 2025-03-17 at 15:51 +0100, Luca Ferrari wrote: > > > On Mon, Mar 17, 2025 at 3:49 PM 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 where you must > enumerate every server/subnet ("Rejected. Subnet range too broad!") that 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. Yours, Laurenz Albe
On Mon, Mar 17, 2025 at 4:30 PM 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 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Mon, 2025-03-17 at 15:51 +0100, Luca Ferrari wrote:
> > > On Mon, Mar 17, 2025 at 3:49 PM 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 where you must
> enumerate every server/subnet ("Rejected. Subnet range too broad!") that 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.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!