Обсуждение: Pg_dump Backup Drops a Few Things

Folks,

I am not subscribed to -bugs.  Please e-mail me directly.

For some time on 7.2.1 I have suspected that one or two items from large,=
=20
complex databases was not getting backed up.  However, I could not say=20
conclusively that this was the case, as it was always possible that I had=
=20
missed something somewhere.

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

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

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

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

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   |/

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   |/

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

Phillip,

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

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

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

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

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