Re: [GENERAL] pg_upgrade problem
| От | Bruce Momjian |
|---|---|
| Тема | Re: [GENERAL] pg_upgrade problem |
| Дата | |
| Msg-id | 201108260418.p7Q4Itk17514@momjian.us обсуждение исходный текст |
| Ответ на | Re: [GENERAL] pg_upgrade problem (hubert depesz lubaczewski <depesz@depesz.com>) |
| Ответы |
Re: [GENERAL] pg_upgrade problem
(hubert depesz lubaczewski <depesz@depesz.com>)
|
| Список | pgsql-hackers |
OK, this was very helpful. I found out that there is a bug in current
9.0.X, 9.1.X, and HEAD that I introduced recently when I excluded temp
tables. (The bug is not in any released version of pg_upgrade.) The
attached, applied patches should fix it for you. I assume you are
running 9.0.X, and not 9.0.4.
---------------------------------------------------------------------------
hubert depesz lubaczewski wrote:
> On Thu, Aug 25, 2011 at 04:43:02PM -0400, Bruce Momjian wrote:
> > Please check the old cluster.
>
> Sure:
>
> =# SELECT reltoastrelid FROM pg_class WHERE relname = 'actions';
> reltoastrelid
> ---------------
> 82510395
> 71637071
> (2 rows)
>
> =# SELECT oid::regclass, reltoastrelid FROM pg_class WHERE relname = 'actions';
> oid | reltoastrelid
> ---------------+---------------
> xxxxx.actions | 82510395
> yyyyy.actions | 71637071
> (2 rows)
>
> =# select oid, relfilenode from pg_class where oid in (SELECT reltoastrelid FROM pg_class WHERE relname =
'actions');
> oid | relfilenode
> ----------+-------------
> 82510395 | 82510395
> 71637071 | 71637071
> (2 rows)
>
> =# select oid from pg_database where datname = current_database();
> oid
> ----------
> 71635381
> (1 row)
>
> $ ls -l 6666/base/71635381/{71637071,82510395}
> -rw------- 1 postgres postgres 0 2009-10-12 06:49 6666/base/71635381/71637071
> -rw------- 1 postgres postgres 0 2010-08-19 14:02 6666/base/71635381/82510395
>
> > > > > One more thing - one of earlier tests actually worked through
> > > > > pg_upgrade, but when running vacuumdb -az on newly started 9.0.4, I got
> > > > > error about missing transaction/clog - don't remember exactly what it
> > > > > was, though.
> > > > THere was a bug in how how pg_upgrade worked in pre-9.0.4 --- could it
> > > > have been that?
> > > It was done definitely using 9.0.4.
> > Good.
>
> Not sure if it's good, since it was after the clog error was fixed, and
> I still got it :/
>
> but anyway - the problem with 71637071 is more important now.
>
> Best regards,
>
> depesz
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c
new file mode 100644
index ca357e7..1f5b7ae
*** a/contrib/pg_upgrade/info.c
--- b/contrib/pg_upgrade/info.c
*************** get_rel_infos(migratorContext *ctx, cons
*** 328,336 ****
" ON c.reltablespace = t.oid "
"WHERE (( "
/* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
! " n.nspname !~ '^pg_' "
! " AND n.nspname != 'information_schema' "
! " AND c.oid >= %u "
" ) OR ( "
" n.nspname = 'pg_catalog' "
" AND relname IN "
--- 328,338 ----
" ON c.reltablespace = t.oid "
"WHERE (( "
/* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
! " n.nspname != 'pg_catalog' "
! " AND n.nspname !~ '^pg_temp_' "
! " AND n.nspname !~ '^pg_toast_temp_' "
! " AND n.nspname != 'information_schema' "
! " AND c.oid >= %u "
" ) OR ( "
" n.nspname = 'pg_catalog' "
" AND relname IN "
diff --git a/contrib/pg_upgrade/version_old_8_3.c b/contrib/pg_upgrade/version_old_8_3.c
new file mode 100644
index 930f76d..6fcd61b
*** a/contrib/pg_upgrade/version_old_8_3.c
--- b/contrib/pg_upgrade/version_old_8_3.c
*************** old_8_3_check_for_name_data_type_usage(m
*** 61,69 ****
" NOT a.attisdropped AND "
" a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
! " n.nspname !~ '^pg_' AND "
! " n.nspname != 'information_schema'");
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
--- 61,71 ----
" NOT a.attisdropped AND "
" a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
! /* exclude possibly orphaned temp tables */
! " n.nspname != 'pg_catalog' AND "
! " n.nspname !~ '^pg_temp_' AND "
! " n.nspname !~ '^pg_toast_temp_' AND "
! " n.nspname != 'information_schema' ");
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
*************** old_8_3_check_for_tsquery_usage(migrator
*** 152,160 ****
" NOT a.attisdropped AND "
" a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
! " n.nspname !~ '^pg_' AND "
! " n.nspname != 'information_schema'");
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
--- 154,164 ----
" NOT a.attisdropped AND "
" a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
! /* exclude possibly orphaned temp tables */
! " n.nspname != 'pg_catalog' AND "
! " n.nspname !~ '^pg_temp_' AND "
! " n.nspname !~ '^pg_toast_temp_' AND "
! " n.nspname != 'information_schema' ");
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
*************** old_8_3_rebuild_tsvector_tables(migrator
*** 252,260 ****
" NOT a.attisdropped AND "
" a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
! " n.nspname !~ '^pg_' AND "
! " n.nspname != 'information_schema'");
/*
* This macro is used below to avoid reindexing indexes already rebuilt
--- 256,266 ----
" NOT a.attisdropped AND "
" a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
! /* exclude possibly orphaned temp tables */
! " n.nspname != 'pg_catalog' AND "
! " n.nspname !~ '^pg_temp_' AND "
! " n.nspname !~ '^pg_toast_temp_' AND "
! " n.nspname != 'information_schema' ");
/*
* This macro is used below to avoid reindexing indexes already rebuilt
*************** old_8_3_rebuild_tsvector_tables(migrator
*** 271,278 ****
" NOT a.attisdropped AND " \
" a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \
" c.relnamespace = n.oid AND " \
! " n.nspname !~ '^pg_' AND " \
! " n.nspname != 'information_schema') "
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
--- 277,286 ----
" NOT a.attisdropped AND " \
" a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \
" c.relnamespace = n.oid AND " \
! " n.nspname != 'pg_catalog' AND " \
! " n.nspname !~ '^pg_temp_' AND " \
! " n.nspname !~ '^pg_toast_temp_' AND " \
! " n.nspname != 'information_schema')"
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
*************** old_8_3_create_sequence_script(migratorC
*** 641,649 ****
" pg_catalog.pg_namespace n "
"WHERE c.relkind = 'S' AND "
" c.relnamespace = n.oid AND "
! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
! " n.nspname !~ '^pg_' AND "
! " n.nspname != 'information_schema'");
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
--- 649,660 ----
" pg_catalog.pg_namespace n "
"WHERE c.relkind = 'S' AND "
" c.relnamespace = n.oid AND "
! /* exclude possibly orphaned temp tables */
! " n.nspname != 'pg_catalog' AND "
! " n.nspname !~ '^pg_temp_' AND "
! " n.nspname !~ '^pg_toast_temp_' AND "
! " n.nspname != 'information_schema' ");
!
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c
new file mode 100644
index 3ef3429..10188ca
*** a/contrib/pg_upgrade/info.c
--- b/contrib/pg_upgrade/info.c
*************** get_rel_infos(ClusterInfo *cluster, DbIn
*** 266,274 ****
" LEFT OUTER JOIN pg_catalog.pg_tablespace t "
" ON c.reltablespace = t.oid "
"WHERE relkind IN ('r','t', 'i'%s) AND "
! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
! " ((n.nspname !~ '^pg_' AND "
! " n.nspname NOT IN ('information_schema', 'binary_upgrade') AND "
" c.oid >= %u) "
" OR (n.nspname = 'pg_catalog' AND "
" relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index'%s) )) "
--- 266,275 ----
" LEFT OUTER JOIN pg_catalog.pg_tablespace t "
" ON c.reltablespace = t.oid "
"WHERE relkind IN ('r','t', 'i'%s) AND "
! /* exclude possible orphaned temp tables */
! " ((n.nspname !~ '^pg_temp_' AND "
! " n.nspname !~ '^pg_toast_temp_' AND "
! " n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade') AND "
" c.oid >= %u) "
" OR (n.nspname = 'pg_catalog' AND "
" relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index'%s) )) "
diff --git a/contrib/pg_upgrade/version_old_8_3.c b/contrib/pg_upgrade/version_old_8_3.c
new file mode 100644
index 1c736d2..d63050c
*** a/contrib/pg_upgrade/version_old_8_3.c
--- b/contrib/pg_upgrade/version_old_8_3.c
*************** old_8_3_check_for_name_data_type_usage(C
*** 59,67 ****
" NOT a.attisdropped AND "
" a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
! " n.nspname !~ '^pg_' AND "
! " n.nspname != 'information_schema'");
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
--- 59,68 ----
" NOT a.attisdropped AND "
" a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
! /* exclude possible orphaned temp tables */
! " n.nspname !~ '^pg_temp_' AND "
! " n.nspname !~ '^pg_toast_temp_' AND "
! " n.nspname NOT IN ('pg_catalog', 'information_schema')");
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
*************** old_8_3_check_for_tsquery_usage(ClusterI
*** 149,157 ****
" NOT a.attisdropped AND "
" a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
! " n.nspname !~ '^pg_' AND "
! " n.nspname != 'information_schema'");
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
--- 150,159 ----
" NOT a.attisdropped AND "
" a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
! /* exclude possible orphaned temp tables */
! " n.nspname !~ '^pg_temp_' AND "
! " n.nspname !~ '^pg_toast_temp_' AND "
! " n.nspname NOT IN ('pg_catalog', 'information_schema')");
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
*************** old_8_3_rebuild_tsvector_tables(ClusterI
*** 247,255 ****
" NOT a.attisdropped AND "
" a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
! " n.nspname !~ '^pg_' AND "
! " n.nspname != 'information_schema'");
/*
* This macro is used below to avoid reindexing indexes already rebuilt
--- 249,258 ----
" NOT a.attisdropped AND "
" a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
! /* exclude possible orphaned temp tables */
! " n.nspname !~ '^pg_temp_' AND "
! " n.nspname !~ '^pg_toast_temp_' AND "
! " n.nspname NOT IN ('pg_catalog', 'information_schema')");
/*
* This macro is used below to avoid reindexing indexes already rebuilt
*************** old_8_3_create_sequence_script(ClusterIn
*** 619,627 ****
" pg_catalog.pg_namespace n "
"WHERE c.relkind = 'S' AND "
" c.relnamespace = n.oid AND "
! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
! " n.nspname !~ '^pg_' AND "
! " n.nspname != 'information_schema'");
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
--- 622,631 ----
" pg_catalog.pg_namespace n "
"WHERE c.relkind = 'S' AND "
" c.relnamespace = n.oid AND "
! /* exclude possible orphaned temp tables */
! " n.nspname !~ '^pg_temp_' AND "
! " n.nspname !~ '^pg_toast_temp_' AND "
! " n.nspname NOT IN ('pg_catalog', 'information_schema')");
ntups = PQntuples(res);
i_nspname = PQfnumber(res, "nspname");
В списке pgsql-hackers по дате отправления: