Re: Upgraded, now permission denied.

On Jun 11, 2014 8:00 PM, "Jason Whitener" <jwhitene(at)pcc(dot)edu> wrote:
>
> I did a pg_dumpall on PostgreSQL 8.1.23 and then imported that data to
8.4.20 following this guide:
http://www.postgresql.org/docs/8.4/static/install-upgrading.html
>
> Queries that used to run on 8.1 now complain about relations not found,
like:
>
> Select * from uiddate where dateonline >= '#hourago#'
>
> returns: ERROR: relation "uiddate" does not exist Position: 15
>
> The database name is 'mypcc'. The schema is 'public'. I can visibly see
the table 'uiddate' in the PgAdmin gui.
>
> If I do a query like:
> SELECT * FROM public.uiddate where dateonline >= '2014-06-11 14:33:14';
>
> It returns:
> ERROR: permission denied for schema public
>
> I checked on permissions:
> SELECT datname as "Relation", datacl as "Access permissions" FROM
pg_database WHERE datname = 'mypcc'; Relation | Access permissions
> ----------+--------------------
> mypcc | {mypcc=CTc/mypcc}
> (1 row)
>
> This is my first upgrade, so I guess I must be missing something simple.
>
> Visibly in the PgAdmin gui, it looks like the data import went fine. I
see all my tables and users.
>
> This query is being initiated by an app running on the same host. I made
sure that the 8.4 pg_hba.conf lines for localhost matched what I had on the
8.1 server.
>
> host all all 127.0.0.1/32 password
>
> Both servers respond identically to nslookup localhost.
>
> I'm not sure what to try next.
>
>

Did you get any errors during the restore? If so, that could give us some
more insight to the cause. Also, if possible make sure you do the dump
with the 8.4 pg_dump to ensure it works best restoring to the new version.

Keith