Skip site navigation (1) Skip section navigation (2)

Critical fix for pg_upgrade/pg_migrator users

From: momjian(at)postgresql(dot)org
To: PostgreSQL-announce <pgsql-announce(at)postgresql(dot)org>
Subject: Critical fix for pg_upgrade/pg_migrator users
Date: 2011-04-08 22:28:27
Message-ID: 201104082228.p38MSRk01583@momjian.us (view raw or flat)
Thread:
Lists: pgsql-announce
        Critical Fix for pg_upgrade/pg_migrator Users
        ---------------------------------------------

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.  You
might also want to make a backup of the pg_clog directory if you do not
already have a recent copy.  If you fail to take action promptly, it
might result in unexpected downtime.   

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=20

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-8.4.8 and pre-PG 9.0.4
	-- servers that were 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.
	--
	-- Depending on the size and configuration of your database, 
	-- this script might generate a lot of I/O and degrade database
	-- performance.  Users should execute this script during a low
	-- traffic period and monitor the database load.
	--
	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.
These releases will remove the need to run the above script after
upgrades by correctly restoring all TOAST tables in the migrated
databases.  However, databases which have already been upgraded still
need the script run, even if they are running Postgres 9.0.4.

For further details, see http://wiki.postgresql.org/wiki/20110408pg_upgrade_fix.

2011-04-08

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

pgsql-announce by date

Next:From: David FetterDate: 2011-04-11 05:30:23
Subject: == PostgreSQL Weekly News - April 10 2011 ==
Previous:From: Daniele VarrazzoDate: 2011-04-08 22:03:29
Subject: PostgreSQL Multiple Precision Arithmetic 1.0b1 available for testing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group