I'm trying to upgrade my 9.0 PSQL to 9.1 on Windows Server 2003. I've installed 9.1, stopped both services, and generally followed all steps on this guide. When I actually run the command, however, I get this:
E:\pgtemp>"c:/program files/postgresql/9.1/bin/pg_upgrade.exe" --old-datadir "e:/postgresdata" --new-datadir "e:\pgdata" --old-bindir "c:/program files/postgresql/9.0/bin" --new-bindir "c:/program files/postgresql/9.1/bin"
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system oid user data types ok
Checking for invalid indexes from concurrent index builds ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating catalog dump ok
There were problems executing ""c:/program files/postgresql/9.0/bin/pg_ctl" -w -l "nul" -D "e:/postgresdata" stop >> "nul" 2>&1"
Failure, exiting
How can I determine what the error was, and (more importantly) how to fix it/work around it?
I have tried entering the command "c:/program files/postgresql/9.0/bin/pg_ctl" -w -l "nul" -D "e:/postgresdata" stop >> "nul" 2>&1
verbatim into the command line and received no error.
Upgrading from version 9.0.1 to 9.1.8
Edit: After deleting the contents of the temp directory where the command was executed (since it left some *.sql
files lying about), and then deleting the postgresql.pid
file it left lying in the new directory, and running the command again along with --logfile upgrade.log
on the command, here's the log output:
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
""c:/program files/postgresql/9.0/bin/pg_ctl" -w -l "nul" -D "e:/postgresdata" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "nul" 2>&1"
executing: SELECT datcollate, datctype FROM pg_catalog.pg_database WHERE datname = 'template0'
executing: SELECT pg_catalog.pg_encoding_to_char(encoding) FROM pg_catalog.pg_database WHERE datname = 'template0'
executing: SELECT c.relname, c.relfilenode FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND n.nspname = 'pg_catalog' AND c.relname = 'pg_database' ORDER BY c.relname
executing: SELECT d.oid, d.datname, t.spclocation FROM pg_catalog.pg_database d LEFT OUTER JOIN pg_catalog.pg_tablespace t ON d.dattablespace = t.oid WHERE d.datallowconn = true ORDER BY 2
executing: SELECT c.oid, n.nspname, c.relname, c.relfilenode, c.reltablespace, t.spclocation FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid WHERE relkind IN ('r','t', 'i', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade') AND c.oid >= 16384) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index', 'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index') )) ORDER BY 1;
executing: SELECT c.oid, n.nspname, c.relname, c.relfilenode, c.reltablespace, t.spclocation FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid WHERE relkind IN ('r','t', 'i', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade') AND c.oid >= 16384) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index', 'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index') )) ORDER BY 1;
executing: SELECT spclocation FROM pg_catalog.pg_tablespace WHERE spcname != 'pg_default' AND spcname != 'pg_global'
executing: SELECT DISTINCT probin FROM pg_catalog.pg_proc WHERE prolang = 13 /* C */ AND probin IS NOT NULL AND oid >= 16384;
executing: SELECT 1 FROM pg_catalog.pg_proc JOIN pg_namespace ON pronamespace = pg_namespace.oid WHERE proname = 'plpython_call_handler' AND nspname = 'public' AND prolang = 13 /* C */ AND probin = '$libdir/plpython' AND pg_proc.oid >= 16384;
executing: SELECT DISTINCT probin FROM pg_catalog.pg_proc WHERE prolang = 13 /* C */ AND probin IS NOT NULL AND oid >= 16384;
executing: SELECT 1 FROM pg_catalog.pg_proc JOIN pg_namespace ON pronamespace = pg_namespace.oid WHERE proname = 'plpython_call_handler' AND nspname = 'public' AND prolang = 13 /* C */ AND probin = '$libdir/plpython' AND pg_proc.oid >= 16384;
Checking database user is a superuser executing: SELECT rolsuper, oid FROM pg_catalog.pg_roles WHERE rolname = current_user
executing: SELECT COUNT(*) FROM pg_catalog.pg_roles
ok
Checking for prepared transactions executing: SELECT * FROM pg_catalog.pg_prepared_xacts
ok
Checking for reg* system oid user data types executing: SELECT n.nspname, c.relname, a.attname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid IN ( 'pg_catalog.regproc'::pg_catalog.regtype, 'pg_catalog.regprocedure'::pg_catalog.regtype, 'pg_catalog.regoper'::pg_catalog.regtype, 'pg_catalog.regoperator'::pg_catalog.regtype, 'pg_catalog.regconfig'::pg_catalog.regtype, 'pg_catalog.regdictionary'::pg_catalog.regtype) AND c.relnamespace = n.oid AND n.nspname != 'pg_catalog' AND n.nspname != 'information_schema'
executing: SELECT n.nspname, c.relname, a.attname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid IN ( 'pg_catalog.regproc'::pg_catalog.regtype, 'pg_catalog.regprocedure'::pg_catalog.regtype, 'pg_catalog.regoper'::pg_catalog.regtype, 'pg_catalog.regoperator'::pg_catalog.regtype, 'pg_catalog.regconfig'::pg_catalog.regtype, 'pg_catalog.regdictionary'::pg_catalog.regtype) AND c.relnamespace = n.oid AND n.nspname != 'pg_catalog' AND n.nspname != 'information_schema'
ok
Checking for invalid indexes from concurrent index builds executing: SELECT n.nspname, c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_index i WHERE (i.indisvalid = false OR i.indisready = false) AND i.indexrelid = c.oid AND c.relnamespace = n.oid AND n.nspname != 'pg_catalog' AND n.nspname != 'information_schema' AND n.nspname != 'pg_toast'
executing: SELECT n.nspname, c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_index i WHERE (i.indisvalid = false OR i.indisready = false) AND i.indexrelid = c.oid AND c.relnamespace = n.oid AND n.nspname != 'pg_catalog' AND n.nspname != 'information_schema' AND n.nspname != 'pg_toast'
ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating catalog dump ""c:/program files/postgresql/9.1/bin/pg_dumpall" --port 5432 --username "postgres" --schema-only --binary-upgrade -f "E:\pgtemp/pg_upgrade_dump_all.sql""
ok
""c:/program files/postgresql/9.0/bin/pg_ctl" -w -l "nul" -D "e:/postgresdata" stop >> "nul" 2>&1"
There were problems executing ""c:/program files/postgresql/9.0/bin/pg_ctl" -w -l "nul" -D "e:/postgresdata" stop >> "nul" 2>&1"
""c:/program files/postgresql/9.0/bin/pg_ctl" -w -l "nul" -D "e:/postgresdata" -m fast stop >> "nul" 2>&1"
There were problems executing ""c:/program files/postgresql/9.0/bin/pg_ctl" -w -l "nul" -D "e:/postgresdata" -m fast stop >> "nul" 2>&1"
Best Answer
Wow, good question. PG 9.2 has vastly improved pg_upgrade logging, for the exact reason you are seeing here --- that failures just aren't reported well, particularly on Windows where we can't output two streams to the same file. Is there a reason you are going to 9.1 and not 9.2?
It is also odd that the stop failed, while the start worked. Usually the failure is with server start. Are you using any non-default ports or anything?