Change The Location of PostgreSQL Database Store Its Table and Index

Find a way to split the postgres database into different partitions.
Two main use case [1]

1. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.
2. Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.

To Create a Tablespace

CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';

To Change the Tablespace of an Existing Table and Index

The following psql command will create a list of alternate table command to change the table's tablespace [2]

-- tabelspace is null - find table that doesn't have any tablespace set
select 'alter table '||schemaname||'.'||tablename||' set tablespace fastspace;' from pg_tables where schemaname='myschema' and tablespace is null; 
select 'alter index '||schemaname||'.'||indexname||' set tablespace fastspace;' from pg_indexes where schemaname='myschema' and tablespace is null;

It will return something similar to this

                               ?column?
-----------------------------------------------------------------------
 alter table myschema.table1 set tablespace fastspace;
 alter table myschema.table2 set tablespace fastspace;
 alter table myschema.table3 set tablespace fastspace;
(3 rows)

                               ?column?
-----------------------------------------------------------------------
 alter index myschema.index1 set tablespace fastspace;
 alter index myschema.index2 set tablespace fastspace;
 alter index myschema.index2 set tablespace fastspace;
(3 rows)

 Then just run each generated command to change/set the tablespace.

Reference:
1. http://www.postgresql.org/docs/8.1/static/manage-ag-tablespaces.html
2. http://www.postgresql.org/message-id/01c001c9335f$06e991d0$274610ac@praveenkm