Обсуждение: Stumped by a version conflict.

Hi all...

I joined this list in an effort to solve a puzzle I fail to understand.  Thank you in advance for taking the time to read.

I've been managing a domain for the last eight years as an erstwhile administrator.  Erstwhile meaning it is largely a matter of setting up and automating the routine functions of the machine. I am not by any stretch of the imagination a guru. And please keep in mind that aside from minor tweaks and upgrades, I pretty much leave the machine alone.

In that time, I've upgraded postgres 7.1 to 7.4 and now to 8.1 and have rarely needed to reboot the machine. (Yay CentOS!)  Manual installs from source.  Not RPM's

A few days ago however, the co-lo appears to have taken the system down for a short time and brought it back up, essentially rebooting the system.  No big deal, except that until then, it had been running for almost two years.

Everything came back up as expected, with the exception of postgres.  When I went to start it manually, I got an error warning of an unrecognized tcpip_socket parameter in postgresql.conf.  Searching the 8.1 manual I discovered it had been deprecated in favor of listen_addresses.  Odd, but okay.

I commented out the parameter (irrelevant since psql was configured with defaults anyway) and tried starting it again.  The next error I got was that the database was initialized with version 7.4 and this version was 8.1.  What?

I checked the time stamps of the database I knew to be active, and yes, there are several timestamps from two days ago so I kow that's the most recent.  PG_VERSION does indeed show 7.4 as the current version, and yet, the binaries return 8.1.11 as the current version.

It should be noted at this point that medical problems have affected my memory, so I may well be forgetting something obvious to the rest of you.   :)

So my questions are these:  Since 8.1 binaries are the only ones that exist on the machine, (in /usr/bin) how is it possible that they could have utilized a database initialized with 7.4 in the first place?  I thought perhaps I started the server back then with an old binary, rather than what's in the path, but I find no other instances of psql, pg_ctl, etc. except whats in /bin/.

Without an answer to that, and since I can't start the server as it is (apparently) currently compiled, I'd have to recompile 7.4, open the database, dumpall with those binaries, initialize a new database with the 8.1 binaries, and restore the backed up db into the 8.1 structure.  But if the old 7.4 (current) db was running under 8.1 (and I still cant see how that was possible) will it damage the db structure when it is restored? 

Maybe the better question, though probably harder to answer is, "What am I forgetting?"

Any thoughts or suggestions on how to restart postgres with the database I know to be current will be especially helpful. 

Thank you.

On 7/16/10 3:31 PM, Patric Michael wrote:
> Hi all...
>
> I joined this list in an effort to solve a puzzle I fail to understand.
> Thank you in advance for taking the time to read.
>
> I've been managing a domain for the last eight years as an erstwhile
> administrator.  Erstwhile meaning it is largely a matter of setting up
> and automating the routine functions of the machine. I am not by any
> stretch of the imagination a guru. And please keep in mind that aside
> from minor tweaks and upgrades, I pretty much leave the machine alone.
>
> In that time, I've upgraded postgres 7.1 to 7.4 and now to 8.1 and have
> rarely needed to reboot the machine. (Yay CentOS!)  Manual installs from
> source.  Not RPM's
>
> A few days ago however, the co-lo appears to have taken the system down
> for a short time and brought it back up, essentially rebooting the
> system.  No big deal, except that until then, it had been running for
> almost two years.
>
> Everything came back up as expected, with the exception of postgres.
> When I went to start it manually, I got an error warning of an
> unrecognized tcpip_socket parameter in postgresql.conf.  Searching the
> 8.1 manual I discovered it had been deprecated in favor of
> listen_addresses.  Odd, but okay.
>
> I commented out the parameter (irrelevant since psql was configured with
> defaults anyway) and tried starting it again.  The next error I got was
> that the database was initialized with version 7.4 and this version was
> 8.1.  What?
>
> I checked the time stamps of the database I knew to be active, and yes,
> there are several timestamps from two days ago so I kow that's the most
> recent.  PG_VERSION does indeed show 7.4 as the current version, and
> yet, the binaries return 8.1.11 as the current version.

Is it possible that you built the Postgres 8.1, then accidentally started the old 7.4 version of Postgres, and then
deletedthe 7.4 tree?  It may have been still running for the last two years on a Postgres 7.4 server. 

Craig


>
> It should be noted at this point that medical problems have affected my
> memory, so I may well be forgetting something obvious to the rest of
> you.   :)
>
> So my questions are these:  Since 8.1 binaries are the only ones that
> exist on the machine, (in /usr/bin) how is it possible that they could
> have utilized a database initialized with 7.4 in the first place?  I
> thought perhaps I started the server back then with an old binary,
> rather than what's in the path, but I find no other instances of psql,
> pg_ctl, etc. except whats in /bin/.
>
> Without an answer to that, and since I can't start the server as it is
> (apparently) currently compiled, I'd have to recompile 7.4, open the
> database, dumpall with those binaries, initialize a new database with
> the 8.1 binaries, and restore the backed up db into the 8.1 structure.
> But if the old 7.4 (current) db was running under 8.1 (and I still cant
> see how that was possible) will it damage the db structure when it is
> restored?
>
> Maybe the better question, though probably harder to answer is, "What am
> I forgetting?"
>
> Any thoughts or suggestions on how to restart postgres with the database
> I know to be current will be especially helpful.
>
> Thank you.
>
>

On Fri, Jul 16, 2010 at 4:31 PM, Patric Michael <bluestar43@gmail.com> wrote:
> Hi all...
>
> I joined this list in an effort to solve a puzzle I fail to understand.
> Thank you in advance for taking the time to read.
>
> I've been managing a domain for the last eight years as an erstwhile
> administrator.  Erstwhile meaning it is largely a matter of setting up and
> automating the routine functions of the machine. I am not by any stretch of
> the imagination a guru. And please keep in mind that aside from minor tweaks
> and upgrades, I pretty much leave the machine alone.
>
> In that time, I've upgraded postgres 7.1 to 7.4 and now to 8.1 and have
> rarely needed to reboot the machine. (Yay CentOS!)  Manual installs from
> source.  Not RPM's
>
> A few days ago however, the co-lo appears to have taken the system down for
> a short time and brought it back up, essentially rebooting the system.  No
> big deal, except that until then, it had been running for almost two years.
>
> Everything came back up as expected, with the exception of postgres.  When I
> went to start it manually, I got an error warning of an unrecognized
> tcpip_socket parameter in postgresql.conf.  Searching the 8.1 manual I
> discovered it had been deprecated in favor of listen_addresses.  Odd, but
> okay.

Is it possible you started 8.1 in a different directory by hand
(pg_ctl -D /my/other/dir start) many years ago and that the default
old install of 7.4 is sitting in the /var/lib/pgsql spot?

HI Scott.  I think if anything, the reverse is true.  It is entirely possible that I started 7.4 in a different directory by hand and that the 7.4 server had been running all this time.  Since I can't find any other instances pf pg_ctl, I may well have deleted the old tree afterward as Craig suggested. (And with me being unaware that it was the old version running and not the new.)

Truly, that's the only logical thing I can think of for an explanation.

My only concern at this point, and the one that makes me hesitate, is wondering whether or not the current database is somehow erroneously reporting itself as 7.4 when it is in fact 8.1.  Is there any other means aside from PG_VERSION for the binaries to determine which database structure was initialized?

Thank you for your reply.

Patric Michael <bluestar43@gmail.com> writes:
> HI Scott.  I think if anything, the reverse is true.  It is entirely
> possible that I started 7.4 in a different directory by hand and that the
> 7.4 server had been running all this time.  Since I can't find any other
> instances pf pg_ctl, I may well have deleted the old tree afterward as Craig
> suggested. (And with me being unaware that it was the old version running
> and not the new.)

Yeah, I too think that Craig's theory is the most likely explanation,
if you're sure there is no 7.4 executable to be found on the machine.
On most modern Unixen it's perfectly possible for an existing process to
continue to run an executable that's been deleted from the filesystem.

> My only concern at this point, and the one that makes me hesitate, is
> wondering whether or not the current database is somehow erroneously
> reporting itself as 7.4 when it is in fact 8.1.

Pretty much impossible --- the version number is compiled into the
executable at build time.

> Is there any other means
> aside from PG_VERSION for the binaries to determine which database structure
> was initialized?

The PG_VERSION files contain a human-readable version number, but there
is also a "catalog version number" stored in the pg_control file.  You
might try pg_controldata for an additional check on whether the
executables you have at hand match the database, but it seems pretty
clear they don't :-(

            regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> On most modern Unixen it's perfectly possible for an existing process to
> continue to run an executable that's been deleted from the filesystem.

Indeed, but it's not generally a good thing.  I'd recommend installing
something which checks for such things and sends a report when found
(uh, openvas is the first thing that comes to mind, but TIGER IDS might
catch these also).

    Thanks,

        Stephen

Thank you Tom, and I do agree in theory, but I am still unconvinced that something else isnt amiss because I do recall having to rewrite the web scripts which access the database, after I upgraded.  Seems there was a change where an empty field no longer returned a zero but a null (or vice versa) when pg_python performed a SELECT * statement.  It is that little bit that makes me wonder if is somehow still possible that the database was in fact initialized with 8.1 despite reporting 7.4...

Is there any way for me to tell *without* postmaster or psql running, aside from the contents of PG_VERSION?

Patric

* Patric Michael (bluestar43@gmail.com) wrote:
> Is there any way for me to tell *without* postmaster or psql running, aside
> from the contents of PG_VERSION?

pg_controldata, as Tom suggested...

    Stephen

On Fri, Jul 16, 2010 at 6:08 PM, Patric Michael <bluestar43@gmail.com> wrote:
> Thank you Tom, and I do agree in theory, but I am still unconvinced that
> something else isnt amiss because I do recall having to rewrite the web
> scripts which access the database, after I upgraded.  Seems there was a
> change where an empty field no longer returned a zero but a null (or vice
> versa) when pg_python performed a SELECT * statement.  It is that little bit
> that makes me wonder if is somehow still possible that the database was in
> fact initialized with 8.1 despite reporting 7.4...

So what does

sudo locate postgresql.conf

say?

Scott...

On Fri, Jul 16, 2010 at 11:10 PM, Patric Michael <bluestar43@gmail.com> wrote:
> Scott...
>
> On Fri, Jul 16, 2010 at 5:48 PM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> So what does
>>
>> sudo locate postgresql.conf
>>
>> say?
>
> That I am irredeemably lazy as an admin?  :)

Lazy isn't always bad.  Sometimes it makes you an efficiency expert.

>
> /usr/share/pgsql/postgresql.conf.sample
> /var/lib/pgsql/data/postgresql.conf
> /home/archives/db/postgresql.conf
> /home/postgres/data/postgresql.conf
>
> The first line is the --datadir as reported by pg_configure --configure
> The second line is the 7.4 data dir according to its PG_VERSION
> The third line was a directory backup I made when I upgraded to 8.1
> The fourth line is the most recently updated database according to the time
> stamps. (three days ago)

So, what does PG_VERSION in each of the above directories have to say?

Patric Michael wrote:
> /usr/share/pgsql/postgresql.conf.sample
> /var/lib/pgsql/data/postgresql.conf
> /home/archives/db/postgresql.conf
> /home/postgres/data/postgresql.conf
>
> The first line is the --datadir as reported by pg_configure --configure
> The second line is the 7.4 data dir according to its PG_VERSION
> The third line was a directory backup I made when I upgraded to 8.1
> The fourth line is the most recently updated database according to the
> time stamps. (three days ago)

Is there a PG_VERSION suggesting the 4th one is an 8.1 system?  It's
completely possible you manually started the database cluster like this:

pg_ctl start -D /home/postgres/data

And that's the one that's been running for the last two years.  The
default system PostgreSQL install is going to point at
/var/lib/pgsql/data by default and try to start whatever database is
there, which is the 7.4 one and therefore won't start with your current
binaries.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Hi Greg...