Re: Transfer database tables to a schema

Francisco,
What I ended up doing was to dump the database, edit the 'SET
search_path=public,pg_system' to be 'SET search_path=newschema,pg_system',
CREATE SCHEMA newschema;, then use psql to read the dump back into the new
database. It was very easy and worked flawlessly.
Thanks,
Peter Darley

-----Original Message-----
From: Francisco [mailto:francisco(at)natserv(dot)net]
Sent: Tuesday, February 10, 2004 6:54 AM
To: Peter Darley
Cc: Pgsql-General
Subject: Re: [GENERAL] Transfer database tables to a schema

On Mon, 2 Feb 2004, Peter Darley wrote:

> I have a couple of databases that should really be schemas in the same
> database. I tried to find suggestions on how to easily move all the
> tables/sequences/etc.

I would not call it easy, but what I have done in the past is:
* Create users with access only to schemas you want to copy data to.
* Dump database(s)
* Log with user(s) with restricted schema. Restore from the dumps.

Last time I did this was with 7.3.X and it worked fine. I did not have
multiple schemas in the databases the data was coming from. I think you
may need to do more work and dump each schema separately if your source
DBs have multiple schemas.

You may also be able to do a dump of the entire DB and manually delete the
schema info, but you should be very carefull if you try that approach.