From: Rupa Schomaker (lists) <pgsql-admin@lists.rupa.com>
To: pgsql-admin@postgresql.org
Subject: 7.4 and schema 'maintainance'
Date: Thu, 06 Jan 2005 03:10:16 -0800
Message-ID: <41DD1C98.8060004@lists.rupa.com> (raw)
So.. I wanted to move some tables out of 'public' and put 'em into a
schema. Seemed like a pretty simple thing to do.
The problem is that the data export includes the search path. So, it
overrides the default $user,public and forces public.
If I export with a filetype of plain then I can edit the search path in
the file and then import just fine. I haven't seen what will happend
when I try to do this with large tables with large bytea fields (yes I
have 'em). plain doesn't support blobs but do I need hte --blobs option
for bytea or just lo objects?
I tried the -O option but that just overides the user, not the
search_path (maybe search_path should be left alone if -O is specified?).
Is there another way to do this?
Commands I did to do it:
for i in $tist; do pg_dump \
--format=p --schema-only --table=$i --file $i.schema \
-U $user $db; done
# edit schema file here, modify search_path
for i in $tlist; do pg_dump --format=p -v --data-only \
--table $i --file $i.sql \
-U $user $db; done
# edit sql file here, modify search_path
for i in $tlist; do psql -f $i.schema -U $user $db ; done
for i in $tlist; do psql -f $i.sql -U $user $db ; done
Just realized I didn't really need to create a .schema and a .sql file
-- I thought I was going to be able to get away with just 1 and then
restoring from my -Fc type backups and then when that didn't work
created the .sql files.
--
-Rupa
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: pgsql-admin@lists.rupa.com
Subject: Re: 7.4 and schema 'maintainance'
In-Reply-To: <41DD1C98.8060004@lists.rupa.com>
References: <41DD1C98.8060004@lists.rupa.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