Обсуждение: Upgrading from PG 8.2.5 to 9.1.13

Hi everyone,

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.

I've read several guides for this, but all leave me behind with multiple
big question marks in my head. All these guides look quite complicated
to me and that they require very deep knowledge of PostgreSQL internal
functions, in that they all say that there are actions to apply, but
bound to specific conditions. I do not know these conditions or even
what they mean as a sysadmin and newbee regarding PostgreSQL, so I would
be very thankful if someone could give me hints on this. I also do not
know the existing database structure and how it is used by the
application connecting to it. All I know is, when nowadays a recent
PostgreSQL server of version 9.1+ is filed with fresh data, the
application does not complain and works fine. So there seems to be no
deeper PostgreSQL-Functionality being used but only straight SQL.

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.

When I have read the release notes from 8.2.5 to 9.1.13 [1] correctly, I
have to do the following in the given order:

1) Upgrade 8.2.5 to 8.2.7
In E.98 it says that one "might need to REINDEX indexes on textual
columns after updating, if you are affected by the Windows locale issue"
when updating to 8.2.7 .

2) Upgrade 8.2.7 to 8.2.11
In E.94 it says that "it is recommended to REINDEX all GiST indexes
after the upgrade".

3) Upgrade 8.2.11 to 8.2.14
In E.91 it says that "if you have any hash indexes on interval columns,
you must REINDEX them after updating to 8.2.14."

4) Upgrade 8.2.14 to 8.2.23
In E.82 for 8.2.23, the last release in 8.2, there seems to be a bug
fixed which makes it necessary to drop the information_schema schema and
recreate it by sourcing SHAREDIR/information_schema.sql for every database.

5) Upgrade 8.2.23 to 8.3
In E.81 it is said, that it needs a dump using pg_dump and reinsert it
into the fresh 8.3 server setup to upgrade.

6) Upgrade 8.3 to 8.3.1
In E.80 it is said, that one "might need to REINDEX indexes on textual
columns after updating, if you are affected by the Windows locale issue".

7) Upgrade 8.3.1 to 8.3.5
In E.76 "it is recommended to REINDEX all GiST indexes after the upgrade".

8) Upgrade 8.3.5 to 8.3.8
In E.73 it is said that "if you have any hash indexes on interval
columns, you must REINDEX them after updating to 8.3.8".

9) Upgrade 8.3.8 to 8.3.17
In E.64, the same steps are recommended as in E.82: drop the
information_schema schema and recreate it by sourcing
SHAREDIR/information_schema.sql for every database.

10) Upgrade 8.3.17 to 8.3.23
E.58: Nothing special, just to upgrade to 8.4 from the latest 8.3 minor
possible.

11) Upgrade 8.3.23 to 8.4
E.57: As in E.81 from 8.2.23 to 8.3, it is sufficient to create a dump
using pg_dump and reinsert it into the fresh 8.4 server setup to upgrade.

12) Upgrade 8.4 to 8.4.2
E.55: "if you have any hash indexes, you should REINDEX them after
updating to 8.4.2".

13) Upgrade 8.4.2 to 8.4.12
E.47: As in E.82 and E.64: It is recommended to drop the
information_schema schema and recreate it by sourcing
SHAREDIR/information_schema.sql for every database.

14) Upgrade 8.4.12 to 8.4.17
E.40: "it is advisable to REINDEX any GiST indexes that meet one or more
of the conditions described below."
* REINDEX any GiST indexes on box, polygon, circle, or point columns,
since all of these use gist_box_same.
* Users are advised to REINDEX multi-column GiST indexes after
installing this update.

15) No action necessary for E.38 since it seems to not apply, because we
are strictly upgrading without the actions, possibly leading to
situations this error arises, being taken.

15) Upgrade 8.4.17 to 8.4.22
E.35: "Users with GiST indexes on bit or bit varying columns should
REINDEX those indexes after installing this update."

16) Upgrade 8.4.22 to 9.0
E.34: As in E.81 and E.57, creating a dump using pg_dump and reinsert it
into the fresh 9.0 server setup to upgrade, seems to be necessary to do
the job.

17) Upgrade 9.0 to 9.0.6
E.28: As in E.47, E.82 and E.64: It is recommended to drop the
information_schema schema and recreate it by sourcing
SHAREDIR/information_schema.sql for every database.

18) Upgrade 9.0.6 to 9.0.13
E.21: Apply GiST index fixes as already described in E.40.

19) Upgrade 9.0.13 to 9.0.18
E.16: REINDEX indexes using "bit" or "bit varying" columns after
installing this update.

20) Upgrade 9.0.18 to 9.1
E.15: As with E.34, E.81 and E.57, dumping databases using pg_dump and
reinsert it into the fresh 9.1 server setup to upgrade, seems to be
necessary to do the job.

21) Upgrade 9.1 to 9.1.2
E.13: As with E.28, E.47, E.82 and E.64: It is recommended to drop the
information_schema schema and recreate it by sourcing
SHAREDIR/information_schema.sql for every database.

Also, if the "citext" data type is used and a dump from a pre-9.1
database is restored and the CREATE EXTENSION command has not be run
yet, one should run "CREATE EXTENSION citext FROM unpackaged" to avoid
collation-related failures in "citext" operations.
If the CREATE EXTENSION command *has* been applied before, one will
instead need to do manual catalog updates as explained in the following.

One can manually run (as superuser) the UPDATE commands found at the end
of SHAREDIR/extension/citext--unpackaged--1.0.sql. There is no harm in
doing this again if unsure.

22) Upgrade 9.1.2 to 9.1.4
E.11: The same as described in E.13 seems to be necessary.

23) Upgrade 9.1.4 to 9.1.6
E.9: One may need to perform REINDEX operations on all "btree" and "GIN"
indexes after upgrading to 9.1.6.
As a security measurement it is recommended to perform a VACUUM of all
tables while having vacuum_freeze_table_age set to zero.

24) Upgrade 9.1.6 to 9.1.9
E.6: After installing this update, it is advisable to REINDEX any GiST
indexes as described in E.40 already.
Also, REINDEX of indexes that use contrib/btree_gist for variable-width
data types, that is "text", "bytea", "bit", and "numeric" columns is needed.
Users are advised to REINDEX multi-column GiST indexes after installing
this update.

25) Upgrade 9.1.9 to 9.1.11
E.4: Users have to vacuum all tables in all databases while having
vacuum_freeze_table_age set to zero.

26) Upgrade 9.1.11 to 9.1.13
E.2: 9.1.13 is the version which is contained in Debian Stable (wheezy).
Since no special actions seems to be necessary to take, just creating a
dump from the (self compiled) version 9.1.11 using pg_dump and inserting
that dump into the PostgreSQL server from Debian's Repository seems to
be sufficient.


These are very much steps to take, but I do not complain: Thats the cost
of being lazy when it comes to updating things (even I wasn't the one
not doing them :-/).

I guess it is most reasonable to ask the questions and understanding
issues to every of the listed 26 points in order. If there are
unnecessary steps listed, or there is another shortcut between major
versions available, please let me know.

1) How do I "REINDEX indexes on textual columns"?

2, 7) How do I identify "GiST indexes" in a database dozens of GB in
size and apply a REINDEX on them?

3) How do I identify which interval columns are using hash indexes and
how do I apply a REINDEX on them?

4, 9, 13, 17, 21) How to drop the information_schema schema and how to
"source" information_schema.sql?

6) If I do not know if I'm affected by that "Windows locale issue", is
there any harm in applying the following step anyway?
How do I identify indexes on textual columns and REINDEX them?

8, 12) How do I find if there are any hash indexes on interval columns
and how to REINDEX them?

14, 18, 24) How do I identify "multi-column GiST indexes" and "GiST
indexes on box, polygon, circle, or point columns" and REINDEX them?

15, 19) How do I find GiST indexes on "bit" or "bit varying" columns and
REINDEX them?

21, 22) This one is the hardest to understand for me, since there seem
to have multiple things to be taken into account, which decides what's
to do and what not.
How do I ...
... find if the "citext" data type is used?
... know if the CREATE EXTENSION command has not already executed before?

23) How do I find indexes using "btree" and "GIN" and how to REINDEX them?

24) How do I find indexes using contrib/btree_gist for variable-width
data types, that is "text", "bytea", "bit", and "numeric" columns and
how to REINDEX them?


This all looks like a huge amount of steps which all have to be applied
OK to not corrupt the data of the database ... and especially for
someone like me, who not is that familiar with PostgreSQL.
Because of that, I really could use some advice.

Thanks for reading, everyone!

Best regards,
Marc

[1] http://www.postgresql.org/docs/9.1/static/release.html

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.

> When I have read the release notes from 8.2.5 to 9.1.13 [1] correctly, I
> have to do the following in the given order:

[long list with upgrade issues from all minor releases between the versions]

> This all looks like a huge amount of steps which all have to be applied
> OK to not corrupt the data of the database ... and especially for
> someone like me, who not is that familiar with PostgreSQL.
> Because of that, I really could use some advice.

You really made an effort there, but it is much simpler than that.

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

Marc Richter <mail@marc-richter.info> 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.

> [dozens of upgrades through various minor and major releases]

You're making this way too hard.  If I were in your shoes, this is
what I would do:

(1) Stop the database and make a filesystem-level copy of the
entire $PGDATA directory tree and any tablespaces.  Keep this
somewhere safe for the next year or two -- just in case you have
problems later.

(2) Install the 8.2.23 software, built with the same options as
your current 8.2.5, and start the database.  Run REINDEX DATABASE
in each database.  (The REINDEX may not be strictly necessary,
since pg_dump[all] will normally be doing heap scans of the tables,
but it may eliminate any confusion if your run queries to check
results.)

(3) Install the latest minor release of the target major release.
I would seriously consider 9.2.9 or 9.3.5 instead of 9.1, to get an
extra year or two of supported usage before worrying about
upgrading or going out of support again.  If you go to 9.1, the
latest is now 9.1.14.  You don't need to worry about
storage-compatible build options, since pg_dump[all] will re-create
the database objects through source code.  Create an empty cluster.

(4) Using the new version's pg_dump and/or pg_dumpall, dump the 8.2
databases and restore them into the new cluster.  Personally, I
have usually done this by using pg_dumpall with -g to get the
global objects (like users) onto the new systems, then one at a
time I have created target databases and run pg_dump piped to psql
to copy across without a dump file.  Some prefer to have the dump
file.

(5) I strongly recommend running VACUUM FREEZE ANALYZE in each new
database after the load and before using it.

There is no need to go through all of those intermediate versions,
but do be careful to test.  There are differences in string literal
handling, bytea literals, and casting rules which may require
adjustments in your software.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi Kevin,

Thank you for your reply!

I do not understand step 2) in your list: When all these REINDEX and
Upgrading extra steps are prevented when using full dumps and psql, why
is it necessary to update to 8.2.23 first?

Best regards,
Marc

Am Freitag, 8. August 2014 16:20:37 schrieb Kevin Grittner:
> Marc Richter <mail@marc-richter.info> 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.
>
>> [dozens of upgrades through various minor and major releases]
>
> You're making this way too hard.  If I were in your shoes, this is
> what I would do:
>
> (1) Stop the database and make a filesystem-level copy of the
> entire $PGDATA directory tree and any tablespaces.  Keep this
> somewhere safe for the next year or two -- just in case you have
> problems later.
>
> (2) Install the 8.2.23 software, built with the same options as
> your current 8.2.5, and start the database.  Run REINDEX DATABASE
> in each database.  (The REINDEX may not be strictly necessary,
> since pg_dump[all] will normally be doing heap scans of the tables,
> but it may eliminate any confusion if your run queries to check
> results.)
>
> (3) Install the latest minor release of the target major release.
> I would seriously consider 9.2.9 or 9.3.5 instead of 9.1, to get an
> extra year or two of supported usage before worrying about
> upgrading or going out of support again.  If you go to 9.1, the
> latest is now 9.1.14.  You don't need to worry about
> storage-compatible build options, since pg_dump[all] will re-create
> the database objects through source code.  Create an empty cluster.
>
> (4) Using the new version's pg_dump and/or pg_dumpall, dump the 8.2
> databases and restore them into the new cluster.  Personally, I
> have usually done this by using pg_dumpall with -g to get the
> global objects (like users) onto the new systems, then one at a
> time I have created target databases and run pg_dump piped to psql
> to copy across without a dump file.  Some prefer to have the dump
> file.
>
> (5) I strongly recommend running VACUUM FREEZE ANALYZE in each new
> database after the load and before using it.
>
> There is no need to go through all of those intermediate versions,
> but do be careful to test.  There are differences in string literal
> handling, bytea literals, and casting rules which may require
> adjustments in your software.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

--As of August 11, 2014 9:06:36 AM +0200, Marc Richter is alleged to have
said:

> I do not understand step 2) in your list: When all these REINDEX and
> Upgrading extra steps are prevented when using full dumps and psql, why
> is it necessary to update to 8.2.23 first?

--As for the rest, it is mine.

It's not really necessary, but upgrading to the most recent 'related'
version (in the 8.2 series) would mean if there were any bugfixes in that
series that affect you you'll get them before the big upgrade, which might
be helpful, and might smooth the bigger transition.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

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

Hey Keith,

the issue is, that I do not know what the reason was to use it this way
several years ago. Unfortunately, no one is employed in the company
anymore who might know this.
I have a database that should be migrated to newer PostgreSQL version,
which is several years old and no one knows about the details really.
I'm a system administrator and don't know even plain SQL well. So I
don't know about PostgreSQL's internal Data Structure even more.

So, these errors only mean that the pg_freespacemap - functions won't
work and there will no data be missing?

Am 15.08.2014 18:15, schrieb Keith:
>
>
>
> On Fri, Aug 15, 2014 at 8:31 AM, Marc Richter <mail@marc-richter.info
> <mailto:mail@marc-richter.info>> wrote:
>
>     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
>
>
>
>
>     --
>     Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org
>     <mailto:pgsql-novice@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/__mailpref/pgsql-novice
>     <http://www.postgresql.org/mailpref/pgsql-novice>
>
>
>
> If your reasoning for having this contrib module installed was to manage
> the freespace mapping issues prior to improvements added in 8.4, you may
> not need this contrib module anymore and can ignore the errors.
>
> http://www.chesnok.com/daily/2009/02/10/fsm-visibility-map-and-new-vacuum-awesomeness/
>
>

Great, thank you very much for this clarification :)

Marc

Am 18.08.2014 02:34, schrieb Keith:
> I'm not quite familiar with all that the pg_freespacemap contrib module
> did back in 8.2, but if it's anything like the current one, it just
> provided queries to return data for you to act upon.
> I've been using PostgreSQL since 8.4 myself and since then have found no
> typical usage for the pg_freespacemap extension any longer. I wouldn't
> worry about its functions not restoring properly in this case unless you
> find something to the contrary in your migration process.
>
>
> On Sun, Aug 17, 2014 at 11:36 AM, Marc Richter <mail@marc-richter.info
> <mailto:mail@marc-richter.info>> wrote:
>
>     Hey Keith,
>
>     the issue is, that I do not know what the reason was to use it this way
>     several years ago. Unfortunately, no one is employed in the company
>     anymore who might know this.
>     I have a database that should be migrated to newer PostgreSQL version,
>     which is several years old and no one knows about the details really.
>     I'm a system administrator and don't know even plain SQL well. So I
>     don't know about PostgreSQL's internal Data Structure even more.
>
>     So, these errors only mean that the pg_freespacemap - functions won't
>     work and there will no data be missing?
>
>     Am 15.08.2014 18:15, schrieb Keith:
>      >
>      >
>      >
>      > On Fri, Aug 15, 2014 at 8:31 AM, Marc Richter
>     <mail@marc-richter.info <mailto:mail@marc-richter.info>
>      > <mailto:mail@marc-richter.info <mailto:mail@marc-richter.info>>>
>     wrote:
>      >
>      >     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
>      >
>      >
>      >
>      >
>      >     --
>      >     Sent via pgsql-novice mailing list
>     (pgsql-novice@postgresql.org <mailto:pgsql-novice@postgresql.org>
>      >     <mailto:pgsql-novice@postgresql.org
>     <mailto:pgsql-novice@postgresql.org>>)
>      >     To make changes to your subscription:
>      > http://www.postgresql.org/__mailpref/pgsql-novice
>      >     <http://www.postgresql.org/mailpref/pgsql-novice>
>      >
>      >
>      >
>      > If your reasoning for having this contrib module installed was to
>     manage
>      > the freespace mapping issues prior to improvements added in 8.4,
>     you may
>      > not need this contrib module anymore and can ignore the errors.
>      >
>      >
>     http://www.chesnok.com/daily/2009/02/10/fsm-visibility-map-and-new-vacuum-awesomeness/
>      >
>      >
>
>
>     --
>     Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org
>     <mailto:pgsql-novice@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-novice
>
>

--
Freenode IRC: Judge | Homepage: http://www.marc-richter.info
PGP Key: 75D429DE | GitHub: The-Judge | Bitbucket: Judge82