Re: two databases in one

Soulabaille Samantha wrote:
> Hello,
>
> I'm working with a postgresql 7.4.7
> I've got two databases with the same tables and schema on two different
> servers.
> I want to have only one database.
> So i would like to insert in the first database the data of the second
> database.
> I don't know how to do this because almost of the tables contains serial.
> It's for production databases, i can't make mistakes.
>
> Can anyone help me? It's urgent.

Whatever you do, I would suggest doing it in a test environment first to
work out any problems. You can also get some ideas of the time required
and possibly tune the process. And of course make a good backup first.

I would examine all the tables to determine whether there is likely to
be an underlying data collision (duplicate id in both places where
unique is required). If the only place that is likely to happen is with
sequences then first make a list of them:

select * from pg_class where relkind = 'S';

You will need to "make room" in the main tables for the data from the
tables from the other database. I would look at the tables influenced by
the sequence in question like this:

select min(some_serial), max(some_serial),
max(some_serial)-min(some_serial) as range from some_table;

-[ RECORD 1 ]----
min   | 104973931
max   | 111209210
range | 6235279

So you need a gap of 6235279 ids in the target table so check the status
of the target sequence then advance it as appropriate  - give yourself
whatever cushion is appropriate given how rapidly the sequence is used.

select * from some_sequence ;

-[ RECORD 1 ]-+--------------------
sequence_name | some_sequence
last_value    | 111209231
increment_by  | 1
max_value     | 9223372036854775807
min_value     | 100000000
cache_value   | 1
log_cnt       | 25
is_cycled     | f
is_called     | t


select setval('some_sequence', 111209231 + 6500000);

Then you need to move the source data sequences so they will land in the
gap so in this case they need to be moved forward by
111209231-104973931+1 so run the appropriate updates on the source database.

This is a basic example which assumes you can shut down the source
database (sequences stop incrementing) prior to the transfer. You may
also need to examine multiple tables if they all rely on the same sequence.

You will need to also determine the order in which to put data back into
the target server in order to avoid violating foreign-key restrictions.

Sounds like a headache. Good luck.

Cheers,
Steve