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@xxxxxxxxx>: >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@xxxxxxxxx> >wrote: > >> >> >> On Fri, May 27, 2016 at 5:09 PM, Jeff Baldwin <tarheeljeff@xxxxxxxxx> >> 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@xxxxxxxxx> >>> wrote: >>> >>>> >>>> On Fri, May 27, 2016 at 4:56 PM, Jeff Baldwin ><tarheeljeff@xxxxxxxxx> >>>> 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. >> -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
