Обсуждение: need a method to ping a running database
I need a shell scriptable method to determine if a postgresql database is running and accepting connections. I've written a script to start out postgresql database and I was using: "pg_ctl status" to determine if the database had been started. There are a couple of problems with this method. It seems that if you start postgresql and then issue a "kill -9" on the postmaster processes a .pid file is left behind (understandable), but then if you run "pg_ctl status" it will immediately report that the database is running and give you the pid number and whatever else. This is a bit misleading to say the least. To solve this problem I changed my db_ping() function to be more like "ps ax |grep -q postmaster && pg_ctl status". This works better, but there is still a problem. The .pid file is being written immediately, not when the database is actually up and accepting connections. This causes my java app to try and open the database and setup a connection pool before it should. I need a shell scriptable method to determine if the database is started and ready to accept connections.
> It seems that if you start postgresql and then issue a "kill -9" on the postmaster processes a .pid file is left >behind (understandable), but then if you run "pg_ctl status" it will >immediately report that the database is running and give you the pid >number and whatever else. This is a bit misleading to say the least. First thoght is don't do "kill -9" other than the pid problem is the probem that postgresql caches data in memory and that could be a problem if you kill like that. Use pg_ctl stop -m fast or pg_ctl stop -m immediate Take care, Jay
Yes, of course that would be best, but I was expicitly testing postgresql's ability to recover from power spikes and other harsh failure scenarios. So far it hasn't missed a beat. Jay O'Connor wrote: >>It seems that if you start postgresql and then issue a "kill -9" on the >> >> >postmaster processes a .pid file is left > > >>behind (understandable), but then if you run "pg_ctl status" it will >>immediately report that the database is running and give you the pid >>number and whatever else. This is a bit misleading to say the least. >> >> > >First thoght is don't do "kill -9" other than the pid problem is the probem >that postgresql caches data in memory and that could be a problem if you >kill like that. Use pg_ctl stop -m fast or pg_ctl stop -m immediate > >Take care, >Jay > > >
What about down= \ `psql -d database -h server -c 'select 1;' 2>&1 | grep -c '^ERROR'` ? Dima. P.S. One thing to be cautious about is that if it has just been started, you'll get something like 'ERROR: The database is starting up.', that will get counted as an error (probably incorrectly)... So, the real implementation has to be a little more elaborate then that to be able to catch and handle that situation... Travis Hume wrote: > I need a shell scriptable method to determine if a postgresql database > is running and accepting connections. > > I've written a script to start out postgresql database and I was using: > "pg_ctl status" > > to determine if the database had been started. There are a couple of > problems with this method. It seems that if you start postgresql and > then issue a "kill -9" on the postmaster processes a .pid file is left > behind (understandable), but then if you run "pg_ctl status" it will > immediately report that the database is running and give you the pid > number and whatever else. This is a bit misleading to say the least. > To solve this problem I changed my db_ping() function to be more like > "ps ax |grep -q postmaster && pg_ctl status". This works better, but > there is still a problem. > > The .pid file is being written immediately, not when the database is > actually up and accepting connections. This causes my java app to try > and open the database and setup a connection pool before it should. > > I need a shell scriptable method to determine if the database is started > and ready to accept connections. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Travis Hume <travis.hume@tenzing.com> writes:
> I need a shell scriptable method to determine if a postgresql database
> is running and accepting connections.
Right now I think the best you can do is see if a command actually
succeeds, eg try
psql -c "select 1"
and see if it complains.
There are some speculations in the archives about writing a pg_ping
utility that would check to see if the postmaster is alive without
expending the overhead of really executing a SQL command --- but no
one's got round to writing any code for it, AFAIK. In any case, psql -c
is a bit more thorough of a test, since it not only checks whether the
database is alive but whether it is willing to accept commands from
*you*. I can think of scenarios where each kind of test would be more
appropriate, so it's something you have to make up your own mind about.
regards, tom lane
Travis Hume wrote: > to determine if the database had been started. There are a couple of > problems with this method. It seems that if you start postgresql and > then issue a "kill -9" on the postmaster processes a .pid file is left > behind (understandable), but then if you run "pg_ctl status" it will > immediately report that the database is running and give you the pid > number and whatever else. I've seen this message at the bottom of some emails to this list: ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster Erik
So try: `psql -d database -h server -c "select 'It is running'" 2>/dev/null | grep -c "It is running"` Though I don't think the "ERROR: database is staring up" is an incorrect error as it still means that the database is not ready to handle queries. Still, I think it's safer to look for the existence of expected results that the absence of specific error messages. Cheers, Steve On Wednesday 11 June 2003 6:42 pm, Dima Tkach wrote: > What about > > down= \ > `psql -d database -h server -c 'select 1;' 2>&1 | grep -c '^ERROR'` > > ? > > Dima. > > P.S. One thing to be cautious about is that if it has just been started, > you'll get something like 'ERROR: The database is starting up.', that > will get counted as an error (probably incorrectly)... So, the real > implementation has to be a little more elaborate then that to be able to > catch and handle that situation... > > Travis Hume wrote: > > I need a shell scriptable method to determine if a postgresql database > > is running and accepting connections. > > > > I've written a script to start out postgresql database and I was using: > > "pg_ctl status" > > > > to determine if the database had been started. There are a couple of > > problems with this method. It seems that if you start postgresql and > > then issue a "kill -9" on the postmaster processes a .pid file is left > > behind (understandable), but then if you run "pg_ctl status" it will > > immediately report that the database is running and give you the pid > > number and whatever else. This is a bit misleading to say the least. > > To solve this problem I changed my db_ping() function to be more like > > "ps ax |grep -q postmaster && pg_ctl status". This works better, but > > there is still a problem. > > > > The .pid file is being written immediately, not when the database is > > actually up and accepting connections. This causes my java app to try > > and open the database and setup a connection pool before it should. > > > > I need a shell scriptable method to determine if the database is started > > and ready to accept connections. > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
Steve Crawford wrote: >So try: >`psql -d database -h server -c "select 'It is running'" 2>/dev/null | grep -c >"It is running"` > > It doesn't make any difference - if it is 'starting up', you won't get that 'it is running string' anyway... BTW, 2>/dev/null redirect is pointless - the ERROR: stuff goes to stdout anyway :-) >Though I don't think the "ERROR: database is staring up" is an incorrect error >as it still means that the database is not ready to handle queries. > Sure. I did not say it was "incorrect"... But it is *different* from most if not all other errors, because it requires a different item... For example, if the server is dead, you are probably going to want to restart it.... If it is 'starting up' though, you just need to wait a little bit. > Still, I >think it's safer to look for the existence of expected results that the >absence of specific error messages. > I don't know what is "safer" about it... :-) It seems to be more like a question of taste... You must be one of those guys who prefers to write something like if (condition == FALSE) instead of if (!condition) :-) Dima > >Cheers, >Steve > >On Wednesday 11 June 2003 6:42 pm, Dima Tkach wrote: > > >>What about >> >>down= \ >>`psql -d database -h server -c 'select 1;' 2>&1 | grep -c '^ERROR'` >> >>? >> >>Dima. >> >>P.S. One thing to be cautious about is that if it has just been started, >>you'll get something like 'ERROR: The database is starting up.', that >>will get counted as an error (probably incorrectly)... So, the real >>implementation has to be a little more elaborate then that to be able to >>catch and handle that situation... >> >>Travis Hume wrote: >> >> >>>I need a shell scriptable method to determine if a postgresql database >>>is running and accepting connections. >>> >>>I've written a script to start out postgresql database and I was using: >>>"pg_ctl status" >>> >>>to determine if the database had been started. There are a couple of >>>problems with this method. It seems that if you start postgresql and >>>then issue a "kill -9" on the postmaster processes a .pid file is left >>>behind (understandable), but then if you run "pg_ctl status" it will >>>immediately report that the database is running and give you the pid >>>number and whatever else. This is a bit misleading to say the least. >>>To solve this problem I changed my db_ping() function to be more like >>>"ps ax |grep -q postmaster && pg_ctl status". This works better, but >>>there is still a problem. >>> >>>The .pid file is being written immediately, not when the database is >>>actually up and accepting connections. This causes my java app to try >>>and open the database and setup a connection pool before it should. >>> >>>I need a shell scriptable method to determine if the database is started >>>and ready to accept connections. >>> >>> >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >>> >>> >>---------------------------(end of broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >>http://www.postgresql.org/docs/faqs/FAQ.html >> >>
> Sure. I did not say it was "incorrect"... > But it is *different* from most if not all other errors, because it > requires a different item... It just depends on the use of the script (about which I didn't know the details). If the only purpose is to determine if I can run queries then the simple test will do. If the purpose is to determine whether or not to start/restart the db server then my "script" is not complete. > > Still, I > >think it's safer to look for the existence of expected results that the > >absence of specific error messages. > > I don't know what is "safer" about it... :-) > It seems to be more like a question of taste... Not taste - just the voice of experience from one who has been burned. Suppose your db is on a remote machine and someone screws up DNS so the host doesn't resolve or the db admin has made an error in updating pg_hba.conf or a network cable is unplugged or a routing table is damaged. You can even have something as silly as psql being deleted or not in the path. None of these cases will yield the string "ERROR" so a script that looks for the absence of "ERROR" will say everything is OK. I've given up trying to dream up/test for every possible error - there are too many and once I think I've thought of them all I "discover" another. :) Checking for a known expected result will definitely let you know if the db is or is not available (note, not available != down - just not available to the testing program - again it depends on the purpose of your script). Cheers, Steve
Steve Crawford wrote: >>Sure. I did not say it was "incorrect"... >>But it is *different* from most if not all other errors, because it >>requires a different item... >> >> > >It just depends on the use of the script (about which I didn't know the >details). If the only purpose is to determine if I can run queries then the >simple test will do. > If that is your only purpose, you don't need any scripts - just proceed directly to running your query, and see if it works :-) >Not taste - just the voice of experience from one who has been burned. Suppose >your db is on a remote machine and someone screws up DNS so the host doesn't >resolve or the db admin has made an error in updating pg_hba.conf or a >network cable is unplugged or a routing table is damaged. You can even have >something as silly as psql being deleted or not in the path. None of these >cases will yield the string "ERROR" so a script that looks for the absence of >"ERROR" will say everything is OK. > No, not "none of them" - the former will complain.... As for the latter, it doesn't matter what you grep for, because, the 'Command not found' thing will abort your script, so you'll never be able to look at the results. It is a good ide to check the exist status of the script, yes. > >I've given up trying to dream up/test for every possible error - there are too >many and once I think I've thought of them all I "discover" another. :) > >Checking for a known expected result will definitely let you know if the db is >or is not available > Oh, yeah? ;-) Try this: alias psql 'echo \!*' psql -c "select 'It is running';" | grep -c "It is running" :-) > (note, not available != down - just not available to the > testing program - again it depends on the purpose of your script). If you are talking about the name of that variable I used in the original message, that is *definitely* a question of taste:-) Dima