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

On 07/12/2013 04:56 AM, shankar(dot)kotamarthy(at)gmail(dot)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(dot)klaver(at)gmail(dot)com