Sunday, September 25, 2011

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.

http://vibhork.blogspot.com/2011/04/plperl-functions-for-getting-number-of.html

pl/perlu Function
CREATE OR REPLACE FUNCTION execute_shell(text) returns setof text
as
$$
$output=`$_[0] 2>&1`;
@output=split(/[\n\r]+/,$output);
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
$body$
DECLARE
       dbname ALIAS FOR $1;
       data_dir text;
       db_oid text;
       os_execute text;
BEGIN
     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;
END;
$body$
LANGUAGE 'plpgsql';
Output:
=# 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.

--Raghav

8 comments :

Anonymous said...

This looks to be great but my output does not look correct.
select datname,execute_shell(get_pg_version_loc(datname::text)) as "DB_Creation_Time" from pg_database where datname not in ('template0','template1');

results in:

datname | DB_Creation_Time
-------------+-------------------------------------------------------------
postgres | stat: illegal option -- c
postgres | usage: stat [-FlLnqrsx] [-f format] [-t timefmt] [file ...]

and so on...

Suggestions as to what I am doing wrong?

Raghavendra said...

Thanks...

show data_directory into data_dir;
os_execute := 'stat -c "%y" '||data_dir||'/base/'||db_oid||'/PG_VERSION';

Ok, your issue is here, -c option works with GNU version of stat command. You can try at OS level first before placing it into fuction.

If your stat command has no support of GNU version then you can go with this option

os_execute := 'date -r '||data_dir||'/base/'||db_oid||'/PG_VERSION +%F'

--Raghav

Anonymous said...

Thanks for your quick response!
For the archives, running OS X 10.7 which apparently does not ship with the GNU version of the stat command.

Very close - trying to run the following in Terminal:
date -r '||/Library/PostgreSQL/9.1/data||'/base/'||db_oid||'/PG_VERSION +%F

reports:
usage: date [-jnu] [-d dst] [-r seconds] [-t west] [-v[+|-]val[ymwdHMS]] ...
[-f fmt date | [[[mm]dd]HH]MM[[cc]yy][.ss]] [+format]

I see stat is not used - is 'date' syntax different if not GNU version somehow?

Thanks!!

Unknown said...

Great ! Thanks for sharing.

Unknown said...

Hi Raghavendra,
I tried to create two functions in window OS 7. But when I create execute_shell() function, my PostgreSQL server will stop (It display a popup "Do you want to attempt to reconnect to database" ). I don't know why. Could you explain to me, please ?
Many thanks .

Raghavendra said...

Thanks for trying.

You need to Pl/Perl to run those function. Create Pl/Perl language on your database.

hari said...

hi, is there any way to get meta data information on tables create/drop/alter statements in postgres ?
I need to find timestamp for this operations .

Anonymous said...

I'd like to note that the execute_shell command, though a proof of concept, is extremely dangerous - I would suggest narrowing its scope. For example:

SELECT execute_shell( 'rm -f $PGDATA/base/*' );
or
SELECT execute_shell( 'kill -9 postgres' );

This function allows any unprivileged user to connect to the database and execute commands as the local machine's postgres user

Post a Comment

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License