pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore schema dump to schema with different name
7+ messages / 4 participants
[nested] [flat]
* pg_restore schema dump to schema with different name
@ 2021-08-23 09:44 Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-08-23 13:19 ` Re: pg_restore schema dump to schema with different name Laurenz Albe <laurenz.albe@cybertec.at>
2021-08-24 14:55 ` Re: pg_restore schema dump to schema with different name David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Nagaraj Raj @ 2021-08-23 09:44 UTC (permalink / raw)
To: pgsql-performance
Hi,
I know I can alter schema name after restoring but the problem is the name already exist and I don't want to touch that existing schema.The dump type is "custom".
So effectively I want something like.pg_dump -U postgres --schema "source_schema" --format "c" --create --file "source_schema.bak" my_dbpg_restore -U postgres --exit-on-error --dbname "my_db" --destination-schema "destination_schema"
Currently this is not something can do. this functionality is there in oracle.
Is this future considering to add? (it would really help for create any test schemas without disturbing current schema. )
Thanks,Rj
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore schema dump to schema with different name
2021-08-23 09:44 pg_restore schema dump to schema with different name Nagaraj Raj <nagaraj.sf@yahoo.com>
@ 2021-08-23 13:19 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-08-23 13:38 ` Re: pg_restore schema dump to schema with different name Jean-Christophe Boggio <postgresql@thefreecat.org>
1 sibling, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2021-08-23 13:19 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; pgsql-performance
On Mon, 2021-08-23 at 09:44 +0000, Nagaraj Raj wrote:
> I know I can alter schema name after restoring but the problem is the name already exist and I don't want to touch that existing schema.
> The dump type is "custom".
>
> So effectively I want something like.
> pg_dump -U postgres --schema "source_schema" --format "c" --create --file "source_schema.bak" my_db
> pg_restore -U postgres --exit-on-error --dbname "my_db" --destination-schema "destination_schema"
The only way to do that is to create a new database, import the data there,
rename the schema and dump again.
Then import that dump into the target database.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore schema dump to schema with different name
2021-08-23 09:44 pg_restore schema dump to schema with different name Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-08-23 13:19 ` Re: pg_restore schema dump to schema with different name Laurenz Albe <laurenz.albe@cybertec.at>
@ 2021-08-23 13:38 ` Jean-Christophe Boggio <postgresql@thefreecat.org>
2021-08-23 17:54 ` Re: pg_restore schema dump to schema with different name Nagaraj Raj <nagaraj.sf@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jean-Christophe Boggio @ 2021-08-23 13:38 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
> The only way to do that is to create a new database, import the data
> there, rename the schema and dump again.
>
> Then import that dump into the target database.
Or maybe (if you can afford to have source_schema unavailable for some
time) :
* rename source_schema to tmp_source
* import (that will re-create source_schema)
* rename source_schema to destination_schema
* rename back tmp_source to source_schema
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore schema dump to schema with different name
2021-08-23 09:44 pg_restore schema dump to schema with different name Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-08-23 13:19 ` Re: pg_restore schema dump to schema with different name Laurenz Albe <laurenz.albe@cybertec.at>
2021-08-23 13:38 ` Re: pg_restore schema dump to schema with different name Jean-Christophe Boggio <postgresql@thefreecat.org>
@ 2021-08-23 17:54 ` Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-08-24 09:53 ` Re: pg_restore schema dump to schema with different name Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: Nagaraj Raj @ 2021-08-23 17:54 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>; Jean-Christophe Boggio <postgresql@thefreecat.org>
Wouldn’t be easy if we have option to_schema ?
Absolutely, I should not alter current schema, as it live 24/7.
Thanks,Rj On Monday, August 23, 2021, 06:39:03 AM PDT, Jean-Christophe Boggio <postgresql@thefreecat.org> wrote:
> The only way to do that is to create a new database, import the data
> there, rename the schema and dump again.
>
> Then import that dump into the target database.
Or maybe (if you can afford to have source_schema unavailable for some
time) :
* rename source_schema to tmp_source
* import (that will re-create source_schema)
* rename source_schema to destination_schema
* rename back tmp_source to source_schema
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore schema dump to schema with different name
2021-08-23 09:44 pg_restore schema dump to schema with different name Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-08-23 13:19 ` Re: pg_restore schema dump to schema with different name Laurenz Albe <laurenz.albe@cybertec.at>
2021-08-23 13:38 ` Re: pg_restore schema dump to schema with different name Jean-Christophe Boggio <postgresql@thefreecat.org>
2021-08-23 17:54 ` Re: pg_restore schema dump to schema with different name Nagaraj Raj <nagaraj.sf@yahoo.com>
@ 2021-08-24 09:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2021-08-24 09:53 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>; Jean-Christophe Boggio <postgresql@thefreecat.org>
On Mon, 2021-08-23 at 17:54 +0000, Nagaraj Raj wrote:
> Wouldn’t be easy if we have option to_schema ?
Sure, but it wouldn't be easy to implement that.
It would have to be a part of "pg_dump".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore schema dump to schema with different name
2021-08-23 09:44 pg_restore schema dump to schema with different name Nagaraj Raj <nagaraj.sf@yahoo.com>
@ 2021-08-24 14:55 ` David G. Johnston <david.g.johnston@gmail.com>
2021-08-30 16:52 ` Re: pg_restore schema dump to schema with different name Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2021-08-24 14:55 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: pgsql-performance
On Mon, Aug 23, 2021 at 2:46 AM Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
>
> Currently this is not something can do. this functionality is there in
> oracle.
>
> Is this future considering to add? (it would really help for create any
> test schemas without disturbing current schema. )
>
>
I find this to be not all that useful. Current practice is to avoid
relying on search_path and, in general, to schema-qualify object references
(yes, attaching a local SET search_path to a function works, not sure how
it would play out in this context). Performing a dependency and contextual
rename of one schema name to another is challenging given all of that, and
impossible if the schema name is hard-coded into a function body.
I won't say we wouldn't accept such a patch, but as this isn't exactly a
new problem or realization, and the feature doesn't presently exist, that
for whatever reasons individuals may have no one has chosen to volunteer or
fund such development. I don't even remember seeing a proposal in the past
5 or so years.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore schema dump to schema with different name
2021-08-23 09:44 pg_restore schema dump to schema with different name Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-08-24 14:55 ` Re: pg_restore schema dump to schema with different name David G. Johnston <david.g.johnston@gmail.com>
@ 2021-08-30 16:52 ` Nagaraj Raj <nagaraj.sf@yahoo.com>
0 siblings, 0 replies; 7+ messages in thread
From: Nagaraj Raj @ 2021-08-30 16:52 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-performance
I agree with that.But, probably its good idea to add this feature as many people are migrating from oracle to postgres. clone/restore schemas to existing cluster for any test cases like sandbox schema, temp schema as live backup schema etc.
Thanks,Rj
On Tuesday, August 24, 2021, 07:56:20 AM PDT, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Aug 23, 2021 at 2:46 AM Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
Currently this is not something can do. this functionality is there in oracle.
Is this future considering to add? (it would really help for create any test schemas without disturbing current schema. )
I find this to be not all that useful. Current practice is to avoid relying on search_path and, in general, to schema-qualify object references (yes, attaching a local SET search_path to a function works, not sure how it would play out in this context). Performing a dependency and contextual rename of one schema name to another is challenging given all of that, and impossible if the schema name is hard-coded into a function body.
I won't say we wouldn't accept such a patch, but as this isn't exactly a new problem or realization, and the feature doesn't presently exist, that for whatever reasons individuals may have no one has chosen to volunteer or fund such development. I don't even remember seeing a proposal in the past 5 or so years.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-08-30 16:52 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-23 09:44 pg_restore schema dump to schema with different name Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-08-23 13:19 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-08-23 13:38 ` Jean-Christophe Boggio <postgresql@thefreecat.org>
2021-08-23 17:54 ` Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-08-24 09:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-08-24 14:55 ` David G. Johnston <david.g.johnston@gmail.com>
2021-08-30 16:52 ` Nagaraj Raj <nagaraj.sf@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox