pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steve Crawford <scrawford@pinpointresearch.com>
To: Soulabaille Samantha <samantha.soulabaille@sihpic.fr>
Cc: pgsql-admin@postgresql.org
Subject: Re: two databases in one
Date: Wed, 16 Aug 2006 16:14:14 -0700
Message-ID: <44E3A6C6.9030702@pinpointresearch.com> (raw)
In-Reply-To: <44E2C5A9.4070505@sihpic.fr>
References: <44E2C5A9.4070505@sihpic.fr>

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



view thread (6+ messages)  latest in thread

Message-ID: <44E3A6C6.9030702@pinpointresearch.com>
Permalink:  ../44E3A6C6.9030702@pinpointresearch.com/
Also on:    postgresql.org/message-id/44E3A6C6.9030702@pinpointresearch.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: scrawford@pinpointresearch.com, samantha.soulabaille@sihpic.fr
  Subject: Re: two databases in one
  In-Reply-To: <44E3A6C6.9030702@pinpointresearch.com>
  References: <44E2C5A9.4070505@sihpic.fr> <44E3A6C6.9030702@pinpointresearch.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox