Setting Postgresql Data Path

eagle862

Hello,

I am running RH7.1 stock and Postgresql 7.1.3 RPM binary packages downloaded from Postgresql.org.

Every time I edit the /etc/rc.d/init.d/postgresql script to reflect a different location for PGDATA the system indicates that the db is being initialized [ok]
starting postgresql [failed]

I would what I have to change to make the PGDATA path point to /data.

I created the /data partition on a separate drive and would like to use it.

TIA

John

gid__

I start postgres up with the following command:

su -c '/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data </dev/null >>server.log 2>&1 </dev/null &' postgres

the -D specifies the data directory, so this needs to get passed to postgres somehow, as I don't use redhat, I can't really be of much help on their start up script.

smarlowe

I'm not sure what you want, so I'll ask you some more questions.

Do you want to be able to have MORE than one area to store data? Or just one?

Do you already have a working database instance somewhere on the machine you'd like to keep?

If the answer to both of these questions is no, then the fix is pretty easy. Log in as root, 'su - postgres' and 'echo $PGDATA'

If PGDATA is set and returns something like /db/data then you're nealy done. If not, then edit your .bash_profile file and add a line like this:

PGDATA=/db/data

Of course, change the path to your path...

Also, in the .bash_profile, edit the export line to add PGDATA to the end of it.

i.e. this:

export BASH_ENV PATH

becomes this:

export BASH_ENV PATH PGDATA

then exit out of the postgres account and then 'su - postgres' again to make the changes take effect.

Finally, (assuming there's nothing in your $PGDATA directory) run initdb as postgres, and your database should be ready for 'pg_ctl start' or the postgres script in the /etc/rc.d/init.d directory.

jlpierce

Steve, thanks for the response, I would have responded sooner but I did not receive a notification of your post.

I tried to use the command that you use, of course my binary and data paths not yours, and I am informed that the postmaster must be started from an unprivileged account.

I am still not sure how to modify the startup scripts.

Thanks again for your response.

John

jlpierce

Scott,

I do not wish to have more than one data storage area, and I do not have an existing data structure that I want to protect.

I modified the necessary bash files to set PGDATA variable to point to /data/pgsql. /data is an entire disk partition mounted under /, pgsql is a non-existant directory, PGDATA is set to /data/pgsql. When I su to postgres and execute initdb it informs me that it is initializing the db. When it finishes the data structure /data/pgsql exists, with several files and directories below it. These files include PGVERSION, pg_hba.con, and postgresql.conf.

If I then execute /etc/init.d/postgresql start I the postmaster starts and initializes a data structure under /var/lib/pgsql, and uses the structure.

I have tried to modify the script by replacing all instances of /var/lib/pgsql with /data/pgsql and when I do the postmaster will not start and I receive the following two warnings:

1) Initializing database: su: warning: cannot change directory to /var/lib/pgsql: No such file or directory

2) Starting postgresql service: su: warning: cannot change directory to /var/lib/pgsql: No such file or directory

I have even tried to su to postgres and execute the following:

/usr/bin/postmaster -D /data/pgsql -l server.log

and it to fails.

Any assistance is greatly appreciated and I thank you for your response.

John

smarlowe

Oh. Due to RedHat's inability to keep up with the latest postgresql, I've always run i from a tar ball.

I'm not at all sure how the environment for the RPM version is setup, so I'm not sure what to tell you.

Somewhere some file is starting up postgresql with the $PGDATA setting. More than likely it's in the passwd file or something like that. But I'm just guessing, someone with more RedHat/Postgresql expertise will have to jump in now.

jlpierce

Thanks Scott!

Since I am just starting to develop the db interface for my project I can remove and reinstall postgresql without any problems.

I think that I will find a binary tarball of postgresql and forget the rpm install.

I do appreciate your assistance though, you helped me set the pgdata variable.

John