Re: Migrate 2 DB's - v8.3 - Mailing list pgsql-general

Jeff,


is (temporarily) migrating the whole cluster an option? What I have in mind is roughly this:
- rsync/copy complete db dir to target (with src still being in production), throttle/repeat as necessary
- stop source db
- rsync again
- start src + target dbs
- drop moved databases in src
- drop unwanted databases in target

That way you could have minimal downtime (seconds to minutes) at the expense of temporary disk usage on the target
host.
Additional bonus: it's all standard Postgres tools (in contrast to e.g. a fancy trigger-based replication) and will
alsokeep any statistics and analyzes. 



Best regards,

-hannes




Am 27. Mai 2016 23:23:04 MESZ, schrieb Jeff Baldwin <tarheeljeff@gmail.com>:
>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@gmail.com>
>wrote:
>
>>
>>
>> On Fri, May 27, 2016 at 5:09 PM, Jeff Baldwin <tarheeljeff@gmail.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@gmail.com>
>>> wrote:
>>>
>>>>
>>>> On Fri, May 27, 2016 at 4:56 PM, Jeff Baldwin
><tarheeljeff@gmail.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.
>>