Re: pg_upgrade bug found!

Bruce,

* Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> OK, here is a draft email announcement:

Couple suggestions (also on IRC):

> ---------------------------------------------------------------------------

A bug has been discovered in all released versions of pg_upgrade and
(formerly) pg_migrator. Anyone who has used pg_upgrade or pg_migrator
should take the following corrective actions as soon as possible.

This bug can cause queries to return the following error:

ERROR: could not access status of transaction ######
DETAIL: could not open file "pg_clog/####": No such file or directory

This error prevents access to very wide values stored in the database.
To prevent such failures users need to run the following psql script,
as the superuser, in all upgraded databases as soon as possible:

-- This script fixes data in pre-PG 9.0.4 and pre-8.4.8
-- servers that was upgraded by pg_upgrade and pg_migrator.
-- Run the script using psql for every database in the cluster
-- except 'template0', e.g.:
-- psql -U postgres -a -f pg_upgrade_fix.sql dbname
-- This must be run from a writable directory.
-- It will not lock any tables but will generate I/O.
--
CREATE TEMPORARY TABLE pg_upgrade_fix AS
SELECT 'VACUUM FREEZE pg_toast.' || c.relname || ';'
FROM pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid AND
n.nspname = 'pg_toast' AND
c.relkind = 't'
ORDER by c.oid;
\copy pg_upgrade_fix TO 'pg_upgrade_tmp.sql';
\i pg_upgrade_tmp.sql

A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4.
The fixed version of pg_uprade will remove the need for the above script
by correctly updating the TOAST tables in the migrated database.

Thanks,

Stephen