Get database creation date on PostgreSQL

Question

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?

Answer 1

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).

Answer 2

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.