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 <han...@erven.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 <tarheelj...@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 <melvin6...@gmail.com> > >wrote: > > > >> > >> > >> On Fri, May 27, 2016 at 5:09 PM, Jeff Baldwin <tarheelj...@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 > ><melvin6...@gmail.com> > >>> wrote: > >>> > >>>> > >>>> On Fri, May 27, 2016 at 4:56 PM, Jeff Baldwin > ><tarheelj...@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. > >> > >
