Multiple databases on seperate drives/file systems?

[Scot L. Harris]

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
we***@cfl.rr.com

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

- Alan Cox on linux-kernel
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Scot L. Harris]

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
we***@cfl.rr.com

If you keep anything long enough, you can throw it away.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Scot L. Harris]

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
we***@cfl.rr.com

If you keep anything long enough, you can throw it away.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Jim Seymour]

"Scot L. Harris" <we***@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Jim Seymour]

"Scot L. Harris" <we***@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Scot L. Harris]

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
we***@cfl.rr.com

Real computer scientists don't comment their code. The identifiers are
so long they can't afford the disk space.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Scot L. Harris]

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
we***@cfl.rr.com

Real computer scientists don't comment their code. The identifiers are
so long they can't afford the disk space.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

"Scot L. Harris" <we***@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"Scot L. Harris" <we***@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"Scot L. Harris" <we***@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

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

[Tom Lane]

"Scot L. Harris" <we***@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

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

[Scot L. Harris]

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 <we***@cfl.rr.com>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Scot L. Harris]

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 <we***@cfl.rr.com>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Jim Seymour]

"Scot L. Harris" <we***@cfl.rr.com> wrote:

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

Not a clue, Scot. Sorry.

Jim

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Jim Seymour]

"Scot L. Harris" <we***@cfl.rr.com> wrote:

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

Not a clue, Scot. Sorry.

Jim

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Mike Castle]

In article <20**************@sss.pgh.pa.us>,
Tom Lane <tg*@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 da*****@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings