Alternative Locations
5.5. Alternative Locations
It is possible to create a database in a location other than the default location for the installation. Remember that all database access occurs through the database server, so any location specified must be accessible by the server.
Alternative database locations are referenced by an environment variable which gives the absolute path to the intended storage location. This environment variable must be present in the server's environment, so it must have been defined before the server was started. (Thus, the set of available alternative locations is under the site administrator's control; ordinary users can't change it.) Any valid environment variable name may be used to reference an alternative location, although using variable names with a prefix of PGDATA is recommended to avoid confusion and conflict with other variables.
To create the variable in the environment of the server process you must first shut down the server, define the variable, initialize the data area, and finally restart the server. (See Section 3.6 and Section 3.3.) To set an environment variable, type
PGDATA2=/home/postgres/data export PGDATA2
in Bourne shells, or
setenv PGDATA2 /home/postgres/data
in csh or tcsh. You have to make sure that this environment variable is always defined in the server environment, otherwise you won't be able to access that database. Therefore you probably want to set it in some sort of shell start-up file or server start-up script.
To create a data storage area in PGDATA2, ensure that the containing directory (here, /home/postgres) already exists and is writable by the user account that runs the server (see Section 3.1). Then from the command line, type
initlocation PGDATA2
(not initlocation $PGDATA2). Then you can restart the server.
To create a database within the new location, use the command
CREATE DATABASE name WITH LOCATION = 'location'
where location is the environment variable you used, PGDATA2 in this example. The createdb command has the option -D for this purpose.
Databases created in alternative locations can be accessed and dropped like any other database.
Note: It can also be possible to specify absolute paths directly to the CREATE DATABASE command without defining environment variables. This is disallowed by default because it is a security risk. To allow it, you must compile PostgreSQL with the C preprocessor macro ALLOW_ABSOLUTE_DBPATHS defined. One way to do this is to run the compilation step like this:
gmake CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS all