Re: Pg_dump Backup Drops a Few Things

Tom,

> Do you still have the original database available?  The obvious route to
> finding the problem is to watch pg_dump in action and see why it misses
> that view.  How do you feel about letting someone else have access to
> your system to do this?  (Or get out a debugger and do it yourself...)

OK, more specifics:  The problem only seems to happen with views and functi=
ons=20
that are part of unresolved dependancies.   e.g., here's how I produced the=
=20
problem:

1. Edited the view lock_users, on which 6 other views depended.=20
2. This broke the 6 other views.
3.  Tried to re-load the other views and had problems finding them all.=20=
=20
Decided to dump and restore to resolve the dependancies.
4. Did a text pg_dump (not binary).
5. Dropped database and reloaded.  Discovered that lock_users was not loade=
d;=20
in fact, it wasn't part of the pg_dump file at all.
6. Hand-edited the pg_dump file (yay Joe text editor!) and re-inserted the=
=20
lock_users view after its dependancies, but before the other views.
7. Re-loaded the database.  After a couple of tries, it worked.

As the broken dependancy problem no longer exists, futher pg_dumps now back=
 up=20
lock_users correctly.=20

At a blind guess, I would hypothesize that the problem occurrs becuase pg_d=
ump=20
is trying to backup stuff in correct dependancy order, but becuase of the=
=20
broken links gets confused and drops the object entirely.   However, this=
=20
becomes a circular problem for Postgres db developers, as drop and restore =
is=20
one of the primary ways of fixing broken dependancy chains.

I will see if I can re-produce this on a sample database.   lock_users is a=
=20
view with 6 view dependancies, and itself depends on 2 tables and a custom=
=20
function.  So I can see how this would be a destruction test.

I do have the Postgresql log files for the last few days, but my mastery of=
=20
command-line text parsing is not sufficient to find the relevant section of=
=20
the log.=20=20

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco