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