pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




Message-ID: <41DD1C98.8060004@lists.rupa.com>
Permalink:  ../41DD1C98.8060004@lists.rupa.com/
Also on:    postgresql.org/message-id/41DD1C98.8060004@lists.rupa.com

 · 

reply

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