On Sat, 16 May 2015 23:09 Daniel Begin <jfd553@hotmail.com> wrote:
Hi all, first some context…
I’ve had a tread about this topic on pgsql-general@postgresql.org mailing list (Restarting DB after moving to another drive). I had a lot of help to learn how these things should be done from pgsql general list. However, at this point, I feel I should continue the tread on the novice list because my questions are getting more basic about PostgreSQL and databases…
I have a large PostgreSQL database (2TB) on my PC (Windows). Few weeks ago, the drive dedicated to pgsql started overheating and I decided to move the DB on a new drive. Considering my particular context and the time it was going to take to restore from pg_dumpall output, I decided to copy the content of the old drive to the new one and make things transparent to PostgreSQL – if possible.
As expected, I was able to copy the database cluster on the new drive during the night. I changed the drive letter to fit the original database drive and I restarted the DB. Since then, everything seems running on the new drive as it used to be with the old one, with a small exception...
The time expected to run queries on some tables seems longer.
First check the explain plan if such queries to see if indexes are being missed in the query.
My questions are …
- How indexes are actually implemented in PostgreSQL (how pgsql point to a record from an index)?
- Could copying tables and indexes have had an effect on indexes?
- How can I verify that some of the indexes were not corrupted?
Daniel