unable to pass variable PGDATA2 into server environment

[Jean-Christophe Weis]

Hello list,

I am a member of the french translation group for the PostgreSQL project.

I run PostgreSQL 7.2.1-2 on Debian Linux Woody (3.0r1).

I can't reproduce the steps described in Section 18.5 of the PostgreSQL 7.4
Documentation (Server Administration > Managing Databases > Alternative
Locations), which has quite the same contents as Section 6.1.2 of the
PostgreSQL 7.2.1 Documentation that came with Woody.

I have created a containing directory for a new data storage area and made it
writable by the user account that runs the server, i.e. 'postgres':

| root# ls -ld /home/jc/postgres
| drwx------ 3 postgres postgres 4096 Jun 29 19:59 /home/jc/postgres/

The server being shut down, I have created a variable named 'PGDATA2' to
reference the new data storage area, and marked it for export:

| root# PGDATA2=/home/jc/postgres/data
| root# export PGDATA2

I have created the new data storage area under the identity of the database
superuser, 'postgres' too:

| root# su postgres
| postgres$ /usr/lib/postgresql/bin/initlocation PGDATA2
| The location will be initialized with username "postgres".
| This user will own all the files and must also own the server process.
|
| Creating directory /home/jc/postgres/data
| Creating directory /home/jc/postgres/data/base
|
| initlocation is complete.
| You can now create a database using
| CREATE DATABASE <name> WITH LOCATION = 'PGDATA2'
| in SQL, or
| createdb <name> -D 'PGDATA2'
| from the shell.

Apparently everything went fine so far. I have restarted the server, under the
root identity:

| postgres$ exit
| root# /etc/init.d/postgresql start
| ...

But when I tried to create a database in the new data storage area, under the
'postgres' identity, it failed:

| root# su postgres
| postgres$ createdb testdb -D 'PGDATA2'
| ERROR: Postmaster environment variable 'PGDATA2' not set
| createdb: database creation failed

And, by the way:

| postgres$ createdb testdb -D $PGDATA2
| ERROR: Absolute paths are not allowed as database locations
| createdb: database creation failed

The variable PGDATA2 is set and marked for export in the root shell where I
start the server, but it is not passed into the server's environment.

Any Debian user knows if there is something special with Debian and
environment variables of the PostgreSQL server?

Thanks,

--
JC Weis <***@melix.net>

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Post by Jean-Christophe Weis
The variable PGDATA2 is set and marked for export in the root shell where I
start the server, but it is not passed into the server's environment.

No, because the start script is going to su to the postgres account,
and su is going to reset the environment. The usual advice for doing
this sort of thing is to set the environment variable in the postgres
user's .profile file. That way the postmaster has it, and you also
have it if you manually su to postgres.

(This whole benighted environment-variable scheme is gone in CVS tip,
thank goodness.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Jean-Christophe Weis]

Hello list,

Post by Tom Lane

Post by Jean-Christophe Weis
The variable PGDATA2 is set and marked for export in the root shell where I
start the server, but it is not passed into the server's environment.

No, because the start script is going to su to the postgres account,
and su is going to reset the environment. The usual advice for doing
this sort of thing is to set the environment variable in the postgres
user's .profile file. That way the postmaster has it, and you also
have it if you manually su to postgres.
(This whole benighted environment-variable scheme is gone in CVS tip,
thank goodness.)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

I did not think that su might reset the environment as Tom says, because I had
seen:

| root# PGDATA2=/home/jc/postgres/data
| root# export PGDATA2
| root# su postgres
| postgres$ echo $PGDATA2
| /home/jc/postgres/data

and also because '/path/to/initlocation PGDATA2' worked in the 'postgres'
shell.

But after Tom's remark, I wanted to have a closer look at my
'/etc/init.d/postgresql' script, and this is what I saw:

| startup () {
| ...
| su - postgres -c /usr/lib/postgresql/bin/postgresql-startup
| ...
| }

There is a dash after 'su'. And this what 'man su' says:

| The optional argument - may be used to provide an environment similar to
| what the user would expect had the user logged in directly.

That is to say, my problem was that the variable PGDATA2 was set and marked
for export in the root shell where I started the server, but it was not passed
into the server's environment _because_the_init_script_does_a_'su dash'_.

When I delete the dash in the init script, I can create a database in the new
data storage area as described in the documentation:

| jc$ createdb testdb -D PGDATA2
| CREATE DATABASE

And if I let the dash in, and follow Tom's advice of setting (and marking for
export) the PGDATA2 environment variable in the postgres user's '.profile', it
works fine too. (I had a hard time finding that the home directory for the
postgres user was '/var/lib/postgres'...)

So, thanks to the list, thanks to Tom and thanks to Guido Barosio too, who
answered me directly. So long,