X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C3C0E3A1A1F for ; Thu, 6 Jan 2005 11:10:52 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 39700-09 for ; Thu, 6 Jan 2005 11:10:46 +0000 (GMT) Received: from shakti.rupa.com (gw.rupa.com [66.92.32.63]) by svr1.postgresql.org (Postfix) with ESMTP id B21FB3A42E0 for ; Thu, 6 Jan 2005 11:10:47 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by localhost.rupa.com (Postfix) with ESMTP id 0CB7F9423E7 for ; Thu, 6 Jan 2005 03:10:45 -0800 (PST) Received: from shakti.rupa.com ([127.0.0.1]) by localhost (shakti [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 25976-02-3 for ; Thu, 6 Jan 2005 03:10:41 -0800 (PST) Received: from [192.168.1.11] (laptop.rupa.com [192.168.1.11]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by shakti.rupa.com (Postfix) with ESMTP id E4A999423E6 for ; Thu, 6 Jan 2005 03:10:40 -0800 (PST) Message-ID: <41DD1C98.8060004@lists.rupa.com> Date: Thu, 06 Jan 2005 03:10:16 -0800 From: "Rupa Schomaker (lists)" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.5) Gecko/20041206 Thunderbird/1.0 Mnenhy/0.7 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: 7.4 and schema 'maintainance' Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at rupa.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/36 X-Sequence-Number: 15895 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