From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Wang, Marcus" <Marcus(dot)Wang(at)team(dot)telstra(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: pg_dump and restore - views |
Date: | 2006-04-20 02:39:36 |
Message-ID: | 6243.1145500776@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
"Wang, Marcus" <Marcus(dot)Wang(at)team(dot)telstra(dot)com> writes:
> We've got postgresql 7.4 and a few views. I'm using pg_dump to backup
> the database and everything is ok. Recently, we added 5 new views - but
> when I restore the dump with psql, out of 5 new views, only 3 is
> recreated, 2 are missing. But when I check the dump file - those views
> are in the dump, but somehow they are not recreated. While the database
> is being restored - I've got error messages for relattioni "view_name"
> does not exist.
Sounds to me like pg_dump is dumping the views in the wrong order,
ie, before the tables they depend on. This is a generic hazard in
pre-8.0 releases. It's likely to occur any time you use CREATE OR
REPLACE VIEW to make a view refer to a table that was created after the
view was first created, because pg_dump mostly goes by creation order
to determine dump order.
Fixes: manually adjust the dump-script order (pg_restore -l can help);
drop the views entirely in the source DB, and recreate them; update
to PG 8.x which has a smarter pg_dump.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Wang, Marcus | 2006-04-20 04:44:01 | Re: pg_dump and restore - views |
Previous Message | Wang, Marcus | 2006-04-20 02:09:44 | pg_dump and restore - views |