Thread: Back to my pg_upgrade stoppage problem

Hi all,

I'm back to trying to figure out the pg_upgrade stoppage problem on my V9.4.0 upgrade from V9.3.3. As stated earlier,
myenvironment is CentOS 6.5 kernel and the server is  
virtualized, but has been running 9.3.3 for some time now. I ran pg_upgrade on this system as follows, I apologize for
thelength of the message but I putting the entire screen  
capture into this. You can see at the end that it got hung up trying to create a temporary table, but the server has
beenon this step for nearly an hour now. It had successfully  
performed several queries prior to this, and those were successful.

  [ postgres@csgha2] /usr/pgsql-9.4/bin/pg_upgrade -b /usr/pgsql-9.3/bin -B /usr/pgsql-9.4/bin -d
/var/lib/pgsql/9.3/data-D /var/lib/pgsql/9.4/data -v 
Running in verbose mode
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
pg_control values:

First log segment after reset:        000000080000004200000034
pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           5999528689196325886
Latest checkpoint's TimeLineID:       8
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/3223
Latest checkpoint's NextOID:          42583
Latest checkpoint's NextMultiXactId:  2
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1798
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  3222
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 0
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Current pg_control values:

pg_control version number:            942
Catalog version number:               201409291
Database system identifier:           6104926301746638117
Latest checkpoint's TimeLineID:       1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/1810
Latest checkpoint's NextOID:          13004
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1800
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0


Values to be changed:

First log segment after reset:        000000010000000000000002
"/usr/pgsql-9.3/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/9.3/data" -o "-p 50432 -b  -c
listen_addresses=''-c unix_socket_permissions=0700 -c  
unix_socket_directories='/var/lib/pgsql/9.4/data'" start >> "pg_upgrade_server.log" 2>&1
executing: SELECT datcollate, datctype FROM pg_catalog.pg_database WHERE    datname = 'template0'
executing: SELECT pg_catalog.pg_encoding_to_char(encoding) FROM pg_catalog.pg_database WHERE    datname = 'template0'
executing: SELECT c.relname, c.relfilenode FROM pg_catalog.pg_class c,          pg_catalog.pg_namespace n WHERE
c.relnamespace= n.oid AND           n.nspname = 'pg_catalog'  
AND            c.relname = 'pg_database' ORDER BY c.relname
executing: SELECT d.oid, d.datname, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM pg_catalog.pg_database
d LEFT OUTER JOIN pg_catalog.pg_tablespace t  ON  
d.dattablespace = t.oid WHERE d.datallowconn = true ORDER BY 2
executing: CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM pg_catalog.pg_class c JOIN
pg_catalog.pg_namespacen 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
DISTINCTFROM 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') )); 

Thanks in advance,
Jay

On Fri, Jan 16, 2015 at 10:56:19AM -0500, John Scalia wrote:
> Hi all,
>
> I'm back to trying to figure out the pg_upgrade stoppage problem on
> my V9.4.0 upgrade from V9.3.3. As stated earlier, my environment is
> CentOS 6.5 kernel and the server is virtualized, but has been
> running 9.3.3 for some time now. I ran pg_upgrade on this system as
> follows, I apologize for the length of the message but I putting the
> entire screen capture into this. You can see at the end that it got
> hung up trying to create a temporary table, but the server has been
> on this step for nearly an hour now. It had successfully performed
> several queries prior to this, and those were successful.

I suggest you upgrade to V9.3.5 at a minimum.  Second, try the why on
its own to see if it works.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

I did manage to get everything working properly yesterday. What I did not first realize is that while pg_upgrade
requiresboth the original server and the new one to be off, if  
your original server is the primary in a streaming replication cluster, then the standby(s) must still be running when
youstart pg_upgrade. The temporary table creation appeared  
hung as the primary was waiting on the standby to commit the change, but it wasn't running. After resolving that, I hit
anothersnag where pgpool had a function installed that I  
couldn't easily find to remove it. pg_upgrade would fail stating it couldn't find the function's *.so to apply to the
newinstance. I ended up pulling a pg_dumpall from the  
original instance, and manually looked in that to find anything with the name pgpool in it. I then removed those from
theDb, and pg_upgrade completed successfully. 

Thanks to everyone for their assistance. I just needed to think a little harder on what was happening.

On 1/21/2015 10:46 PM, Bruce Momjian wrote:
> On Fri, Jan 16, 2015 at 10:56:19AM -0500, John Scalia wrote:
>> Hi all,
>>
>> I'm back to trying to figure out the pg_upgrade stoppage problem on
>> my V9.4.0 upgrade from V9.3.3. As stated earlier, my environment is
>> CentOS 6.5 kernel and the server is virtualized, but has been
>> running 9.3.3 for some time now. I ran pg_upgrade on this system as
>> follows, I apologize for the length of the message but I putting the
>> entire screen capture into this. You can see at the end that it got
>> hung up trying to create a temporary table, but the server has been
>> on this step for nearly an hour now. It had successfully performed
>> several queries prior to this, and those were successful.
> I suggest you upgrade to V9.3.5 at a minimum.  Second, try the why on
> its own to see if it works.
>