pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedfeature request: pg_restore renaming target schema
6+ messages / 3 participants
[nested] [flat]
* feature request: pg_restore renaming target schema
@ 2015-12-17 07:44 CN <cnliou9@fastmail.fm>
2015-12-17 13:20 ` Re: feature request: pg_restore renaming target schema Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: CN @ 2015-12-17 07:44 UTC (permalink / raw)
To: pgsql-admin
Hi!
I need to restore one source schema to multiple target schema currently.
For example,
source database: db1
source schema: s1
target database: db2
The existing approach I know is following these steps:
(1) pg_dump -n s1 -Fc -f source.db db1
(2a) pg_restore -d db2 -n s1 source.db
(2b) psql -c "ALTER SCHEMA s1 RENAME TO t1" db2
(3a) pg_restore -d db2 -n s1 source.db
(3b) psql -c "ALTER SCHEMA s1 RENAME TO t2" db2
So far so good.
However, with existing tool I know, obviously step groups (2) and (3)
can only run sequentially.
If pg_restore allows "redirecting" source schema to target schema, then
I can concurrently issue some imaginary commands like these:
pg_restore -d db2 -n s1 --target-schema=t1 source.db
pg_restore -d db2 -n s1 --target-schema=t2 source.db
pg_restore -d db2 -n s1 --target-schema=t3 source.db
Best Regards,
CN
--
http://www.fastmail.com - Faster than the air-speed velocity of an
unladen european swallow
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: feature request: pg_restore renaming target schema
2015-12-17 07:44 feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
@ 2015-12-17 13:20 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2015-12-17 15:19 ` Re: feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
0 siblings, 1 reply; 6+ messages in thread
From: Shreeyansh Dba @ 2015-12-17 13:20 UTC (permalink / raw)
To: CN <cnliou9@fastmail.fm>; +Cc: pgsql-admin
Hi CN,
The procedure you have followed in the beginning is right.
But if you are trying to do with second option like from source schema(s1)
of db1 to target schema(t1) of db2, it is not possible with pg_restore
because while restoring from backup dump, it follows the sequence of
commands like
"CREATE SCHEMA s1"
"SET SEARCH_PATH=s1"
"CREATE TABLE table_name(id int)"
"INSERT INTO table_name VALUES(1)"
2: If you are taking the dump in plan text format you can edit the plain
text file and replace schema old name to new name and then you can restore
the file using psql -d <db> -U <user> -f <path of plain text file>.
--
Thanks & Regards
Venkataramana Aitla
Database Administrator
Shreeyansh Technologies
On Thu, Dec 17, 2015 at 1:14 PM, CN <cnliou9@fastmail.fm> wrote:
> Hi!
>
> I need to restore one source schema to multiple target schema currently.
> For example,
>
> source database: db1
> source schema: s1
> target database: db2
>
> The existing approach I know is following these steps:
>
> (1) pg_dump -n s1 -Fc -f source.db db1
> (2a) pg_restore -d db2 -n s1 source.db
> (2b) psql -c "ALTER SCHEMA s1 RENAME TO t1" db2
> (3a) pg_restore -d db2 -n s1 source.db
> (3b) psql -c "ALTER SCHEMA s1 RENAME TO t2" db2
>
> So far so good.
> However, with existing tool I know, obviously step groups (2) and (3)
> can only run sequentially.
>
> If pg_restore allows "redirecting" source schema to target schema, then
> I can concurrently issue some imaginary commands like these:
>
> pg_restore -d db2 -n s1 --target-schema=t1 source.db
> pg_restore -d db2 -n s1 --target-schema=t2 source.db
> pg_restore -d db2 -n s1 --target-schema=t3 source.db
>
> Best Regards,
> CN
>
> --
> http://www.fastmail.com - Faster than the air-speed velocity of an
> unladen european swallow
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: feature request: pg_restore renaming target schema
2015-12-17 07:44 feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
2015-12-17 13:20 ` Re: feature request: pg_restore renaming target schema Shreeyansh Dba <shreeyansh2014@gmail.com>
@ 2015-12-17 15:19 ` CN <cnliou9@fastmail.fm>
2015-12-17 15:34 ` Re: feature request: pg_restore renaming target schema John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: CN @ 2015-12-17 15:19 UTC (permalink / raw)
To: pgsql-admin
Thank you! Venkataramana,
On Thu, Dec 17, 2015, at 09:20 PM, Shreeyansh Dba wrote:
> But if you are trying to do with second option like from source
> schema(s1) of db1 to target schema(t1) of db2, it is not possible with
> pg_restore because while restoring from backup dump, it follows the
> sequence of commands like "CREATE SCHEMA s1" "SET SEARCH_PATH=s1"
> "CREATE TABLE table_name(id int)" "INSERT INTO table_name VALUES(1)"
Indeed. I have just peeked the content of the dumped file in plain
format and have noticed that all objects are prefixed with original
schema name. This makes me feel that the task of adding the mentioned
feature to pg_restore or pg_dump must be nontrivial :-(
> 2: If you are taking the dump in plan text format you can edit the
> plain text file and replace schema old name to new name and then
> you can restore the file using psql -d <db> -U <user> -f <path of
> plain text file>.
>
Manual editing is not quite feasible because I need to automate the
sequence of jobs by executing pg_dump and pg_restore in program.
Best Regards, CN
--
http://www.fastmail.com - Accessible with your email software
or over the web
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: feature request: pg_restore renaming target schema
2015-12-17 07:44 feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
2015-12-17 13:20 ` Re: feature request: pg_restore renaming target schema Shreeyansh Dba <shreeyansh2014@gmail.com>
2015-12-17 15:19 ` Re: feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
@ 2015-12-17 15:34 ` John Scalia <jayknowsunix@gmail.com>
2015-12-17 16:18 ` Re: feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
0 siblings, 1 reply; 6+ messages in thread
From: John Scalia @ 2015-12-17 15:34 UTC (permalink / raw)
To: CN <cnliou9@fastmail.fm>; +Cc: pgsql-admin
Maybe a bit off-postgresql, but why couldn't you just add to your existing
automations script, Use sed or your favorite stream editor and have that do
the renaming in the dump output?
FWIW,
Jay
On Thu, Dec 17, 2015 at 9:19 AM, CN <cnliou9@fastmail.fm> wrote:
> Thank you! Venkataramana,
>
> On Thu, Dec 17, 2015, at 09:20 PM, Shreeyansh Dba wrote:
>
> But if you are trying to do with second option like from source schema(s1)
> of db1 to target schema(t1) of db2, it is not possible with pg_restore
> because while restoring from backup dump, it follows the sequence of
> commands like
> "CREATE SCHEMA s1"
> "SET SEARCH_PATH=s1"
> "CREATE TABLE table_name(id int)"
> "INSERT INTO table_name VALUES(1)"
>
>
> Indeed. I have just peeked the content of the dumped file in plain format
> and have noticed that all objects are prefixed with original schema name.
> This makes me feel that the task of adding the mentioned feature to
> pg_restore or pg_dump must be nontrivial :-(
>
>
> 2: If you are taking the dump in plan text format you can edit the plain
> text file and replace schema old name to new name and then you can restore
> the file using psql -d <db> -U <user> -f <path of plain text file>.
>
>
>
> Manual editing is not quite feasible because I need to automate the
> sequence of jobs by executing pg_dump and pg_restore in program.
>
> Best Regards,
> CN
>
> -- http://www.fastmail.com - Accessible with your email software
> or over the web
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: feature request: pg_restore renaming target schema
2015-12-17 07:44 feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
2015-12-17 13:20 ` Re: feature request: pg_restore renaming target schema Shreeyansh Dba <shreeyansh2014@gmail.com>
2015-12-17 15:19 ` Re: feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
2015-12-17 15:34 ` Re: feature request: pg_restore renaming target schema John Scalia <jayknowsunix@gmail.com>
@ 2015-12-17 16:18 ` CN <cnliou9@fastmail.fm>
2015-12-22 16:19 ` Re: feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
0 siblings, 1 reply; 6+ messages in thread
From: CN @ 2015-12-17 16:18 UTC (permalink / raw)
To: pgsql-admin
On Thu, Dec 17, 2015, at 11:34 PM, John Scalia wrote:
> Maybe a bit off-postgresql, but why couldn't you just add to your
> existing automations script, Use sed or your favorite stream editor
> and have that do the renaming in the dump output? FWIW, Jay
> or over the web
I thought this approach before I raised this request. I had peeked the
dumped file and had a feeling that manipulating the dumped file with
stream editors was infeasible because I thought that the source schema
name was scrambled everywhere in the dumped file, data included.
Having received your message, I started to think that the approach I
gave up in the very beginning might actually be the feasible one -
thinking that you would not have mentioned it otherwise!
Hence I went back and took a more thorough look at the dumped file and
happily to find that it *appears* to me that I only need to edit the
first several lines of the dumped file in addition to such lines:
ALTER FUNCTION <schema name>.my_function(....) OWNER TO ....
With that said, the appearance pattern of source schema name in the
dumped file seems to be predictable. I will study more to make sure
stream editors can do this job.
Many thanks for the hint!
Best Regards, CN
--
http://www.fastmail.com - The professional email service
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: feature request: pg_restore renaming target schema
2015-12-17 07:44 feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
2015-12-17 13:20 ` Re: feature request: pg_restore renaming target schema Shreeyansh Dba <shreeyansh2014@gmail.com>
2015-12-17 15:19 ` Re: feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
2015-12-17 15:34 ` Re: feature request: pg_restore renaming target schema John Scalia <jayknowsunix@gmail.com>
2015-12-17 16:18 ` Re: feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
@ 2015-12-22 16:19 ` CN <cnliou9@fastmail.fm>
0 siblings, 0 replies; 6+ messages in thread
From: CN @ 2015-12-22 16:19 UTC (permalink / raw)
To: pgsql-admin
I am pleased to revoke this request.
Once option -O is applied to pg_dump, only two lines in the dumped file
need to be edited.
In case it is also useful to anyone, here is the script I use to dump
schema "ss" from source database "sd" and restore as new schema "ts" to
database "td". Hard disk is not used as intermediate file is not
created. I have a feeling that it is safe to run this script
concurrently.
#!/bin/sh
SOURCE_DATABASE=sd TARGET_DATABASE=td TEMPLATE_SCHEMA=ss NEW_SCHEMA=ts
pg_dump -O -E utf8 -n $TEMPLATE_SCHEMA -w -U postgres $SOURCE_DATABASE
2>&1 | awk -v source="$TEMPLATE_SCHEMA" -v target="$NEW_SCHEMA" ' BEGIN
{ pattern1="CREATE SCHEMA " source ";"; pattern2="SET
search_path = " source ", pg_catalog;"; pattern1_is_done=0
pattern2_is_done=0 is_data=0; print "BEGIN;"; #Wrap all
statements in single transaction. } { if(!is_data){
if(substr($0,1,2) == "--") $0="";
else if(!pattern1_is_done){ if($0 == pattern1){
$0="CREATE SCHEMA " target ";";
pattern1_is_done=1; } }
else if(!pattern2_is_done){ if($0 == pattern2){
$0="SET search_path TO " target ";";
pattern2_is_done=1; } }
else if(match($0,/^COPY .+ FROM stdin;$/) > 0)
is_data=1; } print } END{ print "COMMIT;";
#Wrap all statements in single transaction. } ' | psql -q -o /dev/null
-w -U postgres $TARGET_DATABASE 2>&1 vacuumdb -q -Z -w -U postgres
$TARGET_DATABASE 2>&1
>
--
http://www.fastmail.com - The professional email service
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2015-12-22 16:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-12-17 07:44 feature request: pg_restore renaming target schema CN <cnliou9@fastmail.fm>
2015-12-17 13:20 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2015-12-17 15:19 ` CN <cnliou9@fastmail.fm>
2015-12-17 15:34 ` John Scalia <jayknowsunix@gmail.com>
2015-12-17 16:18 ` CN <cnliou9@fastmail.fm>
2015-12-22 16:19 ` CN <cnliou9@fastmail.fm>
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