Postgres Pro
Downloads
Home   >   mailing lists

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

is possible to upgrade from 9.2 to 9.4 with pg_upgrade

From
Pavel Stehule
Date:
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

?

Regards

Pavel


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

From
Alvaro Herrera
Date:
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.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

From
Pavel Stehule
Date:


2015-05-06 15:15 GMT+02:00 Alvaro Herrera <alvherre@2ndquadrant.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

Regards

Pavel
 

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

From
Jeff Janes
Date:
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>:
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?

Thanks,

Jeff

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

From
Jeff Janes
Date:
On Wed, May 6, 2015 at 10:26 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
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>:
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?

Also, what is the full path to pg_upgrade?

Cheers,

Jeff

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

From
Pavel Stehule
Date:


2015-05-06 19:40 GMT+02:00 Jeff Janes <jeff.janes@gmail.com>:
On Wed, May 6, 2015 at 10:26 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
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>:
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?

Also, what is the full path to pg_upgrade?

I am calling pg_upgrade via command

 su postgres -c "cd /mnt/ebs/pgsql/upgrade_data; pg_upgrade -b /usr/lib64/pgsql/postgresql-9.2/bin -B /usr/bin/ -d /mnt/ebs/pgsql/data -D /mnt/ebs/pgsql/data94 -k"

It is based on customized RHEL packages


Cheers,

Jeff

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

From
Pavel Stehule
Date:


2015-05-06 19:26 GMT+02:00 Jeff Janes <jeff.janes@gmail.com>:
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>:
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@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

Regards

Pavel
 

Thanks,

Jeff

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

From
Alvaro Herrera
Date:
The problem is here:

> [root@ps-test5:/etc/puppet/modules/postgresql/files] pg_controldata
> /mnt/ebs/pgsql/data
> pg_control version number:            922
> Catalog version number:               201302181

The catversion for 9.2 is 201204301; you have modified it with your
patches in a way that breaks this check in pg_upgrade:
   /*    * If the old server is before the MULTIXACT_FORMATCHANGE_CAT_VER change    * (see pg_upgrade.h) and the new
serveris after, then we don't copy    * pg_multixact files, but we need to reset pg_control so that the new    * server
doesn'tattempt to read multis older than the cutoff value.    */   if (old_cluster.controldata.cat_ver >=
MULTIXACT_FORMATCHANGE_CAT_VER&&       new_cluster.controldata.cat_ver >= MULTIXACT_FORMATCHANGE_CAT_VER)
 

pg_upgrade behaves differently if the source catversion is earlier than
this value:

/** pg_multixact format changed in 9.3 commit 0ac5ad5134f2769ccbaefec73844f85,* ("Improve concurrency of foreign key
locking")which also updated catalog* version to this value.  pg_upgrade behavior depends on whether old and new* server
versionsare both newer than this, or only the new one is.*/
 
#define MULTIXACT_FORMATCHANGE_CAT_VER 201301231

because it expects to see the "oldest multixact id" in pg_controldata,
but 9.2 did not have that.

You either need to change your database's catversion, or patch your
pg_upgrade so that it knows to consider your catversion part of 9.2
instead of 9.3.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

From
Pavel Stehule
Date:


2015-05-07 13:43 GMT+02:00 Alvaro Herrera <alvherre@2ndquadrant.com>:
The problem is here:

> [root@ps-test5:/etc/puppet/modules/postgresql/files] pg_controldata
> /mnt/ebs/pgsql/data
> pg_control version number:            922
> Catalog version number:               201302181

The catversion for 9.2 is 201204301; you have modified it with your
patches in a way that breaks this check in pg_upgrade:

yes, It was a reason.

Thank you very much for help

Regards

Pavel

 

    /*
     * If the old server is before the MULTIXACT_FORMATCHANGE_CAT_VER change
     * (see pg_upgrade.h) and the new server is after, then we don't copy
     * pg_multixact files, but we need to reset pg_control so that the new
     * server doesn't attempt to read multis older than the cutoff value.
     */
    if (old_cluster.controldata.cat_ver >= MULTIXACT_FORMATCHANGE_CAT_VER &&
        new_cluster.controldata.cat_ver >= MULTIXACT_FORMATCHANGE_CAT_VER)

pg_upgrade behaves differently if the source catversion is earlier than
this value:

/*
 * pg_multixact format changed in 9.3 commit 0ac5ad5134f2769ccbaefec73844f85,
 * ("Improve concurrency of foreign key locking") which also updated catalog
 * version to this value.  pg_upgrade behavior depends on whether old and new
 * server versions are both newer than this, or only the new one is.
 */
#define MULTIXACT_FORMATCHANGE_CAT_VER 201301231

because it expects to see the "oldest multixact id" in pg_controldata,
but 9.2 did not have that.

You either need to change your database's catversion, or patch your
pg_upgrade so that it knows to consider your catversion part of 9.2
instead of 9.3.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services