Thread: Upgrade from postgresql-9.5alpha1 to postgresql-9.5alpha2 (Debian packages)

It looks like the catalog version has changed between 9.5alpha1 and
9.5alpha2:

 FATAL:  database files are incompatible with server
 DETAIL:  The database cluster was initialized with CATALOG_VERSION_NO 201506282,
          but the server was compiled with CATALOG_VERSION_NO 201507281.
 HINT:  It looks like you need to initdb.

Normally, one would use pg_upgradecluster to do the upgrade, but
pg_upgradecluster assumes that the old and new version are installed in
parallel. Likewise, the low-level tool pg_upgrade needs the old bindir,
if I read the man-page correctly, and of course, apt-get upgrade
overwrites that, since it's just two versions of the same package
(unlike a major upgrade which is a new package).

So, what's the best way to do the upgrade?

* Copy the bindir before the upgrade (or restore from backup) to a safe
  place and do pg_upgrade?
* Initdb a new cluster and restore yesterdays backup?
* Something else?

    hp

--
   _  | Peter J. Holzer    | I want to forget all about both belts and
|_|_) |                    | suspenders; instead, I want to buy pants
| |   | hjp@hjp.at         | that actually fit.
__/   | http://www.hjp.at/ |   -- http://noncombatant.org/

On Tue, Aug 18, 2015 at 6:06 PM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> It looks like the catalog version has changed between 9.5alpha1 and
> 9.5alpha2:
>
>  FATAL:  database files are incompatible with server
>  DETAIL:  The database cluster was initialized with CATALOG_VERSION_NO 201506282,
>           but the server was compiled with CATALOG_VERSION_NO 201507281.
>  HINT:  It looks like you need to initdb.
>
> Normally, one would use pg_upgradecluster to do the upgrade, but
> pg_upgradecluster assumes that the old and new version are installed in
> parallel. Likewise, the low-level tool pg_upgrade needs the old bindir,
> if I read the man-page correctly, and of course, apt-get upgrade
> overwrites that, since it's just two versions of the same package
> (unlike a major upgrade which is a new package).
>
> So, what's the best way to do the upgrade?
>
> * Copy the bindir before the upgrade (or restore from backup) to a safe
>   place and do pg_upgrade?
> * Initdb a new cluster and restore yesterdays backup?
> * Something else?
>

I've not used pg_upgrade at such case, but If you have a enough time
to do upgrading, I will suggest to take backup(pg_dumpall) from
current cluster and then restore it to new cluster.
I think pg_upgrade is basically used at major version upgrading.

Regards,

--
Masahiko Sawada

On 2015-08-18 20:40:10 +0900, Masahiko Sawada wrote:
> On Tue, Aug 18, 2015 at 6:06 PM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> > It looks like the catalog version has changed between 9.5alpha1 and
> > 9.5alpha2:
[...]
> > So, what's the best way to do the upgrade?
> >
> > * Copy the bindir before the upgrade (or restore from backup) to a safe
> >   place and do pg_upgrade?
> > * Initdb a new cluster and restore yesterdays backup?
> > * Something else?
> >
>
> I've not used pg_upgrade at such case, but If you have a enough time
> to do upgrading, I will suggest to take backup(pg_dumpall) from
> current cluster and then restore it to new cluster.

Of course you would have to make a backup before the upgrade to restore
it afterwards. I could of course have forcibly downgraded to alpha1
again and made a new backup, but since this is a test system I just
decided to drop and recreate the cluster and restore yesterday's backup.
(One of my colleagues won't be too pleased about that, I think)

> I think pg_upgrade is basically used at major version upgrading.

This was basically a major version upgrade, the problem was that it
wasn't reflected in the package/directory structure (normally the
postgres debian packages are quite fastidious in separating everything
so that you have both an old and a new installation in the places where
pg_upgradecluster expects them), and that I didn't expect it (the
possibility of catalog version changes from one alpha release to the
next was discussed before the release of alpha1, but I somehow
classified that as "theoretically possible but not likely" - my fault),
and finally that I don't really understand the finer points of
pg_upgrade (I managed to use it in a similar situation some time ago,
but I had to read the source code of pg_upgradecluster (and I think I
even single-stepped through it in the debugger) to figure out the
parameters and unfortunately I didn't take notes).

No big harm done (alpha software on a test system - I expect things to
blow up once in a while), but maybe the person preparing the alpha
releases can figure out how to make the upgrade smoother. At least a
warning in the release announcement would be nice (wouldn't have helped
me as I have to admit that I read that only after I upgraded, but it
would help those who do things in the right order ;-) ).

    hp

--
   _  | Peter J. Holzer    | I want to forget all about both belts and
|_|_) |                    | suspenders; instead, I want to buy pants
| |   | hjp@hjp.at         | that actually fit.
__/   | http://www.hjp.at/ |   -- http://noncombatant.org/