FAQ
I am running postgresql 7.2.4 on a Redhat 8.0 system.

I have been looking for a way to setup another database besides the
initial one setup under /var/lib/pgsql/data on a different file system.

I have found a few references to configuring a separate PGDATA2
environment variable and running the initlocation PGDATA2 followed by
createdb -D PGDATA2 testdb.

The directories are created.

However when executing those steps I get the following error:

-bash-2.05b$ createdb testdb2 -D 'PGDATA2'
ERROR: Postmaster environment variable 'PGDATA2' not set
createdb: database creation failed


I have verified that PGDATA2 environment variable is set.
-bash-2.05b$ echo $PGDATA2
/vhost/database/data

I have restarted postmaster with the assumption that it needed the
environment variables setup in the postgres users shell.

My guess is that PGDATA2 is not set for some reason for the postmaster
service. I looked in the init.d/postgresql startup script and can see
where PGDATA is checked for and setup. I take it that the environment
variables from postgres user are not used?

Is there a way to set databases in different file systems using a single
postmaster service?

--
Scot L. Harris
webid@cfl.rr.com

... mindreading equipment is currently classified CIA property at
best (hello echelon!)

- Alan Cox on linux-kernel

Search Discussions

  • Richard Huxton at Jun 8, 2004 at 6:34 pm

    Scot L. Harris wrote:
    I am running postgresql 7.2.4 on a Redhat 8.0 system.

    I have been looking for a way to setup another database besides the
    initial one setup under /var/lib/pgsql/data on a different file system.

    I have found a few references to configuring a separate PGDATA2
    environment variable and running the initlocation PGDATA2 followed by
    createdb -D PGDATA2 testdb.

    The directories are created.

    However when executing those steps I get the following error:

    -bash-2.05b$ createdb testdb2 -D 'PGDATA2'
    ERROR: Postmaster environment variable 'PGDATA2' not set
    createdb: database creation failed
    1. Have you tried not quoting PGDATA2 (I seem to remember it being picky
    about such things).

    2. Are you aware you can use a full path (/vhost/database/data) instead
    of PGDATA2? This requires setting a compile-time flag though.

    --
    Richard Huxton
    Archonet Ltd
  • Scot L. Harris at Jun 8, 2004 at 7:04 pm

    On Tue, 2004-06-08 at 14:34, Richard Huxton wrote:


    1. Have you tried not quoting PGDATA2 (I seem to remember it being picky
    about such things).

    2. Are you aware you can use a full path (/vhost/database/data) instead
    of PGDATA2? This requires setting a compile-time flag though.
    Thanks for responding.

    Yes I have tried it without quoting the PGDATA2. Same result.

    I have also tried the full path but the flag is apparently not set to
    allow that.

    Besides the error I am getting it appears to me that postmaster would
    not be able to find this new location for the new database. From
    looking at the startup script in init.d it looks like it has PGDATA hard
    coded and I did not see any place in the other config files to specify
    additional database locations.

    It seems like I am missing a piece of the puzzle.

    --
    Scot L. Harris
    webid@cfl.rr.com

    If you keep anything long enough, you can throw it away.
  • Jim Seymour at Jun 8, 2004 at 9:01 pm

    "Scot L. Harris" wrote: [snip]
    Yes I have tried it without quoting the PGDATA2. Same result.

    I have also tried the full path but the flag is apparently not set to
    allow that.

    Besides the error I am getting it appears to me that postmaster would
    not be able to find this new location for the new database. From
    looking at the startup script in init.d it looks like it has PGDATA hard
    coded and I did not see any place in the other config files to specify
    additional database locations.

    It seems like I am missing a piece of the puzzle.
    Perhaps the man pages are screwed-up?

    $ man createdb
    [snip]
    -D location

    --location location
    Specifies the alternative location for the database.
    See also initlocation(1).

    $ man initlocation
    [snip]
    EXAMPLES
    To create a database in an alternate location, using an
    environment variable:

    $ export PGDATA2=/opt/postgres/data

    Stop and start postmaster so it sees the PGDATA2 environment
    variable. The system must be configured so the postmaster
    sees PGDATA2 every time it starts. Finally:

    $ initlocation PGDATA2
    $ createdb -D PGDATA2 testdb


    Alternatively, if you allow absolute paths you could write:

    $ initlocation /opt/postgres/data
    $ createdb -D /opt/postgres/data/testdb testdb

    From this I gather that what they *mean*, for the initlocation and
    createdb commands, is $PGDATA2. (Note the "$".)

    Jim
  • Scot L. Harris at Jun 9, 2004 at 1:45 pm

    On Tue, 2004-06-08 at 17:01, Jim Seymour wrote:
    Perhaps the man pages are screwed-up?

    $ man createdb
    [snip]
    -D location

    --location location
    Specifies the alternative location for the database.
    See also initlocation(1).

    $ man initlocation
    [snip]
    EXAMPLES
    To create a database in an alternate location, using an
    environment variable:

    $ export PGDATA2=/opt/postgres/data

    Stop and start postmaster so it sees the PGDATA2 environment
    variable. The system must be configured so the postmaster
    sees PGDATA2 every time it starts. Finally:

    $ initlocation PGDATA2
    $ createdb -D PGDATA2 testdb


    Alternatively, if you allow absolute paths you could write:

    $ initlocation /opt/postgres/data
    $ createdb -D /opt/postgres/data/testdb testdb
    From this I gather that what they *mean*, for the initlocation and
    createdb commands, is $PGDATA2. (Note the "$".)

    Jim
    I started trying this using the man pages instructions. With the $
    included I get the following results:

    -bash-2.05b$ createdb -D $PGDATA2 testdb3
    ERROR: Absolute paths are not allowed as database locations
    createdb: database creation failed

    Without the $ I get the following:

    -bash-2.05b$ createdb -D PGDATA2 testdb3
    ERROR: Postmaster environment variable 'PGDATA2' not set
    createdb: database creation failed
    -bash-2.05b$ echo $PGDATA2
    /vhost/database/data

    Which indicates to me that the PGDATA2 environment variable that is
    defined in the postgres users .bash_profile is not being picked up by
    the postmaster process. Which I have restarted numerous times in an
    effort to get it to pick that variable up.

    I am beginning to suspect that I would have to find a way to add the
    PGDATA2 variable to the startup script for postgresql. Which the docs I
    have read so far do not indicate as being required.

    Any more ideas before I have to go hacking on the startup script?

    --
    Scot L. Harris
    webid@cfl.rr.com

    Real computer scientists don't comment their code. The identifiers are
    so long they can't afford the disk space.
  • Tom Lane at Jun 9, 2004 at 2:20 pm

    "Scot L. Harris" <webid@cfl.rr.com> writes:
    Which indicates to me that the PGDATA2 environment variable that is
    defined in the postgres users .bash_profile is not being picked up by
    the postmaster process.
    Depends which startup script you are using. I know that up till
    recently the Red Hat init script did

    su -l postgres -s /bin/sh -c "pg_ctl start ..."

    and because it forced /bin/sh, anything you might have put in say
    ~postgres/.bash_profile wouldn't get read. You can just take out
    that -s switch though.

    regards, tom lane
  • Scot L. Harris at Jun 9, 2004 at 3:04 pm

    On Wed, 2004-06-09 at 10:20, Tom Lane wrote:
    Depends which startup script you are using. I know that up till
    recently the Red Hat init script did

    su -l postgres -s /bin/sh -c "pg_ctl start ..."

    and because it forced /bin/sh, anything you might have put in say
    ~postgres/.bash_profile wouldn't get read. You can just take out
    that -s switch though.

    regards, tom lane
    Thanks for the pointer.

    The startup script in /etc/init.d does have the su line as you describe
    above. On RH8 it appears that /bin/sh is linked to /bin/bash. So does
    the shell being executed not look at the .bash_profile since it was
    invoked as /bin/sh?

    Or can I just put the PGDATA2 environment variable in a .profile file?


    --
    Scot L. Harris <webid@cfl.rr.com>
  • Tom Lane at Jun 9, 2004 at 3:28 pm

    "Scot L. Harris" <webid@cfl.rr.com> writes:
    The startup script in /etc/init.d does have the su line as you describe
    above. On RH8 it appears that /bin/sh is linked to /bin/bash. So does
    the shell being executed not look at the .bash_profile since it was
    invoked as /bin/sh?
    Not sure; you could probably find out with some study of its man page.
    Seems likely though, else you'd not be here complaining ;-)
    Or can I just put the PGDATA2 environment variable in a .profile file?
    That would be an alternative answer. Be aware though that future RH
    releases will remove the -s switch, so eventually you'll want to do
    it that way.

    regards, tom lane
  • Mike Castle at Jun 29, 2004 at 5:51 am
    In article <20754.1086790815@sss.pgh.pa.us>,
    Tom Lane wrote:
    Depends which startup script you are using. I know that up till
    recently the Red Hat init script did

    su -l postgres -s /bin/sh -c "pg_ctl start ..."

    and because it forced /bin/sh, anything you might have put in say
    ~postgres/.bash_profile wouldn't get read. You can just take out
    that -s switch though.

    Actually, I think it's that bash is broken (well, it is if you ask me, but
    not if you ask Chet Ramey). At least it was circa 2001 when I last looked
    at this issue.

    I had to ask -l to get my init scripts to work (that is, to source .profile
    when started with su - ).

    mrc
    --
    Mike Castle dalgoda@ix.netcom.com www.netcom.com/~dalgoda/
    We are all of us living in the shadow of Manhattan. -- Watchmen
    fatal ("You are in a maze of twisty compiler features, all different"); -- gcc
  • Jim Seymour at Jun 9, 2004 at 8:32 pm

    "Scot L. Harris" wrote: [snip]
    Any more ideas before I have to go hacking on the startup script?
    Not a clue, Scot. Sorry.

    Jim

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJun 8, '04 at 4:00p
activeJun 29, '04 at 5:51a
posts10
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase