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 functions
that are part of unresolved dependancies. e.g., here's how I produced the
problem:

1. Edited the view lock_users, on which 6 other views depended.
2. This broke the 6 other views.
3. Tried to re-load the other views and had problems finding them all.
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 loaded;
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
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
lock_users correctly.

At a blind guess, I would hypothesize that the problem occurrs becuase pg_dump
is trying to backup stuff in correct dependancy order, but becuase of the
broken links gets confused and drops the object entirely. However, this
becomes a circular problem for Postgres db developers, as drop and restore is
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
view with 6 view dependancies, and itself depends on 2 tables and a custom
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
command-line text parsing is not sufficient to find the relevant section of
the log.

--
-Josh Berkus
Aglio Database Solutions
San Francisco