Re: Postgres DB Migration from 8.3 to 9.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..?

Thanks in Advance.

--
Best Regards,
Vishalakshi.N

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

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

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Fri, Oct 12, 2012 at 7:45 AM, Vishalakshi Navaneethakrishnan <
nvishalakshi(at)sirahu(dot)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

On 10/12/2012 08:05 AM, Amitabh Kant wrote:
> On Fri, Oct 12, 2012 at 7:45 AM, Vishalakshi Navaneethakrishnan
> <nvishalakshi(at)sirahu(dot)com <mailto:nvishalakshi(at)sirahu(dot)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

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Thu, Oct 11, 2012 at 8:15 PM, Vishalakshi Navaneethakrishnan
<nvishalakshi(at)sirahu(dot)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.

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(dot)marlowe(at)gmail(dot)com>wrote:

> On Thu, Oct 11, 2012 at 8:15 PM, Vishalakshi Navaneethakrishnan
> <nvishalakshi(at)sirahu(dot)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

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

On 2012-10-12, Vishalakshi Navaneethakrishnan <nvishalakshi(at)sirahu(dot)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

On 2012-10-12, Bruce Momjian <bruce(at)momjian(dot)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

On Sun, Oct 14, 2012 at 12:13:03AM +0000, Jasen Betts wrote:
> On 2012-10-12, Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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