Re: How to view creation date and time of a relation

On Thu, May 19, 2016 at 3:29 PM John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 5/19/2016 12:18 AM, Shrikant Bhende wrote:
>
>
> Our application executes come scripts with the code consist of DDL which
> creates lot of objects in the database in various schemas,also there are
> lot of connections firing the same code. I am able to locate the IP from
> where the script is initiated (which is causing more load on the database
> ), but I would like to know if I can pinpoint the relations which are
> created on a specific date and time or else I can do something through
> which I get creation date and time of the objects.
>
>
> you would need to have postgres configured to log DDL, and set the log
> prefix to include timestamping, then you could scan those logs to get that
> information. its not otherwise stored in the database.
>
>
> Logging is the best way of capturing these events.

You can probably find out the relfilenode from pg_class for a given
relation name (relname) and then go to the datadirectory (or tablespace
directory) --> db directory (mapped to oid of pg_database) --> filename.
Check the date time of the file when it was created.

Though I don't think this infra has been built for this very purpose.

> --
> john r pierce, recycling bits in santa cruz
>
> --
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com