Postgres Pro
Downloads
Home   >   mailing lists

Thread: Need suggestion to restructure a database....

Need suggestion to restructure a database....

From
"P Kapat"
Date:
Setup: Postgres 8.1.11 on RedHat EL 5 with only one (password
protected) user: me and this machine does not have a web server. So,
the security issues can be a little bit relaxed!!

I have a bunch of tables which are divided to 4 groups based on some
criteria. So, I was using four different databases. I now have
realized that this is the wrong approach and one should use four
schemas instead. So, I want to mend. Need suggestions, preferably with
codes (either pgsql / linux / pgAdmin3 GUI instructions):

I have created four schemas inside the main db, say scha, schb, schc,
schd. The main db is named maindb, and the four dbs that I am using
currently are dba, dbb, dbc, dbd. maindb already has a public schema
with some tables and views and functions.

How do I transfer the tables,views,indexes,sequences,.... from dba to
scha (b,c,d will follow)?

--
Regards
PK
--------------------------------------
http://counter.li.org  #402424

Re: Need suggestion to restructure a database....

From
"George Pavlov"
Date:
Many ways to do it either by creating schemas in the target DBs, or by
changing search paths (the default one or for the script duration).
Here's a "one-liner",
assuming your 4 original databases do not have multiple schemas within
them:

  psql -dmaindb -c"create schema scha"
  pg_dump -Fp -x -O dba | sed 's/SET search_path = public/SET
search_path = scha/' | psql -dmaindb



> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-
> owner@postgresql.org] On Behalf Of P Kapat
> Sent: Wednesday, December 31, 2008 12:28 PM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Need suggestion to restructure a database....
>
> Setup: Postgres 8.1.11 on RedHat EL 5 with only one (password
> protected) user: me and this machine does not have a web server. So,
> the security issues can be a little bit relaxed!!
>
> I have a bunch of tables which are divided to 4 groups based on some
> criteria. So, I was using four different databases. I now have
> realized that this is the wrong approach and one should use four
> schemas instead. So, I want to mend. Need suggestions, preferably with
> codes (either pgsql / linux / pgAdmin3 GUI instructions):
>
> I have created four schemas inside the main db, say scha, schb, schc,
> schd. The main db is named maindb, and the four dbs that I am using
> currently are dba, dbb, dbc, dbd. maindb already has a public schema
> with some tables and views and functions.
>
> How do I transfer the tables,views,indexes,sequences,.... from dba to
> scha (b,c,d will follow)?
>
> --
> Regards
> PK
> --------------------------------------
> http://counter.li.org  #402424
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

Re: Need suggestion to restructure a database....

From
"P Kapat"
Date:
Thanks George:

On 12/31/08, George Pavlov <gpavlov@mynewplace.com> wrote:
> Many ways to do it either by creating schemas in the target DBs, or by
> changing search paths (the default one or for the script duration).
> Here's a "one-liner",
> assuming your 4 original databases do not have multiple schemas within
> them:

correct assumption, all of my DBs, have only the public schema

>   psql -dmaindb -c"create schema scha"
>   pg_dump -Fp -x -O dba | sed 's/SET search_path = public/SET
> search_path = scha/' | psql -dmaindb

Looks simple! After this, would I be able to delete/drop/cascade dba?

--
Regards
PK
--------------------------------------
http://counter.li.org  #402424