Cannot upgrade from 9.3 to 9.4 using pg_upgrade

Started by Arthur Pembertonover 10 years ago9 messagesgeneral

pemboa@gmail.com

over 10 years ago

I'm trying to use pg_upgrade to upgrade from 9.3 to 9.4 on CentOS 6 with
packages from Postgres' yum repo.

I've revered to vanlla ph_hda.conf on 9.3, and ran initdb on 9.4. I don't
get very far however, I get the following error, and Google doesn't seem to
help.

-bash-4.1$ /usr/pgsql-9.4/bin/pg_upgrade --jobs 4 \

--old-datadir "/var/lib/pgsql/9.3/data" \
--new-datadir "/var/lib/pgsql/9.4/data" \
--old-bindir "/usr/pgsql-9.3/bin" \
--new-bindir "/usr/pgsql-9.4/bin"

Performing Consistency Checks
-----------------------------
Checking cluster versions ok
SQL command failed
CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM
pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n 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 DISTINCT FROM 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') ));
ERROR: relation "info_rels" already exists

Failure, exiting

Please advise,
Arthur Pemberton

Adrian Klaver

adrian.klaver@aklaver.com

over 10 years ago

In reply to: Arthur Pemberton (#1)

Re: Cannot upgrade from 9.3 to 9.4 using pg_upgrade

On 01/03/2016 12:03 AM, Arthur Pemberton wrote:

I'm trying to use pg_upgrade to upgrade from 9.3 to 9.4 on CentOS 6 with
packages from Postgres' yum repo.

I've revered to vanlla ph_hda.conf on 9.3, and ran initdb on 9.4. I
don't get very far however, I get the following error, and Google
doesn't seem to help.

-bash-4.1$ /usr/pgsql-9.4/bin/pg_upgrade --jobs 4 \

--old-datadir "/var/lib/pgsql/9.3/data" \
--new-datadir "/var/lib/pgsql/9.4/data" \
--old-bindir "/usr/pgsql-9.3/bin" \
--new-bindir "/usr/pgsql-9.4/bin"

Performing Consistency Checks
-----------------------------
Checking cluster versions ok
SQL command failed
CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM
pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n 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 DISTINCT FROM 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') ));
ERROR: relation "info_rels" already exists

Failure, exiting

Hmm, the error message is fairly clear, the reason for it, not.

Given that it seems to be the program walking over itself, have you
tried without the --jobs argument?

Also what is the results from pg_config for the 9.3 and 9.4 clusters?

www.postgresql.org/docs/9.4/interactive/app-pgconfig.html

Please advise,
Arthur Pemberton

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Arthur Pemberton

pemboa@gmail.com

over 10 years ago

In reply to: Adrian Klaver (#2)

Re: Cannot upgrade from 9.3 to 9.4 using pg_upgrade

Yes, I have tried it without --jobs, just to simplify.

# /usr/pgsql-9.3/bin/pg_config
BINDIR = /usr/pgsql-9.3/bin
DOCDIR = /usr/share/doc/pgsql
HTMLDIR = /usr/share/doc/pgsql
INCLUDEDIR = /usr/pgsql-9.3/include
PKGINCLUDEDIR = /usr/pgsql-9.3/include
INCLUDEDIR-SERVER = /usr/pgsql-9.3/include/server
LIBDIR = /usr/pgsql-9.3/lib
PKGLIBDIR = /usr/pgsql-9.3/lib
LOCALEDIR = /usr/pgsql-9.3/share/locale
MANDIR = /usr/pgsql-9.3/share/man
SHAREDIR = /usr/pgsql-9.3/share
SYSCONFDIR = /etc/sysconfig/pgsql
PGXS = /usr/pgsql-9.3/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--disable-rpath' '--prefix=/usr/pgsql-9.3'
'--includedir=/usr/pgsql-9.3/include' '--mandir=/usr/pgsql-9.3/share/man'
'--datadir=/usr/pgsql-9.3/share' '--with-perl' '--with-python' '--with-tcl'
'--with-tclconfig=/usr/lib64' '--with-openssl' '--with-pam' '--with-krb5'
'--with-gssapi' '--with-includes=/usr/include'
'--with-libraries=/usr/lib64' '--enable-nls' '--with-ossp-uuid'
'--with-libxml' '--with-libxslt' '--with-ldap'
'--with-system-tzdata=/usr/share/zoneinfo'
'--sysconfdir=/etc/sysconfig/pgsql' '--docdir=/usr/share/doc' 'CFLAGS=-O2
-g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector
--param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et' 'CPPFLAGS=
-I/usr/include/et'
CC = gcc
CPPFLAGS = -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2
-I/usr/include
CFLAGS = -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions
-fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic
-I/usr/include/et -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv
CFLAGS_SL = -fpic
LDFLAGS = -L../../../src/common -L/usr/lib64 -Wl,--as-needed
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgport -lpgcommon -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5
-lz -lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 9.3.10

# /usr/pgsql-9.4/bin/pg_config
BINDIR = /usr/pgsql-9.4/bin
DOCDIR = /usr/share/doc/pgsql
HTMLDIR = /usr/share/doc/pgsql
INCLUDEDIR = /usr/pgsql-9.4/include
PKGINCLUDEDIR = /usr/pgsql-9.4/include
INCLUDEDIR-SERVER = /usr/pgsql-9.4/include/server
LIBDIR = /usr/pgsql-9.4/lib
PKGLIBDIR = /usr/pgsql-9.4/lib
LOCALEDIR = /usr/pgsql-9.4/share/locale
MANDIR = /usr/pgsql-9.4/share/man
SHAREDIR = /usr/pgsql-9.4/share
SYSCONFDIR = /etc/sysconfig/pgsql
PGXS = /usr/pgsql-9.4/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--disable-rpath' '--prefix=/usr/pgsql-9.4'
'--includedir=/usr/pgsql-9.4/include' '--mandir=/usr/pgsql-9.4/share/man'
'--datadir=/usr/pgsql-9.4/share' '--with-perl' '--with-python' '--with-tcl'
'--with-tclconfig=/usr/lib64' '--with-openssl' '--with-pam' '--with-gssapi'
'--with-includes=/usr/include' '--with-libraries=/usr/lib64' '--enable-nls'
'--with-uuid=e2fs' '--with-libxml' '--with-libxslt' '--with-ldap'
'--with-system-tzdata=/usr/share/zoneinfo'
'--sysconfdir=/etc/sysconfig/pgsql' '--docdir=/usr/share/doc' 'CFLAGS=-O2
-g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector
--param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et' 'CPPFLAGS=
-I/usr/include/et'
CC = gcc
CPPFLAGS = -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2
-I/usr/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv -O2 -g -pipe -Wall
-Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector
--param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et
CFLAGS_SL = -fpic
LDFLAGS = -L../../../src/common -L/usr/lib64 -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.5

On Sun, Jan 3, 2016 at 4:27 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Show quoted text

On 01/03/2016 12:03 AM, Arthur Pemberton wrote:

I'm trying to use pg_upgrade to upgrade from 9.3 to 9.4 on CentOS 6 with
packages from Postgres' yum repo.

I've revered to vanlla ph_hda.conf on 9.3, and ran initdb on 9.4. I
don't get very far however, I get the following error, and Google
doesn't seem to help.

-bash-4.1$ /usr/pgsql-9.4/bin/pg_upgrade --jobs 4 \

--old-datadir "/var/lib/pgsql/9.3/data" \
--new-datadir "/var/lib/pgsql/9.4/data" \
--old-bindir "/usr/pgsql-9.3/bin" \
--new-bindir "/usr/pgsql-9.4/bin"

Performing Consistency Checks
-----------------------------
Checking cluster versions ok
SQL command failed
CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM
pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n 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 DISTINCT FROM 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') ));
ERROR: relation "info_rels" already exists

Failure, exiting

Hmm, the error message is fairly clear, the reason for it, not.

Given that it seems to be the program walking over itself, have you tried
without the --jobs argument?

Also what is the results from pg_config for the 9.3 and 9.4 clusters?

www.postgresql.org/docs/9.4/interactive/app-pgconfig.html

Please advise,
Arthur Pemberton

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver

adrian.klaver@aklaver.com

over 10 years ago

In reply to: Arthur Pemberton (#3)

Re: Cannot upgrade from 9.3 to 9.4 using pg_upgrade

On 01/04/2016 12:53 PM, Arthur Pemberton wrote:

Yes, I have tried it without --jobs, just to simplify.

That would have been too easy. Not sure what is going on.

So are there any other errors, warnings, etc between?:

Checking cluster versions ok

and

SQL command failed

On Sun, Jan 3, 2016 at 4:27 PM, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 01/03/2016 12:03 AM, Arthur Pemberton wrote:

I'm trying to use pg_upgrade to upgrade from 9.3 to 9.4 on
CentOS 6 with
packages from Postgres' yum repo.

I've revered to vanlla ph_hda.conf on 9.3, and ran initdb on 9.4. I
don't get very far however, I get the following error, and Google
doesn't seem to help.

-bash-4.1$ /usr/pgsql-9.4/bin/pg_upgrade --jobs 4 \

--old-datadir "/var/lib/pgsql/9.3/data" \
--new-datadir "/var/lib/pgsql/9.4/data" \
--old-bindir "/usr/pgsql-9.3/bin" \
--new-bindir "/usr/pgsql-9.4/bin"

Performing Consistency Checks
-----------------------------
Checking cluster versions ok
SQL command failed
CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM
pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n 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 DISTINCT FROM 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') ));
ERROR: relation "info_rels" already exists

Failure, exiting

Hmm, the error message is fairly clear, the reason for it, not.

Given that it seems to be the program walking over itself, have you
tried without the --jobs argument?

Also what is the results from pg_config for the 9.3 and 9.4 clusters?

www.postgresql.org/docs/9.4/interactive/app-pgconfig.html
<http://www.postgresql.org/docs/9.4/interactive/app-pgconfig.html>

Please advise,
Arthur Pemberton

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Arthur Pemberton

pemboa@gmail.com

over 10 years ago

In reply to: Adrian Klaver (#4)

Re: Cannot upgrade from 9.3 to 9.4 using pg_upgrade

Nothing useful. I'm just going to give up and use pg_dump to a new machine.
Hopefully that allows me to bypass this issue.

On Mon, Jan 4, 2016 at 4:58 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Show quoted text

On 01/04/2016 12:53 PM, Arthur Pemberton wrote:

Yes, I have tried it without --jobs, just to simplify.

That would have been too easy. Not sure what is going on.

So are there any other errors, warnings, etc between?:

Checking cluster versions ok

and

SQL command failed

On Sun, Jan 3, 2016 at 4:27 PM, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 01/03/2016 12:03 AM, Arthur Pemberton wrote:

I'm trying to use pg_upgrade to upgrade from 9.3 to 9.4 on
CentOS 6 with
packages from Postgres' yum repo.

I've revered to vanlla ph_hda.conf on 9.3, and ran initdb on 9.4.
I
don't get very far however, I get the following error, and Google
doesn't seem to help.

-bash-4.1$ /usr/pgsql-9.4/bin/pg_upgrade --jobs 4 \

--old-datadir "/var/lib/pgsql/9.3/data" \
--new-datadir "/var/lib/pgsql/9.4/data" \
--old-bindir "/usr/pgsql-9.3/bin" \
--new-bindir "/usr/pgsql-9.4/bin"

Performing Consistency Checks
-----------------------------
Checking cluster versions ok
SQL command failed
CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM
pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n 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 DISTINCT FROM 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')
));
ERROR: relation "info_rels" already exists

Failure, exiting

Hmm, the error message is fairly clear, the reason for it, not.

Given that it seems to be the program walking over itself, have you
tried without the --jobs argument?

Also what is the results from pg_config for the 9.3 and 9.4 clusters?

www.postgresql.org/docs/9.4/interactive/app-pgconfig.html
<http://www.postgresql.org/docs/9.4/interactive/app-pgconfig.html>

Please advise,
Arthur Pemberton

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver

adrian.klaver@aklaver.com

over 10 years ago

In reply to: Arthur Pemberton (#5)

Re: Cannot upgrade from 9.3 to 9.4 using pg_upgrade

On 01/04/2016 02:37 PM, Arthur Pemberton wrote:

Nothing useful. I'm just going to give up and use pg_dump to a new
machine. Hopefully that allows me to bypass this issue.

Did that help?

To help someone else troubleshoot this, did the error you get occur
after the pg_upgrade line:

Restoring global objects in the new cluster

Restoring database schemas in the new cluster

or even better yet could you post the section of the log above the error?

On Mon, Jan 4, 2016 at 4:58 PM, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Arthur Pemberton

pemboa@gmail.com

over 10 years ago

In reply to: Adrian Klaver (#6)

Re: Cannot upgrade from 9.3 to 9.4 using pg_upgrade

Maybe there was a verbose settings that would have given me that, but what
I pasted was literally all the output I got. In the end, I did a pg_dumpapp

On Mon, Jan 4, 2016 at 8:40 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Show quoted text

On 01/04/2016 02:37 PM, Arthur Pemberton wrote:

Nothing useful. I'm just going to give up and use pg_dump to a new
machine. Hopefully that allows me to bypass this issue.

Did that help?

To help someone else troubleshoot this, did the error you get occur after
the pg_upgrade line:

Restoring global objects in the new cluster

or

Restoring database schemas in the new cluster

or even better yet could you post the section of the log above the error?

On Mon, Jan 4, 2016 at 4:58 PM, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

--
Adrian Klaver
adrian.klaver@aklaver.com

Michael Paquier

michael@paquier.xyz

over 10 years ago

In reply to: Adrian Klaver (#4)

Re: Cannot upgrade from 9.3 to 9.4 using pg_upgrade

On Tue, Jan 5, 2016 at 6:58 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 01/04/2016 12:53 PM, Arthur Pemberton wrote:

Yes, I have tried it without --jobs, just to simplify.

That would have been too easy. Not sure what is going on.

So are there any other errors, warnings, etc between?:

Checking cluster versions ok

and

SQL command failed

Yeah, that's basically the same temporary relation created through the
same connection. That's likely an effect of --jobs though I cannot see
directly how get_db_and_rel_infos would get called twice for the same
connection.
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jim Nasby

Jim.Nasby@BlueTreble.com

over 10 years ago

In reply to: Adrian Klaver (#6)

Re: Cannot upgrade from 9.3 to 9.4 using pg_upgrade

On 1/4/16 7:40 PM, Adrian Klaver wrote:

or even better yet could you post the section of the log above the error?

The server log itself might be useful, especially if full query logging
was turned on. Dunno how easy/possible that is with pg_upgrade.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general