Re: is possible to upgrade from 9.2 to 9.4 with pg_upgrade

2015-05-06 19:26 GMT+02:00 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:

> On Wed, May 6, 2015 at 6:16 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>>
>>
>> 2015-05-06 15:15 GMT+02:00 Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>:
>>
>>> Pavel Stehule wrote:
>>> > Hi
>>> >
>>> > I am working on preparation the migration from 9.2 to 9.4
>>> >
>>> > pg_upgrade fails
>>> >
>>> > pg_upgrade -b /usr/lib64/pgsql/postgresql-9.2/bin -B /usr/bin/ -d
>>> > /mnt/ebs/pgsql/data -D /mnt/ebs/pgsql/data94 -k'
>>> > Performing Consistency Checks
>>> > -----------------------------
>>> > Checking cluster versions ok
>>> > The old cluster lacks some required control information:
>>> > latest checkpoint oldest MultiXactId
>>> >
>>> > ?
>>>
>>> Uh, this is certainly supposed to work. Maybe pg_controldata or
>>> pg_resetxlog changed output format and pg_upgrade doesn't know how to
>>> read it.
>>>
>>
>> It is tested on fresh 9.2.10 to 9.4.1
>>
>
> I've done that migration many times. Can you give the pg_config output
> for both installations, and the pg_controldata output for the 9.2.10?
>

[root(at)ps-test5:/etc/puppet/modules/postgresql/files] pg_config
BINDIR = /usr/bin
DOCDIR = /usr/share/doc/pgsql
HTMLDIR = /usr/share/doc/pgsql
INCLUDEDIR = /usr/include
PKGINCLUDEDIR = /usr/include/pgsql
INCLUDEDIR-SERVER = /usr/include/pgsql/server
LIBDIR = /usr/lib64
PKGLIBDIR = /usr/lib64/pgsql
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/man
SHAREDIR = /usr/share/pgsql
SYSCONFDIR = /etc/sysconfig/pgsql
PGXS = /usr/lib64/pgsql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--build=x86_64-gdc-linux-gnu' '--host=x86_64-gdc-linux-gnu'
'--target=x86_64-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr'
'--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin'
'--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include'
'--libdir=/usr/lib64' '--libexecdir=/usr/libexec' '--localstatedir=/var'
'--sharedstatedir=/var/lib' '--mandir=/usr/share/man'
'--infodir=/usr/share/info' '--disable-rpath' '--with-perl' '--with-python'
'--with-ldap' '--with-openssl' '--with-pam' '--with-krb5' '--with-gssapi'
'--with-ossp-uuid' '--with-libxml' '--with-libxslt' '--enable-nls'
'--enable-dtrace' '--enable-thread-safety'
'--with-system-tzdata=/usr/share/zoneinfo'
'--sysconfdir=/etc/sysconfig/pgsql' '--datadir=/usr/share/pgsql'
'build_alias=x86_64-gdc-linux-gnu' 'host_alias=x86_64-gdc-linux-gnu'
'target_alias=x86_64-redhat-linux-gnu' 'CFLAGS=-O2 -g -pipe -Wall
-Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector
--param=ssp-buffer-size=4 -m64 -mtune=generic -DLINUX_OOM_ADJ=0'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions
-fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic
-DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv
CFLAGS_SL = -fpic
LDFLAGS = -Wl,--as-needed
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz
-lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 9.2.10

[root(at)ps-test5:/etc/puppet/modules/postgresql/files] pg_controldata
/mnt/ebs/pgsql/data
pg_control version number: 922
Catalog version number: 201302181
Database system identifier: 6145713518966079483
Database cluster state: in production
pg_control last modified: Thu 07 May 2015 11:44:38 AM CEST
Latest checkpoint location: 0/B88E280
Prior checkpoint location: 0/B88E220
Latest checkpoint's REDO location: 0/B88E280
Latest checkpoint's TimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/2269
Latest checkpoint's NextOID: 17175
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1798
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Time of latest checkpoint: Thu 07 May 2015 11:41:59 AM CEST
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: 200
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 300
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: 65
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

15/05/07 11:46:42 rack-na/rat (rest-pg-minimal)

[root(at)ps-test5:/etc/puppet/modules/postgresql/files] pg_config
BINDIR = /usr/bin
DOCDIR = /usr/share/doc/pgsql
HTMLDIR = /usr/share/doc/pgsql
INCLUDEDIR = /usr/include
PKGINCLUDEDIR = /usr/include/pgsql
INCLUDEDIR-SERVER = /usr/include/pgsql/server
LIBDIR = /usr/lib64
PKGLIBDIR = /usr/lib64/pgsql
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/man
SHAREDIR = /usr/share/pgsql
SYSCONFDIR = /etc/sysconfig/pgsql
PGXS = /usr/lib64/pgsql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--build=x86_64-gdc-linux-gnu' '--host=x86_64-gdc-linux-gnu'
'--target=x86_64-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr'
'--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin'
'--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include'
'--libdir=/usr/lib64' '--libexecdir=/usr/libexec' '--localstatedir=/var'
'--sharedstatedir=/var/lib' '--mandir=/usr/share/man'
'--infodir=/usr/share/info' '--disable-rpath' '--enable-debug'
'--enable-cassert' '--with-perl' '--with-python' '--with-ldap'
'--with-openssl' '--with-pam' '--with-gssapi' '--with-ossp-uuid'
'--with-libxml' '--with-libxslt' '--enable-nls' '--enable-dtrace'
'--enable-thread-safety' '--with-system-tzdata=/usr/share/zoneinfo'
'--sysconfdir=/etc/sysconfig/pgsql' '--datadir=/usr/share/pgsql'
'--with-extra-version=, with GoodData patches,'
'build_alias=x86_64-gdc-linux-gnu' 'host_alias=x86_64-gdc-linux-gnu'
'target_alias=x86_64-redhat-linux-gnu' 'CFLAGS=-O2 -g -pipe -Wall
-Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector
--param=ssp-buffer-size=4 -m64 -mtune=generic -DLINUX_OOM_ADJ=0'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -g -pipe -Wall
-Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector
--param=ssp-buffer-size=4 -m64 -mtune=generic -DLINUX_OOM_ADJ=0
CFLAGS_SL = -fpic
LDFLAGS = -L../../../src/common -Wl,--as-needed
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5
-lz -lreadline -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 9.4.1, with GoodData patches,

[postgres(at)ps-test5:~] /usr/bin/pg_controldata /mnt/ebs/pgsql/data
pg_control version number: 942
Catalog version number: 201409291
Database system identifier: 6146064324491818031
Database cluster state: shut down
pg_control last modified: Thu 07 May 2015 11:54:10 AM CEST
Latest checkpoint location: 0/171A1F0
Prior checkpoint location: 0/171A188
Latest checkpoint's REDO location: 0/171A1F0
Latest checkpoint's REDO WAL file: 000000010000000000000001
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 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
Time of latest checkpoint: Thu 07 May 2015 11:54:10 AM CEST
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
Current wal_level setting: minimal
Current wal_log_hints setting: off
Current max_connections setting: 200
Current max_worker_processes setting: 8
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 300
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: 65
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

Verbose output

Running in verbose mode
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
pg_control values:

First log file ID after reset: 0
First log file segment after reset: 22
pg_control version number: 922
Catalog version number: 201302181
Database system identifier: 6145713518966079483
Latest checkpoint's TimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/2290
Latest checkpoint's NextOID: 17247
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1798
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 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: 65
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
The old cluster lacks some required control information:
latest checkpoint oldest MultiXactId

Cannot continue without required control information, terminating
Failure, exiting

Regards

Pavel

>
> Thanks,
>
> Jeff
>