Postgresql – Problems executing pg_ctl during pg_upgrade from 9.0 to 9.1 on Windows

postgresqlupgrade

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?