How to get Database Creation Time in PostgreSQL 9.0 ?

In PostgreSQL, database creation time is not stored in any of the pg_catalogs. So question arises, how do we know when was database created.

For every database, a directory is created with database-oid number under $PGDATA/base along with a set of OID’s,OID_fsm,OID_vm, PG_VERSION files for each Object(Tables/Indexes/View/ etc.,).

Every OID,OID_fsm,OID_vm, files will be get updated as per the changes made at database-level. However, PG_VERSION file will never get updated on any changes made to the database. So, we going to use timestamp of PG_VERSION file as database creation time. I believe that there will be a chance of changing PG_VERSION timestamp, but I am not sure in which case this changes happen.

To get timestamp of PG_VERSION, I need something which executes OS command at PG Instance-level. So, I used pl/perlu function created by one of my collegue Vibhor Kumar.

pl/perlu Function

CREATE OR REPLACE FUNCTION execute_shell(text) returns setof text
$output=`$_[0] 2>&1`;
foreach $out (@output)
{ return_next($out);
return undef;
$$ language plperlu;

And, one function to get database oid.

CREATE OR REPLACE FUNCTION public.get_pg_version_loc(dbname varchar) RETURNS text AS
dbname ALIAS FOR $1;
data_dir text;
db_oid text;
os_execute text;
SELECT INTO db_oid oid from pg_database where datname = dbname;
show data_directory into data_dir;
os_execute := 'stat -c "%y" '||data_dir||'/base/'||db_oid||'/PG_VERSION';
return os_execute;
LANGUAGE 'plpgsql';


=# select datname,execute_shell(get_pg_version_loc(datname::text)) as "DB_Createion_Time"
-# from pg_database where datname not in ('template0','template1');
datname | DB_Createion_Time
postgres | 2011-01-10 21:48:37.222016571 +0530
provider | 2011-05-26 11:40:14.253434477 +0530
pgbench_test | 2011-08-14 16:52:21.689198728 +0530
pgpool | 2011-08-26 12:30:19.864134713 +0530
(4 rows)

Will be back with more stuff :). Do post your comments if any, they will be highly appreciated.