Thread: Migrating a live database

Migrating a live database

From
Vladislav Geller
Date:
Hello,

I'm currently in the process of migrating a huge live database from one part of the world to the other (for local use
anddata analysis). The bandwidth does not allow me to get a decent transfer speed. Furthermore i can not migrate during
businesshours since the connection is critical. This leaves me with a timeframe of 9 hours a day where i can migrate
thisdatabase. Does anyone have experience he is willing to share in respect how to migrate such databases?  

Regards,
Vladislav Geller

Re: Migrating a live database

From
Scott Marlowe
Date:
On Mon, Jan 17, 2011 at 10:07 PM, Vladislav Geller
<vladislav.geller@vincorex.ch> wrote:
> Hello,
>
> I'm currently in the process of migrating a huge live database from one part of the world to the other (for local use
anddata analysis). The bandwidth does not allow me to get a decent transfer speed. Furthermore i can not migrate during
businesshours since the connection is critical. This leaves me with a timeframe of 9 hours a day where i can migrate
thisdatabase. Does anyone have experience he is willing to share in respect how to migrate such databases? 

Do you have the bandwidth to transfer the whole database in those 9 hours?
Or, can you set up replication using slony / PITR / Bucardo /
Londiste, and use traffic shaping to make sure it doesn't flood your
network and wait a week or two til it catches up?

Those are the only two answers I can think of.

Re: Migrating a live database

From
Vladimir Rusinov
Date:


On Tue, Jan 18, 2011 at 8:07 AM, Vladislav Geller <vladislav.geller@vincorex.ch> wrote:
Hello,

I'm currently in the process of migrating a huge live database from one part of the world to the other (for local use and data analysis). The bandwidth does not allow me to get a decent transfer speed. Furthermore i can not migrate during business hours since the connection is critical. This leaves me with a timeframe of 9 hours a day where i can migrate this database. Does anyone have experience he is willing to share in respect how to migrate such databases?

run pg_start_backup() and use rsync on live data files to transfer them.
If sync won't finish in 9 hours, abort it, run pg_stop_backup() and continue next day - I assume most of the data won't change, so rsync won't re-transfer it (but will calculate hash and compare).

--
Vladimir Rusinov
http://greenmice.info/

Re: Migrating a live database

From
Vladislav Geller
Date:
HI,

The problem with this solution is that i don't have enough space on the targeted live to implement such a procedure. It would have to work by pulling data directly off the live server. The issue with new data whilst the procedure is running (over a span of days or weeks in not severe as each daily activity generates new tables) 

As  someone else mentioned - I am against implementing any kind of replication as the live server will only have a subset of the data after i'm migrating thus making replication useless. And i also don't have the bandwidth to pull out the data in one day. 

Regards,

Vladislav Geller


On Jan 18, 2011, at 8:39 AM, Vladimir Rusinov wrote:



On Tue, Jan 18, 2011 at 8:07 AM, Vladislav Geller <vladislav.geller@vincorex.ch> wrote:
Hello,

I'm currently in the process of migrating a huge live database from one part of the world to the other (for local use and data analysis). The bandwidth does not allow me to get a decent transfer speed. Furthermore i can not migrate during business hours since the connection is critical. This leaves me with a timeframe of 9 hours a day where i can migrate this database. Does anyone have experience he is willing to share in respect how to migrate such databases?

run pg_start_backup() and use rsync on live data files to transfer them.
If sync won't finish in 9 hours, abort it, run pg_stop_backup() and continue next day - I assume most of the data won't change, so rsync won't re-transfer it (but will calculate hash and compare).

--
Vladimir Rusinov
http://greenmice.info/

Re: Migrating a live database

From
Viktor Bojović
Date:
Try to backup schema by schema or table by table starting from those which are being changed rarely.

On Tue, Jan 18, 2011 at 8:49 AM, Vladislav Geller <vladislav.geller@vincorex.ch> wrote:
HI,

The problem with this solution is that i don't have enough space on the targeted live to implement such a procedure. It would have to work by pulling data directly off the live server. The issue with new data whilst the procedure is running (over a span of days or weeks in not severe as each daily activity generates new tables) 

As  someone else mentioned - I am against implementing any kind of replication as the live server will only have a subset of the data after i'm migrating thus making replication useless. And i also don't have the bandwidth to pull out the data in one day. 

Regards,

Vladislav Geller


On Jan 18, 2011, at 8:39 AM, Vladimir Rusinov wrote:



On Tue, Jan 18, 2011 at 8:07 AM, Vladislav Geller <vladislav.geller@vincorex.ch> wrote:
Hello,

I'm currently in the process of migrating a huge live database from one part of the world to the other (for local use and data analysis). The bandwidth does not allow me to get a decent transfer speed. Furthermore i can not migrate during business hours since the connection is critical. This leaves me with a timeframe of 9 hours a day where i can migrate this database. Does anyone have experience he is willing to share in respect how to migrate such databases?

run pg_start_backup() and use rsync on live data files to transfer them.
If sync won't finish in 9 hours, abort it, run pg_stop_backup() and continue next day - I assume most of the data won't change, so rsync won't re-transfer it (but will calculate hash and compare).

--
Vladimir Rusinov
http://greenmice.info/




--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me

Re: Migrating a live database

From
Vladimir Rusinov
Date:
You don't need a lot of additional space to run pg_start_backup(). Read following: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html (see 24.3.2)

On Tue, Jan 18, 2011 at 10:49 AM, Vladislav Geller <vladislav.geller@vincorex.ch> wrote:
HI,

The problem with this solution is that i don't have enough space on the targeted live to implement such a procedure. It would have to work by pulling data directly off the live server. The issue with new data whilst the procedure is running (over a span of days or weeks in not severe as each daily activity generates new tables) 

As  someone else mentioned - I am against implementing any kind of replication as the live server will only have a subset of the data after i'm migrating thus making replication useless. And i also don't have the bandwidth to pull out the data in one day. 

Regards,

Vladislav Geller


On Jan 18, 2011, at 8:39 AM, Vladimir Rusinov wrote:



On Tue, Jan 18, 2011 at 8:07 AM, Vladislav Geller <vladislav.geller@vincorex.ch> wrote:
Hello,

I'm currently in the process of migrating a huge live database from one part of the world to the other (for local use and data analysis). The bandwidth does not allow me to get a decent transfer speed. Furthermore i can not migrate during business hours since the connection is critical. This leaves me with a timeframe of 9 hours a day where i can migrate this database. Does anyone have experience he is willing to share in respect how to migrate such databases?

run pg_start_backup() and use rsync on live data files to transfer them.
If sync won't finish in 9 hours, abort it, run pg_stop_backup() and continue next day - I assume most of the data won't change, so rsync won't re-transfer it (but will calculate hash and compare).

--
Vladimir Rusinov
http://greenmice.info/




--
Vladimir Rusinov
http://greenmice.info/

Re: Migrating a live database

From
Jasen Betts
Date:
On 2011-01-18, Vladislav Geller <vladislav.geller@vincorex.ch> wrote:
> Hello,
>
> I'm currently in the process of migrating a huge live database from
> one part of the world to the other (for local use and data analysis).
> The bandwidth does not allow me to get a decent transfer speed.
> Furthermore i can not migrate during business hours since the
> connection is critical. This leaves me with a timeframe of 9 hours a
> day where i can migrate this database. Does anyone have experience he
> is willing to share in respect how to migrate such databases?

how about using replication?

eg: slony1 or wal-shipping

compression will help too, but I assume you're already doing that.


Re: Migrating a live database

From
Scott Ribe
Date:
On Jan 18, 2011, at 12:39 AM, Vladimir Rusinov wrote:

> If sync won't finish in 9 hours, abort it, run pg_stop_backup() and continue next day - I assume most of the data
won'tchange, so rsync won't re-transfer it (but will calculate hash and compare). 

And there's --partial in case you have to stop it in the middle of a large file.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice