Re: Help needed

On Oct 19, 2006, at 8:19 AM, <Craig_Walker(at)McAfee(dot)com>
<Craig_Walker(at)McAfee(dot)com> wrote:

> I want to upgrade a system from 7.3.8 ro 8.1.5
>
> I am new to Psql and looking for handy hints
>
> Any known problems or pit-falls ?

You'll need to dump the database and reload (pg_dump and pg_restore),
8.1 uses a different data format.

There can be issues with character encodings. IIRC 8.1 is much more
strict about what it allows than 7.4 was. So you could potentially
run into errors importing the data into 8.1 fro 7.x.

I would keep the 7.4 database around after dumping it to correct any
possible encoding glitches.

There are also quite a few query planner changes between these
versions. They are certainly improvements, but they can mean that
queries that work correctly on 7.4 that fail to use the same indices
or just behave differently on 8.1. Most of the time things will just
work (and work better), but you'll want to watch out for that.

> Anything we need to do in preparation ?

I would just do a dry run. Dump the 7.4 database and install 8.1.5
somewhere (perhaps in a local directory or on another machine). Then
load the dump into it and run the ANALYZE sql command.

If it loads successfully, test out your applications against it. Test
it and test it some more.

8.1 is typically configured differently than 7.4 wrt things like
shared_buffers and whatnot. You'll definitely want to adjust the
defaults for things (upwards) and you'll probably want different
settings than before even running on the same hardware.

You'll probably also want to turn autovacuum on, and possible
vacuum_cost_delay. This page describes a lot of the settings with
recommendations (even though it's getting pretty dated now, the info
is still useful):

http://www.powerpostgresql.com/Downloads/annotated_conf_80.html

hth,

-Casey