Thread: Transfer database tables to a schema
Folks,
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. from the public schema in a database to a different
schema in a different database, but came up blank. If anyone has a
suggestion it would be appreciated.
Thanks,
Peter Darley
Try this:
(tested for PostgreSQL 7.4)
CREATE OR REPLACE FUNCTION move_relation(VARCHAR, VARCHAR, VARCHAR)
RETURNS BOOL
AS '
-- $1 is the table name
-- $2 is the source schema
-- $3 is the destination schema
--
UPDATE pg_catalog.pg_class
SET relnamespace = (SELECT oid FROM pg_catalog.pg_namespace
WHERE nspname = $3)
WHERE relnamespace = (SELECT oid FROM pg_catalog.pg_namespace
WHERE nspname = $2)
AND relname = $1;
UPDATE pg_catalog.pg_type
SET typnamespace = (SELECT oid FROM pg_catalog.pg_namespace
WHERE nspname = $3)
WHERE typnamespace = (SELECT oid FROM pg_catalog.pg_namespace
WHERE nspname = $2)
AND typname = $1;
SELECT TRUE;
' LANGUAGE SQL;
----- Original Message -----
From: "Peter Darley" <pdarley@kinesis-cem.com>
To: "Pgsql-General" <pgsql-general@postgresql.org>
Sent: Tuesday, February 03, 2004 2:47 AM
Subject: [GENERAL] Transfer database tables to a schema
> Folks,
> 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. from the public schema in a database to a different
> schema in a different database, but came up blank. If anyone has a
> suggestion it would be appreciated.
> Thanks,
> Peter Darley
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
"Chris Travers" <chris@travelamericas.com> writes:
> Try this:
Er ... what about the indexes belonging to the relation?
regards, tom lane
Peter Darley wrote: > I don't have large objects or schemas in the db I want to move, but I do > have languages and functions. I'm not sure if I need to be worried about > collisions there or not. I can't tell weather languages/functions live in > the database, or in a schema of the database. Functions are dumped. They are specific to schemas as well. There could be two functions with same name/signature in different schemas and that is perfectly fine. That's where your schema search path comes into picture. Set it right and everything will be in the right cell. As goes for languages, I am not sure if they are dumped. However you can create them manually if required. There are hardly few dozens of them out there..:-) But adding language is only required if source database had it but not the target database. If two databases are failrly similar, then it should not be needed. <OT> I request you to make a habit to reply all, while replying to postgresql mailing lists. These lists do not set the reply-to to mailing list. I don't know why it is so but I am sure it is been requested in past. I found it difficult to adjust initially but got used to it. Not that it is much of a hassle for me. Its just different than what I am/was used to. </OT> HTH Shridhar
Shridhar,
Thanks again for the info, this is exactly what I needed.
I apologize for not replying to all; I usually do, but I'm out of practice
with this list. :)
Thanks,
Peter Darley
-----Original Message-----
From: Shridhar Daithankar [mailto:shridhar@frodo.hserus.net]
Sent: Wednesday, February 04, 2004 12:19 AM
To: Peter Darley; Pgsql-General
Subject: Re: [GENERAL] Transfer database tables to a schema
Peter Darley wrote:
> I don't have large objects or schemas in the db I want to move, but I do
> have languages and functions. I'm not sure if I need to be worried about
> collisions there or not. I can't tell weather languages/functions live in
> the database, or in a schema of the database.
Functions are dumped. They are specific to schemas as well. There could be
two
functions with same name/signature in different schemas and that is
perfectly
fine. That's where your schema search path comes into picture. Set it right
and
everything will be in the right cell.
As goes for languages, I am not sure if they are dumped. However you can
create
them manually if required. There are hardly few dozens of them out
there..:-)
But adding language is only required if source database had it but not the
target database. If two databases are failrly similar, then it should not be
needed.
<OT>
I request you to make a habit to reply all, while replying to postgresql
mailing
lists. These lists do not set the reply-to to mailing list.
I don't know why it is so but I am sure it is been requested in past. I
found it
difficult to adjust initially but got used to it.
Not that it is much of a hassle for me. Its just different than what I
am/was
used to.
</OT>
HTH
Shridhar
Ok, I see what you mean. I was looking at pg_index, when the indexes are also listed in pg_class. I will have to add this and the pg_depend stuff. Best Wishes, Chris Travers ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Chris Travers" <chris@travelamericas.com> Cc: "Peter Darley" <pdarley@kinesis-cem.com>; "Pgsql-General" <pgsql-general@postgresql.org> Sent: Wednesday, February 04, 2004 12:47 PM Subject: Re: [GENERAL] Transfer database tables to a schema > "Chris Travers" <chris@travelamericas.com> writes: > > Try this: > > Er ... what about the indexes belonging to the relation? > > regards, tom lane > >
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@natserv.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.
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.