3

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"
6
  • 1
    What's in the latest log entry in e:/postgresdata/pg_log? Feb 24, 2013 at 4:34
  • @CraigRinger Nothing relevant to the conversion, as far as I can tell.
    – Phrogz
    Feb 25, 2013 at 3:42
  • It's odd that pg_upgrade directs pg_ctl output to nul. I'll ask about that; there must be a better way to diagnose this kind of issue. Feb 25, 2013 at 3:50
  • What user are you running the pg_upgrade command as? And what user owns the existing datadir and runs the existing PostgreSQL services? You may need to use runas.exe to run the command as user postgres. Feb 25, 2013 at 3:54
  • @CraigRinger Per the guide, I'm logged into a cmd.exe using runas 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 the postgres account is not a part of). Note that the 9.0 server is able to operate perfectly under this set of permissions.
    – Phrogz
    Feb 25, 2013 at 4:01

2 Answers 2

2

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?

2
  • I'm bringing the server in line with several other that are also running 9.1. The 9.0 server is on 5432, the 9.1 server is on 5433.
    – Phrogz
    Feb 25, 2013 at 5:40
  • 9.1 makes sense then. Usually it is the start that fails, not the stop. If you manually run the command without the NULL redirections, does it properly stop the server? Do you see an error? Feb 25, 2013 at 6:02
0

I had exactly the same issue. I resolved it (in the end ...) after doing a good-old ctrl-alt-delete, i.e. reboot the machine.

Probably it had to do that I tried to run this command several times before getting the syntax right in combination of letting the machine go into hibernate and back ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.