Re: pg_dump native format will not restore correctly

"Scot Kreienkamp" <SKreien@la-z-boy.com> writes:
> I have successfully been doing text based backups and restores to a
few
> servers for reporting and testing purposes for a few years now.  Due
to
> growth in the database I am switching to the native format backup and
> using pg_restore to restore the database.  The problem I've run into
is
> that at least one table is inaccessible after the restore finishes.
Any
> queries to that table just hang.  Other tables seem to work ok.  I
have
> no idea why.

That's pretty bizarre, because one of the standard test procedures we
use is to check that the SQL emitted by pg_restore from an -Fc backup
is exactly the same as a text-mode dump.  I have to think there's
something else you did differently.

Have you looked into pg_locks to see if there's a lock blocking your
query?  Have you tried comparing EXPLAIN results to see if the query
plan is the same?  (If not, maybe you forgot an ANALYZE step?)

            regards, tom lane
[Scot Kreienkamp]

It's definitely not locks.  I'm exercising this on a sandbox server that
I'm the only one that has access to it.  And it's not a complex query by
any means.  It's a simple select query.


Here's the query on our production server:
psql -U postgres -d rms-prod -c "explain select * from soldtrx" -h
rets5000
                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on soldtrx  (cost=0.00..223945.51 rows=4833151 width=276)
(1 row)


Here's the query in the sandbox:
psql -U postgres -d rms-prod -c "explain select * from soldtrx"
                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on soldtrx  (cost=0.00..223459.51 rows=4833151 width=278)
(1 row)

Another strange thing I just found when composing this email... If I set
limit 5 on the query it works on the soldtrx table, but if I don't set a
limit it just hangs.

Thanks,

Scot Kreienkamp
skreien@la-z-boy.com