We have our production environment database server in Postgres 8.3 version.
we have planned to upgrade to lastest version 9.1. Dump from 8.3 and
restore in Postgres 9.1 takes more than 5 hours. Any other quick method to
upgrade from 8.3 to 9.1. We need to reduce our downtime below 1 hour. Any
Possibilities..?
On 12/10/12 15:15, Vishalakshi Navaneethakrishnan wrote:
> Hi Friends,
> We have our production environment database server in Postgres 8.3 > version. we have planned to upgrade to lastest version 9.1. Dump from > 8.3 and restore in Postgres 9.1 takes more than 5 hours. Any other > quick method to upgrade from 8.3 to 9.1. We need to reduce our > downtime below 1 hour. Any Possibilities..?
> Thanks in Advance.
> -- > Best Regards,
> Vishalakshi.N
Have you considered migrating to 9.2? It has some performance and other useful enhancements over 9.1.
I suspect that testing on early versions of 9.2.2 might be a good idea, with the intent of migrating to 9.2.2 when it is ready for production use.
On 10/11/12 7:15 PM, Vishalakshi Navaneethakrishnan wrote:
> We have our production environment database server in Postgres 8.3 > version. we have planned to upgrade to lastest version 9.1. Dump from > 8.3 and restore in Postgres 9.1 takes more than 5 hours. Any other > quick method to upgrade from 8.3 to 9.1. We need to reduce our > downtime below 1 hour. Any Possibilities..?
is it the dump or the restore taking the lions share of that time?
I don't know if pg_upgrade supports 8.3, but that would be one approach. getting it setup to work correctly can require some tinkering, but once you have that sorted, you start with a base backup of the 8.3 file system, and pg_upgrade 'converts' it to the newer version. you need both runtimes setup side by side so either can be run as pg_upgrade will need to start the old version in order to dump its metadata catalogs prior to migrating the data files. if you put both data directories on the same file system, it can use hard linking to 'move' the datafiles.
-- john r pierce N 37, W 122
santa cruz ca mid-left coast
> We have our production environment database server in Postgres 8.3
> version. we have planned to upgrade to lastest version 9.1. Dump from 8.3
> and restore in Postgres 9.1 takes more than 5 hours. Any other quick
> method to upgrade from 8.3 to 9.1. We need to reduce our downtime below 1
> hour. Any Possibilities..?
> You should consider 9.2.. Ofcourse, you need to test your application
against 9.2 before migrating, because as you mentioned you are on 8.3 where
you application might be compatible to it. There are some changes to
functions,string handling etc which are more advanced in 9.2. Once you
agree that app.. is good to go with 9.2, now to think for option with less
downtime or affordable downtime.
I would say, use pg_upgrade which has minimal downtime. I guess pg_uprades
supports from 8.3 onwards.
If you say no downtime or even minimal then prefer Slony-I (trigger based
replication).
> We have our production environment database server in Postgres 8.3
> version. we have planned to upgrade to lastest version 9.1. Dump from 8.3
> and restore in Postgres 9.1 takes more than 5 hours. Any other quick
> method to upgrade from 8.3 to 9.1. We need to reduce our downtime below 1
> hour. Any Possibilities..?
> On Fri, Oct 12, 2012 at 7:45 AM, Vishalakshi Navaneethakrishnan > <nvishalak...@sirahu.com <mailto:nvishalak...@sirahu.com>> wrote:
> Hi Friends,
> We have our production environment database server in Postgres 8.3
> version. we have planned to upgrade to lastest version 9.1. Dump
> from 8.3 and restore in Postgres 9.1 takes more than 5 hours. Any
> other quick method to upgrade from 8.3 to 9.1. We need to reduce
> our downtime below 1 hour. Any Possibilities..?
Note that one issue with the -j option is that it requires the input be a regular file rather than a pipe so you have to wait until you have a complete dump stored on-disk somewhere before you can start the restore. This delay may offset, eliminate or overshadow any benefit from the parallel-restore speedup.
Depending on the nature of your data, you may be able to roll-your-own. Some of our systems have large tables of data that, once collected, remain static. If you have that type of situation you may be able to pre-migrate historical data and then have a reduced window to migrate recent/live data.
On Thu, Oct 11, 2012 at 07:38:07PM -0700, John R Pierce wrote:
> On 10/11/12 7:15 PM, Vishalakshi Navaneethakrishnan wrote:
> >We have our production environment database server in Postgres 8.3
> >version. we have planned to upgrade to lastest version 9.1. Dump
> >from 8.3 and restore in Postgres 9.1 takes more than 5 hours. Any
> >other quick method to upgrade from 8.3 to 9.1. We need to reduce
> >our downtime below 1 hour. Any Possibilities..?
> is it the dump or the restore taking the lions share of that time?
> I don't know if pg_upgrade supports 8.3, but that would be one
> approach. getting it setup to work correctly can require some
> tinkering, but once you have that sorted, you start with a base
> backup of the 8.3 file system, and pg_upgrade 'converts' it to the
> newer version. you need both runtimes setup side by side so either
> can be run as pg_upgrade will need to start the old version in order
> to dump its metadata catalogs prior to migrating the data files. if
> you put both data directories on the same file system, it can use
> hard linking to 'move' the datafiles.
Upgrading with pg_upgrade from 8.3 is going to require 9.2 to be
compiled with --disable-integer-datetimes.
> We have our production environment database server in Postgres 8.3 version.
> we have planned to upgrade to lastest version 9.1. Dump from 8.3 and
> restore in Postgres 9.1 takes more than 5 hours. Any other quick method to
> upgrade from 8.3 to 9.1. We need to reduce our downtime below 1 hour. Any
> Possibilities..?
As downtime required approaches zero, the likelyhood of needing slony
approaches 1.
> On Thu, Oct 11, 2012 at 8:15 PM, Vishalakshi Navaneethakrishnan
> <nvishalak...@sirahu.com> wrote:
> > Hi Friends,
> > We have our production environment database server in Postgres 8.3
> version.
> > we have planned to upgrade to lastest version 9.1. Dump from 8.3 and
> > restore in Postgres 9.1 takes more than 5 hours. Any other quick method
> to
> > upgrade from 8.3 to 9.1. We need to reduce our downtime below 1 hour.
> Any
> > Possibilities..?
> As downtime required approaches zero, the likelyhood of needing slony
> approaches 1.
> On Thu, Oct 11, 2012 at 07:38:07PM -0700, John R Pierce wrote:
>> On 10/11/12 7:15 PM, Vishalakshi Navaneethakrishnan wrote:
>> >We have our production environment database server in Postgres 8.3
>> >version. we have planned to upgrade to lastest version 9.1. Dump
>> >from 8.3 and restore in Postgres 9.1 takes more than 5 hours. Any
>> >other quick method to upgrade from 8.3 to 9.1. We need to reduce
>> >our downtime below 1 hour. Any Possibilities..?
>> is it the dump or the restore taking the lions share of that time?
>> I don't know if pg_upgrade supports 8.3, but that would be one
>> approach. getting it setup to work correctly can require some
>> tinkering, but once you have that sorted, you start with a base
>> backup of the 8.3 file system, and pg_upgrade 'converts' it to the
>> newer version. you need both runtimes setup side by side so either
>> can be run as pg_upgrade will need to start the old version in order
>> to dump its metadata catalogs prior to migrating the data files. if
>> you put both data directories on the same file system, it can use
>> hard linking to 'move' the datafiles.
> Upgrading with pg_upgrade from 8.3 is going to require 9.2 to be
> compiled with --disable-integer-datetimes.
Doesn't that depend on what the 8.3 is using? Eg. Debian has used
integer datetimes since 8.1 (or earlier - 8.1 is the oldest I have at hand)
If he is using float datetimes is that going to be discontinued
sometime?
> We have our production environment database server in Postgres 8.3 version.
> we have planned to upgrade to lastest version 9.1. Dump from 8.3 and
> restore in Postgres 9.1 takes more than 5 hours. Any other quick method to
> upgrade from 8.3 to 9.1. We need to reduce our downtime below 1 hour. Any
> Possibilities..?
Using slony1 you can continue to operate the 8.3 until the instant you
want to change over.
On Sun, Oct 14, 2012 at 12:13:03AM +0000, Jasen Betts wrote:
> On 2012-10-12, Bruce Momjian <br...@momjian.us> wrote:
> > On Thu, Oct 11, 2012 at 07:38:07PM -0700, John R Pierce wrote:
> >> On 10/11/12 7:15 PM, Vishalakshi Navaneethakrishnan wrote:
> >> >We have our production environment database server in Postgres 8.3
> >> >version. we have planned to upgrade to lastest version 9.1. Dump
> >> >from 8.3 and restore in Postgres 9.1 takes more than 5 hours. Any
> >> >other quick method to upgrade from 8.3 to 9.1. We need to reduce
> >> >our downtime below 1 hour. Any Possibilities..?
> >> is it the dump or the restore taking the lions share of that time?
> >> I don't know if pg_upgrade supports 8.3, but that would be one
> >> approach. getting it setup to work correctly can require some
> >> tinkering, but once you have that sorted, you start with a base
> >> backup of the 8.3 file system, and pg_upgrade 'converts' it to the
> >> newer version. you need both runtimes setup side by side so either
> >> can be run as pg_upgrade will need to start the old version in order
> >> to dump its metadata catalogs prior to migrating the data files. if
> >> you put both data directories on the same file system, it can use
> >> hard linking to 'move' the datafiles.
> > Upgrading with pg_upgrade from 8.3 is going to require 9.2 to be
> > compiled with --disable-integer-datetimes.
> Doesn't that depend on what the 8.3 is using? Eg. Debian has used
> integer datetimes since 8.1 (or earlier - 8.1 is the oldest I have > at hand)
> If he is using float datetimes is that going to be discontinued
> sometime?
No one has mentioned removing the ability to do floating-point
timestamps.
