Обсуждение: How to find table creation time
Hi, Unfortunately not. This is a feature that is lacking in postgres. One could always look at the file node times, but this is unreliable too, as an operation like a vacuum full will createa new file node. I haven't found a good way to work around this either. Reiner On 5 apr 2014, at 01:39, Nik Tek <niktek2005@gmail.com> wrote: > Hi, > > Is there a way to find table creation time in postgres? > pg version is 9.0 > > > Thank you > Nik
On Sat, 2014-04-05 at 08:26 +0200, reiner peterke wrote: > Hi, > > Unfortunately not. This is a feature that is lacking in postgres. > > One could always look at the file node times, but this is unreliable too, as an operation like a vacuum full will createa new file node. > I haven't found a good way to work around this either. > I don't think it would be quite difficult to create an extension that uses a hook to record the timestamp when a user creates a table. Something like what was done around CREATE EXTENSION to allow adding some extensions but not all, according to a white list. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
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
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 = ddlMoving 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.
Jason Mathis wrote: > someone “sneaking in” a change. Or even think about data recovery, “what time did you drop that production table?” Whaaat? You need to do a security review *now*. These possibilities indicate anarchy, and are a sure recipe for disaster.Someone has dropped a production table and you want it to be logged, or to be emailed about it? You don't *grant* privileges to drop production tables to all and sundry.
On April 7, 2014 at 6:26:38 PM, Walter Hurry (walterhurry@gmail.com) wrote:
Jason Mathis wrote:
> someone “sneaking in” a change. Or even think about data recovery, “what time did you drop that production table?”
Whaaat? You need to do a security review *now*. These possibilities indicate anarchy, and are a sure recipe for disaster. Someone has dropped a production table and you want it to be logged, or to be emailed about it?
You don't *grant* privileges to drop production tables to all and sundry.
--
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.
Nik Tek <niktek2005@gmail.com> writes: > Hi, > > Is there a way to find table creation time in postgres? > pg version is 9.0 Not retroactively unless (as already discussed here) you had DDL logging enabled. But see docs relating to EVENT triggers when you move to 9.3 or higher release at some point. > > Thank you > Nik > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800