In which case PG_VERSION file updates ?

On Saturday, September 24, 2011 12:34:02 pm Raghavendra wrote:

Respected All,

In which case $PGDATA/base/database-oid/PG_VERSION file updates ?

I have observed, PG_VERSION file is created at DB creation time and will
never get
updated. I mean file PG_VERSION TIMESTAMP.

See here:
http://www.postgresql.org/docs/9.1/interactive/storage-file-layout.html

Thanks in advance.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

--
Adrian Klaver
adrian.klaver@gmail.com

Thanks Adrian Klaver,

Provided link gives about the information of what PG_VERSION file, which am
aware of:)

In my observation, all the object related OID's, _fsm, _vm files under
$PGDATA/base/database-oid/ directory will change as per the changes made to
the database, whereas PG_VERSION file never changes. Because its the file
tells on which version of Binary the Data directory is built upon.

So, my question is, Is there any case, where PG_VERSION file updates with
any of the utility process or
PG_VERSION file never been touched by PG-Instance ?

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Sun, Sep 25, 2011 at 2:00 AM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

On Sunday, September 25, 2011 2:26:56 am Raghavendra wrote:

Thanks Adrian Klaver,

Provided link gives about the information of what PG_VERSION file, which am
aware of:)

In my observation, all the object related OID's, _fsm, _vm files under
$PGDATA/base/database-oid/ directory will change as per the changes made to
the database, whereas PG_VERSION file never changes. Because its the file
tells on which version of Binary the Data directory is built upon.

So, my question is, Is there any case, where PG_VERSION file updates with
any of the utility process or
PG_VERSION file never been touched by PG-Instance ?

From what I can see, PG_VERSION is written at database creation to document the
major version of the Postgres instance used to create the database. Since it
only holds the major version string (i.e. 9.0) it is not touched during minor
updates, for example 9.0.0 --> 9.0.1. Grepping the pg_upgrade code shows it
might touch PG_VERSION. A quick grep on the rest of the source code shows only
initdb writing out PG_VERSION, though it is checked by other code. This is by no
means an in depth look and it would take some one with more knowledge of
Postgres internals to give you a definitive answer. Might be worth a post on -
hackers.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

--
Adrian Klaver
adrian.klaver@gmail.com

On Sun, Sep 25, 2011 at 3:26 AM, Raghavendra
<raghavendra.rao@enterprisedb.com> wrote:

Thanks Adrian Klaver,
Provided link gives about the information of what PG_VERSION file, which am
aware of:)
In my observation, all the object related OID's, _fsm, _vm files under
$PGDATA/base/database-oid/ directory will change as per the changes made to
the database, whereas PG_VERSION file never changes. Because its the file
tells on which version of Binary the Data directory is built upon.
So, my question is, Is there any case, where PG_VERSION file updates with
any of the utility process or
PG_VERSION file never been touched by PG-Instance ?

If you run pg_upgrade I'd expect that to change it.

From what I can see, PG_VERSION is written at database creation to document
the
major version of the Postgres instance used to create the database. Since
it
only holds the major version string (i.e. 9.0) it is not touched during
minor
updates, for example 9.0.0 --> 9.0.1. Grepping the pg_upgrade code shows
it
might touch PG_VERSION. A quick grep on the rest of the source code shows
only
initdb writing out PG_VERSION, though it is checked by other code. This is
by no
means an in depth look and it would take some one with more knowledge of
Postgres internals to give you a definitive answer. Might be worth a post
on -
hackers.

Thank you Adrian Klaver / Scott Marlowe for your valuable inputs. I got
clarified.

As said, PG_VERSION file is created at the time of Database creation.
Sometimes, question arises that at what time database created. For this we
don't have any information to get from pg_catalogs, so with PG_VERSION file
timestamp we can pull database creation time.

However, with your inputs its clear that when PG_VERSION file is touched. In
pg_upgrade or any of the Postgres Internals touching PG_VERSION file will
never get exact database creation time.

I am not knowing why database creation time is not considered to be in
pg_catalogs.

-- Raghav

Scott Marlowe wrote:

On Sun, Sep 25, 2011 at 3:26 AM, Raghavendra
<raghavendra.rao@enterprisedb.com> wrote:

Thanks Adrian Klaver,
Provided link gives about the information of what PG_VERSION file, which am
aware of:)
In my observation, all the object related OID's, _fsm, _vm files under
$PGDATA/base/database-oid/ directory will change as per the changes made to
the database, whereas PG_VERSION file never changes. Because its the file
tells on which version of Binary the Data directory is built upon.
So, my question is, Is there any case, where PG_VERSION file updates with
any of the utility process or
PG_VERSION file never been touched by PG-Instance ?

If you run pg_upgrade I'd expect that to change it.

Yes, PG_VERSION is one of the files that is set via initdb on the new
cluster, and that file is not copied over from the old cluster (only
heap/index user files).

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +