Обсуждение: feature request: pg_restore renaming target schema
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
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
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
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
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
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
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