Re: [GENERAL] Migrate 2 DB's - v8.3

[Francisco Olarte]

Martin:

Could you please avoid unedited top posts?

On Sat, May 28, 2016 at 7:53 PM, Martín Marqués  wrote:
> I still don't understand why the OP is getting into so much trouble and
> doesn't upgrade to a newer version like 9.3 or 9.4 (or even 9.5).

Neither do I, but the thing is trying to find the fastest way to move
a single database between two clusters on different machines, or
trying to find a way to do it without downtime, is useful on its own,
whicever the versions are.

> All this hassle to stay on an unsupported postgres is just useless, IMNSHO.

You can ask the OP for the reason to stay in 8.3 directly. Maybe is
something as simple as "I'm the one who pays, you do what I pay you
for.". I've had several of these.


Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migrate 2 DB's - v8.3

[Martín Marqués]

I still don't understand why the OP is getting into so much trouble and
doesn't upgrade to a newer version like 9.3 or 9.4 (or even 9.5).

All this hassle to stay on an unsupported postgres is just useless, IMNSHO.

Regards,

El 28/05/16 a las 12:26, Francisco Olarte escribió:
> Jeff:
> 
> On Sat, May 28, 2016 at 12:38 AM, Jeff Baldwin  wrote:
>> Thank you for your time Alan.
> ..
>> To move the DB,  you are suggesting something like this:
>> pg_dump -h dbms11 -U postgres -C mls11 | psql -h localhost -d mls11 -U
>> postgres
> 
> I'd like to point one thing, you MAY get a little more speed if you
> run pg_dump AND psql each in the same host as the DB it's operating on
> to minimize latency ( and I would time unix socket vs network first in
> case it differs ). ( to do that I would try something like 'ssh dbms11
> "pg_dump  mls11 " | psql -d mls11' with all the needed doodahs, and
> maybe use something like netcat or socat instead of ssh ). The
> rationale being the intermediate dump is just a data stream and not
> latency sensitive ( except for the window*latency problem, but you are
> not going to hit that on a LAN ), while the dump/restore does DB work
> which is more latency sensitive ( I do not know how many RTTs it would
> need, specially with blobs, but you can try it ).
> 
> ¿ How many hours does it take in your tests? Because if you have 1-2
> and you can do the dump psql pipe trick, which is quite robust, in 3-4
> you may push for it ( arguing it's a simpler an more testable process
> ).
> 
> Francisco Olarte.
> 
> 


-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migrate 2 DB's - v8.3

[Francisco Olarte]

Jeff:

On Sat, May 28, 2016 at 12:38 AM, Jeff Baldwin  wrote:
> Thank you for your time Alan.
..
> To move the DB,  you are suggesting something like this:
> pg_dump -h dbms11 -U postgres -C mls11 | psql -h localhost -d mls11 -U
> postgres

I'd like to point one thing, you MAY get a little more speed if you
run pg_dump AND psql each in the same host as the DB it's operating on
to minimize latency ( and I would time unix socket vs network first in
case it differs ). ( to do that I would try something like 'ssh dbms11
"pg_dump  mls11 " | psql -d mls11' with all the needed doodahs, and
maybe use something like netcat or socat instead of ssh ). The
rationale being the intermediate dump is just a data stream and not
latency sensitive ( except for the window*latency problem, but you are
not going to hit that on a LAN ), while the dump/restore does DB work
which is more latency sensitive ( I do not know how many RTTs it would
need, specially with blobs, but you can try it ).

¿ How many hours does it take in your tests? Because if you have 1-2
and you can do the dump psql pipe trick, which is quite robust, in 3-4
you may push for it ( arguing it's a simpler an more testable process
).

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migrate 2 DB's - v8.3

[Hannes Erven]

Hi Jeff,


> 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.

Oh, that's important information.

The only way to "merge" additional databases into an existing cluster 
would be dumping and importing (in either direction -- either merge 
existing target dbs into the rsync'ed cluster or vice versa).



My original suggestion would transfer the original cluster as-is to the 
target machine into an empty directory, and create a new (possibly 
additional) postgresql instance on the target.


In Ubuntu (and I guess in CentOS as well) there is built-in support for 
multiple clusters (instances) on a single host (see pg_lsclusters or 
pg_ctlcluster). Every cluster will listen on a different port.


This enables (but also forces) per-cluster management of replication, 
backup and upgrades. However, that approach might not scale well and 
tuning memory/io settings might be more difficult than with a single 
cluster on the DB host.



> 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.

That would be an option, but consider that in this case the IO subsystem 
of the target server would have to read and write your data at the same 
time.
Unless you have source and target clusters on independent spindles or a 
really slow source server, this will possibly take even longer than to 
pipe the data over your GBIT network connection.



Best regards,

-hannes


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migrate 2 DB's - v8.3

[Jeff Baldwin]

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  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  >:
> >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 
> >wrote:
> >
> >>
> >>
> >> On Fri, May 27, 2016 at 5:09 PM, Jeff Baldwin 
> >> 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
> >
> >>> wrote:
> >>>
> 
>  On Fri, May 27, 2016 at 4:56 PM, Jeff Baldwin
> >
>  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.
> >>
>
>

Re: [GENERAL] Migrate 2 DB's - v8.3

[Hannes Erven]

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 :
>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 
>wrote:
>
>>
>>
>> On Fri, May 27, 2016 at 5:09 PM, Jeff Baldwin 
>> 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
>
>>> wrote:
>>>

 On Fri, May 27, 2016 at 4:56 PM, Jeff Baldwin
>
 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migrate 2 DB's - v8.3

[Jeff Baldwin]

Thank you for your time Alan.

I'd like to confirm my understanding of your statement, and ask a question.

To move the DB,  you are suggesting something like this:
pg_dump -h dbms11 -U postgres -C mls11 | psql -h localhost -d mls11 -U
postgres

I'm not familiar with removing/adding indexes (I'm not a DBA, just trying
to pretend to be one for this project).   Can you elaborate on what might I
need to do there?

Kind Regards,
Jeff

On Fri, May 27, 2016 at 6:05 PM Alan Hodgson 
wrote:

> On Friday, May 27, 2016 05:32:08 PM Melvin Davidson wrote:
> > Well, Slony certainly will do the trick.
> > Keep in mind you will need to do schema only first to the slave.
> > You set up replication from the old server with the db on the new server
> as
> > the slave. Then you initiate replication. It will probably take a long
> time
> > to
> > replicate, but then you have the option to promote the slave at your time
> > preference (IE: your 2 hr window). It should only take a few minutes for
> > Slony to do the switchover, but the best thing to do is a dry run first.
> > IOW, you'll have to do the whole thing twice to get an accurate switch
> time,
> > but you won't need to change your network until you are ready to go live.
>
> Slony doesn't do BLOBs, afaik, unless he's using BYTEA fields.
>
> Otherwise I believe dump/reload is OP's only choice. He should be able to
> do
> 90GB in 2 hours on fast enough hardware; just pipe it over the network to
> do
> the restore simultaneous with the dump.
>
> Also remove as many indexes as possible beforehand and use create
> concurrently
> manually afterwards to add them back in.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Migrate 2 DB's - v8.3

[Alan Hodgson]

On Friday, May 27, 2016 05:32:08 PM Melvin Davidson wrote:
> Well, Slony certainly will do the trick.
> Keep in mind you will need to do schema only first to the slave.
> You set up replication from the old server with the db on the new server as
> the slave. Then you initiate replication. It will probably take a long time
> to
> replicate, but then you have the option to promote the slave at your time
> preference (IE: your 2 hr window). It should only take a few minutes for
> Slony to do the switchover, but the best thing to do is a dry run first.
> IOW, you'll have to do the whole thing twice to get an accurate switch time,
> but you won't need to change your network until you are ready to go live.

Slony doesn't do BLOBs, afaik, unless he's using BYTEA fields.

Otherwise I believe dump/reload is OP's only choice. He should be able to do 
90GB in 2 hours on fast enough hardware; just pipe it over the network to do 
the restore simultaneous with the dump. 

Also remove as many indexes as possible beforehand and use create concurrently 
manually afterwards to add them back in.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migrate 2 DB's - v8.3

[Melvin Davidson]

On Fri, May 27, 2016 at 5:23 PM, Jeff Baldwin  wrote:

> 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 
> wrote:
>
>>
>>
>> On Fri, May 27, 2016 at 5:09 PM, Jeff Baldwin 
>> 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 
>>> wrote:
>>>

 On Fri, May 27, 2016 at 4:56 PM, Jeff Baldwin 
 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.
>>
>
Well, Slony certainly will do the trick.
Keep in mind you will need to do schema only first to the slave.
You set up replication from the old server with the db on the new server as
the slave. Then you initiate replication. It will probably take a long time
to
replicate, but then you have the option to promote the slave at your time
preference (IE: your 2 hr window). It should only take a few minutes for
Slony to do the switchover, but the best thing to do is a dry run first.
IOW, you'll have to do the whole thing twice to get an accurate switch time,
but you won't need to change your network until you are ready to go live.


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Migrate 2 DB's - v8.3

[Jeff Baldwin]

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 
wrote:

>
>
> On Fri, May 27, 2016 at 5:09 PM, Jeff Baldwin 
> 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 
>> wrote:
>>
>>>
>>> On Fri, May 27, 2016 at 4:56 PM, Jeff Baldwin 
>>> 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.
>

Re: [GENERAL] Migrate 2 DB's - v8.3

[Melvin Davidson]

On Fri, May 27, 2016 at 5:09 PM, Jeff Baldwin  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 
> wrote:
>
>>
>> On Fri, May 27, 2016 at 4:56 PM, Jeff Baldwin 
>> 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.

Re: [GENERAL] Migrate 2 DB's - v8.3

[Jeff Baldwin]

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 
wrote:

>
> On Fri, May 27, 2016 at 4:56 PM, Jeff Baldwin 
> 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.
>

Re: [GENERAL] Migrate 2 DB's - v8.3

[Melvin Davidson]

On Fri, May 27, 2016 at 4:56 PM, Jeff Baldwin  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.

[GENERAL] Migrate 2 DB's - v8.3

[Jeff Baldwin]

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