Обсуждение: Migrating a live database
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
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.
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/
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/
Try to backup schema by schema or table by table starting from those which are being changed rarely.
--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me
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 GellerOn 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
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)
--
Vladimir Rusinov
http://greenmice.info/
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 GellerOn 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/
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.
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