Thread: Waarschuwing na pg_dumpall en restore

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.

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.
psql:db03.ak1.sql:10155: WARNING:  column "hplan_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "stp_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "plan_datum_start" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "plan_datum_eind" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "plan_inspanning" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "real_datum_start" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "real_datum_eind" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "med_uitvoering" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "conflict" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "conflict_text" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "handmatig_actief" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "mijlpaal" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "handmatig_groep" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "mijlpaaldatum" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "plan_pro_noshow_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10155: WARNING:  column "doc_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column "plan_status" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column "plan_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column "hplan_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column "stp_id" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
psql:db03.ak1.sql:10185: WARNING:  column "plan_datum_start" 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.

What are the consequences of these messages for the system?

Can I ignore the warnings and simply correct the views later?

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?

PROBLEM 3

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

D. Kniep

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!

Excerpts from Dick Kniep's message of vie jun 25 04:54:15 -0400 2010:

> 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

These are probably views constructed on top of system tables or views
that changed between 8.1 and 8.3.  You probably need to investigate the
"column foo does not exist" ones; my guess is that fixing those would
let subsequent commands that are failing with "relation foo does not
exist" to be automatically fixed.


The FK problems may be caused by problems 1 or 2, but my guess is that
they are actually caused by a real problem in your data.  Have a look at
the data in the original database; you should be able to find missing
rows in the referenced tables.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support