On 08/27/10 8:43 AM, A. Kretschmer wrote:
In response to Callum Scott :
Hi All,
I am looking for a way to define where index files should be located.
I am wanting to store the index files on a separate SSD from the database
itself. I can see that there is a way to define this from within the
database. Is there a way to do this at a system level within the configuration
file?
You have to define a own tablespace and then you can create new indexes
on this tablespace.
test=# \h create tablespace
Command: CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION 'directory'
test=# \h create index
Command: CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS {
FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]
you can also ALTER INDEX indexname SET TABLESPACE tablespacename;
this will move the existing index to the specified tablespace.
note that you want to do this when your applications are quiescent as
this will require an exclusive access lock for the duration of the move.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
