Postgresql – Seeting up a PostgreSQL database on another hard drive than the system one on OS X

mac os xpostgresql

I am on OS X 10.8.3, I've install PostgreSQL from macports, but I've realized that I have not enough space on my system drive for the DB I am about to populate with data.

Is there a way to create new database on another drive/partition (not the system one, where postgres is installed)?

How can I do that?

Best Answer

Create the new database, then create a tablespace on your external drive:

CREATE TABLESPACE mytablespace LOCATION '/Volumes/externaldisk/pg';

Then alter the database so that the new tablespace is the default location for it:

ALTER DATABASE MYDB TABLESPACE mytablespace;

The other way to do this is to just move your entire Postgres data directory to the external drive, then symbolic link the external drive directory back to the original location.

If you want to create multiple tablespaces over many drives you can also specify which tablespace a table or index should reside in using the TABLESPACE parameter in the creation DDL.

Related Question