pg_upgrade problem (fwd)

I am sending this report to hackers to all hacker subscribers can read
the original bug report.

---------------------------------------------------------------------------

----- Forwarded message from hubert depesz lubaczewski -----

hi

I have 8.3.11 database, ~ 600GB in size.

I want to upgrade it to 9.0.

First, I tried with 9.0.4, and when I hit problem (the same) I tried
git, head of 9.0 branch.

So. I did pg_upgrade with -c, and it looked like this:

$ time pg_upgrade -c -v -b /opt/pgsql-8.3.11-int/bin/ -B /opt/pgsql-9.0.5a-int/bin/ -d /var/postgresql/6666/ -D /var/postgresql/6666-9.0 -k -l pg_upgrade.log -p 6666 -P 4329
Running in verbose mode
Performing Consistency Checks
-----------------------------
Checking old data directory (/var/postgresql/6666) ok
Checking old bin directory (/opt/pgsql-8.3.11-int/bin) ok
Checking new data directory (/var/postgresql/6666-9.0) ok
Checking new bin directory (/opt/pgsql-9.0.5a-int/bin) ok
"/opt/pgsql-8.3.11-int/bin/pg_ctl" -l "pg_upgrade.log" -D "/var/postgresql/6666" -o "-p 6666 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "pg_upgrade.log" 2>&1
Checking for reg* system oid user data types ok
Checking for /contrib/isn with bigint-passing mismatch ok
Checking for invalid 'name' user columns ok
Checking for tsquery user columns ok
Checking for tsvector user columns ok
Checking for hash and gin indexes warning

| Your installation contains hash and/or gin
| indexes. These indexes have different
| internal formats between your old and new
| clusters so they must be reindexed with the
| REINDEX command. After migration, you will
| be given REINDEX instructions.

Checking for bpchar_pattern_ops indexes ok
Checking for large objects ok
"/opt/pgsql-8.3.11-int/bin/pg_ctl" -l "pg_upgrade.log" -D "/var/postgresql/6666" stop >> "pg_upgrade.log" 2>&1
"/opt/pgsql-9.0.5a-int/bin/pg_ctl" -l "pg_upgrade.log" -D "/var/postgresql/6666-9.0" -o "-p 4329 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "pg_upgrade.log" 2>&1
Checking for presence of required libraries ok

*Clusters are compatible*
"/opt/pgsql-9.0.5a-int/bin/pg_ctl" -l "pg_upgrade.log" -D "/var/postgresql/6666-9.0" stop >> "pg_upgrade.log" 2>&1

real 0m6.417s
user 0m0.040s
sys 0m0.060s

All looks ok. So I ran the upgrade without -c:

$ time pg_upgrade -v -b /opt/pgsql-8.3.11-int/bin/ -B /opt/pgsql-9.0.5a-int/bin/ -d /var/postgresql/6666/ -D /var/postgresql/6666-9.0 -k -l pg_upgrade.log -p 6666 -P 4329
Running in verbose mode
Performing Consistency Checks
-----------------------------
Checking old data directory (/var/postgresql/6666) ok
Checking old bin directory (/opt/pgsql-8.3.11-int/bin) ok
Checking new data directory (/var/postgresql/6666-9.0) ok
Checking new bin directory (/opt/pgsql-9.0.5a-int/bin) ok
"/opt/pgsql-8.3.11-int/bin/pg_ctl" -l "pg_upgrade.log" -D "/var/postgresql/6666" -o "-p 6666 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "pg_upgrade.log" 2>&1
Checking for reg* system oid user data types ok
Checking for /contrib/isn with bigint-passing mismatch ok
Checking for invalid 'name' user columns ok
Checking for tsquery user columns ok
Creating script to adjust sequences ok
Checking for large objects ok
Creating catalog dump "/opt/pgsql-9.0.5a-int/bin/pg_dumpall" --port 6666 --username "postgres" --schema-only --binary-upgrade > "/var/postgresql/pg_upgrade_dump_all.sql"
ok
"/opt/pgsql-8.3.11-int/bin/pg_ctl" -l "pg_upgrade.log" -D "/var/postgresql/6666" stop >> "pg_upgrade.log" 2>&1
"/opt/pgsql-9.0.5a-int/bin/pg_ctl" -l "pg_upgrade.log" -D "/var/postgresql/6666-9.0" -o "-p 4329 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "pg_upgrade.log" 2>&1
Checking for presence of required libraries ok

| If pg_upgrade fails after this point, you must
| re-initdb the new cluster before continuing.
| You will also need to remove the ".old" suffix
| from /var/postgresql/6666/global/pg_control.old.

Performing Migration
--------------------
Adding ".old" suffix to old global/pg_control ok
Analyzing all rows in the new cluster "/opt/pgsql-9.0.5a-int/bin/vacuumdb" --port 4329 --username "postgres" --all --analyze >> "pg_upgrade.log" 2>&1
ok
Freezing all rows on the new cluster "/opt/pgsql-9.0.5a-int/bin/vacuumdb" --port 4329 --username "postgres" --all --freeze >> "pg_upgrade.log" 2>&1
ok
"/opt/pgsql-9.0.5a-int/bin/pg_ctl" -l "pg_upgrade.log" -D "/var/postgresql/6666-9.0" stop >> "pg_upgrade.log" 2>&1
Deleting new commit clogs ok
Copying old commit clogs to new server cp -Rf "/var/postgresql/6666/pg_clog" "/var/postgresql/6666-9.0/pg_clog"
ok
Setting next transaction id for new cluster "/opt/pgsql-9.0.5a-int/bin/pg_resetxlog" -f -x 3673553615 "/var/postgresql/6666-9.0" > /dev/null
ok
Resetting WAL archives "/opt/pgsql-9.0.5a-int/bin/pg_resetxlog" -l 1,26478,133 "/var/postgresql/6666-9.0" >> "pg_upgrade.log" 2>&1
ok
"/opt/pgsql-9.0.5a-int/bin/pg_ctl" -l "pg_upgrade.log" -D "/var/postgresql/6666-9.0" -o "-p 4329 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "pg_upgrade.log" 2>&1
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster "/opt/pgsql-9.0.5a-int/bin/psql" --set ON_ERROR_STOP=on --no-psqlrc --port 4329 --username "postgres" -f "/var/postgresql/pg_upgrade_dump_globals.sql" --dbname template1 >> "pg_upgrade.log"
psql:/var/postgresql/pg_upgrade_dump_globals.sql:26: NOTICE: schema "check_postgres" does not exist
psql:/var/postgresql/pg_upgrade_dump_globals.sql:26: NOTICE: schema "contrib" does not exist
psql:/var/postgresql/pg_upgrade_dump_globals.sql:57: NOTICE: schema "check_postgres" does not exist
psql:/var/postgresql/pg_upgrade_dump_globals.sql:57: NOTICE: schema "ltree" does not exist
psql:/var/postgresql/pg_upgrade_dump_globals.sql:57: NOTICE: schema "pgcrypto" does not exist
ok
"/opt/pgsql-9.0.5a-int/bin/pg_ctl" -l "pg_upgrade.log" -D "/var/postgresql/6666-9.0" stop >> "pg_upgrade.log" 2>&1
"/opt/pgsql-9.0.5a-int/bin/pg_ctl" -l "pg_upgrade.log" -D "/var/postgresql/6666-9.0" -o "-p 4329 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "pg_upgrade.log" 2>&1
Adding support functions to new cluster ok
Restoring database schema to new cluster "/opt/pgsql-9.0.5a-int/bin/psql" --set ON_ERROR_STOP=on --no-psqlrc --port 4329 --username "postgres" -f "/var/postgresql/pg_upgrade_dump_db.sql" --dbname template1 >> "pg_upgrade.log"
ok
Removing support functions from new cluster ok
"/opt/pgsql-9.0.5a-int/bin/pg_ctl" -l "pg_upgrade.log" -D "/var/postgresql/6666-9.0" stop >> "pg_upgrade.log" 2>&1
Restoring user relation files
/var/postgresql/6666/base/113953649/2613 linking /var/postgresql/6666/base/113953649/2613 to /var/postgresql/6666-9.0/base/11826/11790
/var/postgresql/6666/base/113953649/2683 linking /var/postgresql/6666/base/113953649/2683 to /var/postgresql/6666-9.0/base/11826/11792

Could not find 71637071 in old cluster

real 0m53.065s
user 0m0.520s
sys 0m0.870s

What can be wrong? How can I fix it?

I don't care about current instance - it was just a test, but I need to
know how to make the upgrade actually work.

I did grep in generated log files for this value - 71637071, and found:

$ grep -C3 71637071 pg_upgrade*
pg_upgrade_dump_all.sql-
pg_upgrade_dump_all.sql--- For binary upgrade, must preserve relfilenodes
pg_upgrade_dump_all.sql-SELECT binary_upgrade.set_next_heap_relfilenode('71637068'::pg_catalog.oid);
pg_upgrade_dump_all.sql:SELECT binary_upgrade.set_next_toast_relfilenode('71637071'::pg_catalog.oid);
pg_upgrade_dump_all.sql-SELECT binary_upgrade.set_next_index_relfilenode('71637073'::pg_catalog.oid);
pg_upgrade_dump_all.sql-
pg_upgrade_dump_all.sql-CREATE TABLE actions (
--
pg_upgrade_dump_db.sql-
pg_upgrade_dump_db.sql--- For binary upgrade, must preserve relfilenodes
pg_upgrade_dump_db.sql-SELECT binary_upgrade.set_next_heap_relfilenode('71637068'::pg_catalog.oid);
pg_upgrade_dump_db.sql:SELECT binary_upgrade.set_next_toast_relfilenode('71637071'::pg_catalog.oid);
pg_upgrade_dump_db.sql-SELECT binary_upgrade.set_next_index_relfilenode('71637073'::pg_catalog.oid);
pg_upgrade_dump_db.sql-
pg_upgrade_dump_db.sql-CREATE TABLE actions (
--
pg_upgrade.log-linking /var/postgresql/6666/base/113953649/2613 to /var/postgresql/6666-9.0/base/11826/11790
pg_upgrade.log- /var/postgresql/6666/base/113953649/2683
pg_upgrade.log-linking /var/postgresql/6666/base/113953649/2683 to /var/postgresql/6666-9.0/base/11826/11792
pg_upgrade.log:Could not find 71637071 in old cluster

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.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
----- End of forwarded message from hubert depesz lubaczewski -----

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

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