Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cOgBN-00053b-44 for pgsql-admin@arkaria.postgresql.org; Wed, 04 Jan 2017 07:40:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cOgBM-0001WP-Is for pgsql-admin@arkaria.postgresql.org; Wed, 04 Jan 2017 07:40:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cOg9e-0007yB-Sw for pgsql-admin@postgresql.org; Wed, 04 Jan 2017 07:38:38 +0000 Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=smadev.internal.net) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cOg9b-0005ii-FE for pgsql-admin@postgresql.org; Wed, 04 Jan 2017 07:38:38 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id v047cSUn049895 for ; Wed, 4 Jan 2017 09:38:28 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: Table/View creation time To: pgsql-admin@postgresql.org References: From: Achilleas Mantzios Message-ID: <586CA674.5070107@matrix.gatewaynet.com> Date: Wed, 4 Jan 2017 09:38:28 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:38.0) Gecko/20100101 Thunderbird/38.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------060007080404030809080209" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------060007080404030809080209 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 03/01/2017 17:23, Payal Singh wrote: > You might be able to get some useful info by running stat on $PGDATA/base/ (for instance when the file was created). Don't think this is applicable for a view though. Modern Linux'es do not support exposing creation time. EXT4 stores this though. > > A better way is to search for the respective DDL command in postgres log files, if you are logging all DDLs > > Payal > > Payal Singh, > Database Administrator, > OmniTI Computer Consulting Inc. > Phone: 240.646.0770 x 253 > > On Mon, Jan 2, 2017 at 5:17 AM, Ram DBA > wrote: > > Hi team, > > Do we have a way of figuring out when a table/view is created in the postgres database? > > ram=# \dv > List of relations > Schema | Name | Type | Owner > --------+--------+------+---------- > public | v_test | view | postgres > (1 row) > > > Thank you, > Ram > > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt --------------060007080404030809080209 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 03/01/2017 17:23, Payal Singh wrote:

You might be able to get some useful info by running stat on $PGDATA/base/<table's relid> (for instance when the file was created). Don't think this is applicable for a view though.

Modern Linux'es do not support exposing creation time. EXT4 stores this though.

A better way is to search for the respective DDL command in postgres log files, if you are logging all DDLs

Payal

Payal Singh,
Database Administrator,
OmniTI Computer Consulting Inc.
Phone: 240.646.0770 x 253

On Mon, Jan 2, 2017 at 5:17 AM, Ram DBA <postgresdbas@gmail.com> wrote:

Hi team,

Do we have a way of figuring out when a table/view is created in the postgres database?

ram=# \dv
List of relations
Schema | Name | Type | Owner
--------+--------+------+----------
public | v_test | view | postgres
(1 row)

Thank you,

Ram

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

--------------060007080404030809080209--