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

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.