Get database creation date on PostgreSQL
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).
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.
Also alternative solution:
- Find database OID:
postgres=# select oid,datname from pg_database where datname='bars_paidserv';-[ RECORD 1 ]----------oid | 5137290datname | bars_paidserv
- Go to database datatfiles (you can find your directory by executing
show data_directory
):
cd /var/lib/postgresql/9.6/main/base
- 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.