Re: Alternate database locations

I would like to create multiple databases, each with its own disk location.

I have followed the instructions in the Administrator's Guide and have a
couple of questions.

1. Since the pg_ctl -D option only appears to take a single location, should
this be set to the location of the template1 database created as part of the
Postgres installation, or, do I have to somehow specify the location of each
of my databases in alternate locations when starting Postgres ?

2. As user postgres, I shut down the server, defined the following
environment variable:
PGDATA_ALTERNATE=/home/alternate/pgsql/data
and ran initlocation:
initlocation PGDATA_ALTERNATE
note: /home/alternate/pgsql/data is writeable by postgres

So far so good.

Now, the problem: Per the Administrator's guide, the environment variable
needs to be defined before the backend is started.

I am using the startup script from the contrib/start-scripts/linux directory
of PostgreSQL source (v. 7.1.3), so I define an environment variable within
this file, prior to the command to start the server --- let's say it is
defined as :
PGDATA_ALTERNATE="/home/alternate/pgsql/data"

After starting the server using the script, I switch to user postgres, and I
issue the following command to create a database at the alternate location:

createdb -D 'PGDATA_ALTERNATE'

I get the following error:
ERROR: Postmaster environment variable 'PGDATA_ALTERNATE' not set
createdb: database creation failed

I assumed this environment variable was defined as I put it in the startup
script (just below the line PGDATA="/usr/local/data/pgsql"). Even if I define
the PGDATA_ALTERNATE explicitly in the shell, and then run the createdb
command above, I get the same error.

Any ideas ?

Thanks
Charlie

Charlie Toohey <ctoohey(at)pacbell(dot)net> writes:
> 1. Since the pg_ctl -D option only appears to take a single location, should
> this be set to the location of the template1 database created as part of the
> Postgres installation, or, do I have to somehow specify the location of each
> of my databases in alternate locations when starting Postgres ?

-D points at the root of the data directory, a/k/a $PGDATA. This is
where postgresql.conf and some other installation-wide files live.
Individual databases are normally subdirectories under $PGDATA/base/,
but can be relocated elsewhere using the "alternate database location"
mechanism.

> I am using the startup script from the contrib/start-scripts/linux directory
> of PostgreSQL source (v. 7.1.3), so I define an environment variable within
> this file, prior to the command to start the server --- let's say it is
> defined as :
> PGDATA_ALTERNATE="/home/alternate/pgsql/data"

I think you also need "export PGDATA_ALTERNATE"; otherwise it's only a
local variable in that shell script.

regards, tom lane