Thread: In which case PG_VERSION file updates ?
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.
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
On Sun, Sep 25, 2011 at 2:00 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On Saturday, September 24, 2011 12:34:02 pm Raghavendra wrote:See here:
> 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.
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
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. +