Обсуждение: Moving Database Cluster to another drive
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. 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, 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