Обсуждение: 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>"

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>"

Στις Τρίτη 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

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"

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

Στις Τρίτη 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

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.

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?

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 --

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.

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

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)