[GENERAL] Index File locations
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? Regards Callum
Re: [GENERAL] Index File locations
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 ]
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Index File locations
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
