Re: pg_dump native format will not restore correctly

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Sam Mason
Sent: Thursday, November 05, 2009 10:50 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] pg_dump native format will not restore correctly

On Thu, Nov 05, 2009 at 10:41:54AM -0500, Scot Kreienkamp wrote:
> 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.

That's to be expected; it's trying to copy all 5 million rows into the
psql process and that'll take a while. I presume you weren't trying
a similar test on the original box as you'd have got exactly the same
thing. Running COUNT(*) on the table is probably a better way to make
sure you've got all the rows you'd expect in there as you'll only have
to copy a single row over to psql--it'll still take a while for PG to
churn through things though. iostat or vmstat are good tools to see what
the database box is doing while it's working.

[Scot Kreienkamp]
I guess I could buy that, but why would it show up suddenly in one
night? We've never had a problem with this prior to last night.
Otherwise our reporting would have found it. Those queries didn't
change from yesterday to today.

Thanks,

Scot Kreienkamp
skreien(at)la-z-boy(dot)com