Thread: Alternate data location, again

  I had forgotten about the 'initlocation' command (page 265 of the
Reference Manual). So following the directions, I ensured that PGDATA2 was
exported in my environment (using the echo command), restarted the
postmaster (to ensure it had the opportunity to pick up that variable), then
ran initlocation with this result:

[rshepard@salmo ~/development/accounting]$ initlocation PGDATA2
The location will be initialized with username "rshepard".
This user will own all the files and must also own the server process.

Creating directory /opt/paisley/acctdata/
Creating directory /opt/paisley/acctdata//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.

  I see two slashes between acctdata and base; did I define the variable
improperly by terminating it with a slash? ... I just fixed this problem and
it makes no difference. Here's what I see when I try to create a database
there:

[rshepard@salmo ~/development/accounting]$ createdb paisley -D 'PGDATA2'
ERROR:  Postmaster environment variable 'PGDATA2' not set
createdb: database creation failed

  Since I followed the procedure in the description of the 'initlocation'
command and 'createdb' command, I am puzzled why it's not working here. The
only thing that occurs to me is this: the postmaster is started by root, not
by user postgres or user rshepard. Only root has permission to run
'/etc/rc.d/init.d/postgresql restart'. Could this have any bearing on the
results?

  When the postmaster tries to create a database, does it pick up the
environment variables of the user ordering the creation? Do I need to put
the PGDATA2 environment variable in root's .bash_profile?

  I have been trying to figure out by myself why this isn't working, but I
believe I've done all the docs tell me to do. If it's helpful, all this is
on a RH 6.2 system running the 2.2.19 kernel.

TIA,

Rich

Dr. Richard B. Shepard, President

                       Applied Ecosystem Services, Inc. (TM)
            2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
 + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
                         http://www.appl-ecosys.com

Rich Shepard <rshepard@appl-ecosys.com> writes:
> only thing that occurs to me is this: the postmaster is started by root, not
> by user postgres or user rshepard. Only root has permission to run
> '/etc/rc.d/init.d/postgresql restart'. Could this have any bearing on the
> results?

Hm.  I assume that init script does something like
    su postgres -c 'postmaster ...'

Check the man page for "su".  Usually there is some option needed to
make "su" pick up the login environment of the target user.  On HPUX
you'd do
    su - postgres postmaster ...
but I'm too lazy to go check it on other Unixen...

            regards, tom lane

On Tue, 19 Feb 2002, Tom Lane wrote:

> Hm.  I assume that init script does something like
>     su postgres -c 'postmaster ...'

Tom,

  In /etc/rc.d/init.d/postgresql I see:

        else
                #all systems go -- remove any stale lock files
                rm -f /tmp/.s.PGSQL.* > /dev/null
                echo -n "$PSQL_START"
                su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D $PGDATA -p /usr/bin/postmaster start  > /dev/null
2>&1"< /dev/null 
                sleep 1
                pid=pidof -s postmaster
        if [ $pid ]
                then
                        if echo "$TYPESET"|grep "declare -f success ()" >/dev/null
                        then
                                success "$PSQL_START"

  which looks to me as if $PGDATA is hard-wired as the data directory
without checking to see if there is an alternate.

> Check the man page for "su".  Usually there is some option needed to
> make "su" pick up the login environment of the target user.  On HPUX
> you'd do
>     su - postgres postmaster ...
> but I'm too lazy to go check it on other Unixen...

  That's what we do when we ask for help. :-)

  The '-l' means log in as the username following, and '-s' means to start a
shell and run the command ('-c') /usr/bin/pg_ctl.

Rich

Dr. Richard B. Shepard, President

                       Applied Ecosystem Services, Inc. (TM)
            2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
 + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
                         http://www.appl-ecosys.com

Rich Shepard <rshepard@appl-ecosys.com> writes:
>                 su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D $PGDATA -p /usr/bin/postmaster start  > /dev/null
2>&1"< /dev/null 

-s /bin/sh ?

Perhaps this is causing the thing to log in with /bin/sh not bash.
IIRC, you were putting the environment variables in .bash_profile?

            regards, tom lane

On Tue, 19 Feb 2002, Tom Lane wrote:

> Rich Shepard <rshepard@appl-ecosys.com> writes:
> >                 su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D $PGDATA -p /usr/bin/postmaster start  > /dev/null
2>&1"< /dev/null 
>
> -s /bin/sh ?
>
> Perhaps this is causing the thing to log in with /bin/sh not bash.
> IIRC, you were putting the environment variables in .bash_profile?

  You recall correctly, Tom. bash is the default shell for us users (and
root, too). I've not edited any initialization/shutdown scripts before, but
I suppose that changing /bin/sh to /bin/bash will not break anything.

Thanks,

Rich