Thread: Cannot upgrade from 9.3 to 9.4 using pg_upgrade

On 01/03/2016 12:03 AM, Arthur Pemberton wrote:
> I'm trying to use pg_upgrade to upgrade from 9.3 to 9.4 on CentOS 6 with
> packages from Postgres' yum repo.
>
> I've revered to vanlla ph_hda.conf on 9.3, and ran initdb on 9.4. I
> don't get very far however, I get the following error, and Google
> doesn't seem to help.
>
> -bash-4.1$ /usr/pgsql-9.4/bin/pg_upgrade --jobs 4 \
>  > --old-datadir "/var/lib/pgsql/9.3/data" \
>  > --new-datadir "/var/lib/pgsql/9.4/data" \
>  > --old-bindir "/usr/pgsql-9.3/bin" \
>  > --new-bindir "/usr/pgsql-9.4/bin"
> Performing Consistency Checks
> -----------------------------
> Checking cluster versions                                   ok
> SQL command failed
> CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM
> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n        ON
> c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_index i
> ON c.oid = i.indexrelid WHERE relkind IN ('r', 'm', 'i', 'S') AND
>   i.indisvalid IS DISTINCT FROM false AND  i.indisready IS DISTINCT FROM
> false AND   ((n.nspname !~ '^pg_temp_' AND     n.nspname !~
> '^pg_toast_temp_' AND     n.nspname NOT IN ('pg_catalog',
> 'information_schema',             'binary_upgrade', 'pg_toast') AND
>       c.oid >= 16384)   OR (n.nspname = 'pg_catalog' AND     relname IN
> ('pg_largeobject', 'pg_largeobject_loid_pn_index',
> 'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index') ));
> ERROR:  relation "info_rels" already exists
>
> Failure, exiting


Hmm, the error message is fairly clear, the reason for it, not.

Given that it seems to be the program walking over itself, have you
tried without the --jobs argument?


Also what is the results from pg_config for the 9.3 and 9.4 clusters?

www.postgresql.org/docs/9.4/interactive/app-pgconfig.html

>
>
> Please advise,
> Arthur Pemberton


--
Adrian Klaver
adrian.klaver@aklaver.com

On 01/04/2016 12:53 PM, Arthur Pemberton wrote:
> Yes, I have tried it without --jobs, just to simplify.
>

That would have been too easy. Not sure what is going on.

So are there any other errors, warnings, etc between?:

Checking cluster versions                                   ok

and

SQL command failed


>
> On Sun, Jan 3, 2016 at 4:27 PM, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 01/03/2016 12:03 AM, Arthur Pemberton wrote:
>
>         I'm trying to use pg_upgrade to upgrade from 9.3 to 9.4 on
>         CentOS 6 with
>         packages from Postgres' yum repo.
>
>         I've revered to vanlla ph_hda.conf on 9.3, and ran initdb on 9.4. I
>         don't get very far however, I get the following error, and Google
>         doesn't seem to help.
>
>         -bash-4.1$ /usr/pgsql-9.4/bin/pg_upgrade --jobs 4 \
>           > --old-datadir "/var/lib/pgsql/9.3/data" \
>           > --new-datadir "/var/lib/pgsql/9.4/data" \
>           > --old-bindir "/usr/pgsql-9.3/bin" \
>           > --new-bindir "/usr/pgsql-9.4/bin"
>         Performing Consistency Checks
>         -----------------------------
>         Checking cluster versions                                   ok
>         SQL command failed
>         CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM
>         pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n        ON
>         c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_index i
>         ON c.oid = i.indexrelid WHERE relkind IN ('r', 'm', 'i', 'S') AND
>            i.indisvalid IS DISTINCT FROM false AND  i.indisready IS
>         DISTINCT FROM
>         false AND   ((n.nspname !~ '^pg_temp_' AND     n.nspname !~
>         '^pg_toast_temp_' AND     n.nspname NOT IN ('pg_catalog',
>         'information_schema',             'binary_upgrade', 'pg_toast') AND
>                c.oid >= 16384)   OR (n.nspname = 'pg_catalog' AND
>           relname IN
>         ('pg_largeobject', 'pg_largeobject_loid_pn_index',
>         'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index') ));
>         ERROR:  relation "info_rels" already exists
>
>         Failure, exiting
>
>
>
>     Hmm, the error message is fairly clear, the reason for it, not.
>
>     Given that it seems to be the program walking over itself, have you
>     tried without the --jobs argument?
>
>
>     Also what is the results from pg_config for the 9.3 and 9.4 clusters?
>
>     www.postgresql.org/docs/9.4/interactive/app-pgconfig.html
>     <http://www.postgresql.org/docs/9.4/interactive/app-pgconfig.html>
>
>
>
>         Please advise,
>         Arthur Pemberton
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 01/04/2016 02:37 PM, Arthur Pemberton wrote:
> Nothing useful. I'm just going to give up and use pg_dump to a new
> machine. Hopefully that allows me to bypass this issue.

Did that help?

To help someone else troubleshoot this, did the error you get occur
after the pg_upgrade line:

Restoring global objects in the new cluster

or

Restoring database schemas in the new cluster

or even better yet could you post the section of the log above the error?

>
> On Mon, Jan 4, 2016 at 4:58 PM, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:


--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Jan 5, 2016 at 6:58 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 01/04/2016 12:53 PM, Arthur Pemberton wrote:
>>
>> Yes, I have tried it without --jobs, just to simplify.
>>
>
> That would have been too easy. Not sure what is going on.
>
> So are there any other errors, warnings, etc between?:
>
> Checking cluster versions                                   ok
>
> and
>
> SQL command failed

Yeah, that's basically the same temporary relation created through the
same connection. That's likely an effect of --jobs though I cannot see
directly how get_db_and_rel_infos would get called twice for the same
connection.
--
Michael

On 1/4/16 7:40 PM, Adrian Klaver wrote:
> or even better yet could you post the section of the log above the error?

The server log itself might be useful, especially if full query logging
was turned on. Dunno how easy/possible that is with pg_upgrade.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com