On Fri, Feb 4, 2011 at 7:34 PM, Shaun Thomas
<sthomas@peak6.com> wrote:
Why is it asking for the password over and over again? It shouldn't be doing that.
because I asked it to: -W
on the production server I need to enter password and I'm testing on dev first.
I just sudo tried it but still no report
and do you agree that I should turn CLUSTER ON ?
djns4=# cluster fastadder_fastadderstatus;
ERROR: there is no previously clustered index for table "fastadder_fastadderstatus"
djns4=# alter table fastadder_fastadderstatus CLUSTER ON fastadder_fastadderstatus_pkey; ALTER TABLE djns4=# CLUSTER fastadder_fastadderstatus; CLUSTER
ok, that's why I figured I was turning something on. the table has been altered.
it will be pk ordered, new entries always at the end and no deletes
but this means I have to manually run cluster from time to time, right ? not that there will be much or any reordering. or it should be fine going forward with vacuum and enlarging the free space memory map.
It's like vacuum full, in that it basically rebuilds the table and all indexes from scratch. The major issue you'll run into is that it reorders the table by the index you chose, so you'd best select the primary key unless you have reasons to use something else. And you have to do it table by table, which will really suck since we already know your whole db has bloated, not just one or two tables.
do we know that ? many of the tables are fairly static.
only this one is seriously borked, and yet other related tables seem to be fine.
You're going to be doing some scripting, buddy. :) Well, unless you just do a dump/restore and start over with sane postgresql.conf settings.
well who knew the defaults were unsane ? :)
scripting this is trivial, I already have the script
I have made the mistake of doing VACUUM FULL in the past. in fact on this table, and it had to be killed because it took down my entire website ! that may well be the major borking event. a credit to postgres that the table still functions if that's the case.
scott marlowe:
begin;
select * into temporaryholdingtable order by somefield;
truncate oldtable;
insert into oldtables select * from temporaryholdingtable;
commit;
that sounds like a good approach.
gentlemen, 300,000 + thanks for your generous time !
(a small number, I know)
-felix