Re: is possible to upgrade from 9.2 to 9.4 with pg_upgrade - Mailing list pgsql-hackers
From | Pavel Stehule |
---|---|
Subject | Re: is possible to upgrade from 9.2 to 9.4 with pg_upgrade |
Date | |
Msg-id | CAFj8pRCewgK3ayRQoW22fr94h9Fh9_qQEOnEna5dmqA3eu7GQg@mail.gmail.com Whole thread Raw |
In response to | Re: is possible to upgrade from 9.2 to 9.4 with pg_upgrade (Jeff Janes <jeff.janes@gmail.com>) |
Responses |
Re: is possible to upgrade from 9.2 to 9.4 with pg_upgrade
|
List | pgsql-hackers |
2015-05-06 19:26 GMT+02:00 Jeff Janes <jeff.janes@gmail.com>:
[root@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@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@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@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
On Wed, May 6, 2015 at 6:16 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:2015-05-06 15:15 GMT+02:00 Alvaro Herrera <alvherre@2ndquadrant.com>:Uh, this is certainly supposed to work. Maybe pg_controldata orPavel 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
>
> ?
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.1I'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@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@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@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@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
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
pgsql-hackers by date: