From: Jason Mathis <jmathis@redzonesoftware.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
To: Steve Crawford <scrawford@pinpointresearch.com>
To: Nik Tek <niktek2005@gmail.com>
Subject: Re: How to find table creation time
Date: Mon, 7 Apr 2014 13:51:27 -0600
Message-ID: <etPan.534301bf.46e87ccd.2ad6@palos> (raw)
In-Reply-To: <5342DA72.2030103@pinpointresearch.com>
References: <CAHySzWVNoyn803AOU3fU6yzjTa1bmQ28RED_+t3MKmCV7tgCSA@mail.gmail.com>
<5342DA72.2030103@pinpointresearch.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Sorry Guys i responded earlier but only to Guillaume for some unexplained reason:/ Anyways steve suggested what I was going to suggest. I think its a great advice going forward.
That is crazy that creation time is not logged anywhere.
One thing to note, you can log ddl changes in postgres. Some would argue this should be on by default and i would agree.
In the postgres.conf>>
log_statement = ddl
Moving forward that will give you the creation time of tables. Although I know your objective may have been to go back in time:) Anyways I actually parse the logs out and have an email sent to me if a DDL statement happens. Its a nice way to keep a pulse on schema changes or to catch someone “sneaking in” a change. Or even think about data recovery, “what time did you drop that production table?” is no longer a question.
hopefully that can help!
-jason
That is crazy that creation time is not logged anywhere.
One thing to note, you can log ddl changes in postgres. Some would argue this should be on by default and i would agree.
In the postgres.conf>>
log_statement = ddl
Moving forward that will give you the creation time of tables. Although I know your objective may have been to go back in time:) Anyways I actually parse the logs out and have an email sent to me if a DDL statement happens. Its a nice way to keep a pulse on schema changes or to catch someone “sneaking in” a change. Or even think about data recovery, “what time did you drop that production table?” is no longer a question.
hopefully that can help!
-jason
On April 7, 2014 at 11:04:21 AM, Steve Crawford (scrawford@pinpointresearch.com) wrote:
On 04/04/2014 04:39 PM, Nik Tek wrote:
> Hi,
>
> Is there a way to find table creation time in postgres?
> pg version is 9.0
>
Not by some baked-in command but you can determine it from the logs if
you have the appropriate log-settings and sufficient log-retention.
Cheers,
Steve
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--
This transmission contains confidential and privileged information intended
solely for the party identified above. If you receive this message in
error, you must not use it or convey it to others. Please destroy it
immediately and contact the sender at (303) 386-3955 or by return e-mail to
the sender.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jmathis@redzonesoftware.com, scrawford@pinpointresearch.com, niktek2005@gmail.com
Subject: Re: How to find table creation time
In-Reply-To: <etPan.534301bf.46e87ccd.2ad6@palos>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox