Thread: pg_upgrade problem from 8.4 to 9.2 problems on OSX

On Wed, Jan 16, 2013 at 03:06:41PM -0800, Charles Porter wrote:
> I am trying to migrate from 8.4 to 9.2 on OSX 10.8 (Mountain Lion)
>
> Both 8.4 and 9.2 have been installed with the installers for OSX from
> EnterpriseDB
>
> Both have been stopped.
>
> As user postgres, I run the pg_upgrade command and get the error
> 'old and new pg_controldata alignments are invalid or do not match'
>
> Is there anything I can do to correct this?
>
> The 8.4 installation contains a lot of databases, so I would rather not do
> pg_dump/pg_restore on every database unless necessary.
>
> This is the actual command and output:
>
> [/Volumes/DISK2/Postgres/9.2/data]$  /Library/PostgreSQL/9.2/bin/pg_upgrade  \
>  -b /Library/PostgreSQL8.4/bin \
>  -B /Library/PostgreSQL/9.2/bin \
>  -d /Library/PostgreSQL8.4/data \
>  -D /Volumes/DISK2/Postgres/9.2/data
>
>
> Performing Consistency Checks
> -----------------------------
> Checking current, bin, and data directories                 ok
> Checking cluster versions                                   ok
>
> old and new pg_controldata alignments are invalid or do not match
> Failure, exiting

OK, sure.  Run this on the old and new clusters:

    $ pg_controldata /u/pg/data.old
    $ pg_controldata /u/pg/data.new

You will see a mismatch here:

    Maximum data alignment:               8
                                          ^

My quick guess is that one is 32-bit mode and the other is 64-bit mode.

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

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

On Wed, Jan 16, 2013 at 06:47:12PM -0800, Charles Porter wrote:
> Bruce -
>
> Yep. That's it. Thank you
>
> Maximum data alignment:               4
> Maximum data alignment:               8
>
> There are several differences, including TOAST chunk 2000 vs 1996.
>
> I take it that this means that I cannot us pg_upgrade.

That is correct.  I have updated the pg_upgrade error to suggest 32-bit
vs 64-bit binaries.

---------------------------------------------------------------------------


>
> Thanks,
>
> Charles
>
> [511][/Library/PostgreSQL8.4/bin]$ ./pg_controldata /Library/PostgreSQL8.4/data
> pg_control version number:            843
> Catalog version number:               200904091
> Database system identifier:           5381387821761095614
> Database cluster state:               shut down
> pg_control last modified:             Wed Jan 16 13:23:37 2013
> Latest checkpoint location:           3A/D507B5A0
> Prior checkpoint location:            3A/D507B55C
> Latest checkpoint's REDO location:    3A/D507B5A0
> Latest checkpoint's TimeLineID:       1
> Latest checkpoint's NextXID:          0/22082243
> Latest checkpoint's NextOID:          75427786
> Latest checkpoint's NextMultiXactId:  1
> Latest checkpoint's NextMultiOffset:  0
> Time of latest checkpoint:            Wed Jan 16 13:23:37 2013
> Minimum recovery ending location:     0/0
>
> Maximum data alignment:               4
>
> 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:        2000
> Date/time type storage:               64-bit integers
> Float4 argument passing:              by value
> Float8 argument passing:              by reference
>
> =================================================
>
> [512][/Library/PostgreSQL8.4/bin]$ cd ../../PostgreSQL/9.2/bin/
> [513][/Library/PostgreSQL/9.2/bin]$ ./pg_controldata /Volumes/DISK2/Postgres/
> 9.2/data
> pg_control version number:            922
> Catalog version number:               201204301
> Database system identifier:           5834152691411927039
> Database cluster state:               shut down
> pg_control last modified:             Wed Jan 16 13:23:58 2013
> Latest checkpoint location:           0/17A5580
> Prior checkpoint location:            0/17A5520
> Latest checkpoint's REDO location:    0/17A5580
> Latest checkpoint's TimeLineID:       1
> Latest checkpoint's full_page_writes: on
> Latest checkpoint's NextXID:          0/1000
> Latest checkpoint's NextOID:          16393
> Latest checkpoint's NextMultiXactId:  1
> Latest checkpoint's NextMultiOffset:  0
> Latest checkpoint's oldestXID:        989
> Latest checkpoint's oldestXID's DB:   1
> Latest checkpoint's oldestActiveXID:  0
> Time of latest checkpoint:            Wed Jan 16 13:23:58 2013
> Minimum recovery ending location:     0/0
> Backup start location:                0/0
> Backup end location:                  0/0
> End-of-backup record required:        no
> Current wal_level setting:            minimal
> Current max_connections setting:      100
> Current max_prepared_xacts setting:   0
> Current max_locks_per_xact setting:   64
>
> 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
>
>
>

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

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

Charles Porter <charles.porter@gmail.com> writes:
> Yep. That's it. Thank you
> Maximum data alignment:               4
> Maximum data alignment:               8
> There are several differences, including TOAST chunk 2000 vs 1996.

> I take it that this means that I cannot us pg_upgrade.

Well, not if you want the new server to run 64-bit.  If you install
a 32-bit version of 9.2 it should work.  Note that in a lot of
workloads the server's word width doesn't make that much difference.

            regards, tom lane