Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
You don't really need the symbolic link. Just tell pg_ctl where the data directory is located using the -D switch. Don't know how Postgres is started on OSX, so I can't tell where this is configured. –  a_horse_with_no_name May 10 '13 at 22:14
    
Yeah, same. Thought a symbolic link was nice and transparent & needed no config changes. –  FreshPhilOfSO May 10 '13 at 22:26
    
Tablespaces on removable volumes are a pretty bad, eat-my-data idea. Really not recommended. The PostgreSQL documentation does not make it as clear as it should that tablespaces are integral parts of the DB; if you lose a tablespace you can't just drop it and keep using the rest of the DB. Removing just one tablespace and keeping the rest of the DB running could have all sorts of exciting data-munching consequences. –  Craig Ringer May 11 '13 at 9:06
    
thanks, symlink is a smooth solution :) –  DataGreed May 12 '13 at 9:35
add comment

I very strongly recommend that you do not put a tablespace on an external removable drive. If the tablespaces disappears (ie: drive unplugged) you risk severe database corruption that will be difficult to recover from. You'll likely find permissions management a challenge, too.

You should generally initdb a new database cluster on the drive, then start it with pg_ctl. If you like you can change your launchd properties to auto-start the database (this will require messing with file permissions) or you can just manually start it with pg_ctl when you want to use it.

It may be easier to move the existing data directory to the new location and either create a symlink from the old location to the new one or edit your launchd configuration to tell the PostgreSQL launcher to look in the new location. If your postgresql.conf is outside the data directory you can just change the data_directory parameter in postgresql.conf instead.

See the PostgreSQL user manual for details on initdb and pg_ctl if you need to use them.

For how to set PostgreSQL up to start your new database on boot-up you'll want to do some learning about file permisisons and launchd properties. I don't use OS X much so I can't be of much help with this part; if you're stumped and you've done some reading to try to figure it out, consider asking for help on the OSX stack exchange site. Just remember to explain what you've tried and what you've done already. You won't need to do this if you just symlink it from the old location.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.