Re: 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


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice