Thread: Postgresql upgrade from 8.4 to latest

On 07/28/2015 01:12 PM, AI Rumman wrote:
> Hi,
>
> I need to upgrade Postgresql database from 8.4 to latest stable version
> (9.4). The db size is almost 2.5 TB.
> Is pg_upgrade in-place is a good idea for it?

With quite a bit of testing, yes.

But keep in mind, it is still an outage.

JD


>
> Thanks for advice.
>
> Regards.


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

On 7/28/2015 1:29 PM, Joshua D. Drake wrote:
>> I need to upgrade Postgresql database from 8.4 to latest stable version
>> (9.4). The db size is almost 2.5 TB.
>> Is pg_upgrade in-place is a good idea for it?
>
> With quite a bit of testing, yes.

yeah, that was my thought to...   clone the database to a test/staging
machine, and practice running pg_upgrade on it til you get it right.

frankly, I'd want to use a new machine for the final upgrade, too,
stopping your applications, taking a fresh clone, and doing the upgrade,
then swapping it into place, and restarting your applications.

--
john r pierce, recycling bits in santa cruz

On 7/28/2015 1:35 PM, AI Rumman wrote:
> But what I read, in-place upgrade has smaller outage, compared to
> dump/restore. But so many articles on having bugs afterwards.
> Do you think it is a good idea to use pg_upgrade for critical database
> application?

most of those are application issues with 9.4 vs 8.4, not directly
related to the pg_upgrade process.


--
john r pierce, recycling bits in santa cruz

On Tue, Jul 28, 2015 at 10:35 PM, AI Rumman <rummandba@gmail.com> wrote:
> But what I read, in-place upgrade has smaller outage, compared to
> dump/restore.

Please, keep in mind that while the direct outage (when the database
is shut down) is small, there will be a time period right after the
upgrade when PostgreSQL will be rebuilding statistics. Until it's
rebuilt, the planner won't be able to generate decent plans (because
there will be no statistics to use, since that is cleared after the
upgrade), hence, your application might run so slow that it won't be a
big improvement over the time when the DB is shut down. The only way
to find out how long it would take in your environment and for your
dataset is to test.

But so many articles on having bugs afterwards.
> Do you think it is a good idea to use pg_upgrade for critical database
> application?

Yes.

> Or any other tool should I consider? For example - slony?

I think if you can afford the downtime pg_upgrade would be the easiest
solution. You can use Slony, Londiste, Bucardo or any other
replication solution, but they require a complex setup and restrict
you to only a subset of operations possible on the database
(basically, only those that can activate a trigger) until all of your
data is replicated.

For really small databases (up tot tens/hunderds of megabytes)
dump/restore can be faster than pg_upgrade. Make sure you use the
pg_dump/pg_restore from 9.4 and not 8.4

Kind regards,
--
Oleksii

On 07/28/2015 01:35 PM, AI Rumman wrote:
> But what I read, in-place upgrade has smaller outage, compared to
> dump/restore.

Correct, in fact if you do it with the link option, it will be very fast.


> But so many articles on having bugs afterwards.
> Do you think it is a good idea to use pg_upgrade for critical database
> application?

It entirely depends, I have successfully used pg_upgrade many, many
times. That is what -c is for, to work out all the kinks before you upgrade.

> Or any other tool should I consider? For example - slony?

On at 2.5TB database, you very well be doing a lot more harm than good
using a tool such as slony.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.