Re: Upgrading from PG 8.2.5 to 9.1.13

Hi everyone,

Unfortunately, I'm stuck :P

Referring to the suggestion, to simply use pg_dumpall / psql to migrate
a PostgreSQL 8.2 to a 9.1 database server, I'm facing the following
issue right now:

The functions from contrib extension "pg_freespacemap" was used in 8.2 .
pg_freespacemap is installed in 9.1, too, but I get the following kind
of errors when inserting the dump:

ERROR: couldn't find function »pg_freespacemap_pages« in file
»/usr/lib/postgresql/9.1/lib/pg_freespacemap.so«
ERROR: function public.pg_freespacemap_pages() doesn't exist
ERROR: couldn't find function »pg_freespacemap_relations« in file
»/usr/lib/postgresql/9.1/lib/pg_freespacemap.so«
ERROR: function public.pg_freespacemap_relations() doesn't exist
ERROR: column pg_locks.transaction doesn't exist
LINE 2: ...locks.classid, pg_locks.objid, pg_locks.objsubid, pg_locks."...
ERROR: relation »public.System.Lockview« doesn't exist

When I have a look at the list of functions in postgres.public, there
seem to exist only two, both named "pg_freespace()".

What am I doing wrong? Is this a serious error or can it safely be ignored?

Best regards, Marc

Am 08.08.2014 16:08, schrieb Albe Laurenz:
> Marc Richter wrote:
>> First of all, I'm sorry for the length of this mail, but I want to get a
>> full description of steps which might be necessary to proceed to upgrade
>> a PostgreSQL server from 8.2.5 to 9.1.13. And these are all steps I
>> found, studying the docs.
>>
>> I'm quite unexperienced with PostgreSQL and need to do an upgrade from
>> 8.2.5 to something recent. Since we are running Debian wheezy as
>> default, which uses 9.1.13 currently, this is the destination version to
>> upgrade to.
>
> [...]
>
>> As a general rule of thumb, I understand that one should use that
>> version of pg_dump/pg_dumpall and psql that ships with the newer version
>> when updating from a major release to another.
>
> Right.
>
> [...]
>
> There are basically two kinds of upgrade:
> - Minor release upgrade from version x.y.a to version x.y.b, in
> which all you normally do is replace the binaries and restart the database.
> - Major release upgrade from x.a.y to x.b.y (with b > a) or
> from a.x.y to b.x.y (with b > a).
> These are done with pg_dumpall and restore or (in later versions)
> with pg_upgrade.
>
> All of the issues you picked from the release notes ar only relevant for
> minor release upgrades to fix the database data.
>
> With pg_dumpall you create a logical copy of the database, and the
> database is rebuilt from scratch during restore, so all of these problems
> will not occur (e.g., Indexes will be rebuilt and cannot be corrupt).
>
> So it all boils own to pg_dump and restore, an the relevant part of the
> release notes is the one that points out incompatibilities to previous versions.
> These might bite your application after the upgrade.
>
> Yours,
> Laurenz Albe