Question: drop database problem

[Ben Kim]

Dear list,

I'm using Postgresql 7.2 on Solaris.

I have this error message when I try to drop a database. I couldn't find a
mention anywhere on this error. Could someone advise how to circumvent
this error?

pgsql=# drop database mydb;
ERROR: Postmaster environment variable 'PGDATA3' not set

The database owner is pgsql, and if I echo $PGDATA3 as the owner, it
returns a valid directory. Also, the datpath is correct.

select * from pg_database; shows
datname ... datpath
-----------------+...+---------
mydb PGDATA3

In the $PGDATA3/data/base/ directory, I confirmed that one of the
directories is being used, by bulk inserting some test data into mydb
database.

Also, I have several databases sharing the datpath of PGDATA3. Is there a
system table associating the datpath and the actual OS path? (like mydb ->
$PGDATA3/data/base/335253, etc.)

dropdb gives the same result.
ERROR: Postmaster environment variable 'PGDATA3' not set
dropdb: database removal failed




Thanks in advance.




Ben Kim
Database Developer/Systems Administrator
College of Education
Texas A&M University




---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Post by Ben Kim
pgsql=# drop database mydb;
ERROR: Postmaster environment variable 'PGDATA3' not set
The database owner is pgsql, and if I echo $PGDATA3 as the owner, it
returns a valid directory. Also, the datpath is correct.

You have $PGDATA3 set in your interactive environment, but evidently not
in the environment of the postmaster process. How are you starting the
postmaster exactly, and where are you expecting it to get $PGDATA3 from?

Post by Ben Kim
Also, I have several databases sharing the datpath of PGDATA3. Is there a
system table associating the datpath and the actual OS path?

No, just the environment variable. (This is all changing in 8.0 with
tablespaces, thank goodness.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Ben Kim]

Many thanks for the advice.

I'm starting it with something like the following, as root in /etc/init.d
script.

su pgsql -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data
-p /usr/local/pgsql/bin/postmaster start"

And pgsql has PGDATA3 defined in .cshrc.

The PGDATA3 is not being used for new databases. I guess I need to refine
the startup script if for the rest of the databases.

Will I have to shut down the server and restart it (introduce PGDATA3
properly) before I can drop that particular database?


Regards,

Ben Kim
Database Developer/Systems Administrator
College of Education
Texas A&M University

Post by Tom Lane

Post by Ben Kim
pgsql=# drop database mydb;
ERROR: Postmaster environment variable 'PGDATA3' not set
The database owner is pgsql, and if I echo $PGDATA3 as the owner, it
returns a valid directory. Also, the datpath is correct.

You have $PGDATA3 set in your interactive environment, but evidently not
in the environment of the postmaster process. How are you starting the
postmaster exactly, and where are you expecting it to get $PGDATA3 from?

Post by Ben Kim
Also, I have several databases sharing the datpath of PGDATA3. Is there a
system table associating the datpath and the actual OS path?

No, just the environment variable. (This is all changing in 8.0 with
tablespaces, thank goodness.)
regards, tom lane

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

[Nikhil Parva]

unregister


---------------------------(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

[Tom Lane]

Post by Ben Kim
I'm starting it with something like the following, as root in /etc/init.d
script.
su pgsql -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data
-p /usr/local/pgsql/bin/postmaster start"
And pgsql has PGDATA3 defined in .cshrc.

su is most likely executing its -c command with /bin/sh, which will pay
zero attention to .cshrc. You may need to set up a .profile as well as
.cshrc. Also, I think that su won't cause *any* of these setup scripts
to be executed unless you use the "-" or "-l" options; a bare su just
runs the command in your current root environment.

This stuff varies across different Unix variants, but it's uniformly
a source of gotchas :-(. Read your su and shell man pages carefully.

Post by Ben Kim
Will I have to shut down the server and restart it (introduce PGDATA3
properly) before I can drop that particular database?

Yeah. That part's not so hard: if you restart the postmaster manually
then it will inherit your interactive variables. But I do *not*
recommend that, because it'll break after your next reboot. You need to
do the legwork to be sure that the variables show up in the basic
boot-time context.

Again, this definitely sucks, and we are moving away from it as fast as
we can. But that's where things stand in current releases.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Ben Kim]

Thanks for the advice!!

I'll probably add those PGDATA variables to my startup script.

Also, is there a way to circumvent this error (of missing $PGDATA3)?
Otherwise I'll need to wait for a couple of months if I have to restart
the server.

The database in question, and the datpath, has not been used for some
time. It didn't give an error about the missing env. variables when the
server started, so we didn't bother about it as long as it starts.

It seems the information is somewhere in the system (because I can still
use the database and see that it's correctly changing OS files) so I
vaguely guess there's some difference in the startup code and shutdown
code.



On a side note,


I wonder if it's possible to change postgresql's smart mode to something
like Apache's graceful restart. This will give me some freedom to restart
the server any time.

(Postgres's smart shutdown blocks new connections - Apache's graceful
accepts new connections with new configuration)

http://httpd.apache.org/docs-2.0/stopping.html
apachectl -k graceful
=====================
The USR1 or graceful signal causes the parent process to advise the
children to exit after their current request (or to exit immediately if
they're not serving anything). The parent re-reads its configuration files
and re-opens its log files. As each child dies off the parent replaces it
with a child from the new generation of the configuration, which begins
serving new requests immediately.


http://www.postgresql.org/docs/7.3/interactive/postmaster-shutdown.html
SIGTERM
=======
After receiving SIGTERM, the postmaster disallows new connections, but
lets existing backends end their work normally. It shuts down only after
all of the backends terminate normally. This is Smart Shutdown.


Thanks again, you are great.


Ben Kim
Database Developer/Systems Administrator
College of Education
Texas A&M University

Post by Tom Lane

Post by Ben Kim
I'm starting it with something like the following, as root in /etc/init.d
script.
su pgsql -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data
-p /usr/local/pgsql/bin/postmaster start"
And pgsql has PGDATA3 defined in .cshrc.

su is most likely executing its -c command with /bin/sh, which will pay
zero attention to .cshrc. You may need to set up a .profile as well as
.cshrc. Also, I think that su won't cause *any* of these setup scripts
to be executed unless you use the "-" or "-l" options; a bare su just
runs the command in your current root environment.
This stuff varies across different Unix variants, but it's uniformly
a source of gotchas :-(. Read your su and shell man pages carefully.

Post by Ben Kim
Will I have to shut down the server and restart it (introduce PGDATA3
properly) before I can drop that particular database?

Yeah. That part's not so hard: if you restart the postmaster manually
then it will inherit your interactive variables. But I do *not*
recommend that, because it'll break after your next reboot. You need to
do the legwork to be sure that the variables show up in the basic
boot-time context.
Again, this definitely sucks, and we are moving away from it as fast as
we can. But that's where things stand in current releases.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)