28

I want to get the Database creation date in POSTGRESQL. My version is 9.3.4 running on Ubuntu 14.04. Can I do it with a select statement or can I do it with access to the server?

tshepang
  • 12,111
  • 21
  • 91
  • 136
Za7pi
  • 1,338
  • 7
  • 22
  • 33
  • I don't think this is possible. –  Jul 17 '14 at 14:27
  • Perhaps related with http://stackoverflow.com/questions/3211769/how-to-get-last-access-modification-date-of-a-postgresql-database?rq=1 ? – Houari Jul 17 '14 at 14:41
  • 1
    Maybe something like this can get it? http://www.postgresql.org/message-id/Pine.LNX.4.63.0603301925130.13116@linux.site and http://raghavt.blogspot.com/2011/09/how-to-get-database-creation-time-in.html – Kuberchaun Jul 17 '14 at 14:43

3 Answers3

64

I completely agree with Craig Ringer (excellent answer!)... but for my purposes , this is good enough: 

SELECT (pg_stat_file('base/'||oid ||'/PG_VERSION')).modification, datname FROM pg_database;

Simple and clean (but superuser).

Bernardo Jerez
  • 898
  • 1
  • 8
  • 8
  • this is modification date on database or it can treat as creation date too? – Gurupreet Singh Bhatia Sep 02 '20 at 17:55
  • According the documentation: "pg_stat_file returns a record containing the file size, last accessed time stamp, last modified time stamp, last file status change time stamp (Unix platforms only), file creation time stamp (Windows only), and a boolean indicating if it is a directory. " .... so only if you are in a Windows enviroment you can use the "creation" attribute. In my linux machine the "modification" attribute is good enough to get a pseudo creation date. – Bernardo Jerez Nov 25 '20 at 16:45
13

There is no built-in record of the PostgreSQL database creation time in the system. All approaches that rely on file system creation/modification times can produce wrong answers.

For example, if you pg_basebackup a replica node then fail over to it, the creation time will appear to be the time the replica was created. pg_basebackup doesn't preserve file creation/modification times because they're generally not considered relevant for operation of the database system.

The only reliable way to get the database creation time is to create a 1-row table with the creation time in it when you create the database, then set permissions / add a trigger so it rejects updates.

If you don't mind the risk of a wrong answer where the creation time is reported as being much newer than it really was, you can look at the timestamp of the base/[dboid]/PG_VERSION file. @Bob showed a useful way to do that.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
2

Also alternative solution:

  1. Find database OID:
postgres=# select oid,datname from pg_database where datname='bars_paidserv';
-[ RECORD 1 ]----------
oid     | 5137290
datname | bars_paidserv
  1. Go to database datatfiles (you can find your directory by executing show data_directory):

cd /var/lib/postgresql/9.6/main/base

  1. list PG_VERSION file in your OID:
postgres@test-rp:~/9.6/main/base$ ls -l 5137290/PG_VERSION
-rw------- 1 postgres postgres 4 Jan 29 12:34 5137290/PG_VERSION

i.e. my database bars_paidserv with OID 5137290 was created on Jan 29.

Vsevolod Gromov
  • 471
  • 4
  • 11