pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to find table creation time
8+ messages / 7 participants
[nested] [flat]
* How to find table creation time
@ 2014-04-04 23:39 Nik Tek <niktek2005@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Nik Tek @ 2014-04-04 23:39 UTC (permalink / raw)
To: pgsql-admin
Hi,
Is there a way to find table creation time in postgres?
pg version is 9.0
Thank you
Nik
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find table creation time
@ 2014-04-05 06:26 reiner peterke <zedaardv@drizzle.com>
parent: Nik Tek <niktek2005@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: reiner peterke @ 2014-04-05 06:26 UTC (permalink / raw)
To: Nik Tek <niktek2005@gmail.com>; +Cc: pgsql-admin
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 create a 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find table creation time
@ 2014-04-05 15:44 Guillaume Lelarge <guillaume@lelarge.info>
parent: reiner peterke <zedaardv@drizzle.com>
0 siblings, 0 replies; 8+ messages in thread
From: Guillaume Lelarge @ 2014-04-05 15:44 UTC (permalink / raw)
To: reiner peterke <zedaardv@drizzle.com>; +Cc: Nik Tek <niktek2005@gmail.com>; pgsql-admin
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 create a 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find table creation time
@ 2014-04-07 17:03 Steve Crawford <scrawford@pinpointresearch.com>
parent: Nik Tek <niktek2005@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Steve Crawford @ 2014-04-07 17:03 UTC (permalink / raw)
To: Nik Tek <niktek2005@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find table creation time
@ 2014-04-07 19:51 Jason Mathis <jmathis@redzonesoftware.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jason Mathis @ 2014-04-07 19:51 UTC (permalink / raw)
To: pgsql-admin; Steve Crawford <scrawford@pinpointresearch.com>; Nik Tek <niktek2005@gmail.com>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find table creation time
@ 2014-04-08 00:25 Walter Hurry <walterhurry@gmail.com>
parent: Jason Mathis <jmathis@redzonesoftware.com>
0 siblings, 1 reply; 8+ messages in thread
From: Walter Hurry @ 2014-04-08 00:25 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find table creation time
@ 2014-04-08 15:30 Jason Mathis <jmathis@redzonesoftware.com>
parent: Walter Hurry <walterhurry@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jason Mathis @ 2014-04-08 15:30 UTC (permalink / raw)
To: Walter Hurry <walterhurry@gmail.com>; pgsql-admin
Hi Walter,
Thanks for your feedback and suggestions. Although I believe you may have misunderstood or assumed a few key points on this thread. The original question was just to get the creation time of tables. I answered that question and gave an example of different ideas on how the increased logging could benefit your infrastructure.
In terms of auditing ddl changes in the logs, yes I do want an email. We all have different rules and regulations we need to follow. Most of us have more than one dba (or superuser) working in house. Or maybe you just started at a new place and wanted to quickly get a handle on whats going on.
As for disaster recovery, I like to take the approach of “when it happens” more than “it will never happen because I did xyz.” It will rain, lets be ready for it. I was not implying to forgo a “security audit” or give every user super permissions, that will be just silly. In fact we just underwent a security review here but I would still like to have this in place. This additional logging/parsing is a second line of defense. When it does go down (think junior dba or oops I thought that was dev) at least you will know right away, have the exact second it happened and a paper trail. Pretty nice uh? I bet the boy scouts would be jealous about that:)
Embrace those logs, parse them out, send emails, alerts, whatever. Nobody, system, or process is perfect. It will rain, how prepared are you going to be?
Hoping for many sunny days ahead!
-jason
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to find table creation time
@ 2014-04-08 17:43 Jerry Sievers <gsievers19@comcast.net>
parent: Nik Tek <niktek2005@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Jerry Sievers @ 2014-04-08 17:43 UTC (permalink / raw)
To: Nik Tek <niktek2005@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2014-04-08 17:43 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-04 23:39 How to find table creation time Nik Tek <niktek2005@gmail.com>
2014-04-05 06:26 ` reiner peterke <zedaardv@drizzle.com>
2014-04-05 15:44 ` Guillaume Lelarge <guillaume@lelarge.info>
2014-04-07 17:03 ` Steve Crawford <scrawford@pinpointresearch.com>
2014-04-07 19:51 ` Jason Mathis <jmathis@redzonesoftware.com>
2014-04-08 00:25 ` Walter Hurry <walterhurry@gmail.com>
2014-04-08 15:30 ` Jason Mathis <jmathis@redzonesoftware.com>
2014-04-08 17:43 ` Jerry Sievers <gsievers19@comcast.net>
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