Thread: Multiple databases on seperate drives/file systems?
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
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
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.
Re: Multiple databases on seperate drives/file systems?
From
jseymour@linxnet.com (Jim Seymour)
Date:
"Scot L. Harris" <webid@cfl.rr.com> 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
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.
"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" <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
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>
Re: Multiple databases on seperate drives/file systems?
From
jseymour@linxnet.com (Jim Seymour)
Date:
"Scot L. Harris" <webid@cfl.rr.com> wrote: > [snip] > > Any more ideas before I have to go hacking on the startup script? Not a clue, Scot. Sorry. Jim
Re: Multiple databases on seperate drives/file systems?
From
dalgoda@ix.netcom.com (Mike Castle)
Date:
In article <20754.1086790815@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> 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