Thread: upgrade 8.0.3 -> 8.2.4

Are there any gotchas? I've got the opportunity to move to another database server for this application and yould like
totake the opportunity to upgrade at the same time. 

tia,

Steve

Steve Holdoway wrote:
> Are there any gotchas? I've got the opportunity to move to another database server for this application and yould
liketo take the opportunity to upgrade at the same time. 

Yes.  One of them that got me was that in 8.0.x, you could insert "invalid"
unicode byte sequences and it would warn you but accept them just fine.  In 8.2,
this is strictly enforced and you will not be able to restore your dump file
without running a utility to "fix" the dump file.

This change has prevented me from doing the upgrade as my dump file is nearly
100GB and the programming design of this utility requires that the whole file be
read into RAM before it begins processing it ( I've let it run for days and it
doesn't complete ).  I didn't think I had any Unicode in my database at all, but
I guess there are a few instances of "noise" in there that cause this..

Hopefully this doesn't block someone else from upgrading.

-Dan

A few months ago, I upgraded postgres from 7.4 to 8.2. There were a few gotchas, but we don't keep a whole lot of data so even the biggest problems were, on the whole, minor.

- The cidr data type became more strict, and a few tables in our network database would not restore until this was fixed.
- One of the primary keys broke and couldn't get created. This was more the fault of poor admins (before my time) and internal fragmentation than postgres 7.4. I had to fix the underlying table before it would restore.
- There were a few permissions issues (new acls + an inconsistent previous policy = fun).
- The system databases went from SQL_ASCII encoding to UTF8 encoding. I had to explicitly create the database during the restore, or else the database would have the wrong encoding.

I doubt that you will run into these exact problems, but my point is that there are inevitably some gotchas. If it's not prohibitively time-consuming, I'd recommend a full dump/restore of your database(s) from 8.0 to 8.2 so you can catch many of these gotchas before going live. Also, you might want to create test versions of your apps and try them against the 8.2 server.

On the whole, I've found postgres to be very good at maintaining backwards compatibility of interfaces and sql, so I estimate that most queries and db apps should "just work" with 8.2.

Peter

I almost forgot one of the biggest gotchas. Remember that users and
groups were conflated into "roles" in postgres 8.1. In addition to being
a change, there were some other issues, namely that you couldn't
manually specify user and group sysids. I had to change our user and
group management scripts to properly reflect this.

Peter

Peter Koczan wrote:
> A few months ago, I upgraded postgres from 7.4 to 8.2. There were a
> few gotchas, but we don't keep a whole lot of data so even the biggest
> problems were, on the whole, minor.
>
> - The cidr data type became more strict, and a few tables in our
> network database would not restore until this was fixed.
> - One of the primary keys broke and couldn't get created. This was
> more the fault of poor admins (before my time) and internal
> fragmentation than postgres 7.4. I had to fix the underlying table
> before it would restore.
> - There were a few permissions issues (new acls + an inconsistent
> previous policy = fun).
> - The system databases went from SQL_ASCII encoding to UTF8 encoding.
> I had to explicitly create the database during the restore, or else
> the database would have the wrong encoding.
>
> I doubt that you will run into these exact problems, but my point is
> that there are inevitably some gotchas. If it's not prohibitively
> time-consuming, I'd recommend a full dump/restore of your database(s)
> from 8.0 to 8.2 so you can catch many of these gotchas before going
> live. Also, you might want to create test versions of your apps and
> try them against the 8.2 server.
>
> On the whole, I've found postgres to be very good at maintaining
> backwards compatibility of interfaces and sql, so I estimate that most
> queries and db apps should "just work" with 8.2.
>
> Peter
>
> On 5/10/07, *Steve Holdoway* <steve.holdoway@firetrust.com
> <mailto:steve.holdoway@firetrust.com>> wrote:
>
>     Are there any gotchas? I've got the opportunity to move to another
>     database server for this application and yould like to take the
>     opportunity to upgrade at the same time.
>
>     tia,
>
>     Steve
>
>     ---------------------------(end of
>     broadcast)---------------------------
>     TIP 7: You can help support the PostgreSQL project by donating at
>
>                     http://www.postgresql.org/about/donate
>
>

Thanks, I knew about that one, and spent half a day cleaning up the data set before transfer.

That was exciting...

Cheers, Steve

On Thu, 10 May 2007 08:57:25 -0600
Dan Harris <fbsd@drivefaster.net> wrote:

> Steve Holdoway wrote:
> > Are there any gotchas? I've got the opportunity to move to another database server for this application and yould
liketo take the opportunity to upgrade at the same time. 
>
> Yes.  One of them that got me was that in 8.0.x, you could insert "invalid"
> unicode byte sequences and it would warn you but accept them just fine.  In 8.2,
> this is strictly enforced and you will not be able to restore your dump file
> without running a utility to "fix" the dump file.
>
> This change has prevented me from doing the upgrade as my dump file is nearly
> 100GB and the programming design of this utility requires that the whole file be
> read into RAM before it begins processing it ( I've let it run for days and it
> doesn't complete ).  I didn't think I had any Unicode in my database at all, but
> I guess there are a few instances of "noise" in there that cause this..
>
> Hopefully this doesn't block someone else from upgrading.
>
> -Dan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org

Thanks all. The only problem that I had was the unicode stuff, and that needed cleaning up anyway so I'm not too
worried.I've at least managed to get this database on it's own tablespace ( hint: when outsourcing development to a
companyyou never met, you may well be paying a bunch of schoolkids to learn some technology! ), although moving it out
ofthe public namespace will take a major rewrite of the application ): 

All is owrking fine so far, and the poor frontend server isn't suffering quite as much as before.

Cheers, Steve

On Thu, 10 May 2007 10:26:56 -0500
"Peter Koczan" <pjkoczan@gmail.com> wrote:

> A few months ago, I upgraded postgres from 7.4 to 8.2. There were a few
> gotchas, but we don't keep a whole lot of data so even the biggest problems
> were, on the whole, minor.
>
> - The cidr data type became more strict, and a few tables in our network
> database would not restore until this was fixed.
> - One of the primary keys broke and couldn't get created. This was more the
> fault of poor admins (before my time) and internal fragmentation than
> postgres 7.4. I had to fix the underlying table before it would restore.
> - There were a few permissions issues (new acls + an inconsistent previous
> policy = fun).
> - The system databases went from SQL_ASCII encoding to UTF8 encoding. I had
> to explicitly create the database during the restore, or else the database
> would have the wrong encoding.
>
> I doubt that you will run into these exact problems, but my point is that
> there are inevitably some gotchas. If it's not prohibitively time-consuming,
> I'd recommend a full dump/restore of your database(s) from 8.0 to 8.2 so you
> can catch many of these gotchas before going live. Also, you might want to
> create test versions of your apps and try them against the 8.2 server.
>
> On the whole, I've found postgres to be very good at maintaining backwards
> compatibility of interfaces and sql, so I estimate that most queries and db
> apps should "just work" with 8.2.
>
> Peter
>
> On 5/10/07, Steve Holdoway <steve.holdoway@firetrust.com> wrote:
> >
> > Are there any gotchas? I've got the opportunity to move to another
> > database server for this application and yould like to take the opportunity
> > to upgrade at the same time.
> >
> > tia,
> >
> > Steve
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: You can help support the PostgreSQL project by donating at
> >
> >                 http://www.postgresql.org/about/donate
> >
>