Alternate database locations

On Tue, 19 Feb 2002, Jokerman wrote:

i think you should create the database using 'initdb /opt/paislay'.
then you can add the PGDATA2 variable on your environment.

I thought of this, too. I created a directory called
/opt/paisley/acctdata/, gave it permissions of 700, changed the owner.group
to postgres.postgres, then ran initdb specifying that directory. It worked
just fine.

Next I changed the environment variable, PGDATA2, to
/opt/paisley/acctdata/ in postgres's and my ~/.bash_profile, restarted the
postmaster and got the same error message: createdb failed because the
postmaster doesn't understand PGDATA2.

Now I'll try it again without quotes on the path.

Thanks,

Rich

Rich Shepard <rshepard@appl-ecosys.com> writes:

Next I changed the environment variable, PGDATA2, to
/opt/paisley/acctdata/ in postgres's and my ~/.bash_profile, restarted the
postmaster and got the same error message: createdb failed because the
postmaster doesn't understand PGDATA2.

Simply changing your .profile doesn't change the environment of your
running shell. Did you log out/back in? Or manually do
export PGDATA2=xxx
?

regards, tom lane

On Mon, 18 Feb 2002, Tom Lane wrote:

Simply changing your .profile doesn't change the environment of your
running shell. Did you log out/back in? Or manually do
export PGDATA2=xxx

Tom,

I logged out, then back in so the environment would be re-established.
Made no difference. I know it must be user error but I cannot find where I'm
going wrong.

Thanks,

Rich

Dr. Richard B. Shepard, President

Applied Ecosystem Services, Inc. (TM)
2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
http://www.appl-ecosys.com

On Mon, 18 Feb 2002, Rich Shepard wrote:

Simply changing your .profile doesn't change the environment of your
running shell. Did you log out/back in? Or manually do
export PGDATA2=xxx

Tom,

I logged out, then back in so the environment would be re-established.
Made no difference. I know it must be user error but I cannot find where I'm
going wrong.

Rather than beating on this dead horse, I decided to try another one. So,
I gave up on the alternative location and created the database in
/var/lib/psql/data/. Worked like a charm. :-)

Now I need to figure out why the copy command isn't copying tables from
the ascii files where they're all defined. Sigh. I'm calling it a night.

Rich

Hi all,

How do I build or get hold of the correct version of pg_dump and
pg_dumpall for PostgreSQL 7.0.1? I want to upgrade, but can't until I
can dump/restore the databases. I'm getting error messages when I
try, and gather from this list that they are due to a version
mismatch between PostgrSQL and pg_dump on my machine.

I had a look through the GNUmakefile in the source, but nothing
jumped out at me. Any help would be greatly appreciated!

--Ray.

--------------------------------------
Raymond O'Donnell
rod@iol.ie
rod@gti.ie
--------------------------------------

"Raymond O'Donnell" <rod@iol.ie> writes:

How do I build or get hold of the correct version of pg_dump and
pg_dumpall for PostgreSQL 7.0.1? I want to upgrade, but can't until I
can dump/restore the databases.

The recommended way is to use the current pg_dump --- 7.2 and 7.1
pg_dump are supposed to be able to dump 7.0.* databases. What
problems are you having, *exactly*?

regards, tom lane

On 19 Feb 2002, at 10:11, Tom Lane wrote:

The recommended way is to use the current pg_dump --- 7.2 and 7.1
pg_dump are supposed to be able to dump 7.0.* databases. What
problems are you having, *exactly*?

The error I'm getting is:

getTables(): relation 'vecs': cannot find function with iod 1647 for
trigger RI_ConstraintTrigger_61453

I posted before about errors using pg_dump, and the advice I got was
that I had a version mismatch. I'm now trying to get hold of a
current version of pg_dump, but I can't find it on the site, nor can
I find out how to compile it without compiling everything!

Thanks for the reply.

--Ray.

--------------------------------------
Raymond O'Donnell
rod@iol.ie
rod@gti.ie
--------------------------------------

"Raymond O'Donnell" <rod@iol.ie> writes:

The error I'm getting is:

getTables(): relation 'vecs': cannot find function with iod 1647 for
trigger RI_ConstraintTrigger_61453

Judging from the wording of the error message, it appears that the
version of pg_dump you are using is 6.5 (or even older), which will
certainly not work with a 7.0 or later database. But you should
have a compatible version of pg_dump somewhere in your installation.
Check your PATH.

regards, tom lane

I want to set up a database in a directory other than
/var/lib/pgsql/data/, so in both postgres' ~/.bash_profile and my
~/.bash_profile I added the line:

PGDATA2="/opt/paisley/"; export PGDATA2

<snip>

ERROR: Postmaster environment variable 'PGDATA2' not set
createdb: database creation failed

your init script is using the '-s /bin/sh' argument to 'su', which means
bash runs in compat mode (sh is a soft link to bash on linux), and does not
use .bash_profile. you need to set up a file ~postgres/.profile (along side
of .bash_profile) and put your PGDATA2 environment export in there, or
possibly source .profile from .bash_profile. restart the postmaster and it
should now work.

guys, is there a good reason why the init script has to specify a shell
explicitly? the passwd entry for postgres should be good enough it seems
like... changing the init script at install time is a bad solution because
it gets over-written on postgres upgrades from RPM. and adding a .profile
file is not exactly intuative, and the need for it is not documented
anywhere that i could find.

Glen

On Tue, 19 Feb 2002, Glen Parker wrote:

your init script is using the '-s /bin/sh' argument to 'su', which means
bash runs in compat mode (sh is a soft link to bash on linux), and does not
use .bash_profile. you need to set up a file ~postgres/.profile (along side
of .bash_profile) and put your PGDATA2 environment export in there, or
possibly source .profile from .bash_profile. restart the postmaster and it
should now work.

Glen,

I'll be darned! I haven't learned this about shells in the four years I've
been running linux here. Sure makes a difference 'cause now the alternate
location works. Perhaps this ought to go into the docs somewhere -- or a
FAQ.

Many thanks!

Rich

Dr. Richard B. Shepard, President

Applied Ecosystem Services, Inc. (TM)
2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
http://www.appl-ecosys.com

location works. Perhaps this ought to go into the docs somewhere -- or a
FAQ.

agreed :-) I just added a blurb to the interactive docs about it, on the
alternate location page.

i think the correct longterm fix is to lose the '-s' option in the init
script... feature request!!

glen

http://www.postgresql.org/idocs/index.php?managing-databases.html