Re: Creating a new database on a different file system

[Tim Gerber]

Hi Ian,

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

Re: Restoring only a subset of schemas

[Adrian Klaver]

On 3/17/25 07:57, Sylvain Cuaz wrote:

Hi all,

     I have a DB with one schema named "Common" holding data referenced 
by other schemas. All other schemas have the same structure (tables and 
fields) and are named "cXXX" where XXX is just an int. Thus the only 
cross-schema foreign keys are in "cXXX" pointing to "Common", and each 
"cXXX" is completely independent of other "cXXX" schemas.
     Now if I want to restore from a full dump of this DB, but with only 
one "cXXX" and the "Common" schema :
- if I pass --create --schema=Common, then the CREATE SCHEMA is missing, 
i.e. it only emits data inside "Common" and the restore fails.


I am not seeing that.

For:

pg_dump -d test -U postgres -s --create --schema=other_sch --schema=public

In the output I get:


[...]

CREATE SCHEMA other_sch;


ALTER SCHEMA other_sch OWNER TO postgres;

--
-- Name: public; Type: SCHEMA; Schema: -; Owner: pg_database_owner
--

CREATE SCHEMA public;


ALTER SCHEMA public OWNER TO pg_database_owner;

[...]

What is the complete command you are using for the pg_dump?

What Postgres version(s) are you using?




Cheers,

Sylvain





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Restoring only a subset of schemas

[Tom Lane]

Sylvain Cuaz  writes:
>      Now if I want to restore from a full dump of this DB, but with only one 
> "cXXX" and the "Common" 
> schema :
> - if I pass --create --schema=Common, then the CREATE SCHEMA is missing, i.e. 
> it only emits data 
> inside "Common" and the restore fails.
> - if I could pass --create --exclude-schema='c*' (fictional notation as 
> patterns are only recognized 
> by pg_dump), then all schemas would be created, with no data inside except 
> for "Common". Creating 
> all schemas is a waste of time, but more importantly would make restoring 
> other schemas more 
> difficult (e.g. rows should be inserted before creating foreign keys).

In general, the solution for edge-case restore selection needs is to
make a list of the dump's contents with "pg_restore -l", edit out what
you don't want using any method you like, then use the edited list with
"pg_restore -L".

While I'd be in favor of improving pg_restore to accept wild-card
patterns, I'm very hesitant to start inventing new kinds of selection
switches for it.  The interactions between such switches would be a
mess.

regards, tom lane

Restoring only a subset of schemas

[Sylvain Cuaz]

Hi all,

    I have a DB with one schema named "Common" holding data referenced by other schemas. All other 
schemas have the same structure (tables and fields) and are named "cXXX" where XXX is just an int. 
Thus the only cross-schema foreign keys are in "cXXX" pointing to "Common", and each "cXXX" is 
completely independent of other "cXXX" schemas.
    Now if I want to restore from a full dump of this DB, but with only one "cXXX" and the "Common" 
schema :
- if I pass --create --schema=Common, then the CREATE SCHEMA is missing, i.e. it only emits data 
inside "Common" and the restore fails.
- if I could pass --create --exclude-schema='c*' (fictional notation as patterns are only recognized 
by pg_dump), then all schemas would be created, with no data inside except for "Common". Creating 
all schemas is a waste of time, but more importantly would make restoring other schemas more 
difficult (e.g. rows should be inserted before creating foreign keys).
Note : to check the behaviour of pg_restore above, I pass -f- to check the SQL as it is far quicker 
than to actually restore a DB.


Maybe a new --include-create-schema option should be added to emit CREATE SCHEMA in addition to 
objects inside it ? That way I could :
1. --create --include-create-schema --schema=Common and have a DB with all DB-level properties 
(DEFAULT PRIVILEGES, COMMENT, SET parameter, etc.) and one schema with all of its data and 
schema-level properties (DEFAULT PRIVILEGES, COMMENT, GRANT USAGE).
2. then at any point later without the --create, with as many schemas I need :  
--include-create-schema --schema=cXXX. And if I need to reset a "cXXX" schema, just manually DROP 
SCHEMA and restore again.


Similarly, maybe add --exclude-create-schema to additionally exclude CREATE SCHEMA for schemas 
targeted by --exclude-schema.


IOW --schema and --exclude-schema both target objects inside schemas, these 2 new options would 
allow to also have control on the schemas themselves (and their properties like DEFAULT PRIVILEGES, 
COMMENT, etc.)


Cheers,

Sylvain

Re: Creating a new database on a different file system

[Laurenz Albe]

On Mon, 2025-03-17 at 15:51 +0100, Luca Ferrari wrote:
> On Mon, Mar 17, 2025 at 3:49 PM Ian Dauncey  
> 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 

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

Re: Creating a new database on a different file system

[Laurenz Albe]

On Mon, 2025-03-17 at 14:33 -0400, Ron Johnson wrote:
> On Mon, Mar 17, 2025 at 11:18 AM Laurenz Albe  
> wrote:
> > On Mon, 2025-03-17 at 15:51 +0100, Luca Ferrari wrote:
> > > On Mon, Mar 17, 2025 at 3:49 PM Ian Dauncey  
> > > 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 
> > 
> > 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

Re: Creating a new database on a different file system

[Ron Johnson]

On Mon, Mar 17, 2025 at 11:18 AM Laurenz Albe 
wrote:

> On Mon, 2025-03-17 at 15:51 +0100, Luca Ferrari wrote:
> > On Mon, Mar 17, 2025 at 3:49 PM Ian Dauncey 
> 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 
>
> 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 , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Creating a new database on a different file system

[Ron Johnson]

On Mon, Mar 17, 2025 at 4:30 PM Laurenz Albe 
wrote:

> On Mon, 2025-03-17 at 14:33 -0400, Ron Johnson wrote:
> > On Mon, Mar 17, 2025 at 11:18 AM Laurenz Albe 
> 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.daun...@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 
> > >
> > > 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 , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Creating a new database on a different file system

[Luca Ferrari]

On Mon, Mar 17, 2025 at 3:49 PM Ian Dauncey  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 

Luca

Creating a new database on a different file system

[Ian Dauncey]

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

Re: #1 - Known bug (memory related) with respect to Aurora postgresql 13.16.3

[Ron Johnson]

Aurora is very nonstandard.  Thus, "we" don't support it.

Having said that... "report running out of memory" smells like work_mem is
set too high.

On Mon, Mar 17, 2025 at 3:12 PM Bharani SV-forum 
wrote:

> Team
> Any one faced similar issue with Ver 13.16.X
>
>
>
> - Forwarded Message -
> *From:* Bharani SV-forum 
> *To:* pgsql-gene...@postgresql.org 
> *Cc:* Bharani SV esteembsv-forum 
> *Sent:* Saturday, March 15, 2025 at 12:18:04 PM EDT
> *Subject:* Known bug (memory related) with respect to Aurora postgresql
> 13.16.3
>
> Team
> We have  Aurora postgresql ver (AWS- RDS) ver 13.16.3 and we faced issued
> with an report process (batch process) running out of memory.
>
> regarding url " https://www.postgresql.org/docs/release/13.18/";
> and url :
> https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Updates.html#aurorapostgresql-versions-version1318x
>
> i am aware of
> a)
> Critical stability enhancements for 13.18
>
>-
>
>Fixed an issue that in rare cases can cause CPU usage spike
>
>
> Will be implementing RDS based Aurora Postgresql ver 13.18 patch fixes by
> next week.
>
> Any one had noticed or have experienced w.r.to memory process w.r.to RDS-
> aurora postgresql 13.16.3 ?
>
> Regards
>
>

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!