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.
Both servers respond identically to nslookup localhost.
I'm not sure what to try next.
