Thread: Moving several databases into one database with several schemas

Dear list,

Scenario:

I'm using PostgreSQL 9.1 on Linux x64 running over CentOS 5. Everything is fine, but now I do have 4 separate databases running on different servers, and every server has some shared tables.

I've been working on a complex logic that is able to "replicate" these tables in a way that is almost impossible to use existing solutions (I've to merge records when replicating). My conclusion is that the logic is just too complex to maintain, and I would like to consolidate databases but keep data separate. Few days ago, I've posted this query in this list, and got the orientation to try using schemas.

That's what I want to do know: I would like to consolidate these 4 separate databases in 1 database with 5 schemas:

- Main schema: will have all shared tables, that will be read only most of time;
- Schema1 to Schema4: will have their own tables, read write.

Now the questions:

1) Is there a way to "backup" database1 and "restore" in the consolidated database, but in "schema1" (not overwriting everything)?
2) Is there a way to specify the default schema in JDBC url (or command I can issue to change the default schema at runtime, like "set path...")?

Thanks in advance,

Edson Richter

Dear list,

Scenario:

I'm using PostgreSQL 9.1 on Linux x64 running over CentOS 5. Everything is fine, but now I do have 4 separate databases running on different servers, and every server has some shared tables.

I've been working on a complex logic that is able to "replicate" these tables in a way that is almost impossible to use existing solutions (I've to merge records when replicating). My conclusion is that the logic is just too complex to maintain, and I would like to consolidate databases but keep data separate. Few days ago, I've posted this query in this list, and got the orientation to try using schemas.

That's what I want to do know: I would like to consolidate these 4 separate databases in 1 database with 5 schemas:

- Main schema: will have all shared tables, that will be read only most of time;
- Schema1 to Schema4: will have their own tables, read write.

Now the questions:

1) Is there a way to "backup" database1 and "restore" in the consolidated database, but in "schema1" (not overwriting everything)?
2) Is there a way to specify the default schema in JDBC url (or command I can issue to change the default schema at runtime, like "set path...")?

Thanks in advance,

Edson Richter

I've tried following command (on Windows platform), but command returns without any import, and "exit code 0" (output translated, because I do use PT-BR):

---------------------------------------------------------------------------------------------------------------------------------------------
pg_restore.exe --host localhost --port 5432 --username "postgres" --dbname "consolidado" --role "MyUser" --no-password  --schema main --verbose "E:\backups\maindatabase.bk"
pg_restore: connecting to database for restore

Process returned exit code 0.
---------------------------------------------------------------------------------------------------------------------------------------------

I'm sure database is running, backup file exists, everything seems to be fine - except that nothing is imported.
I could not find directions in documentation. I suspect that I'll not be able to use Custom format for backups...

Please, help!

Edson

Edson Richter wrote:
> That's what I want to do know: I would like to consolidate these 4
separate databases in 1
> database with 5 schemas:
>
> - Main schema: will have all shared tables, that will be
>   read only most of time;
> - Schema1 to Schema4: will have their own tables, read write.
>
> Now the questions:
>
> 1) Is there a way to "backup" database1 and "restore" in the
> consolidated database, but in
> "schema1" (not overwriting everything)?

There is no simple way.
You could pg_dump in plain format (-F p) and edit the SQL file,
but that's cumbersome and error-prone.

What I would try to do is restore the dump as it is in
a new database, rename the schema, e.g.

ALTER SCHEMA public RENAME TO schema1;

Then pg_dump that and restore it into the destination database.
Adjust the schema permissions as desired.

> 2) Is there a way to specify the default schema in JDBC url
> (or command I can issue to change
> the default schema at runtime, like "set path...")?

SET search_path=schema1,schema2,public;

> I've tried following command (on Windows platform), but command
returns without any import, and "exit
> code 0" (output translated, because I do use PT-BR):

> pg_restore.exe --host localhost --port 5432 --username "postgres"
--dbname "consolidado" --role
> "MyUser" --no-password  --schema main --verbose
"E:\backups\maindatabase.bk"
> pg_restore: connecting to database for restore
>
> Process returned exit code 0.

That will try to restore schema "main" from the dump.
If there is no such schema in the dump (in the original
database), it will do nothing.

Yours,
Laurenz Albe

Em 06/09/2012 05:12, Albe Laurenz escreveu:
> Edson Richter wrote:
>> That's what I want to do know: I would like to consolidate these 4
> separate databases in 1
>> database with 5 schemas:
>>
>> - Main schema: will have all shared tables, that will be
>>    read only most of time;
>> - Schema1 to Schema4: will have their own tables, read write.
>>
>> Now the questions:
>>
>> 1) Is there a way to "backup" database1 and "restore" in the
>> consolidated database, but in
>> "schema1" (not overwriting everything)?
> There is no simple way.
> You could pg_dump in plain format (-F p) and edit the SQL file,
> but that's cumbersome and error-prone.
>
> What I would try to do is restore the dump as it is in
> a new database, rename the schema, e.g.
>
> ALTER SCHEMA public RENAME TO schema1;
>
> Then pg_dump that and restore it into the destination database.
> Adjust the schema permissions as desired.
Ok, seems the way to go. No big deal, just few hours of work to the
cicle "restore in a tempdb", "rename schema", "backup schema", "restore
in consolidated".
>
>> 2) Is there a way to specify the default schema in JDBC url
>> (or command I can issue to change
>> the default schema at runtime, like "set path...")?
> SET search_path=schema1,schema2,public;

Problem is that my application uses JDBC and Connection Pooling. After a
connection is closed, I'll have to set search path again, and again...
Nevertheless, connection pool allows me to have one command to test is
connection is available, I'll try to put SET search_path on there, and
see results. The search path for schema1 will be

SET search_path=schema1,main,public;

>
>> I've tried following command (on Windows platform), but command
> returns without any import, and "exit
>> code 0" (output translated, because I do use PT-BR):
>> pg_restore.exe --host localhost --port 5432 --username "postgres"
> --dbname "consolidado" --role
>> "MyUser" --no-password  --schema main --verbose
> "E:\backups\maindatabase.bk"
>> pg_restore: connecting to database for restore
>>
>> Process returned exit code 0.
> That will try to restore schema "main" from the dump.
> If there is no such schema in the dump (in the original
> database), it will do nothing.

Ok, thanks for the clarification.
I'll share my experience and results after I finish this..

Regards,

Edson.

>
> Yours,
> Laurenz Albe
>
>

Edson Richter wrote:
>>> 2) Is there a way to specify the default schema in JDBC url
>>> (or command I can issue to change
>>> the default schema at runtime, like "set path...")?

>> SET search_path=schema1,schema2,public;

> Problem is that my application uses JDBC and Connection Pooling. After
a
> connection is closed, I'll have to set search path again, and again...
> Nevertheless, connection pool allows me to have one command to test is
> connection is available, I'll try to put SET search_path on there, and
> see results.

I see.
You could change the default setting for the user with

ALTER ROLE someuser SET search_path=...

Yours,
Laurenz Albe

Em 06/09/2012 09:21, Albe Laurenz escreveu:
> Edson Richter wrote:
>>>> 2) Is there a way to specify the default schema in JDBC url
>>>> (or command I can issue to change
>>>> the default schema at runtime, like "set path...")?
>>> SET search_path=schema1,schema2,public;
>> Problem is that my application uses JDBC and Connection Pooling. After
> a
>> connection is closed, I'll have to set search path again, and again...
>> Nevertheless, connection pool allows me to have one command to test is
>> connection is available, I'll try to put SET search_path on there, and
>> see results.
> I see.
> You could change the default setting for the user with
>
> ALTER ROLE someuser SET search_path=...
That is perfect! I can have separate users for each application, and
then they will have the correct search path.
You saved my day,

Thank you very much!

Edson
>
> Yours,
> Laurenz Albe
>
>

On 09/06/12 5:30 AM, Edson Richter wrote:
>> You could change the default setting for the user with
>>
>> ALTER ROLE someuser SET search_path=...
> That is perfect! I can have separate users for each application, and
> then they will have the correct search path.
> You saved my day,

the default search_path is $USER,public, so by naming your schema's to
the usernames, you don't even need to alter role...



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

Em 06/09/2012 15:40, John R Pierce escreveu:
> On 09/06/12 5:30 AM, Edson Richter wrote:
>>> You could change the default setting for the user with
>>>
>>> ALTER ROLE someuser SET search_path=...
>> That is perfect! I can have separate users for each application, and
>> then they will have the correct search path.
>> You saved my day,
>
> the default search_path is $USER,public, so by naming your schema's to
> the usernames, you don't even need to alter role...
>
Wonderful, this would have the effect I expect that the connection
defines the path. Then I'll use user to select the specific schema, and
the "public" schema as the main schema.

Thanks to you all, I think I have everything needed to put my migration
project in practice.

Regards,

Edson.

On Thu, Sep 6, 2012 at 10:12 PM, Edson Richter <edsonrichter@hotmail.com> wrote:
> Em 06/09/2012 15:40, John R Pierce escreveu:
>
>> On 09/06/12 5:30 AM, Edson Richter wrote:
>>>>
>>>> You could change the default setting for the user with
>>>>
>>>> ALTER ROLE someuser SET search_path=...
>>>
>>> That is perfect! I can have separate users for each application, and then
>>> they will have the correct search path.
>>> You saved my day,
>>
>>
>> the default search_path is $USER,public, so by naming your schema's to the
>> usernames, you don't even need to alter role...
>>
> Wonderful, this would have the effect I expect that the connection defines
> the path. Then I'll use user to select the specific schema, and the "public"
> schema as the main schema.
>
> Thanks to you all, I think I have everything needed to put my migration
> project in practice.

I do this exact thing frequently.  I route everything through dumps.
Here's some roughed out bash script for ya.. The basic MO is to
restore hack the restore script with sed, restoring to a scratch
schema so that the drop/reload of the client private schema can be
deferred until the data is already loaded.

function load_client {

  client=$1
  database=master_db

  echo "[`date`] Loading $client "

  psql -c "update client set load_started = now(), LoadedPO = NULL
where name = '$client';" $database
  <get backup database and place into $client.current.gz>
  psql -c "drop schema if exists ${client}_work cascade" $database
2>&1 | grep ERROR
  psql -c "create schema ${client}_work" $database 2>&1 | grep ERROR
  gzip -cd $client.backup.gz | sed "s/^SET search_path = public/SET
search_path=${client}_work/" | psql -XAq $database 2>&1 | grep ERROR |
grep -v "plpgsql"
  psql -c "begin; drop schema if exists ${client} cascade; alter
schema ${client}_work rename to $client; commit;" $database
  psql -c "update client set load_finished = now() where name =
'$client';" $database
  rm -f $client.current.gz
}

To cut restore time down I run them in parallel:

NUM_FORKS=4

function do_parallel {
  while [ `jobs | wc -l` -ge $NUM_FORKS ]
  do
    sleep 1
  done

  "$@" &
}

Then it's just a matter of:
<get $clients somehow>
for client in $clients
do
  do_parallel load_client $client
done

merlin

--
// Dmitriy.