Re: Waarschuwing na pg_dumpall en restore

On 25 Jun 2010, at 10:54, Dick Kniep wrote:

> Hi List,
>
> Thanks for this great product.
>
> We have a database with many schema's and we are trying to migrate the db from version 8.1.9 to 8.3.8.
>
> So we made a pg_dumpall.

Using which version? The one that came with 8.3? I'm suspecting that you used the one from 8.1.9, which would cause
problemsif you try to restore that in an 8.3 database. 

Also, 8.3.8 isn't the latest minor version, there's a (minor) security fix in 8.3.11 for example. Since you're still
upgrading,now would be a good time to pick the latest minor release of 8.3. 

>  Now, when we try to restore with psql, we get loads of errors and warnings:
>
> PROBLEM 1
>
> psql:db03.ak1.sql:10155: WARNING:  column "plan_status" has type "unknown"
> DETAIL:  Proceeding with relation creation anyway.
> psql:db03.ak1.sql:10155: WARNING:  column "plan_id" has type "unknown"
> DETAIL:  Proceeding with relation creation anyway.
...
> From earlier reports on the mailinglist, I assume it is a view that has no explicit cast.

No need to assume anything, the error shows you at which line the problem is, so you can verify whether the problem is
indeedcaused by a view definition. 

> What are the consequences of these messages for the system?
>
> Can I ignore the warnings and simply correct the views later?

If these are about a view, yeah - it won't be used to alter data during the restore.

> PROBLEM 2
>
> Furthermore the following errors are shown:
>
> psql:db03.ak1.sql:251166: ERROR:  column a.transaction does not exist
> LINE 2:     SELECT (a.relation)::regclass AS "table", a."transaction...
>                                                       ^
> psql:db03.ak1.sql:251169: ERROR:  relation "public.locks_tb_aux" does not exist
> psql:db03.ak1.sql:251176: ERROR:  relation "locks_tb_aux" does not exist
> psql:db03.ak1.sql:251179: ERROR:  relation "public.locks_tb" does not exist
> psql:db03.ak1.sql:251186: ERROR:  column a.transaction does not exist
> LINE 2:     SELECT a."transaction", a.pid AS pid_locked, b.pid AS pi...
>                    ^
> psql:db03.ak1.sql:251189: ERROR:  relation "public.locks_tr_aux" does not exist
> psql:db03.ak1.sql:251196: ERROR:  relation "locks_tr_aux" does not exist
> psql:db03.ak1.sql:251199: ERROR:  relation "public.locks_tr" does not exist
> psql:db03.ak1.sql:251206: ERROR:  column a.transaction does not exist
> LINE 2:     SELECT a."transaction", a.pid AS pid_locked, b.pid AS pi...
>                    ^
> psql:db03.ak1.sql:251209: ERROR:  relation "public.locks_trid_aux" does not exist
> psql:db03.ak1.sql:251216: ERROR:  relation "locks_trid_aux" does not exist
> psql:db03.ak1.sql:251219: ERROR:  relation "public.locks_trid" does not exist
>
> These look to me as system tables and are certainly not defined by us.
>
> Can I ignore these messages?

These are probably real problems that you shouldn't ignore, especially if they're related to system tables.

I think these are caused by using the "old" version of pg_dumpall or psql to create the dump. It's quite possible that
thedesign of some system tables has changed between the two versions, and the "old" pg_dumpall/psql have no way of
knowingabout that. The newer versions have. 

>
> PROBLEM 3
>
> We get some messages that referential integrity rules (foreign keys) are violated. How can that be? This undermines
myconfidence in the system!! This would imply that the foreign key at one time did not exist, BUT WHEN IT WAS CREATED
ITDID NOT COMPLAIN THAT THE RULES WERE VIOLATED....... 

These are probably the results of previous errors. For example, because of these errors some data wasn't inserted while
laterdata depends on it, causing a constraint violation. 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4c249b98286211487176982!