Lists: | pgsql-general |
---|
From: | Vishalakshi Navaneethakrishnan <nvishalakshi(at)sirahu(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-12 02:15:47 |
Message-ID: | CAP-PUP3-xbZ9O9BDm+sA9B07gW9hqG5j6n-Sd+n2GLbXh9p0gg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> |
---|---|
To: | Vishalakshi Navaneethakrishnan <nvishalakshi(at)sirahu(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-12 02:33:18 |
Message-ID: | 5077816E.705@archidevsys.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-12 02:38:07 |
Message-ID: | 5077828F.7040109@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com> |
---|---|
To: | Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> |
Cc: | Vishalakshi Navaneethakrishnan <nvishalakshi(at)sirahu(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-12 02:41:24 |
Message-ID: | CA+h6AhgafFJbT+RhqeDNxpWh=XGPTwedSQwb7qFoucPnpjiJEw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
>
> 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/
From: | Amitabh Kant <amitabhkant(at)gmail(dot)com> |
---|---|
To: | Vishalakshi Navaneethakrishnan <nvishalakshi(at)sirahu(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-12 15:05:49 |
Message-ID: | CAPTAQBJH5Kvu+mNOdWSNxmaOX29RDTGor2WuFnyKVBAyJ3bXaA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | Amitabh Kant <amitabhkant(at)gmail(dot)com> |
Cc: | Vishalakshi Navaneethakrishnan <nvishalakshi(at)sirahu(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-12 15:28:52 |
Message-ID: | 50783734.1060408@pinpointresearch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | John R Pierce <pierce(at)hogranch(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-12 15:29:07 |
Message-ID: | 20121012152907.GB4475@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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. +
From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Vishalakshi Navaneethakrishnan <nvishalakshi(at)sirahu(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-12 22:07:09 |
Message-ID: | CAOR=d=0po_a931G44e1hWjMp-QU-=JZ_bbrn_MAxNv1CuKBm3g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
From: | Vishalakshi Navaneethakrishnan <nvishalakshi(at)sirahu(dot)com> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-13 04:52:05 |
Message-ID: | CAP-PUP2J1DzTFf5VHocMHBJPZjVwpxMY6Ze8PE1omcSW0mZFEw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-13 05:32:10 |
Message-ID: | 5078FCDA.60301@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-13 23:56:23 |
Message-ID: | k5cv37$rst$2@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-14 00:13:03 |
Message-ID: | k5d02f$saq$1@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres DB Migration from 8.3 to 9.1 |
Date: | 2012-10-16 00:02:39 |
Message-ID: | 20121016000239.GA7480@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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. +