Re: Migrate 2 DB's - v8.3

On Fri, May 27, 2016 at 5:23 PM, Jeff Baldwin <tarheeljeff(at)gmail(dot)com> wrote:

> Thanks Melvin.
>
> I have done just this, and the time required to dump/restore in this
> manner far exceeds the outage window we can afford to have (max of 2hrs).
> I am looking for alternatives to the standard dump/restore that might help
> me save time.
>
> For instance... if I could do a continuous rsync of only the 2 DB's in
> question. Then stop the source DB and sync only the delta to the target,
> or something along those lines. I've also been looking at barman and
> Slony to see if they might fit the bill as well.
>
> Thanks again for the replies.
>
> Jeff
>
> On Fri, May 27, 2016 at 5:18 PM Melvin Davidson <melvin6925(at)gmail(dot)com>
> wrote:
>
>>
>>
>> On Fri, May 27, 2016 at 5:09 PM, Jeff Baldwin <tarheeljeff(at)gmail(dot)com>
>> wrote:
>>
>>> Melvin,
>>>
>>> Thank you for taking the time to reply to my question.
>>>
>>> Below are the details you have requested:
>>>
>>> SOURCE:
>>> CentOS release 4.6
>>> Postgres 8.3
>>>
>>> TARGET:
>>> CentOS release 6.2
>>> Postgres 8.3
>>>
>>> Kind Regards,
>>> Jeff
>>>
>>> On Fri, May 27, 2016 at 5:05 PM Melvin Davidson <melvin6925(at)gmail(dot)com>
>>> wrote:
>>>
>>>>
>>>> On Fri, May 27, 2016 at 4:56 PM, Jeff Baldwin <tarheeljeff(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>> Hello,
>>>>>
>>>>> I am working to migrate 2 DB's (not the entire postgres instance),
>>>>> from 1 host to another... and I need some guidance on the best
>>>>> approach/practice.
>>>>>
>>>>> I have migrated ~25 other DB's in this environment, and I was able to
>>>>> use pg_dump/pgrestore for those, and it worked fine. These final 2 are
>>>>> live DB's, and I need to move them with minimal downtime (1-2hrs is
>>>>> acceptable).
>>>>>
>>>>> The DB's are blob DB's that are 45 and 90G in size, and are in the
>>>>> same Data Center, with 1G connection in between
>>>>>
>>>>> I am running postres 8.3 (I know :) ), so there may be some
>>>>> limitations there as well.
>>>>>
>>>>> Any help/guidance on the best way to approach this, are greatly
>>>>> appreciated.
>>>>>
>>>>> Kind Regards,
>>>>> Jeff
>>>>>
>>>>
>>>> Well generically speaking, since you are migrating from 8.3, you are
>>>> limited to pg_dump in plain format.
>>>> It would be nice (important) to know the PostgreSQL version you are
>>>> migrating to, as well as what O/S you are working with.
>>>>
>>>>
>>>> --
>>>> *Melvin Davidson*
>>>> I reserve the right to fantasize. Whether or not you
>>>> wish to share my fantasy is entirely up to you.
>>>>
>>>
>> OK, well since both PostgreSQL versions are the same, then you can use
>> custom format.
>>
>> I would first by creating a testdb in the target server. Then export one
>> small table in customer format and verify that you can use pg_restore to
>> load to
>> the testdb. If that works, time how long a full dump takes in the old
>> server as a start point. Then time how long it takes to do a full load into
>> testdb.
>> You will then know how big of a window you need for migrating.
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize. Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
Well, Slony certainly will do the trick.
Keep in mind you will need to do schema only first to the slave.
You set up replication from the old server with the db on the new server as
the slave. Then you initiate replication. It will probably take a long time
to
replicate, but then you have the option to promote the slave at your time
preference (IE: your 2 hr window). It should only take a few minutes for
Slony to do the switchover, but the best thing to do is a dry run first.
IOW, you'll have to do the whole thing twice to get an accurate switch time,
but you won't need to change your network until you are ready to go live.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.