Re: Moving Database Cluster to another drive

On Sat, 16 May 2015 23:09 Daniel Begin <jfd553(at)hotmail(dot)com> wrote:

Hi all, first some context…

I’ve had a tread about this topic on pgsql-general(at)postgresql(dot)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