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.
Alternate database locations are created and referenced by an environment variable which gives the absolute path to the intended storage location. This environment variable must have been defined before the backend was started and the location it points to must be writable by the postgres administrator account. Consult with the site administrator regarding preconfigured alternate database locations. Any valid environment variable name may be used to reference an alternate location, although using variable names with a prefix of “PGDATA” is recommended to avoid confusion and conflict with other variables.
Note: In previous versions of Postgres, it was also permissable to use an absolute path name to specify an alternate storage location. Although the environment variable style of specification is to be preferred since it allows the site administrator more flexibility in managing disk storage, it is also possible to use an absolute path to specify an alternate location. The administrator's guide discusses how to enable this feature.
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 using the environment variable PGDATA2 (for this example set to /alt/postgres), ensure that /alt/postgres already exists and is writable by the Postgres administrator account. Then, from the command line, 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 PGDATA2 from the command line, use the following command:
% createdb -D PGDATA2 mydb
and to do the same from within psql type
* 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.