From: CN <cnliou9@fastmail.fm>
To: pgsql-admin@postgresql.org
Subject: Re: feature request: pg_restore renaming target schema
Date: Wed, 23 Dec 2015 00:19:14 +0800
Message-ID: <1450801154.79237.474120345.758EF3FA@webmail.messagingengine.com> (raw)
In-Reply-To: <1450369108.2740801.470182945.59E4E975@webmail.messagingengine.com>
References: <1450338245.2626908.469822713.6C14EE8F@webmail.messagingengine.com>
<CAGDYbUNK2tQZz4SfFBZit8o5wqHgy3Hz3SOgVhxkHh3aZ1DOwg@mail.gmail.com>
<1450365586.2726209.470114761.326B7E39@webmail.messagingengine.com>
<CABzCKRCYkP400=0ZqOKPsYEb7twKjX8fcQ96hFMGf+6ZsGys=A@mail.gmail.com>
<1450369108.2740801.470182945.59E4E975@webmail.messagingengine.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: cnliou9@fastmail.fm
Subject: Re: feature request: pg_restore renaming target schema
In-Reply-To: <1450801154.79237.474120345.758EF3FA@webmail.messagingengine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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