Обсуждение: Creation date of postgres database
Hi, Is there any way to get the date of creation of a database in Postgres? Does postgres store this information in any system table. If anybody have any idea about this please let me know. I need to find out the creation date of one of my database. Thanks, Pradeep
On Thu, Mar 30, 2006 at 02:05:24PM +0530, Pradeep Sharma wrote: > Is there any way to get the date of creation of a database in Postgres? > Does postgres store this information in any system table. I'm not aware that the creation time is stored anywhere (except perhaps in query logs) but you might be able to infer it from the modification times of the oldest files in the database's directory. PG_VERSION looks like a good candidate -- the backend probably doesn't modify it after the database is created, so if nothing at the OS level has touched that file then its modification time should reflect the database's creation time. -- Michael Fuhr
On Thu, 30 Mar 2006, Michael Fuhr wrote: > On Thu, Mar 30, 2006 at 02:05:24PM +0530, Pradeep Sharma wrote: > > Is there any way to get the date of creation of a database in Postgres? > > Does postgres store this information in any system table. > > I'm not aware that the creation time is stored anywhere (except > perhaps in query logs) but you might be able to infer it from the > modification times of the oldest files in the database's directory. > PG_VERSION looks like a good candidate -- the backend probably > doesn't modify it after the database is created, so if nothing at > the OS level has touched that file then its modification time should > reflect the database's creation time. Micheal, Thanks for the reply. But I guess there is some communication gap between me and you regarding this topic. As I understood from your reply is, you are talking about the date of Postgres setup/installation/upgrade. Am I right? If I am right then sorry for the miscommunication. Please let me clarify my question again. Suppose I created a new database using the command: CREATE DATABASE <database_name> I want to know how can I get created date of the above database. Thanks once again -- Pradeep > > -- > Michael Fuhr > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
On Thu, Mar 30, 2006 at 05:20:00PM +0530, Pradeep Sharma wrote: > Thanks for the reply. But I guess there is some communication gap between > me and you regarding this topic. As I understood from your reply is, you > are talking about the date of Postgres setup/installation/upgrade. No, I was talking about the creation time of a particular database. > Suppose I created a new database using the command: > > CREATE DATABASE <database_name> > > I want to know how can I get created date of the above database. As I mentioned, unless you logged the CREATE DATABASE statement I don't think the creation date is stored anywhere, but you could look at the modification times of the oldest files in the database's directory. For example, one of my databases is named test. I can find that database's oid by querying pg_database: test=> SELECT oid FROM pg_database WHERE datname = 'test'; oid ------- 16388 (1 row) I then list the files in that database's directory, sorted by time: % ls -lt $PGDATA/base/16388 | tail -2 -rw------- 1 postgres postgres 4 Dec 6 09:39 PG_VERSION -rw------- 1 postgres postgres 0 Dec 6 09:39 1248 From this output I infer that the test database was created on 6 Dec. Most files in the directory are susceptible to being modified but I don't think anything touches PG_VERSION, so its modification time should reflect the database creation time unless something at the OS level modifies it. Try a command like the following: % ls -lt $PGDATA/base/*/PG_VERSION You should see that each database has a copy of this file and that each file has a different modification time (some might be within a few seconds of each other if databases were created around the same time, such as during a restore). Is this not what you're looking for? -- Michael Fuhr
On Thu, Mar 30, 2006 at 05:27:12AM -0700, Michael Fuhr wrote: > Try a command like the following: > > % ls -lt $PGDATA/base/*/PG_VERSION > > You should see that each database has a copy of this file and that > each file has a different modification time (some might be within > a few seconds of each other if databases were created around the > same time, such as during a restore). > > Is this not what you're looking for? Are you looking for a creation time that would persist across dump/drop/restore? In that case I don't think it's available. -- Michael Fuhr
On Thu, 30 Mar 2006, Michael Fuhr wrote: > On Thu, Mar 30, 2006 at 05:20:00PM +0530, Pradeep Sharma wrote: > > Thanks for the reply. But I guess there is some communication gap between > > me and you regarding this topic. As I understood from your reply is, you > > are talking about the date of Postgres setup/installation/upgrade. > > No, I was talking about the creation time of a particular database. > > > Suppose I created a new database using the command: > > > > CREATE DATABASE <database_name> > > > > I want to know how can I get created date of the above database. > > As I mentioned, unless you logged the CREATE DATABASE statement I > don't think the creation date is stored anywhere, but you could > look at the modification times of the oldest files in the database's > directory. For example, one of my databases is named test. I can > find that database's oid by querying pg_database: > > test=> SELECT oid FROM pg_database WHERE datname = 'test'; > oid > ------- > 16388 > (1 row) > > I then list the files in that database's directory, sorted by time: > > % ls -lt $PGDATA/base/16388 | tail -2 > -rw------- 1 postgres postgres 4 Dec 6 09:39 PG_VERSION > -rw------- 1 postgres postgres 0 Dec 6 09:39 1248 > > From this output I infer that the test database was created on 6 Dec. > Most files in the directory are susceptible to being modified but I > don't think anything touches PG_VERSION, so its modification time > should reflect the database creation time unless something at the > OS level modifies it. > > Try a command like the following: > > % ls -lt $PGDATA/base/*/PG_VERSION > > You should see that each database has a copy of this file and that > each file has a different modification time (some might be within > a few seconds of each other if databases were created around the > same time, such as during a restore). > > Is this not what you're looking for? > Thanks Micheal, This solved my problem. I got the creation date of my database. -- Pradeep > -- > Michael Fuhr > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >