Alternate Database Locations

It is possible to create a database in a location other than the default location for the installation. Remember that all database access actually occurs through the database backend, so that any location specified must be accessible by the backend.

Either an absolute path name or an environment variable may be specified as a location. Any environment variable specifying an alternate location must have been defined before the backend was started. Consult with the site administrator regarding preconfigured alternate database locations.

Note: The environment variable style of specification is to be preferred since it allows the site administrator more flexibility in managing disk storage.

For security and integrity reasons, any path or environment variable specified has some additional path fields appended.

Alternate database locations must be prepared by running initlocation.

To create a data storage area in /alt/postgres/data, ensure that /alt/postgres already exists. From the command line, type

% initlocation /alt/postgres/data
Creating Postgres database system directory /alt/postgres/data

Creating Postgres database system directory /alt/postgres/data/base

To do the same using an environment variable PGDATA2, type

% initlocation $PGDATA2
Creating Postgres database system directory /alt/postgres/data

Creating Postgres database system directory /alt/postgres/data/base

To create a database in the alternate storage area /alt/postgres/data from the command line, type

% createdb -D /alt/postgres/data mydb

% createdb -D PGDATA2 mydb

* CREATE DATABASE mydb WITH LOCATION = 'PGDATA2';

If you do not have the privileges required to create a database, you will see the following:

% createdb mydb
WARN:user "your username" is not allowed to create/destroy databases
createdb: database creation failed on mydb.

If the specified location does not exist or the database backend does not have permission to access it or to write to directories under it, you will see the following:

% createdb -D /alt/postgres/data mydb
ERROR:  Unable to create database directory /alt/postgres/data/base/mydb
createdb: database creation failed on mydb.