Re: pg_upgrade problem - Mailing list pgsql-general

From Melvin Davidson
Subject Re: pg_upgrade problem
Date
Msg-id CANu8FiwkGNHUuJPEnKeMbmDgUgaFAqQLgJyM7YOm5H=V+eVRyw@mail.gmail.com
Whole thread Raw
In response to Re: pg_upgrade problem  (Jerry Sievers <gsievers19@comcast.net>)
List pgsql-general
Yup, that looks like the problem.
Doing:
postgres=# SELECT * FROM pg_auth_members
postgres-# WHERE roleid NOT IN (SELECT oid FROM pg_authid);

Yields, 11 Rows, so for sure someone must have been messing around. Thanks.


On Tue, Jun 16, 2015 at 11:58 AM, Jerry Sievers <gsievers19@comcast.net> wrote:
Melvin Davidson <melvin6925@gmail.com> writes:

> Using pg_upgrade in 9.4 CentOS release 6.6 (Final) (from PostgreSQL 9.1.15) it fails when GRANTING permits to roles.
>
> Checking pg_upgrade_dump_globals.sql, I see the point of failure is caused by the -- Role memberships section.
>
> In there I see the following troublesome lines.
> -- Role memberships
> ...
> ...
> ....
> GRANT "supers" TO "pgpoolad" GRANTED BY "postgres";
> GRANT "" TO "";
> GRANT "" TO "";
> GRANT "" TO "";
> GRANT "" TO "";
> GRANT "" TO "";
> GRANT "" TO "";
> GRANT "" TO "";
> GRANT "" TO "";
> GRANT "" TO "" GRANTED BY "postgres";
> GRANT "" TO "";
> GRANT "" TO "";
>
> Doing a pg_dumpall -g on the database produces the same result.

Well then I don't presume this is a pg_upgrade issue.

Inspect your pg_auth_members catalog for entries referring to rows
absent from pg_authid.

Did someone manually remove rows from pg_authid?

> I am pretty sure this is catalog corruption.
>
> Can anyone else confirm and/or suggest a recommended fix?
>
> --
> Melvin Davidson
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

pgsql-general by date:

Previous
From: Jerry Sievers
Date:
Subject: Re: pg_upgrade problem
Next
From: Alvaro Herrera
Date:
Subject: Re: [HACKERS] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1