Thread: Index files

Hi,

Where are the database index files located in the $PGDATA directory? I was thinking on soft linking them to another physical hard disk array.

Thanks,
Azad

On Fri, 2007-09-14 at 08:20 +0530, Harsh Azad wrote:
> Hi,
>
> Where are the database index files located in the $PGDATA directory? I
> was thinking on soft linking them to another physical hard disk array.

you have to search through pg_class for the "number"

Alternatively, you can try using tablespaces.

create tablespace indexspace location '/mnt/fastarray'
create index newindex on table (index_1) tablespace indexspace

ah.. thanks. Didn't realize table spaces can be mentioned while creating a index. BTW, are soft links ok to use for pg_clog / pg_xlog . I moved the existing directories to /mnt/logs/pglogs and made soft links for both directories in $PGDATA

Thanks




--
Harsh Azad
=======================
Harsh.Azad@gmail.com

On Fri, 2007-09-14 at 08:33 +0530, Harsh Azad wrote:
> ah.. thanks. Didn't realize table spaces can be mentioned while
> creating a index. BTW, are soft links ok to use for pg_clog /
> pg_xlog . I moved the existing directories to /mnt/logs/pglogs and
> made soft links for both directories in $PGDATA


No idea what is the "proper" solution. Me being a newbie itself.
But from what I've read on the net and google, symlink seems to be the
order of the day.

perhaps others who are more familiar can comment as I'm lost in this.
(I'm doing symlinking btw)

"Harsh Azad" <harsh.azad@gmail.com> writes:
> Where are the database index files located in the $PGDATA directory?

Read
http://www.postgresql.org/docs/8.2/static/storage.html

> I was
> thinking on soft linking them to another physical hard disk array.

Manual symlink management, while not impossible, pretty much sucks
... especially if your tables are big enough that you actually need to
do this.  Use a tablespace instead.  (If you are on a PG version that
hasn't got tablespaces, you are more than overdue to upgrade.)

            regards, tom lane

Le vendredi 14 septembre 2007 à 11:09 +0800, Ow Mun Heng a écrit :
> On Fri, 2007-09-14 at 08:33 +0530, Harsh Azad wrote:
> > ah.. thanks. Didn't realize table spaces can be mentioned while
> > creating a index. BTW, are soft links ok to use for pg_clog /
> > pg_xlog . I moved the existing directories to /mnt/logs/pglogs and
> > made soft links for both directories in $PGDATA
>
>
> No idea what is the "proper" solution. Me being a newbie itself.
> But from what I've read on the net and google, symlink seems to be the
> order of the day.
>
> perhaps others who are more familiar can comment as I'm lost in this.
> (I'm doing symlinking btw)
>

You can also mount a different fs on another disk array in pglogs

I thinks update to index are not written to the wal so having index on a
different tablespace should reduce read and write on the data
tablespace, am I wrong ?


>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Harsh Azad wrote:
> Hi,
>
> Where are the database index files located in the $PGDATA directory? I
> was thinking on soft linking them to another physical hard disk array.
>
I am not an expert, but what I have done is put the Write-Ahead-Log on one
hard drive, some little-used relations and their indices on a second hard
drive, and the main database files on four other drives. These are SCSI hard
drives and I have two SCSI controllers. /dev/sda and /dev/sdb are on one
controller, and the other four hard drives are on the other controller.
These controllers are on a PCI-X bus all their own.

I put $PGDATA (I do not actually set or use that global variable) on /dev/sda.

[/srv/dbms/dataA/pgsql/data]$ ls -l
total 88
-rw------- 1 postgres postgres     4 Aug 11 13:32 PG_VERSION
drwx------ 5 postgres postgres  4096 Aug 11 13:32 base
drwx------ 2 postgres postgres  4096 Sep 14 09:16 global
drwx------ 2 postgres postgres  4096 Sep 13 23:35 pg_clog
-rw------- 1 postgres postgres  3396 Aug 11 13:32 pg_hba.conf
-rw------- 1 root     root      3396 Aug 16 14:32 pg_hba.conf.dist
-rw------- 1 postgres postgres  1460 Aug 11 13:32 pg_ident.conf
drwx------ 4 postgres postgres  4096 Aug 11 13:32 pg_multixact
drwx------ 2 postgres postgres  4096 Sep 14 09:16 pg_subtrans
drwx------ 2 postgres postgres  4096 Aug 12 16:14 pg_tblspc
drwx------ 2 postgres postgres  4096 Aug 11 13:32 pg_twophase
drwx------ 3 postgres postgres  4096 Sep 14 09:13 pg_xlog
-rw------- 1 postgres postgres 15526 Sep 11 22:31 postgresql.conf
-rw------- 1 postgres postgres 13659 Aug 11 13:32 postgresql.conf.dist
-rw------- 1 postgres postgres    56 Sep 14 07:33 postmaster.opts
-rw------- 1 postgres postgres    52 Sep 14 07:33 postmaster.pid

In /dev/sdb are

]$ ls -l
total 12
drwxr-x--- 2 postgres postgres 4096 Aug 18 00:00 pg_log
-rw------- 1 postgres postgres 2132 Sep 14 07:25 pgstartup.log
drwx------ 3 postgres postgres 4096 Aug 12 21:06 stock

The stuff in "stock" are little-used tables and their indices.

Everything else is on the other four drives. I put the index for a table on
a separate drive from the tata for the table.

--
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 09:10:01 up 1:37, 4 users, load average: 5.77, 5.12, 4.58

Great, creating new tablespace for indexes worked! Now the question is whether existing tables/index can be moved to the new tablespace using an alter command or the only way possible is to drop and recreate them?

Azad




--
Harsh Azad
=======================
Harsh.Azad@gmail.com

On Sat, 2007-09-15 at 01:51 +0530, Harsh Azad wrote:
> Great, creating new tablespace for indexes worked! Now the question is
> whether existing tables/index can be moved to the new tablespace using
> an alter command or the only way possible is to drop and recreate
> them?

You can alter an existing index:

http://www.postgresql.org/docs/8.2/static/sql-alterindex.html

On Friday 14 September 2007, "Harsh Azad" <harsh.azad@gmail.com> wrote:
> Great, creating new tablespace for indexes worked! Now the question is
> whether existing tables/index can be moved to the new tablespace using an
> alter command or the only way possible is to drop and recreate them?
>

ALTER TABLE table_name set tablespace new_tablespace;

and ALTER INDEX ...


--
Eat right. Exercise regularly. Die anyway.