Re: [GENERAL] pg_upgrade not detecting version properly

Hi all,

I'm trying to test using pg_upgrade to go from 9.1.6 to 9.2.1 on Ubuntu
server 10.04. But when I run pg_upgrade, it tells me I can only run it
on 8.3 or later.

Old:
postgres=# SELECT version();
version

----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
(1 row)

New:
postgres=# SELECT version();
version

----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2.1 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
(1 row)

Yet when I try to run pg_upgrade:

$ /usr/lib/postgresql/9.2/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin/
-d /postgresql/9.1/main -B /usr/lib/postgresql/9.2/bin/ -D
/postgresql/9.2/main -k -c -v
Running in verbose mode
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions
This utility can only upgrade from PostgreSQL version 8.3 and later.
Failure, exiting

Any idea what could be going on here?

Thank you in advance for your help.

- Chris

On Tue, Oct 9, 2012 at 09:50:22PM -0600, Chris Ernst wrote:
> Hi all,
>
> I'm trying to test using pg_upgrade to go from 9.1.6 to 9.2.1 on Ubuntu
> server 10.04. But when I run pg_upgrade, it tells me I can only run it
> on 8.3 or later.
>
> Old:
> postgres=# SELECT version();
> version
>
> ----------------------------------------------------------------------------------------------------------------
> PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
> (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
> (1 row)
>
>
> New:
> postgres=# SELECT version();
> version
>
> ----------------------------------------------------------------------------------------------------------------
> PostgreSQL 9.2.1 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
> (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
> (1 row)
>
>
> Yet when I try to run pg_upgrade:
>
> $ /usr/lib/postgresql/9.2/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin/
> -d /postgresql/9.1/main -B /usr/lib/postgresql/9.2/bin/ -D
> /postgresql/9.2/main -k -c -v
> Running in verbose mode
> Performing Consistency Checks
> -----------------------------
> Checking current, bin, and data directories ok
> Checking cluster versions
> This utility can only upgrade from PostgreSQL version 8.3 and later.
> Failure, exiting
>
> Any idea what could be going on here?
>
> Thank you in advance for your help.

That is cetainly odd. It is using this C code:

if (GET_MAJOR_VERSION(old_cluster.major_version) < 803)
pg_log(PG_FATAL, "This utility can only upgrade from PostgreSQL version 8.3 and later.\n");

which is assigned from this function:

get_major_server_version(ClusterInfo *cluster)
{
FILE *version_fd;
char ver_filename[MAXPGPATH];
int integer_version = 0;
int fractional_version = 0;

snprintf(ver_filename, sizeof(ver_filename), "%s/PG_VERSION",
cluster->pgdata);
if ((version_fd = fopen(ver_filename, "r")) == NULL)
return 0;

if (fscanf(version_fd, "%63s", cluster->major_version_str) == 0 ||
sscanf(cluster->major_version_str, "%d.%d", &integer_version,
&fractional_version) != 2)
pg_log(PG_FATAL, "could not get version from %s\n", cluster->pgdata);

fclose(version_fd);

return (100 * integer_version + fractional_version) * 100;
}

Can you show me what is in the PG_VERSION file in the old cluster? It
should be "9.1".

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On 10/10/2012 09:56 AM, Bruce Momjian wrote:
> Can you show me what is in the PG_VERSION file in the old cluster? It
> should be "9.1".

Hi Bruce,

Thank you for the reply. Indeed it is "9.1":

# cat /postgresql/9.1/main/PG_VERSION
9.1

And just for good measure:

cat /postgresql/9.2/main/PG_VERSION
9.2

And there are no other PostgreSQL versions on this machine.

Hmm... I was just about to send this when something else occurred to me.
I had initially tried to run pg_upgrade as root and it said it couldn't
be run as root. So I've been running it as my own user (which is in the
postgres group). However, everything in /postgresql/9.1/main is owned
by postgres with 700 permissions.

I switched to the postgres user and now pg_upgrade is running. Perhaps
just a more informative error message is in order.

Thank you for the shove in the right direction =)

Cheers!

- Chris

On Wed, Oct 10, 2012 at 10:35:06AM -0600, Chris Ernst wrote:
> On 10/10/2012 09:56 AM, Bruce Momjian wrote:
> > Can you show me what is in the PG_VERSION file in the old cluster? It
> > should be "9.1".
>
> Hi Bruce,
>
> Thank you for the reply. Indeed it is "9.1":
>
> # cat /postgresql/9.1/main/PG_VERSION
> 9.1
>
> And just for good measure:
>
> cat /postgresql/9.2/main/PG_VERSION
> 9.2
>
> And there are no other PostgreSQL versions on this machine.
>
> Hmm... I was just about to send this when something else occurred to me.
> I had initially tried to run pg_upgrade as root and it said it couldn't
> be run as root. So I've been running it as my own user (which is in the
> postgres group). However, everything in /postgresql/9.1/main is owned
> by postgres with 700 permissions.
>
> I switched to the postgres user and now pg_upgrade is running. Perhaps
> just a more informative error message is in order.
>
> Thank you for the shove in the right direction =)

Oops, that code was returning zero if it couldn't open the file. The
attached, applied patch to head and 9.2 issues a proper error message.

Seems this "zero return" has been in the code since the beginning. :-(

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Wed, Oct 10, 2012 at 01:54:49PM -0400, Bruce Momjian wrote:
> On Wed, Oct 10, 2012 at 10:35:06AM -0600, Chris Ernst wrote:
> > On 10/10/2012 09:56 AM, Bruce Momjian wrote:
> > > Can you show me what is in the PG_VERSION file in the old cluster? It
> > > should be "9.1".
> >
> > Hi Bruce,
> >
> > Thank you for the reply. Indeed it is "9.1":
> >
> > # cat /postgresql/9.1/main/PG_VERSION
> > 9.1
> >
> > And just for good measure:
> >
> > cat /postgresql/9.2/main/PG_VERSION
> > 9.2
> >
> > And there are no other PostgreSQL versions on this machine.
> >
> > Hmm... I was just about to send this when something else occurred to me.
> > I had initially tried to run pg_upgrade as root and it said it couldn't
> > be run as root. So I've been running it as my own user (which is in the
> > postgres group). However, everything in /postgresql/9.1/main is owned
> > by postgres with 700 permissions.
> >
> > I switched to the postgres user and now pg_upgrade is running. Perhaps
> > just a more informative error message is in order.
> >
> > Thank you for the shove in the right direction =)
>
> Oops, that code was returning zero if it couldn't open the file. The
> attached, applied patch to head and 9.2 issues a proper error message.
>
> Seems this "zero return" has been in the code since the beginning. :-(

CC to hackers.

Above is the email that caused me to fixed pg_upgrade's handling when it
can't read the data directory; patch attached.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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