Pg_dump Backup Drops a Few Things

Josh Berkus <josh@agliodbs.com> writes:

Today I just got done with a very painful restore. PG_dump had failed to back
up a small view on which 6 other views and functions depended, and I had to
spend several hours editing the 99mb backup file by hand.

Urgh.

I'd like to work with someone on pinpointing the problem, as obviously this
could be a critical issue for production databases. However, I'm not sure
how to submit the files as they are very large (> 90mb) and how can I send
the original database not as a backup file?

Mailing around 90mb of data seems painful, and in any case the dump will
not show the cause of the problem.

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...)

regards, tom lane

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

At 13:08 16/07/2002 -0700, Josh Berkus wrote:

I'd like to work with someone on pinpointing the problem, as obviously this
could be a critical issue for production databases. However, I'm not sure
how to submit the files as they are very large (> 90mb) and how can I send
the original database not as a backup file?

If Tom's not already on top of this, I'd be happy to help. There seem to be
a few possibilities:

1. The pg_* tables have bad data in them, causing pg_dump to fail and not
report the failure.
2. The pg_* tables are fine, and pg_dump has a serious problem.
3. The data being dumped is somehow relevant.

We can remove (3) by just doing a schema-only dump of the original DB. If
this works, we know it's data related. Ugh.

Most likely it's (1), and someone will need to do as Tom suggested, and run
through pg_dump on the database in question. Assuming you have a vanilla PG
install, shutting down the database and copying the files *may* work. I am
happy to ftp the files if you are comfortable with that, although debugging
on-site would be easier.

Did you see any warning or error messages during the dump or restore?

Are you dumping to a script, tar file, or custom backup file? If either of
the last two, does 'pg_restore -l' on the backup file show the lost view?
When you dump the database (using '-v'), do you see the view in the output?

--
-Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Phillip,

If Tom's not already on top of this, I'd be happy to help. There seem to be
a few possibilities:

See my last e-mail. I'm not on -bugs, so my responses are delayed by the
moderation process.

We can remove (3) by just doing a schema-only dump of the original DB. If
this works, we know it's data related. Ugh.

It's not data related ... the missing object is a view.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

At 17:21 17/07/2002 -0700, Josh Berkus wrote:

We can remove (3) by just doing a schema-only dump of the original DB. If
this works, we know it's data related. Ugh.

It's not data related ... the missing object is a view.

I actually meant here that it may be that dumping the data may somehow be
causing pg_dump to fail to dump the schema correctly, though this is the
least likely of the 3 possibilities.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/