Skip site navigation (1) Skip section navigation (2)

Proposal: Store "timestamptz" of database creation on "pg_database"

From: Fabrízio de Royes Mello <fabriziomello(at)gmail(dot)com>
To: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Proposal: Store "timestamptz" of database creation on "pg_database"
Date: 2012-12-27 00:48:52
Message-ID: CAFcNs+qMGbLmeUOnjmbna_K7=UP817BPw9QxhbCTGNScPKVoeA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi all,

This proposal is about add a column "datcreated" on "pg_database" to store
the "timestamp" of the database creation.

A couple weeks ago I had a trouble with a PostgreSQL instance, actually our
ERP had some strange behaviors with some data loss, but I searched for
ERRORs in log files (OS and PG) and I found nothing.

Looking at the files and directories in the cluster noticed something
strange, the date / time of the file "base/9999/PG_VERSION" (database of
our ERP) was different compared to when we create it. So I used the
following SQL to check the date / time of creation of the databases in the
cluster:

fabrizio=# SELECT datname,
(pg_stat_file('base/'||oid||'/PG_VERSION')).modification AS datcreated
fabrizio-#   FROM pg_database;
  datname  |       datcreated
-----------+------------------------
 template1 | 2012-12-26 12:11:53-02
 template0 | 2012-12-26 12:11:54-02
 postgres  | 2012-12-26 12:11:54-02
 fabrizio  | 2012-12-26 12:12:02-02
(4 rows)

This isn't an elegant solution to do that, but worked fine. However, why
not we have a column to store this information?

Somebody have another idea?

Regards,

-- 
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2012-12-27 01:08:13
Subject: Re: Proposal: Store "timestamptz" of database creation on "pg_database"
Previous:From: Greg StarkDate: 2012-12-27 00:23:43
Subject: Re: buffer assertion tripping under repeat pgbench load

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group