Re: Migrate 2 DB's - v8.3

Hannes,

Thank you for the message. --- I like your idea, but one thing I forgot to
mention is that my target postgres cluster has production DB's running on
it already. I think your solution would overwrite those? Or cause any
other issues on the target side?

Perhaps I could stand up a 2nd postgres instance on the target server, and
move the data there first? Then it would at least be on the same
box/storage. Then I could pg_dump/pgrestore the 2 DB's I need into the
production cluster, and shutdown the 2nd instance on the target server. Or
is that not necessary?

Just some thoughts...

Jeff

On Fri, May 27, 2016 at 6:41 PM Hannes Erven <hannes(at)erven(dot)at> wrote:

> 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 also keep any statistics and
> analyzes.
>
>
>
> Best regards,
>
> -hannes
>
>
>
>
> Am 27. Mai 2016 23:23:04 MESZ, schrieb Jeff Baldwin <tarheeljeff(at)gmail(dot)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(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.
> >>
>
>