Creating a Postgresql 9 database at another filesystem path

If you create a new PostgreSQL database but want it to be stored at a specific place in the filesystem (I’ll assume /srv/pgsql/data/ here), you can do this with the CREATE command. In earlier PostgreSQL versions, you could do it like this:

CREATE DATABASE mynewdb WITH OWNER=somebody LOCATION='/srv/pgsql/data/';

But with PostgreSQL 9, you need to use a tablespace:

CREATE TABLESPACE srv LOCATION '/srv/pgsql/data';
CREATE DATABASE mynewdb WITH OWNER=somebody TABLESPACE=srv;

So here is an example shell session that creates the required directories and the database (the part left of the ‘>’ is the prompt of the bash or psql shell):

root@box> mkdir -p /srv/pgsql/data/
root@box> chown -R postgres: /srv/pgsql/
root@box> su postgres
postgres@box> psql
psql> CREATE TABLESPACE srv LOCATION '/srv/pgsql/data';
psql> CREATE DATABASE mynewdb WITH OWNER=somebody TABLESPACE=srv;

The default database location is /var/lib/pgsql/data/ on most Unix systems, btw.

References:
http://www.postgresql.org/docs/7/static/sql-createdatabase.htm
http://www.postgresql.org/docs/9.0/static/manage-ag-tablespaces.html

Advertisements

About dfspspirit

PhD student in bioinformatics, interested in photography, level design, digital image manipulation, architecture and, of course, bioinformatics.
This entry was posted in databases, linux and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s