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

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



Shankar Kotamarthy
Postgresql DBA



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/pg-upgrade-could-not-create-catalog-dump-while-upgrading-from-9-0-to-9-2-tp5763576.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

On 07/12/2013 11:25 PM, shankar.kotamarthy@gmail.com wrote:
> Thanks Adrian for your reply. You were right i am using Enterprise DB
> version for 9.2 and community version for 9.0. I am including the
> screenshot

 From what I can tell the community version of 9.2 does not have the
same fields as you show for the EDB version. They seem to be an addition
on the part of EDB. You will probably to need to contact the folks at
EDB on this to see if there is a way to have pg_upgrade make the
transition or if they have an alternate tool.


--
Adrian Klaver
adrian.klaver@gmail.com

On 07/12/2013 11:25 PM, shankar.kotamarthy@gmail.com wrote:
> Thanks Adrian for your reply. You were right i am using Enterprise DB
> version for 9.2 and community version for 9.0. I am including the
> screenshot

To follow up on my previous post, I do not use Enterprise DB, but a
little digging leads me to believe you will need to use the EDB
Migration Toolkit:

http://www.enterprisedb.com/docs/en/9.2/migrate/Table%2520of%2520Contents.htm


I base this on the following comment:

http://www.enterprisedb.com/docs/en/9.2/migrate/Postgres_Plus_Migration_Guide-06.htm#P98_9775

"Migration Toolkit also allows you to migrate between Advanced Server
and PostgreSQL."


--
Adrian Klaver
adrian.klaver@gmail.com