'export PGDATA2' 4 use of secondary data location fails

[Rarch]

How do I set the PGDATA2 variable for the (RedHat 7.2) postgres user (no /home/postgres installed: /etc/password references /var/lib/pgsql) to be able to do a "createdb -U rarch -D 'PGDATA2' databasename"?
My efforts to su to postgres and do an "export PGDATA2="/usr/local/pgsql/data2" " and then starting the postmaster daemon result in an "postmaster PGDATA2 variable not set" error. Tried again via .bash_profile in /var/lib/pgsql: same error.
What can I do to make this work? I guess I want to now how I can set environmental variables for non-priviledged users (as 'postgres' is).
BTW: all is needed to use a secondary db location as RH set the PGDATA variable to var/lib/pgsql wich is quite a small partition on my laptop and thus cannot accomodate *any* extra dbases besides templates 0 and 1!

 

[Leland123]

Hi Rarch,

I was able to create a database as follows:

As root

mkdir /test/data
chmod 777 /test/data
chown postgres /test/data

set up environment variable in postgres home directory as follows:

Login to postgres and you should be in the RedHat Home directory for postgres of /var/lib/pgsql

vi .bash_profile

PGPASSWORD=YourPostgresPassword
PGDATA=/var/lib/pgsql/data
PGDATA2=/test/data

export PGPASSWORD PGDATA PGDATA2

logout of postgres and then log back into postgres. Check your environment variable with: env If all varaible are set correctly then:

initlocation PGDATA2

If this works then create the new database

CREATE DATABASE testdb WITH LOCATION = 'PGDATA2'


Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

 

[Rarch]

Thank you for the reply.

I take it that with "log in to postgres" (to edit the environment variables in /var/lib/pgsql/.bash_profile) you mean to edit that file as linux user "postgres" (ie. do "su postgres" as root).
If not, how do I log into postgres?

 

[Leland123]

Hi Rarch,

RedHat Linux comes standard with a postgres user already created. I don't know what the default password is for postgres, so as root you can set up a password for postgres like:

passwd postgres

Then when prompted, type in a password for postgres.

Now you can login as postgres with the new password just like any other user.
Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

 

[Rarch]

I´m sorry to say that it doesn´t work on my box,..... and don´t know why.
Let me outline what I did:

As I´ve pointed out before (but not explicitly), RedHat 7.2 with PostgreSQL7.1.3 installed from RPM´s, none modified.

Login as regular user.

su to root.

passwd postgres.

login as user postgres.
vi .bash_profile and added the lines as you said (PGDATA2= etc and export [...] ). Logout and login again.
env or echo $PGDATA2 both show a correct environment variable.

logout and login with my regular user name:

start postgres daemon (su -c ´/etc/rc.d/initd/postgresql start´)

createdb -U erw -D ´PGDATA2´ dbname
ERROR: postmaster environment variable PGDATA2 not set.

same happens when logged in as user ´postgres´.

/usr/bin/postgres and
/usr/bin/postmaster (one is a symlink to the other) are both owned by root. However, the RedHat startup script calls for the postmaster server to be run as user postgres.
setting PGDATA2 in the /etc/rc.d/initd/postgresql script and in /root/.bash_profile doesn´t change a thing.


I don´t get it, do you?

 

[Leland123]

Hi,

I don't think you should change anything in your /etc/rc.d/init.d/postgresql file. I would leave it as it was orginally installed with no modifications.

Did you create the directory for the new database and assign permissions and change the owner of the new directory to postgres? You must be root to perform these tasks like:

mkdir /test/data
chmod 777 /test/data
chmod 777 /test/data/*
chown postgres /test/data

Did you set up postgres environment variable in the /var/lib/pgsql/.bash_profile like:

PGPASSWORD password_assigned_by_root
PGDATA = /var/lib/pgsql/data
PGDATA2 = /test/data
export PGPASSWORD PGDATA PGDATA2

Next, login as postgres and you should be places in the postgres home directory of /var/lib/pgsql. There is no need to su to postgres as it is always better to become postgres through an ordinary login. Once logged into Linux as postgres you have top privileges to administer the postgres database. There is no need to do any su. When logged in as postgres you should have a $ shell prompt. Whenever you su from an ordinary user you become root. Use the whoami command to determine who the current logged in user is.

As posstgres perform the following command.

initlocation PGDATA2

postgres shold display some messages on the screen and indicate that the command successed. At this point you should now have a new /test/data/base directory. Next issue the following command.

CREATE DATABASE testdb WITH LOCATION = 'PGDATA2'

Now you should have a testdb database. Check to see if the following command works.

psql testdb postgres

Now you should be logged into postgres under the new database. You can begin to create new tables, etc.

It wasn't easy for me to do the first time because postgres can be kind off fussy. I kept messing around with it and re-reading the instructions and finally I had it done without really knowing it until I used phpPgAdmin and discovered I had a testdb database. I added a table and everything worked great.
Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

 

[Leland123]

Hi Rarch,

You are right. I went back this morning an tried to set up another database in /test/data and encountered the same problem about which you asked. I discovered that in my initial attempt, I set up the database in the default PGDATA path instead of PGDATA2 as I had original thought. I tried for a cupple of hours to set a database up in a directory other than the default, but was unable to do so. In the end I kept getting an error message about the PGDATA2 string not being included in the postmaster env.

When I have some more time I'm going to go back and explore this some more. The doc is a little confusing about how to set up a postgres database a location other than the default location.

In the mean time you might try joining the postgres mailing lists and submit this question to some of the postgres gurus. The mailing list can be found at:

http://www.postgres.org

Leland Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

 

[danielhozac]

One thing you could try is to edit the /etc/init.d/postgresql file and add -c PGDATA2=/test/data to the postmaster command. I think this should do it. //Daniel

 

[Leland123]

Thanks Daniel,

I'll give it a try.

Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

 

[Rarch]

Phew, I thought I was getting worried, not being able to do what others could.

A solution I came up with in the end is to define de PGDATA2 variable in /etc/profile. This gets the variable set for *all* environments and did the trick for me.

The question remains however why defining PGDATA2 in the postgres users .bash_profile doesn´t do the trick. It must have something to do with how environment variables are passed to started processes or something.
Reading through "Running Linux", "Linux in a Nutshell", "Linux administration handbook" and "Learning the Bash shell" didn´t get me further (for now). I guess I´ll have to ask the gurus at postgress, redhat and general linux mailinglists as you´ve suggested.

I´ll keep you posted.
Thanks for your continued interest.


Edwin