Thread: pg_upgrade fails in 9.6 beta1

pg_upgrade fails in 9.6 beta1

From
Palle Girgensohn
Date:
Hi,

When trying an upgrade from postgresql94 -> postgresql96 (beta1) using pg_upgrade:

pg_upgrade -p 5433 -P 5434 -b /usr/local/bin  -B /home/girgen/postgres96/bin -d /tank/opt96/pgsql/data94 -D
/tank/opt96/pgsql/data96-U pgsql96 -k 


I eventually get this:

pg_restore: creating TABLE "public.cal_event"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 205; 1259 3538451239 TABLE cal_event nobody
pg_restore: [archiver (db)] could not execute query: ERROR:  could not create directory
"pg_tblspc/16400/PG_9.4_201605051/16403":No such file or directory   Command was: 
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('3538451241'::pg_cat...




now

"pg_tblspc/16400/PG_9.4_201605051/16403"

is a mixup of the new cluster pg_tblspc/16400, the old version PG_9.4_, and the new cluster 201605051/

that is, pg_upgrade seems to take the old version as a parameter ( PG_9.4_ ) when trying to create the new tablespace.


This happens in pg_restore, it seems:

Restoring database schemas in the new cluster loadtest
*failure*


Seems like a bug to me, it shouldn't create pg_tblspc/16400/PG_9.4_201605051, the 9.6 tablespace is in
data96/pg_tblspc/16400/PG_9.6_201605051 (not 9.4). 

Palle


Re: pg_upgrade fails in 9.6 beta1

From
Michael Paquier
Date:
On Tue, May 17, 2016 at 12:07 AM, Palle Girgensohn <girgen@pingpong.net> wrote:
> pg_restore: creating TABLE "public.cal_event"
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 205; 1259 3538451239 TABLE cal_event nobody
> pg_restore: [archiver (db)] could not execute query: ERROR:  could not create directory
"pg_tblspc/16400/PG_9.4_201605051/16403":No such file or directory
 

That's weird, a directory with the old name is tried to be created in
the new 9.6 cluster if I follow that correctly.

>     Command was:
> -- For binary upgrade, must preserve pg_type oid
> SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('3538451241'::pg_cat...
>
> now
> "pg_tblspc/16400/PG_9.4_201605051/16403"
> is a mixup of the new cluster pg_tblspc/16400, the old version PG_9.4_, and the new cluster 201605051/
> Seems like a bug to me, it shouldn't create pg_tblspc/16400/PG_9.4_201605051, the 9.6 tablespace is in
data96/pg_tblspc/16400/PG_9.6_201605051 (not 9.4).
 

Based on the information you gave out, a tablespace and visibly a
custom type, I have tried a 9.4->9.6 upgrade with this schema and
things are working for me with -k:
CREATE TABLESPACE popo LOCATION '$TBSPACE_DIR';
CREATE TYPE yoyo AS (a int);
CREATE TABLE toto (a int PRIMARY KEY, b yoyo) TABLESPACE popo;

The old and new tablespaces are in correct shape, even if I did not
provide a tablespace map.

Could you provide more details about the schema causing this error? Do
you have for example some roles beginning with "pg_" as prefix?
-- 
Michael