Moving several databases into one database with several schemas

Em 05/09/2012 15:30, Edson Richter escreveu:

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.

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

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

2012/9/7 Merlin Moncure <mmoncure@gmail.com>

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

Great stuff, Merlin! ;-)

--
// Dmitriy.