On Thu, May 19, 2016 at 3:29 PM John R Pierce <pie...@hogranch.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
