postgres indexes

melody

I'm using a postgres (7.01 ??) database for which no indexes are set up. As a result, searching is SSSSLLLLOOOOWWWW. This is a VERY important database. I can't pg_dump because the person who created it created it with wrong permissions. if i create indexes on the database, is my data in the database safe? Or could it get wiped?

Thanks

Melody

tomhath

Creating and dropping indexes should not alter the data.

But I would be a lot more concerned about getting the database backed up. Can you create a second database and copy the data with insert...select? Then you can get a backup and have a test system to experiment with.

Sxooter

Hi Melody. If you're running Postgresql, you may wanna look at updating to a newer version, as 7.0.1 is VERY old and no longer supported.

Anyway, as for the permissions, if you can connect as the superuser (usually named postgres) then you should be able to back up your database. Just set the entry for your machine in pg_hba.conf to trust, and connect like so: 'psql -h hostname -U postgres' and you should then be able to do what you want. To backup your database, do 'pg_dump -h hostname dbname -U postgres' and you should be able to back it up.

Indexes will help. Stick to the stock btree indexes since hash indexes, especially back in the 7.0 branch and before, had some serious performance issues.