Re: pg_upgrade could not create catalog dump while upgrading from 9.0 to 9.2

On 07/12/2013 04:56 AM, shankar.kotamarthy@gmail.com wrote:
> Hello,
> I am upgrading from postgresql 9.0 to 9.2 using the utility pg_upgrade.
> Creation of catalog dump failed .The reason is in 9.2 view pg_roles has a
> column "rolecreatedblink" which is missing in 9.0.Please help me to move
> forward.
> Thanks in Advance
>
> *****Last lines in pg_upgradeutility.log******************************
> command: "/u01/PostgresPlus/9.2AS/bin/pg_dumpall" --port 50432 --username
> "postgres" --schema-only --binary-upgrade  -f pg_upgrade_dump_all.sql >>
> "pg_upgrade_utility.log" 2>&1
> pg_dumpall: query failed: ERROR:  column "rolcreatedblink" does not exist
> LINE 1: ...rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcreated...
>                                                               ^
> pg_dumpall: query was: SELECT oid, rolname, rolsuper, rolinherit,
> rolcreaterole, rolcreatedb, rolcreatedblink, rolcreatepublicdblink,
> roldroppublicdblink, false as rolpolicyexempt, rolcanlogin, rolconnlimit,
> rolpassword, rolvaliduntil, false as rolreplication,
> pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment FROM pg_authid
> ORDER BY 2
>

There is something else going here. I am not seeing that column in the docs:

http://www.postgresql.org/docs/9.2/interactive/view-pg-roles.html

nor in the database:

postgres=# select version();


                                                            version



-----------------------------------------------------------------------------------------------------------------------------


  PostgreSQL 9.2.3

postgres=# \d+ pg_roles
                            View "pg_catalog.pg_roles"
      Column     |           Type           | Modifiers | Storage  |
Description
----------------+--------------------------+-----------+----------+-------------
  rolname        | name                     |           | plain    |
  rolsuper       | boolean                  |           | plain    |
  rolinherit     | boolean                  |           | plain    |
  rolcreaterole  | boolean                  |           | plain    |
  rolcreatedb    | boolean                  |           | plain    |
  rolcatupdate   | boolean                  |           | plain    |
  rolcanlogin    | boolean                  |           | plain    |
  rolreplication | boolean                  |           | plain    |
  rolconnlimit   | integer                  |           | plain    |
  rolpassword    | text                     |           | extended |
  rolvaliduntil  | timestamp with time zone |           | plain    |
  rolconfig      | text[]                   |           | extended |
  oid            | oid                      |           | plain    |
View definition:
  SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit,


     pg_authid.rolcreaterole, pg_authid.rolcreatedb,
pg_authid.rolcatupdate,

     pg_authid.rolcanlogin, pg_authid.rolreplication,
pg_authid.rolconnlimit,

     '********'::text AS rolpassword, pg_authid.rolvaliduntil,


     s.setconfig AS rolconfig, pg_authid.oid


    FROM pg_authid


    LEFT JOIN pg_db_role_setting s ON pg_authid.oid = s.setrole AND
s.setdatabase = 0::oid;


I noticed from above you are using PostgresPlus, are you using one of
EDB extended versions of Postgres?

>
>
> Shankar Kotamarthy
> Postgresql DBA
>
>
>
> --
>

--
Adrian Klaver
adrian.klaver@gmail.com