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

pg_upgrade problem (fwd)

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: pg_upgrade problem (fwd)
Date: 2011-08-26 00:54:26
Message-ID: 201108260054.p7Q0sQZ26458@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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. +

pgsql-hackers by date

Next:From: Dave CramerDate: 2011-08-26 02:11:25
Subject: Re: Why doesn't psql use the information schema to get ACL description ?
Previous:From: Tomas VondraDate: 2011-08-25 23:49:15
Subject: Re: PATCH: regular logging of checkpoint progress

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