7

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?

Improve this question

2 Answers 2

Reset to default
6

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.

Improve this answer
5
  • 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.
    – user1822
    Commented May 10, 2013 at 22:14
  • Yeah, same. Thought a symbolic link was nice and transparent & needed no config changes.
    – Philᵀᴹ
    Commented May 10, 2013 at 22:26
  • 1
    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
    Commented May 11, 2013 at 9:06
  • I am using a database for performance bashing etc, so wasn't bothered whether the database survived or not. Writing here that the database in question has been pushed to an external drive, I've unplugged the drive while postgres was connected to the database, I've restarted without the drive connected, I've copied the directory across to a different location on a different drive then updated the symlink (instructions) while running and connected to the database, and the database has survived it all.
    – Chris
    Commented Apr 12, 2017 at 15:06
  • Think the answer misses the SET word, ie, ALTER DATABASE dbname SET TABLESPACE spc;
    – Chris
    Commented Apr 12, 2017 at 15:13
8

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.

Improve this answer
1
  • Is the table space not part of the data directory? Or is the point that if the data directory is missing, the DB won't start up and thus won't corrupt anything?
    – kutschkem
    Commented Mar 2, 2015 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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