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.

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

=> CREATE DATABASE mydb WITH LOCATION = 'PGDATA2';

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

ERROR:  CREATE DATABASE: permission denied

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:

ERROR:  The database path '/no/where' is invalid. This may be due to a character that is not allowed or because the chosen path isn't permitted for databases.