pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Harsh Azad <harsh.azad@gmail.com>
To: Jean-David Beyer <jeandavid8@verizon.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index files
Date: Sat, 15 Sep 2007 01:51:06 +0530
Message-ID: <a199704d0709141321h598e20ebn9b0a8039d79576f@mail.gmail.com> (raw)
In-Reply-To: <46EA8B00.9030600@verizon.net>
References: <a199704d0709131950i3c23ea35wb0a19f393533197e@mail.gmail.com>
	<46EA8B00.9030600@verizon.net>

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

On 9/14/07, Jean-David Beyer <jeandavid8@verizon.net> wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>



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

view thread (10+ messages)  latest in thread

Message-ID: <a199704d0709141321h598e20ebn9b0a8039d79576f@mail.gmail.com>
Permalink:  ../a199704d0709141321h598e20ebn9b0a8039d79576f@mail.gmail.com/
Also on:    postgresql.org/message-id/a199704d0709141321h598e20ebn9b0a8039d79576f@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: harsh.azad@gmail.com, jeandavid8@verizon.net
  Subject: Re: Index files
  In-Reply-To: <a199704d0709141321h598e20ebn9b0a8039d79576f@mail.gmail.com>
  References: <a199704d0709131950i3c23ea35wb0a19f393533197e@mail.gmail.com> <46EA8B00.9030600@verizon.net> <a199704d0709141321h598e20ebn9b0a8039d79576f@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox