Get database creation date on PostgreSQL

Question

I want to get the database creation date in POSTGRESQL. My version 9.3.4 runs on Ubuntu 14.04. Can I do this with the select statement or can I do this with access to the server?

Answer 1

The system does not have a built-in record about the time the PostgreSQL database was created. All approaches based on the creation / modification time of the file system may cause incorrect responses.

For example, if you are a pg_basebackup replica node, then go to it, the creation time will be displayed as the replica creation time. pg_basebackup does not save file creation / modification time, since they are usually not considered relevant for the operation of the database system.

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

If you are not opposed to the risk of a wrong answer when the creation time is reported as much newer than it actually was, you can look at the timestamp of the base/[dboid]/PG_VERSION . @Bob showed a useful way to do this .

Answer 2

I totally agree with Craig Ringer (great 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).