Re: PostgreSQL upgrade from 8.2.3 to 8.3.5 (and also data migration)

On Mon, Mar 2, 2009 at 10:36 PM, leela <its_leela(at)yahoo(dot)com> wrote:
> Hi Friends,
>  This is Leela from India.
>
> I am working on a project that requires upgrade on PostgreSQL version from
> 8.2.3 to 8.3.5 on Unix platforms. Iam trying to write a shell script to do
> the same.
>
> I search on the forum and found that I need to use pg_dump and psql utility
> for data migration from 8.2.3 to 8.3.5.
>
> I am not sure how many databases clusters are running on the machine. I
> would like know the best to Upgrade postgreSQL so that there is no dataloss.
>
> I see there are default databases like postgres, template0 and template1, I
> am sure we are not creating those databases. Do we need to take care of
> migration of postgreSQL and template0 and template1 also while doing backup?

Only if you've modified them in some way. It's quite common to
install languages and such to template1 so that new dbs inherit them.
Other than that, no probably not.

> In order to migrate data i saw that there is a utility to backup ALL using
> pg_dumpall. Can using this utility help me in doing my take easier.

Yep, this is the job it was built for.

> I want to upgrade PostgreSQL from 8.2.3 to 8.3.5 without any dataloss. There
> are around 60k rows in a database we have created.

The recommended method is generally to use the pg_dumpall from 8.3.x
to dump the data from 8.2 so that it produces a dump file that's
optimized for 8.3.x. Note that if you're running debian or ubuntu,
there are pg_upgradecluster commands that can handle a lot of this for
you.