Thread: Postgres DB Migration from 8.3 to 9.1

Postgres DB Migration from 8.3 to 9.1

From
Vishalakshi Navaneethakrishnan
Date:
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


Re: Postgres DB Migration from 8.3 to 9.1

From
Gavin Flower
Date:
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.


Cheers.
Gavin

Re: Postgres DB Migration from 8.3 to 9.1

From
John R Pierce
Date:
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



Re: Postgres DB Migration from 8.3 to 9.1

From
Raghavendra
Date:
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). 

---
Regards,
Raghavendra
EnterpriseDB Corporation

Re: Postgres DB Migration from 8.3 to 9.1

From
Amitabh Kant
Date:
On Fri, Oct 12, 2012 at 7:45 AM, Vishalakshi Navaneethakrishnan <nvishalakshi@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..?

Thanks in Advance.

--
Best Regards,
Vishalakshi.N



Try using the -j <number-of-jobs> option to speed up restore process. See http://www.postgresql.org/docs/9.1/static/app-pgrestore.html . Not  sure though whether it will bring it up within your range. 

Amitabh

Re: Postgres DB Migration from 8.3 to 9.1

From
Steve Crawford
Date:
On 10/12/2012 08:05 AM, Amitabh Kant wrote:
On Fri, Oct 12, 2012 at 7:45 AM, Vishalakshi Navaneethakrishnan <nvishalakshi@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..?

Thanks in Advance.

--
Best Regards,
Vishalakshi.N



Try using the -j <number-of-jobs> option to speed up restore process. See http://www.postgresql.org/docs/9.1/static/app-pgrestore.html . Not  sure though whether it will bring it up within your range. 

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

Pg_upgrade does support upgrades from 8.3: http://www.postgresql.org/docs/current/static/pgupgrade.html but you will need to set up a dev-system to become familiar with the process.

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.

Cheers,
Steve

Re: Postgres DB Migration from 8.3 to 9.1

From
Bruce Momjian
Date:
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.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


Re: Postgres DB Migration from 8.3 to 9.1

From
Scott Marlowe
Date:
On Thu, Oct 11, 2012 at 8:15 PM, Vishalakshi Navaneethakrishnan
<nvishalakshi@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.


Re: Postgres DB Migration from 8.3 to 9.1

From
Vishalakshi Navaneethakrishnan
Date:
Hi all,

While testing upgrade facility, I have installed postgres 9.2 using  source package. 

Because it needs to  be compiled with --disable-integer-datetimes.

I have used this command

./configure --prefix=/opt/PostgreSQL/9.2/ --disable-integer-datetimes --without-readline  

After installation i found pg_upgrade binary missing in bin directoy. How can i get this ?

Thanks in Advance.

On Sat, Oct 13, 2012 at 3:37 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Thu, Oct 11, 2012 at 8:15 PM, Vishalakshi Navaneethakrishnan
<nvishalakshi@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.



--
Best Regards,
Vishalakshi.N


Re: Postgres DB Migration from 8.3 to 9.1

From
John R Pierce
Date:
On 10/12/12 9:52 PM, Vishalakshi Navaneethakrishnan wrote:
> While testing upgrade facility, I have installed postgres 9.2 using
>  source package.
>
> Because it needs to  be compiled with --disable-integer-datetimes.
>
> I have used this command
>
> ./configure --prefix=/opt/PostgreSQL/9.2/ --disable-integer-datetimes
> --without-readline
>
> After installation i found pg_upgrade binary missing in bin directoy.
> How can i get this ?
>

pg_upgrade is part of the optional 'contrib' packages, these have to be
explicitly built.  from your top level source directory, try....

     make contrib
     make install

...


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast



Re: Postgres DB Migration from 8.3 to 9.1

From
Jasen Betts
Date:
On 2012-10-12, Bruce Momjian <bruce@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?

--
⚂⚃ 100% natural

Re: Postgres DB Migration from 8.3 to 9.1

From
Jasen Betts
Date:
On 2012-10-12, Vishalakshi Navaneethakrishnan <nvishalakshi@sirahu.com> wrote:
> --e0cb4efe29e2435cf104cbd3459b
> Content-Type: text/plain; charset=ISO-8859-1
>
> 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..?

Using slony1 you can continue to operate the 8.3 until the instant you
want to change over.

--
⚂⚃ 100% natural

Re: Postgres DB Migration from 8.3 to 9.1

From
Bruce Momjian
Date:
On Sun, Oct 14, 2012 at 12:13:03AM +0000, Jasen Betts wrote:
> On 2012-10-12, Bruce Momjian <bruce@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.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +