Thread: Alternate database locations
This came up back in the days of postgres-6.x and I was told that it would
be working in the 7.x series. I'm sure that it is, but I'm not doing
something correctly.
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
then I restarted the postmaster.
When I try to create a database there I get the message that the
postmaster doesn't know of that directory:
[rshepard@salmo ~]$ createdb --location=PGDATA2 --template=template1 paisley
ERROR: Postmaster environment variable 'PGDATA2' not set
createdb: database creation failed
What have I done incorrectly, please?
TIA,
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 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 > what is the address of the interactive docs page?