Обсуждение: pg_dump and restore - views

Hi all,
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.

I even tried with pg_dumpall and still got the same result - any ideas to troubleshoot?

Thanks

"Wang, Marcus" <Marcus.Wang@team.telstra.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

Thanks for the reply Tom. Since v8.x is not SOE yet, we can't use it for
now - as we are trying to comply with internal standards :(
As far as I know - tables are pre existing and just added views for the
new group of users.

Regards
Marcus


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, 20 April 2006 12:40 PM
To: Wang, Marcus
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] pg_dump and restore - views

"Wang, Marcus" <Marcus.Wang@team.telstra.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