Multiple databases on seperate drives/file systems?

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.

"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

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>

"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

"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

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