In the database world, it’s fair to ask question to database that when have you started? In Oracle world, it can be easily find out by querying system level view V$INSTANCE. In PostgreSQL world, until today, I wasn’t able to answer that question without checking database log files. Today, I asked this question to OmniTi’s database operations irc channel and got answer in 20 minutes. I regret that I should have asked it earlier but anyways, now I know that in PostgreSQL it’s possible using this query.
omniti=# SELECT (pg_stat_file( 'postmaster.pid')).modification;
modification
------------------------
2010-08-13 15:37:14-04
If someone complains that database was restarted , it would be easy as DBA to query and verify it. I hope this query will be helpful to you during day-to-day operations!
Why don't you use pg_postmaster_start_time()? it's available since 8.1
looks like pg_postmaster_start_time() doesn't change in event of DB restart. So this is very handy. thanks!
Thanks gleu. Slava:When i dived into code, pg_postmaster_start_time() is based on postmaster.pid as well so both functions should work.