Thread: Moving Database Cluster to another drive

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

 

On Sat, May 16, 2015 at 7:39 PM, Daniel Begin <jfd553@hotmail.com> wrote:
> - How indexes are actually implemented in PostgreSQL (how pgsql point to a
> record from an index)?
>

Not an easy answer. An index can have different implementation types,
but for short each index has a pointer to the block on disk that
contain the tuple. And indexes are, of course, on disk stuff.

> - Could copying tables and indexes have had an effect on indexes?
>

Should not, but it could be.

> - How can I verify that some of the indexes were not corrupted?
>

Use explain, see pg_stat_user_indexes and do a reindex if you believe
an index is corrupted.

I would expect this being more likely an issue with the hard drive
(e.g., different seek times from the previous one).

Luca

Thank Luca,

You wrote that "each index has a pointer to the block on disk that contains the tuple". If there is no mechanism that
insurethe tuples have the same location on the new drive (the block address), I should then expect a plane copy will
corruptsome/all the indexes, am I right (linked to the second question)? 

Best regards,
Daniel

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Luca Ferrari
Sent: May-18-15 02:27
To: Daniel Begin
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Moving Database Cluster to another drive

On Sat, May 16, 2015 at 7:39 PM, Daniel Begin <jfd553@hotmail.com> wrote:
> - How indexes are actually implemented in PostgreSQL (how pgsql point
> to a record from an index)?
>

Not an easy answer. An index can have different implementation types, but for short each index has a pointer to the
blockon disk that contain the tuple. And indexes are, of course, on disk stuff. 

> - Could copying tables and indexes have had an effect on indexes?
>

Should not, but it could be.

> - How can I verify that some of the indexes were not corrupted?
>

Use explain, see pg_stat_user_indexes and do a reindex if you believe an index is corrupted.

I would expect this being more likely an issue with the hard drive (e.g., different seek times from the previous one).

Luca


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Well, it points to page in a data file, so copying the data files does not do any harm (when Postgres is NOT running,
otherwise- disaster). We are talking about files on a filesystem here, not raw devices as e.g. Informix uses (can use). 

You might find this helpful in general, especiall around ppage 50.

https://momjian.us/main/writings/pgsql/internalpics.pdf

Jan

Von meinem iPad gesendet

> Am 18.05.2015 um 18:28 schrieb Daniel Begin <jfd553@hotmail.com>:
>
> Thank Luca,
>
> You wrote that "each index has a pointer to the block on disk that contains the tuple". If there is no mechanism that
insurethe tuples have the same location on the new drive (the block address), I should then expect a plane copy will
corruptsome/all the indexes, am I right (linked to the second question)? 
>
> Best regards,
> Daniel
>
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Luca Ferrari
> Sent: May-18-15 02:27
> To: Daniel Begin
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Moving Database Cluster to another drive
>
>> On Sat, May 16, 2015 at 7:39 PM, Daniel Begin <jfd553@hotmail.com> wrote:
>> - How indexes are actually implemented in PostgreSQL (how pgsql point
>> to a record from an index)?
>
> Not an easy answer. An index can have different implementation types, but for short each index has a pointer to the
blockon disk that contain the tuple. And indexes are, of course, on disk stuff. 
>
>> - Could copying tables and indexes have had an effect on indexes?
>
> Should not, but it could be.
>
>> - How can I verify that some of the indexes were not corrupted?
>
> Use explain, see pg_stat_user_indexes and do a reindex if you believe an index is corrupted.
>
> I would expect this being more likely an issue with the hard drive (e.g., different seek times from the previous
one).
>
> Luca
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

OMG! That is a reference I should have found and read before,
Many thanks Jan

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Jan Lentfer
Sent: May-18-15 12:58
To: Daniel Begin
Cc: Luca Ferrari; <pgsql-novice@postgresql.org>
Subject: Re: [NOVICE] Moving Database Cluster to another drive

Well, it points to page in a data file, so copying the data files does not
do any harm (when Postgres is NOT running, otherwise - disaster). We are
talking about files on a filesystem here, not raw devices as e.g. Informix
uses (can use).

You might find this helpful in general, especiall around ppage 50.

https://momjian.us/main/writings/pgsql/internalpics.pdf

Jan

Von meinem iPad gesendet

> Am 18.05.2015 um 18:28 schrieb Daniel Begin <jfd553@hotmail.com>:
>
> Thank Luca,
>
> You wrote that "each index has a pointer to the block on disk that
contains the tuple". If there is no mechanism that insure the tuples have
the same location on the new drive (the block address), I should then expect
a plane copy will corrupt some/all the indexes, am I right (linked to the
second question)?
>
> Best regards,
> Daniel
>
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Luca Ferrari
> Sent: May-18-15 02:27
> To: Daniel Begin
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Moving Database Cluster to another drive
>
>> On Sat, May 16, 2015 at 7:39 PM, Daniel Begin <jfd553@hotmail.com> wrote:
>> - How indexes are actually implemented in PostgreSQL (how pgsql point
>> to a record from an index)?
>
> Not an easy answer. An index can have different implementation types, but
for short each index has a pointer to the block on disk that contain the
tuple. And indexes are, of course, on disk stuff.
>
>> - Could copying tables and indexes have had an effect on indexes?
>
> Should not, but it could be.
>
>> - How can I verify that some of the indexes were not corrupted?
>
> Use explain, see pg_stat_user_indexes and do a reindex if you believe an
index is corrupted.
>
> I would expect this being more likely an issue with the hard drive (e.g.,
different seek times from the previous one).
>
> Luca
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make
changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To
> make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice