Re: Upgrading from PG 8.2.5 to 9.1.13

On Fri, Aug 8, 2014 at 9:34 AM, Marc Richter <mail(at)marc-richter(dot)info> wrote:

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

A full dump & restore using pg_dump/pg_restore will negate having to
manually do any reindexing steps. Only index definitions are stored in a
pg_dump file, not the actual indexes themseves, so the restore process
takes care of recreating them all.

When upgrading any minor version number (the last number of the 3 in the
version number), typically all that is required is a restart of the
database. Hence the extra steps you see for upgrades such as 8.4.2 to
8.4.12. Since you are doing a full dump & restore, you can ignore such
issues.

Again, just doing a full dump and restore using the binaries from the newer
version should be sufficient for your upgrade. Hope that helps your sanity.
:)

Keith Fiske
http://www/keithf4.com