How to view creation date and time of a relation
Hi all,
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.
--
Shrikant Bhende
+91-9975543712
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.
--
john r pierce, recycling bits in santa cruz
On Thu, May 19, 2016 at 3:29 PM John R Pierce <pierce@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
On 19/05/2016 10:57, Sameer Kumar wrote:
On Thu, May 19, 2016 at 3:29 PM John R Pierce <pierce@hogranch.com <mailto:pierce@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.
Is this creation timestamp info exposed in Linux (ext4,xfs,etc?)? Last time I checked this info was available in FreeBSD out of the box.
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
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt