Postgres database on a separate drive

In psql:

CREATE TABLESPACE mytablespace LOCATION '/Volumes/MyVolume/postgres';
CREATE DATABASE mydatabase TABLESPACE mytablespace;

Kudos to GregHNZ for posting this on StackOverflow.

In macOS (possibly other OSes as well) you can safely unmount the drive without stopping the Postgres process as long as there are no I/O operations running. When you mount the drive again, the database works instantly — again, Postgres doesn’t even need to be restarted. Magic!

This entry was posted in How to’s and tagged , . Bookmark the permalink.

2 Responses to Postgres database on a separate drive

  1. tabkc says:

    @Dae Thanks for the post. I’m unable to create tablespace on macos. I get could not set permissions on directory “/Volumes/T7/database/pdata”: Operation not permitted
    Although database and pdata folders have postgres as owner. Nothing has worked for me.

    • Dae says:

      Are you sure these folders need to be owned by postgres? I use Postgres from Homebrew under macOS and these folders are owned by my own user, and Postgres uses them just fine.

Leave a Reply

Your email address will not be published. Required fields are marked *