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