pg_upgrade problem

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/