Обсуждение: Question: drop database problem
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
Ben Kim <bkim@coe.tamu.edu> writes:
> 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?
> 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
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
On Mon, 25 Oct 2004, Tom Lane wrote:
> Ben Kim <bkim@coe.tamu.edu> writes:
> > 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?
>
> > 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
>
unregister
Ben Kim <bkim@coe.tamu.edu> writes:
> 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.
> 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
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
On Mon, 25 Oct 2004, Tom Lane wrote:
> Ben Kim <bkim@coe.tamu.edu> writes:
> > 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.
>
> > 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
>