How to get Database Creation Time in PostgreSQL 9.0 ?

Posted on September 24, 2011 by Raghav

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(/[nr]+/,$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

16 Replies to “How to get Database Creation Time in PostgreSQL 9.0 ?”

Anonymous says:

January 1, 2019 at 11:40 pm

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 &#39||/Library/PostgreSQL/9.1/data||&#39/base/&#39||db_oid||&#39/PG_VERSION +%Freports: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 &#39date&#39 syntax different if not GNU version somehow?Thanks!!
Anonymous says:

January 1, 2019 at 11:29 pm

I&#39d 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( &#39rm -f $PGDATA/base/*&#39 );or SELECT execute_shell( &#39kill -9 postgres&#39 );This function allows any unprivileged user to connect to the database and execute commands as the local machine&#39s postgres user
Luan Huynh says:

January 1, 2019 at 11:29 pm

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&#39t know why. Could you explain to me, please ? Many thanks .
Raghavendra says:

January 1, 2019 at 11:29 pm

Thanks… show data_directory into data_dir; os_execute := &#39stat -c "%y" &#39||data_dir||&#39/base/&#39||db_oid||&#39/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 := &#39date -r &#39||data_dir||&#39/base/&#39||db_oid||&#39/PG_VERSION +%F&#39–Raghav
Raghavendra says:

January 1, 2019 at 11:29 pm

Thanks for trying.You need to Pl/Perl to run those function. Create Pl/Perl language on your database.
Hari hara says:

January 1, 2019 at 11:29 pm

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 .
Luan Huynh says:

January 1, 2019 at 11:29 pm

Great ! Thanks for sharing.
Anonymous says:

January 1, 2019 at 11:29 pm

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 (&#39template0&#39,&#39template1&#39);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?
Anonymous says:

May 6, 2015 at 10:00 am

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
Hari hara says:

January 20, 2015 at 9:23 am

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 .
Raghavendra says:

August 13, 2013 at 9:31 am

Thanks for trying.

You need to Pl/Perl to run those function. Create Pl/Perl language on your database.
Luan Huynh says:

August 12, 2013 at 1:49 pm

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 .
Luan Huynh says:

August 9, 2013 at 8:32 am

Great ! Thanks for sharing.
Anonymous says:

November 19, 2011 at 10:26 am

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!!
Raghavendra says:

November 17, 2011 at 4:36 am

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 says:

November 17, 2011 at 4:35 am

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?

Comments are closed.