pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMigrating a live database
8+ messages / 6 participants
[nested] [flat]
* Migrating a live database
@ 2011-01-18 05:07 Vladislav Geller <vladislav.geller@vincorex.ch>
0 siblings, 3 replies; 8+ messages in thread
From: Vladislav Geller @ 2011-01-18 05:07 UTC (permalink / raw)
To: pgsql-admin
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?
Regards,
Vladislav Geller
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Migrating a live database
@ 2011-01-18 06:30 Scott Marlowe <scott.marlowe@gmail.com>
parent: Vladislav Geller <vladislav.geller@vincorex.ch>
2 siblings, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2011-01-18 06:30 UTC (permalink / raw)
To: Vladislav Geller <vladislav.geller@vincorex.ch>; +Cc: pgsql-admin
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 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?
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Migrating a live database
@ 2011-01-18 07:39 Vladimir Rusinov <vladimir@greenmice.info>
parent: Vladislav Geller <vladislav.geller@vincorex.ch>
2 siblings, 2 replies; 8+ messages in thread
From: Vladimir Rusinov @ 2011-01-18 07:39 UTC (permalink / raw)
To: Vladislav Geller <vladislav.geller@vincorex.ch>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Migrating a live database
@ 2011-01-18 07:49 Vladislav Geller <vladislav.geller@vincorex.ch>
parent: Vladimir Rusinov <vladimir@greenmice.info>
1 sibling, 2 replies; 8+ messages in thread
From: Vladislav Geller @ 2011-01-18 07:49 UTC (permalink / raw)
To: Vladimir Rusinov <vladimir@greenmice.info>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Migrating a live database
@ 2011-01-18 08:09 Viktor Bojović <viktor.bojovic@gmail.com>
parent: Vladislav Geller <vladislav.geller@vincorex.ch>
1 sibling, 0 replies; 8+ messages in thread
From: Viktor Bojović @ 2011-01-18 08:09 UTC (permalink / raw)
To: Vladislav Geller <vladislav.geller@vincorex.ch>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Migrating a live database
@ 2011-01-18 08:10 Vladimir Rusinov <vladimir@greenmice.info>
parent: Vladislav Geller <vladislav.geller@vincorex.ch>
1 sibling, 0 replies; 8+ messages in thread
From: Vladimir Rusinov @ 2011-01-18 08:10 UTC (permalink / raw)
To: Vladislav Geller <vladislav.geller@vincorex.ch>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Migrating a live database
@ 2011-01-18 11:32 Jasen Betts <jasen@xnet.co.nz>
parent: Vladislav Geller <vladislav.geller@vincorex.ch>
2 siblings, 0 replies; 8+ messages in thread
From: Jasen Betts @ 2011-01-18 11:32 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Migrating a live database
@ 2011-01-18 14:55 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Vladimir Rusinov <vladimir@greenmice.info>
1 sibling, 0 replies; 8+ messages in thread
From: Scott Ribe @ 2011-01-18 14:55 UTC (permalink / raw)
To: Vladimir Rusinov <vladimir@greenmice.info>; +Cc: Vladislav Geller <vladislav.geller@vincorex.ch>; pgsql-admin
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't change, 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-01-18 14:55 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-18 05:07 Migrating a live database Vladislav Geller <vladislav.geller@vincorex.ch>
2011-01-18 06:30 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-01-18 07:39 ` Vladimir Rusinov <vladimir@greenmice.info>
2011-01-18 07:49 ` Vladislav Geller <vladislav.geller@vincorex.ch>
2011-01-18 08:09 ` Viktor Bojović <viktor.bojovic@gmail.com>
2011-01-18 08:10 ` Vladimir Rusinov <vladimir@greenmice.info>
2011-01-18 14:55 ` Scott Ribe <scott_ribe@elevated-dev.com>
2011-01-18 11:32 ` Jasen Betts <jasen@xnet.co.nz>
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