Обсуждение: Yum upgrade of PostgreSQL 8.4 from to rc1 rendered data unusable

Dear PostgreSQL admins and gurus,

I always knew that installing Postgres using Yum/RPM/etc is a very bad
idea, but decided to try it on one my boxes that runs local wiki/Trac.

I added /etc/yum.repos.d/pgdg-84-centos.repo to my yum configuration
and installed PostgreSQL version 8.4devel_20090310-1PGDG.rhel5.i386

Today I ran "yum update", which quietly upgraded my installation to
"8.4rc1-1PGDG.rhel5.i386" and now my server does not start.

-bash-3.2$ pg_ctl --version
pg_ctl (PostgreSQL) 8.4rc1
-bash-3.2$ pg_ctl -D /db/data start
server starting
-bash-3.2$ FATAL:  database files are incompatible with server
DETAIL:  The database cluster was initialized with CATALOG_VERSION_NO
200902242, but the server was compiled with CATALOG_VERSION_NO
200904091.
HINT:  It looks like you need to initdb.

I do NOT have a pg_dump of the database because I did not anticipate
this idiocy.

I am looking for any help on how I can revert back to the previous
version of PostgreSQL so that I can read the database, do pg_dump, and
never EVER use yum again to upgrade it.

Please halp!

Konstantin

On Mon, Jun 15, 2009 at 4:42 PM, Konstantin
Gredeskoul<kig@dropinmedia.com> wrote:
> Dear PostgreSQL admins and gurus,
>
> I always knew that installing Postgres using Yum/RPM/etc is a very bad idea,
> but decided to try it on one my boxes that runs local wiki/Trac.
>
> I added /etc/yum.repos.d/pgdg-84-centos.repo to my yum configuration and
> installed PostgreSQL version 8.4devel_20090310-1PGDG.rhel5.i386
>
> Today I ran "yum update", which quietly upgraded my installation to
> "8.4rc1-1PGDG.rhel5.i386" and now my server does not start.
>
> -bash-3.2$ pg_ctl --version
> pg_ctl (PostgreSQL) 8.4rc1
> -bash-3.2$ pg_ctl -D /db/data start
> server starting
> -bash-3.2$ FATAL:  database files are incompatible with server
> DETAIL:  The database cluster was initialized with CATALOG_VERSION_NO
> 200902242, but the server was compiled with CATALOG_VERSION_NO 200904091.
> HINT:  It looks like you need to initdb.
>
> I do NOT have a pg_dump of the database because I did not anticipate this
> idiocy.
>
> I am looking for any help on how I can revert back to the previous version
> of PostgreSQL so that I can read the database, do pg_dump, and never EVER
> use yum again to upgrade it.

This is NOT yum's fault.  PostgreSQL does not guarantee data directory
consistency from one rc to another, through beta, etc.  Until 8.4.0
comes out, it could change each and every time.

Got a backup of the original 8.4devel_20090310-1PGDG.rhel5.i386
package?  I'd think reinstalling that would fix your problen.

On Mon, 2009-06-15 at 22:10 -0600, Scott Marlowe wrote:
>
> Got a backup of the original 8.4devel_20090310-1PGDG.rhel5.i386
> package?  I'd think reinstalling that would fix your problen.

Unfortunately I removed binary -devel packages from the repository.
However, SRPM do exist:

http://yum.pgsqlrpms.org/srpms/8.4/redhat/rhel-5-i386/postgresql-8.4devel_20090310-1PGDG.rhel5.src.rpm

You can build them in your platform, and as Scott said, install them,
backup old data and then update to RC1.

Regards,
--
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                   http://www.gunduz.org

OP: Downgrade to your original version, then do a pg_dump.

Thanks all for your help.  I installed from the RPM, found the sources (although it wasn't obvious), rebuilt the identical version that i had before and I am up and running.

On Mon, 2009-06-15 at 23:09 -0700, Konstantin Gredeskoul wrote:
>
> Still, I consider automatic yum updates for Postgres fundamentally
> broken, if they invalidate your database directory.   A 2 minute
> update may cost an hour of downtime for someone who is not expecting
> this, like I wasn't.
>
> I have been installing postgres manually in /usr/local/postgres-
> <version>/ on all other machines, but on this machine I got lazy. Well
> now I know :)

As Scoot wrote: This is **not** yum's fault. It is your fault actually.
Even if you did this upgrade from sources, it would still be broken.
PostgreSQL does not guarantee not to change on-disk data structure
between development snapshots.

Also, please take a look at here:

http://yum.pgsqlrpms.org/rpmchart.php

As you can see, I already marked 8.4 as not suitable for production,
which is already marked as the same by PostgreSQL developers.

I've been pushing RPMS for testers only, so please be careful when you
use any development PG packages next time.

Sincerely,
--
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                   http://www.gunduz.org

On Tue, 2009-06-16 at 10:10 +0300, Devrim GÜNDÜZ wrote:
> As Scoot wrote

Scott, sorry for the typo.
--
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                   http://www.gunduz.org

Konstantin Gredeskoul escribió:

> Still, I consider automatic yum updates for Postgres fundamentally
> broken, if they invalidate your database directory.   A 2 minute update
> may cost an hour of downtime for someone who is not expecting this, like
> I wasn't.

You shouldn't have been using unreleased versions in that case.  If you
want stability, stick to 8.3; and/or wait for 8.4 to be released.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Hi,

> As Scott wrote: This is **not** yum's fault. It is your fault actually.
> Even if you did this upgrade from sources, it would still be broken.
> PostgreSQL does not guarantee not to change on-disk data structure
> between development snapshots.
>

I sort of agree with both sides of the issue - it's not entirely yum's
fault, nor is it PostgreSQL's fault, but neither is it the OP's (except
for the choice of using a different methodology to what normally works!)

As Konstantin mentioned in another e-mail, PostgreSQL is normally
compiled from source and installed into /usr/local/postgres-<version>.
Because yum silently upgraded the _existing_ installation, the old
version was no longer available to dump out the database.

Had the usual procedure been followed (installed from source) the
binaries from the previous version would still have been available in a
different directory to be able to dump/reload the database.

My 2p.

Regards,
Andy

On Tue, 2009-06-16 at 17:46 +0100, Andy Shellam wrote:
>
> As Konstantin mentioned in another e-mail, PostgreSQL is normally
> compiled from source and installed
> into /usr/local/postgres-<version>.

Actually the "default" is /usr/local/pgsql , which means it still could
be overwritten ;)

Anyway, we are talking about an issue which should never ever happen, if
docs were read. I announced -devel RPMs lots of times, and each one
included a notice for dump/reload action.

BTW, I was intending to introduce multiple version installation with
RPMs with 8.4, but it is already pushed to 8.5.

--
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                   http://www.gunduz.org

Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= <devrim@gunduz.org> writes:
> Anyway, we are talking about an issue which should never ever happen, if
> docs were read. I announced -devel RPMs lots of times, and each one
> included a notice for dump/reload action.

I think the real issue here is that the OP was confused about whether
the devel RPMs represented a playpen or something that could be used for
production data.  It's hard to say whether we didn't convey that message
clearly enough, or he failed to pay enough attention, but probably some
blame can be assigned on both sides of that.

            regards, tom lane

2009/6/16 Tom Lane <tgl@sss.pgh.pa.us>:
> Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= <devrim@gunduz.org> writes:
>> Anyway, we are talking about an issue which should never ever happen, if
>> docs were read. I announced -devel RPMs lots of times, and each one
>> included a notice for dump/reload action.
>
> I think the real issue here is that the OP was confused about whether
> the devel RPMs represented a playpen or something that could be used for
> production data.  It's hard to say whether we didn't convey that message
> clearly enough, or he failed to pay enough attention, but probably some
> blame can be assigned on both sides of that.

This whole thread should serve as a lesson in change control /
management to a server.  If you aren't paying attention to the
upgrades / updates of your production servers you're asking for
trouble.

On Mon, 15 Jun 2009 15:42:38 -0700
Konstantin Gredeskoul <kig@dropinmedia.com> wrote:

> Dear PostgreSQL admins and gurus,
>
> I always knew that installing Postgres using Yum/RPM/etc is a very
> bad idea, but decided to try it on one my boxes that runs local
> wiki/Trac.
>
> I added /etc/yum.repos.d/pgdg-84-centos.repo to my yum configuration
> and installed PostgreSQL version 8.4devel_20090310-1PGDG.rhel5.i386
>
> Today I ran "yum update", which quietly upgraded my installation to
> "8.4rc1-1PGDG.rhel5.i386" and now my server does not start.

This is not a rpm/yum/etc's fault. It's your fault...

Also, "yum update" *quietly upgraded* is nonsense. Either you run yum
update -y, or you choose yes after yum offered packages.

So it's your and your fault only... Please don't spread FUD because
rpm/yum work really good.



--
|    Jakov Sosic    |    ICQ: 28410271    |   PGP: 0x965CAE2D   |
=================================================================
| start fighting cancer -> http://www.worldcommunitygrid.org/   |

On Tue, 16 Jun 2009 23:10:40 +0200
Jakov Sosic <jakov.sosic@srce.hr> wrote:

> This is not a rpm/yum/etc's fault. It's your fault...
>
> Also, "yum update" *quietly upgraded* is nonsense. Either you run yum
> update -y, or you choose yes after yum offered packages.
>
> So it's your and your fault only... Please don't spread FUD because
> rpm/yum work really good.

I was maybe a bit harsh in this mail, so I apologize if someone got
insulted.

I will explain the importance of package managers, and maintaining
system via packages versus via source tarballs.

I can see in many of Windows users this habit of searching for
software on google, downloading tarballs, extracting, and running in
some trouble with either configure failing because of missing header or
make failing. People don't understand at first hand that Unices - and
Linux for example is more closer to being Unix than being anything else
- are maintained differently.

Great thing about package managers is that they resolve all your
dependencies, that they provide working binaries, that they install
much faster, that they are searched for in consistent way, etc etc.
Also, user can focus on actually using the software and not searching
for it, compiling it, and loosing much much time on getting it to
work...
Also, software in packages often has some patches that are not included
in "vanilla" tarballs, patches that enhance functionality or stability
of the product. Or just help software to blend in distro enviroment
better.

Now, for example, if you have software A that depends on software B,
and B is from RPM package. User downloads A from web, compiles it, and
installs it. Few months later, there is an upgrade for B, and user
"silently" ;) updates package B. Software A could stop working, because
libs from B are different than the one A is linked to. And believe me -
it's a mess to find the reason A stopped working, especially if you
restart service A few months after B was upgraded... It's a real hell.

Another point, if you install software from source, you're on your own
with security issues (CVE). You should follow bulletins and feeds to
inform you, and on every CVE you should react by recompiling the new
version of A - instead of just leaving that to the distributor. And
compilation on production server rises load, takes precious resources
like RAM, thrashes I/O...
Also, I do not need to mention that compiler on production system is
potential security issue. Minimalism is *the law* for good servers.

And what about binary bundles? Again similar problems. Package managers
offer you possibility to uniquely match every file on your system to
some package. That way you can have cleaner system.

I maintain dozen of Solaris and RedHat servers, and I find Solaris
much superior in every way over RedHat except in one - number of
avaliable packages. And that one thing is driving me crazy because I
have to lose almost 50% of my time on configuring, compiling,
writing manifests and methods for SMF, integrating it into Solaris
enviroment and finally packaging everything up. And one thing I forgot
to mention - I also lose time on looking for CVE's for the software I
packaged.

So I hope that this post will encourage people to use their yum/rpm
more and source/bundles less. Because that is the Unix way.


Once again, I apologize on my harsh tone in first mail, I guess it was
counterproductive, but I hope that people will benefit from this post
that only tipped the iceberg of the whole package manager issue.

Live long and prosper \V/


--
|    Jakov Sosic    |    ICQ: 28410271    |   PGP: 0x965CAE2D   |
=================================================================
| start fighting cancer -> http://www.worldcommunitygrid.org/   |

Jakov Sosic <jakov.sosic@srce.hr> wrote:

> Great thing about package managers is that they resolve all your
> dependencies, that they provide working binaries, that they install
> much faster, that they are searched for in consistent way, etc etc.

I find that when we upgrade to a new major release of PostgreSQL, we
almost always need to have both the old and new major releases
installed at the same time.  That overlap may only need to exist for a
few hours, or it may need to persist for months.  Any installation
solution which doesn't support that pattern can't be used by many.
You might want to consider some way to support that.

-Kevin

I think the problem is mainly with the package manager.

We cannot ask the administrator to be "up to date" of the issues when upgrading. For small companies where the sysadmin is the same that de dbadmin, there should not be problem. But in large IT departments sysadmins have their own problems and if you don't plan a careful communication procedure between database and general administration groups, this kind of problem will appear soon or later...

With the package it could be easy to show a warning, like apt-get and dpkg do. For example, when upgrading in debian from 8.1 to 8.3, dpkg first install 8.3 in another location, and second shows you a warning about the problems you could find during upgrade. I don't know if yum and rpm can show this type of messages...
Perhaps the problem is more with the package system (rpm and yum) that the administrator... Anyway, it's just my opinion and there are opinions for everybody.

And, of course, database admin should be aware of the problems that could surge after upgrading. So, no upgrade should be taken without knowing what is going to be updated. But this is an internal problem between the sysadmin and the dbadmin. :-)


-----Original Message-----
From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-admin@postgresql.org, Jakov Sosic <jakov.sosic@srce.hr>
Subject: Re: [ADMIN] Yum upgrade of PostgreSQL 8.4 from to rc1 rendered dataunusable
Date: Wed, 17 Jun 2009 08:58:11 -0500

Jakov Sosic <jakov.sosic@srce.hr> wrote: 
> Great thing about package managers is that they resolve all your
> dependencies, that they provide working binaries, that they install
> much faster, that they are searched for in consistent way, etc etc.
I find that when we upgrade to a new major release of PostgreSQL, we
almost always need to have both the old and new major releases
installed at the same time.  That overlap may only need to exist for a
few hours, or it may need to persist for months.  Any installation
solution which doesn't support that pattern can't be used by many. 
You might want to consider some way to support that.
-Kevin