Thread: Setting Schema on Restore
Howdy,
Environment:
Solaris 10
Postgres 8.3.11
I’m running a dump/restore in a shell script. There are 3 machines involved. Host1 where the shell script is being run from, host2 where the database is being dumped from, host3 where the database is being restored to. Shell script looks like this:
/usr/postgres/8.3/bin/pg_dump -h <host2> <database> | /usr/postgres/8.3/bin/psql -h <host3> <database>
I want to restore into a specific schema. Psql does not provide a switch for this. I tried this before the pg_dump command above:
Echo “
SET search_path TO <schema>;
“ | /usr/postgres/8.3/bin/psql -h <host3> <database>
Which didn’t work. I also tried this before the pg_dump command:
PGOPTIONS=”—search_path=<schema>”
Which doesn’t work due to the script running from a different machine.
Does anyone have any ideas on how I can achieve this, please?
Thank you,
Sam
Samuel Stearns <SStearns@internode.com.au> writes:
> I want to restore into a specific schema.
The only way to do that is to hand-edit the dump script.
If you're feeling brave you could try letting "sed" do it; but
unless the schema name being replaced is pretty unusual, the
risk of false matches seems high.
regards, tom lane
Thanks, Tom!
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, 1 December 2010 1:37 AM
To: Samuel Stearns
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Setting Schema on Restore
Samuel Stearns <SStearns@internode.com.au> writes:
> I want to restore into a specific schema.
The only way to do that is to hand-edit the dump script.
If you're feeling brave you could try letting "sed" do it; but
unless the schema name being replaced is pretty unusual, the
risk of false matches seems high.
regards, tom lane