Re: pg_upgrade from 9.3 to 9.4 fails

On 10/5/15, 3:02 PM, "Bruce Momjian" <bruce(at)momjian(dot)us> wrote:

>On Mon, Oct 5, 2015 at 06:23:43PM +0000, Guo, Yun wrote:
>> >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') ));
>>
>> Sorry I misunderstood you instruction. I tried this query in all
>>databases
>> and it executes without any error.
>> I guess that¹s the reason when I have the old server running the check
>> could pass.
>> However, somehow when the old server is hut down it would fail.
>
>Wow, that is really odd. Can you post the contents of
>"pg_upgrade_server.log" after the failure? This file is mentioned in
>the error message. You can email it to me privately if it is large.
>
>My only guess is that when the server is running, you have started it
>with some special options that pg_upgrade doesn't know about. If the
>server is down, pg_upgrade has to start it to check it, and that might
>be causing the problem. It would also be good to know if the failure is
>with the old or new cluster.

pg_upgrade_server.log outputs below message:

-----------------------------------------------------------------
pg_upgrade run on Mon Oct 5 19:17:17 2015
-----------------------------------------------------------------

command: "/usr/pgsql-9.3/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
"/data/pg.old" -o "-p 50432 -b -c listen_addresses='' -c
unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/pgsql'"
start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....<2015-10-05 15:17:17.802 EDT>
user=,db=,host=,processid=18761 LOG: redirecting log output to logging
collector process
<2015-10-05 15:17:17.802 EDT> user=,db=,host=,processid=18761 HINT:
Future log output will appear in directory "pg_log".
done
server started

command: "/usr/pgsql-9.3/bin/pg_ctl" -w -D "/data/pg.old" -o "" -m fast
stop >> "pg_upgrade_server.log" 2>&1
waiting for server to shut down.... done
server stopped

I tried to execute the start command on my own, it didn’t throw error but
the server is not accessible. I’m guessing it’s because of the
listen_addresses=‘’ parameter.

Below are the log entries during that start command:

<2015-10-05 15:42:50.506 EDT> user=,db=,host=,processid=18917 LOG:
database system was shut down at 2015-10-05 15:42:30 EDT
<2015-10-05 15:42:50.510 EDT> user=,db=,host=,processid=18915 LOG:
database system is ready to accept connections

>
>I have never seen a failure like this.
>
>--
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
>+ As you are, so once was I. As I am, so you will be. +
>+ Roman grave inscription +