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"
e:/postgresdata/pg_log
?pg_upgrade
directspg_ctl
output tonul
. I'll ask about that; there must be a better way to diagnose this kind of issue.pg_upgrade
command as? And what user owns the existing datadir and runs the existing PostgreSQL services? You may need to userunas.exe
to run the command as userpostgres
.cmd.exe
usingrunas
as the postgres admin,postgres
. This same user has Full Control permissions allocated to the both the old and new data directories. In both cases the actual Owner (Properties▸Security▸Advanced▸Owner) is set to the Administrators group (which thepostgres
account is not a part of). Note that the 9.0 server is able to operate perfectly under this set of permissions.