Thread: Postgres DB Migration from 8.3 to 9.1

Hi Friends,

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

On Fri, Oct 12, 2012 at 7:45 AM, Vishalakshi Navaneethakrishnan <nvishalakshi@sirahu.com> 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

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

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.

Hi all,

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

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

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