Re: pg_dump native format will not restore correctly

"Scot Kreienkamp" <SKreien(at)la-z-boy(dot)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(at)la-z-boy(dot)com