pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase Create Date
13+ messages / 5 participants
[nested] [flat]
* Database Create Date
@ 2007-01-09 15:22 Negandhi, Nishith <Nishith.Negandhi@infoUSA.com>
0 siblings, 2 replies; 13+ messages in thread
From: Negandhi, Nishith @ 2007-01-09 15:22 UTC (permalink / raw)
To: pgsql-admin
Hi,
I need to know the create date of databases on PosegreSQL.
PG_DATABASE does not provide this information.
Are there any other system catalogs that can provide the above
information??
Thanks
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Database Create Date
@ 2007-01-09 15:36 Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
parent: Negandhi, Nishith <Nishith.Negandhi@infoUSA.com>
1 sibling, 1 reply; 13+ messages in thread
From: Andy Shellam (Mailing Lists) @ 2007-01-09 15:36 UTC (permalink / raw)
To: Negandhi, Nishith <Nishith.Negandhi@infoUSA.com>; +Cc: pgsql-admin
Negandhi, Nishith wrote:
> Hi,
> I need to know the create date of databases on PosegreSQL.
> PG_DATABASE does not provide this information.
> Are there any other system catalogs that can provide the above
> information??
>
> Thanks
> !DSPAM:37,45a3b385137101969839670!
I'm sure there is (not known to me atm), but for a quick shot you could
find the DB's "OID" from pg_database and look at the create date of "<pg
data dir>/base/<db oid>"
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Database Create Date
@ 2007-01-09 15:46 Negandhi, Nishith <Nishith.Negandhi@infoUSA.com>
0 siblings, 0 replies; 13+ messages in thread
From: Negandhi, Nishith @ 2007-01-09 15:46 UTC (permalink / raw)
To: Andy Shellam \(Mailing Lists\) <andy.shellam-lists@mailnetwork.co.uk>; +Cc: pgsql-admin
Well..thats one way, however I only hv access to the web interface (i.e.
phpAdmin).
-----Original Message-----
From: Andy Shellam (Mailing Lists)
[mailto:andy.shellam-lists@mailnetwork.co.uk]
Sent: Tuesday, January 09, 2007 9:37 AM
To: Negandhi, Nishith
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Database Create Date
Negandhi, Nishith wrote:
> Hi,
> I need to know the create date of databases on PosegreSQL.
> PG_DATABASE does not provide this information.
> Are there any other system catalogs that can provide the above
> information??
>
> Thanks
> !DSPAM:37,45a3b385137101969839670!
I'm sure there is (not known to me atm), but for a quick shot you could
find the DB's "OID" from pg_database and look at the create date of "<pg
data dir>/base/<db oid>"
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Database Create Date
@ 2007-01-09 16:04 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
0 siblings, 2 replies; 13+ messages in thread
From: Achilleas Mantzios @ 2007-01-09 16:04 UTC (permalink / raw)
To: pgsql-admin
Óôéò Ôñßôç 09 ÉáíïõÜñéïò 2007 17:36, ï/ç Andy Shellam (Mailing Lists) Ýãñáøå:
> Negandhi, Nishith wrote:
> > Hi,
> > I need to know the create date of databases on PosegreSQL.
> > PG_DATABASE does not provide this information.
> > Are there any other system catalogs that can provide the above
> > information??
> >
> > Thanks
> > !DSPAM:37,45a3b385137101969839670!
>
> I'm sure there is (not known to me atm), but for a quick shot you could
> find the DB's "OID" from pg_database and look at the create date of "<pg
> data dir>/base/<db oid>"
>
I was able to find that in FreeBSD the -U in ls (1) does the job.
However i could not find any inode creation time related info for linux
(ext3).
Anyone has any clue on that?
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Database Create Date
@ 2007-01-09 16:10 Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 13+ messages in thread
From: Andy Shellam (Mailing Lists) @ 2007-01-09 16:10 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin
Achilleas Mantzios wrote:
> I was able to find that in FreeBSD the -U in ls (1) does the job.
> However i could not find any inode creation time related info for linux
> (ext3).
> Anyone has any clue on that?
>
I believe "ls -l" by default shows the created time, you can switch to
show the last modified time using "ls -l --time=atime"
Also in FreeBSD (at least, 6.1) there is no "-U" option to ls; there is
a "-u" option but this shows the last access, not the creation time.
--
Andy Shellam
NetServe Support Team
the Mail Network
"an alternative in a standardised world"
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Database Create Date
@ 2007-01-09 16:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 13+ messages in thread
From: Tom Lane @ 2007-01-09 16:11 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin
Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> However i could not find any inode creation time related info for linux
> (ext3).
I don't believe it keeps that. You could possibly look at the mod time
on the PG_VERSION file ... Postgres never touches that after DB creation.
This is not, however, much of a guarantee as something else could have
changed it, eg a file backup/restore process.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Database Create Date
@ 2007-01-10 08:00 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
0 siblings, 1 reply; 13+ messages in thread
From: Achilleas Mantzios @ 2007-01-10 08:00 UTC (permalink / raw)
To: pgsql-admin
Óôéò Ôñßôç 09 ÉáíïõÜñéïò 2007 18:10, ï/ç Andy Shellam (Mailing Lists) Ýãñáøå:
> Achilleas Mantzios wrote:
> > I was able to find that in FreeBSD the -U in ls (1) does the job.
> > However i could not find any inode creation time related info for linux
> > (ext3).
> > Anyone has any clue on that?
>
> I believe "ls -l" by default shows the created time, you can switch to
> show the last modified time using "ls -l --time=atime"
>
No, it seems ext3 does not keep this info. (See also Tom's answer).
> Also in FreeBSD (at least, 6.1) there is no "-U" option to ls; there is
> a "-u" option but this shows the last access, not the creation time.
It must have been introduced somewhere in the 6.1-STABLE cycle.
For my dev machine (6.1-STABLE #8) "-U" support is included,
whereas in my other pet machine 6.1-PRERELEASE #1 it is not.
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Database Create Date
@ 2007-01-10 18:35 Bruno Wolff III <bruno@wolff.to>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Bruno Wolff III @ 2007-01-10 18:35 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin
On Wed, Jan 10, 2007 at 10:00:37 +0200,
Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
> Στις Τρίτη 09 Ιανουάριος 2007 18:10, ο/η Andy Shellam (Mailing Lists) έγραψε:
> > Achilleas Mantzios wrote:
> > > I was able to find that in FreeBSD the -U in ls (1) does the job.
> > > However i could not find any inode creation time related info for linux
> > > (ext3).
> > > Anyone has any clue on that?
> >
> > I believe "ls -l" by default shows the created time, you can switch to
> > show the last modified time using "ls -l --time=atime"
Just another note on this, atime is the last access time. ctime is the real
last modify time, mtime is another modify time that can be changed (which
is useful after backups). atime is often disabled in ext3 file systems to
reduce I/O, since it isn't all that useful.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Database Create Date
@ 2007-01-10 19:33 Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 13+ messages in thread
From: Andy Shellam (Mailing Lists) @ 2007-01-10 19:33 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; pgsql-admin
Bruno Wolff III wrote:
> On Wed, Jan 10, 2007 at 10:00:37 +0200,
> Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
>
>> Στις Τρίτη 09 Ιανουάριος 2007 18:10, ο/η Andy Shellam (Mailing Lists) έγραψε:
>>
>>> Achilleas Mantzios wrote:
>>>
>>>> I was able to find that in FreeBSD the -U in ls (1) does the job.
>>>> However i could not find any inode creation time related info for linux
>>>> (ext3).
>>>> Anyone has any clue on that?
>>>>
>>> I believe "ls -l" by default shows the created time, you can switch to
>>> show the last modified time using "ls -l --time=atime"
>>>
>
> Just another note on this, atime is the last access time. ctime is the real
> last modify time, mtime is another modify time that can be changed (which
> is useful after backups). atime is often disabled in ext3 file systems to
> reduce I/O, since it isn't all that useful.
>
I thought it was as well to begin with - but in "ls" on Linux there's a
separate "atime" and "access" value to the "show time" parameter in "ls"
- so if "atime" is the last access time, what's "access" mean?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Database Create Date
@ 2007-01-10 19:39 Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
parent: Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
0 siblings, 1 reply; 13+ messages in thread
From: Andy Shellam (Mailing Lists) @ 2007-01-10 19:39 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-admin
Andy Shellam (Mailing Lists) wrote:
> Bruno Wolff III wrote:
>> On Wed, Jan 10, 2007 at 10:00:37 +0200,
>> Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
>>
>>> Στις Τρίτη 09 Ιανουάριος 2007 18:10, ο/η Andy Shellam (Mailing
>>> Lists) έγραψε:
>>>
>>>> Achilleas Mantzios wrote:
>>>>
>>>>> I was able to find that in FreeBSD the -U in ls (1) does the job.
>>>>> However i could not find any inode creation time related info for
>>>>> linux
>>>>> (ext3).
>>>>> Anyone has any clue on that?
>>>>>
>>>> I believe "ls -l" by default shows the created time, you can switch to
>>>> show the last modified time using "ls -l --time=atime"
>>>>
>>
>> Just another note on this, atime is the last access time. ctime is
>> the real
>> last modify time, mtime is another modify time that can be changed
>> (which
>> is useful after backups). atime is often disabled in ext3 file
>> systems to
>> reduce I/O, since it isn't all that useful.
>>
> I thought it was as well to begin with - but in "ls" on Linux there's
> a separate "atime" and "access" value to the "show time" parameter in
> "ls" - so if "atime" is the last access time, what's "access" mean?
Hmm ok just done a bit of experimenting on this - "atime" and "access"
show exactly the same information - "mtime" is an invalid value
according to my Fedora 5 system. Confused.
-- start paste --
ls -l / --time=mtime
ls: invalid argument `mtime' for `--time'
Valid arguments are:
- `atime', `access', `use'
- `ctime', `status'
Try `ls --help' for more information.
-- end paste --
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Database Create Date
@ 2007-01-10 20:15 Bruno Wolff III <bruno@wolff.to>
parent: Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
0 siblings, 0 replies; 13+ messages in thread
From: Bruno Wolff III @ 2007-01-10 20:15 UTC (permalink / raw)
To: Andy Shellam \(Mailing Lists\) <andy.shellam-lists@mailnetwork.co.uk>; +Cc: pgsql-admin
On Wed, Jan 10, 2007 at 19:39:09 +0000,
"Andy Shellam (Mailing Lists)" <andy.shellam-lists@mailnetwork.co.uk> wrote:
> >I thought it was as well to begin with - but in "ls" on Linux there's
> >a separate "atime" and "access" value to the "show time" parameter in
> >"ls" - so if "atime" is the last access time, what's "access" mean?
atime includes at least reads of the file. I don't know if it also includes
writes or stats.
> Hmm ok just done a bit of experimenting on this - "atime" and "access"
> show exactly the same information - "mtime" is an invalid value
> according to my Fedora 5 system. Confused.
mtime is the time you normally see, so there may not be a separate option
for it on ls.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Database Create Date
@ 2007-01-10 20:21 Negandhi, Nishith <Nishith.Negandhi@infoUSA.com>
0 siblings, 0 replies; 13+ messages in thread
From: Negandhi, Nishith @ 2007-01-10 20:21 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; Andy Shellam \(Mailing Lists\) <andy.shellam-lists@mailnetwork.co.uk>; +Cc: pgsql-admin
Guys..I don't hv access to the server directely.
Is it possible to get the DB creation date from the system catalog.??
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Bruno Wolff III
Sent: Wednesday, January 10, 2007 2:16 PM
To: Andy Shellam (Mailing Lists)
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Database Create Date
On Wed, Jan 10, 2007 at 19:39:09 +0000,
"Andy Shellam (Mailing Lists)" <andy.shellam-lists@mailnetwork.co.uk>
wrote:
> >I thought it was as well to begin with - but in "ls" on Linux there's
> >a separate "atime" and "access" value to the "show time" parameter in
> >"ls" - so if "atime" is the last access time, what's "access" mean?
atime includes at least reads of the file. I don't know if it also
includes writes or stats.
> Hmm ok just done a bit of experimenting on this - "atime" and "access"
> show exactly the same information - "mtime" is an invalid value
> according to my Fedora 5 system. Confused.
mtime is the time you normally see, so there may not be a separate
option for it on ls.
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Database Create Date
@ 2007-01-15 14:36 Negandhi, Nishith <Nishith.Negandhi@infoUSA.com>
parent: Negandhi, Nishith <Nishith.Negandhi@infoUSA.com>
1 sibling, 0 replies; 13+ messages in thread
From: Negandhi, Nishith @ 2007-01-15 14:36 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: pgsql-admin
Hi Jim,
Thanks for your reply.
Well..there is no particular reason for knowing the database create
date, but I feel this information is something which should be known,
especially in a development environment where multiple database are
created and deleted. This helps in keep historical information.
Regards
-----Original Message-----
From: Jim C. Nasby [mailto:jim@nasby.net]
Sent: Friday, January 12, 2007 11:01 PM
To: Negandhi, Nishith
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Database Create Date
On Tue, Jan 09, 2007 at 09:22:49AM -0600, Negandhi, Nishith wrote:
> Hi,
> I need to know the create date of databases on PosegreSQL.
> PG_DATABASE does not provide this information.
> Are there any other system catalogs that can provide the above
> information??
This is the second time I remember someone asking for this, so maybe
it's worth adding... why is it you need to know when the database was
created?
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2007-01-15 14:36 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-09 15:22 Database Create Date Negandhi, Nishith <Nishith.Negandhi@infoUSA.com>
2007-01-09 15:36 ` Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
2007-01-09 16:04 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-01-09 16:10 ` Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
2007-01-10 08:00 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-01-10 18:35 ` Bruno Wolff III <bruno@wolff.to>
2007-01-10 19:33 ` Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
2007-01-10 19:39 ` Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
2007-01-10 20:15 ` Bruno Wolff III <bruno@wolff.to>
2007-01-09 16:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-01-15 14:36 ` Negandhi, Nishith <Nishith.Negandhi@infoUSA.com>
2007-01-09 15:46 Re: Database Create Date Negandhi, Nishith <Nishith.Negandhi@infoUSA.com>
2007-01-10 20:21 Re: Database Create Date Negandhi, Nishith <Nishith.Negandhi@infoUSA.com>
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