Postgres: Put 1 database in separate HD partition?

Question

I have 2 HD partitions - one with SSD and another with regular hard drives.

I have multiple databases (as in logical database, not physical) with 1 data directory. I want the data for 1 of the databases to reside in the SSD partition because it's a database that will have LOT more reads/writes than the other databases. And since the SSD partition is limited in space, I can't store all the databases there, just that one.

Is there a way to configure this in Postgres - is it possible? Is there a tutorial online that I can read? I tried Googling but all the results talk about partitioning tables logically, not across different hard drive partitions.

• Milen A. Radev: What you need is tablespaces.

Answer 1

You can create a table space that uses the second partition. Then just alter the database to use that partition.

CREATE TABLESPACE fastspace LOCATION '/mnt/ssdpart';
ALTER DATABASE yourdb SET TABLESPACE fastspace;

See also, http://www.postgresql.org/docs/9.3/interactive/manage-ag-tablespaces.html and http://www.postgresql.org/docs/9.3/interactive/sql-alterdatabase.html

Good luck!