URGENT: Index problems

apparently this is the fix:

psql -d mydb -c "select * from title;" > before
# Save for after cmp test...
cd /usr/local/pgsql # my install root...
vacuumdb mydb # clean up before moving...
cp -pr data data.backup # make a backup...
pg_dumpall -s > schema.sql # dump schema w/out data...
killall postmaster # stop the server...
sleep 3
mv data data.old # set it aside...
cd /usr/src/pgsql/src # to the src tree...
gmake install # reinstall binaries...
cd /usr/local/pgsql # back to my install root...
initdb # recreate template1, sys
stuff...
postmaster -i -o "-F -S 4096 -s" >& log & # restart server...
sleep 3
pg_upgrade -f schema.sql data >& upgrade.log # reload schema...
cp -p data.old/base/mydb/* data/base/mydb/ # replace data...
psql -d mydb -c "select * from title;" > after
# verify we still have data...
diff before after # quick sanity check...

will let you know if it works ...
"Steve Brett" <steve.brett@e-mis.com> wrote in message
news:9qjgpp$3h3$1@news.tht.net...

I added a hash index to a varchar value and when i vacuumed i got the
following error:

Index customer_ha_hash: NUMBER OF INDEX' TUPLES (9176) IS NOT THE SAME AS
HEAP' (9181).

dropping and recreating the index gave me the same error (on this index
only)

dropping the database and reimporting the data and then recreating the

index

okay.
i followed the instructions very carefully and get the following error when
i try to run pg_upgrade ..

Cannot find database template1 in ./data/base.
Are you running ./pg_upgrade as the postgres superuser?

i am running as superuser and template1 is not there.

Steve

"Steve Brett" <steve.brett@e-mis.com> wrote in message
news:9qjgpp$3h3$1@news.tht.net...

I added a hash index to a varchar value and when i vacuumed i got the
following error:

Index customer_ha_hash: NUMBER OF INDEX' TUPLES (9176) IS NOT THE SAME AS
HEAP' (9181).

dropping and recreating the index gave me the same error (on this index
only)

dropping the database and reimporting the data and then recreating the

index

"Steve Brett" <steve.brett@e-mis.com> writes:

i followed the instructions very carefully and get the following error when
i try to run pg_upgrade ..

pg_upgrade hasn't worked since 7.0. Where did you find instructions
that told you to run it?

I added a hash index to a varchar value and when i vacuumed i got the
following error:

Index customer_ha_hash: NUMBER OF INDEX' TUPLES (9176) IS NOT THE SAME AS
HEAP' (9181).

If you have any rows that contain NULL in the indexed column, then this
result isn't very surprising, because hash indexes don't index nulls.
(Current sources have been fixed not to issue the cross-check notice
message for hash indexes, btw.)

Personally I'd advise not bothering with hash indexes; use a plain btree
index instead. Does more, works better, doesn't have concurrency
problems.

regards, tom lane

many thanks for the help.

i found the instructions on a mailing list via a search of the web.

i eventually added a btree index but was under the (probably mistaken!)
impression that hash indexes were better for varchar values.

once again many thanks,

Steve
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:26967.1003331234@sss.pgh.pa.us...

"Steve Brett" <steve.brett@e-mis.com> writes:

i followed the instructions very carefully and get the following error

when

i try to run pg_upgrade ..

pg_upgrade hasn't worked since 7.0. Where did you find instructions
that told you to run it?

I added a hash index to a varchar value and when i vacuumed i got the
following error:

Index customer_ha_hash: NUMBER OF INDEX' TUPLES (9176) IS NOT THE SAME

AS