pg_upgrade bug report

[hellower]

enterprisedb 9.4 ---> 10.5.12 upgrade

Tables with unusual options(ex fillfactor, autovacuum..) in the table will get the following error at using pg_upgrade utlity.

os: linux redhat 6

CREATE TABLE tm_ap_event_entry_sate
(
event_cd numeric(10,0) NOT NULL,
win_cond_seqno numeric(5,0) NOT NULL,
mobil_cust_no character varying(13) NOT NULL,
entry_seqno numeric(5,0) NOT NULL,
entry_sate character varying(1000) NOT NULL,
entry_chk_yn character varying(1) NOT NULL DEFAULT 'N'::character varying,
ltry_proc_yn character varying(1) NOT NULL DEFAULT 'N'::character varying,
gvwy_seqno numeric(5,0),
entry_dttm timestamp without time zone,
reg_user_id character varying(20) NOT NULL,
reg_dttm timestamp without time zone NOT NULL DEFAULT sysdate,
last_mod_user_id character varying(20) NOT NULL,
last_mod_dttm timestamp without time zone NOT NULL DEFAULT sysdate,
entry_sbj_amt numeric(15,0) NOT NULL DEFAULT 0,
recr_num numeric(5,0) NOT NULL DEFAULT 1,
gvwy_sate character varying(50),
win_dttm timestamp without time zone,
entry_stat_cd character varying(1) NOT NULL DEFAULT '1'::character varying,
CONSTRAINT pk_ap_event_entry_sate PRIMARY KEY (event_cd, win_cond_seqno, mobil_cust_no, entry_seqno)
)
WITH (
FILLFACTOR=50,
OIDS=FALSE,
autovacuum_vacuum_threshold=2000,
autovacuum_vacuum_scale_factor=0.0,
autovacuum_analyze_threshold=2000,
autovacuum_analyze_scale_factor=0.0
);

pg_upgrade.log error log
.............................................
pg_restore: creating TABLE "ksksadm.tm_ap_event_entry_sate"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2215; 1259 10575064 TABLE tm_ap_event_entry_sate ksksadm
pg_restore: [archiver (db)] could not execute query: ERROR: unrecognized parameter "autovacuum_analyze_scale_factor"
Command was:
...
pg_restore: creating TABLE "ksksadm.tm_ap_event_entry_sate"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2215; 1259 10575064 TABLE tm_ap_event_entry_sate ksksadm
pg_restore: [archiver (db)] could not execute query: ERROR: unrecognized parameter "fillfactor"
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('10575066'::pg_catalog.oid);

...............................

[dhananjayjejur]

Hi hellower,

We have tried to reproduce your upgrade issue on over local test environment. However, we were able to upgrade the database with pg_upgrade utility successfully. In which as9.4 cluster has the table definition present in it.

Kindly refer for the table definition in 9.4 database cluster :

[root@localhost Downloads]# ps -ef | grep postgres | grep D
postgres  30359      1  0 17:26 ?        00:00:00 /opt/edb/as94/bin/edb-postgres -D /opt/edb/as94/data
postgres 100957      1  0 Sep06 ?        00:00:01 /opt/edb/as10/bin/edb-postgres -D /opt/edb/as10/data
postgres 102123      1  0 Sep06 ?        00:00:01 /opt/PostgreSQL/10/bin/postgres -D /opt/PostgreSQL/10/data
[root@localhost Downloads]# /opt/edb/as94/bin/psql -p 5433 -U postgres
Password for user postgres: 
psql.bin (9.4.5.11)
Type "help" for help.

postgres=# CREATE TABLE tm_ap_event_entry_sate
postgres-# (
postgres(# event_cd numeric(10,0) NOT NULL,
postgres(# win_cond_seqno numeric(5,0) NOT NULL,
postgres(# mobil_cust_no character varying(13) NOT NULL,
postgres(# entry_seqno numeric(5,0) NOT NULL,
postgres(# entry_sate character varying(1000) NOT NULL,
postgres(# entry_chk_yn character varying(1) NOT NULL DEFAULT 'N'::character varying,
postgres(# ltry_proc_yn character varying(1) NOT NULL DEFAULT 'N'::character varying,
postgres(# gvwy_seqno numeric(5,0),
postgres(# entry_dttm timestamp without time zone,
postgres(# reg_user_id character varying(20) NOT NULL,
postgres(# reg_dttm timestamp without time zone NOT NULL DEFAULT sysdate,
postgres(# last_mod_user_id character varying(20) NOT NULL,
postgres(# last_mod_dttm timestamp without time zone NOT NULL DEFAULT sysdate,
postgres(# entry_sbj_amt numeric(15,0) NOT NULL DEFAULT 0,
postgres(# recr_num numeric(5,0) NOT NULL DEFAULT 1,
postgres(# gvwy_sate character varying(50),
postgres(# win_dttm timestamp without time zone,
postgres(# entry_stat_cd character varying(1) NOT NULL DEFAULT '1'::character varying,
postgres(# CONSTRAINT pk_ap_event_entry_sate PRIMARY KEY (event_cd, win_cond_seqno, mobil_cust_no, entry_seqno)
postgres(# )
postgres-# WITH (
postgres(# FILLFACTOR=50,
postgres(# OIDS=FALSE,
postgres(# autovacuum_vacuum_threshold=2000,
postgres(# autovacuum_vacuum_scale_factor=0.0,
postgres(# autovacuum_analyze_threshold=2000,
postgres(# autovacuum_analyze_scale_factor=0.0
postgres(# );
CREATE TABLE
postgres=# \q

Below is the upgrade command we have used and its output.

[postgres@localhost ~]$ /opt/edb/as10/bin/pg_upgrade -b /opt/edb/as94/bin/ -B /opt/edb/as10/bin/ -d /opt/edb/as94/data/ -D /opt/edb/as10_upgrade_10_test/ --check
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "unknown" user columns                 ok
Checking for hash indexes                                   ok
Checking for roles starting with "pg_"                      ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*



[postgres@localhost ~]$ /opt/edb/as10/bin/pg_upgrade -b /opt/edb/as94/bin/ -B /opt/edb/as10/bin/ -d /opt/edb/as94/data/ -D /opt/edb/as10_upgrade_10_test/ 
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "unknown" user columns                 ok
Checking for roles starting with "pg_"                      ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok
Checking for hash indexes                                   ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

Upgraded database startup logs :

[postgres@localhost ~]$ /opt/edb/as10/bin/pg_ctl -D /opt/edb/as10_upgrade_10_test/ start
waiting for server to start....2018-09-07 17:48:54 IST LOG:  listening on IPv6 address "::1", port 5431
2018-09-07 17:48:54 IST LOG:  listening on IPv4 address "127.0.0.1", port 5431
2018-09-07 17:48:54 IST LOG:  listening on Unix socket "/tmp/.s.PGSQL.5431"
2018-09-07 17:48:54 IST LOG:  
	
	** EnterpriseDB Dynamic Tuning Agent ********************************************
	*       System Utilization: 66 %                                                *
	*         Database Version: 10.5.12                                             *
	*            Database Size: 0.1    GB                                           *
	*                      RAM: 1.0    GB                                           *
	*            Shared Memory: 978    MB                                           *
	*       Max DB Connections: 112                                                 *
	*               Autovacuum: on                                                  *
	*       Autovacuum Naptime: 60   Seconds                                        *
	*********************************************************************************
	
2018-09-07 17:48:54 IST LOG:  database system was shut down at 2018-09-07 17:44:24 IST
2018-09-07 17:48:54 IST LOG:  database system is ready to accept connections
 done
server started
[postgres@localhost ~]$ 

Reported issue table in an upgraded cluster :

postgres=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 EnterpriseDB 10.5.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)

postgres=# \d+ tm_ap_event_entry_sate
                                                Table "postgres.tm_ap_event_entry_sate"
      Column      |            Type             | Collation | Nullable |        Default         | Storage  | Stats target | Description 
------------------+-----------------------------+-----------+----------+------------------------+----------+--------------+-------------
 event_cd         | numeric(10,0)               |           | not null |                        | main     |              | 
 win_cond_seqno   | numeric(5,0)                |           | not null |                        | main     |              | 
 mobil_cust_no    | character varying(13)       |           | not null |                        | extended |              | 
 entry_seqno      | numeric(5,0)                |           | not null |                        | main     |              | 
 entry_sate       | character varying(1000)     |           | not null |                        | extended |              | 
 entry_chk_yn     | character varying(1)        |           | not null | 'N'::character varying | extended |              | 
 ltry_proc_yn     | character varying(1)        |           | not null | 'N'::character varying | extended |              | 
 gvwy_seqno       | numeric(5,0)                |           |          |                        | main     |              | 
 entry_dttm       | timestamp without time zone |           |          |                        | plain    |              | 
 reg_user_id      | character varying(20)       |           | not null |                        | extended |              | 
 reg_dttm         | timestamp without time zone |           | not null | sysdate                | plain    |              | 
 last_mod_user_id | character varying(20)       |           | not null |                        | extended |              | 
 last_mod_dttm    | timestamp without time zone |           | not null | sysdate                | plain    |              | 
 entry_sbj_amt    | numeric(15,0)               |           | not null | 0                      | main     |              | 
 recr_num         | numeric(5,0)                |           | not null | 1                      | main     |              | 
 gvwy_sate        | character varying(50)       |           |          |                        | extended |              | 
 win_dttm         | timestamp without time zone |           |          |                        | plain    |              | 
 entry_stat_cd    | character varying(1)        |           | not null | '1'::character varying | extended |              | 
Indexes:
    "pk_ap_event_entry_sate" PRIMARY KEY, btree (event_cd, win_cond_seqno, mobil_cust_no, entry_seqno)
Options: fillfactor=50, autovacuum_vacuum_threshold=2000, autovacuum_vacuum_scale_factor=0.0, autovacuum_analyze_threshold=2000, autovacuum_analyze_scale_factor=0.0

postgres=# show data_directory ;
        data_directory         
-------------------------------
 /opt/edb/as10_upgrade_10_test
(1 row)

All the above testing was base on the default configuration. Kindly try the above test case steps in your test environment and let us know if you still face the issue.

Hope this helps.

Regards,

Dhananjay

[hellower]

oops, sorry!

 It is partition-table.

non-partition table ->  good.

partition table -> pg_upgrade error

ex)  PPAS 9.4 table description 

CREATE TABLE tm_ap_event_entry_sate (
event_cd numeric(10,0) NOT NULL,
win_cond_seqno numeric(5,0) NOT NULL,
mobil_cust_no character varying(13) NOT NULL,
entry_seqno numeric(5,0) NOT NULL,
entry_sate character varying(1000) NOT NULL,
entry_chk_yn character varying(1) DEFAULT 'N'::character varying NOT NULL,
ltry_proc_yn character varying(1) DEFAULT 'N'::character varying NOT NULL,
gvwy_seqno numeric(5,0),
entry_dttm timestamp without time zone,
reg_user_id character varying(20) NOT NULL,
reg_dttm timestamp without time zone DEFAULT sysdate NOT NULL,
last_mod_user_id character varying(20) NOT NULL,
last_mod_dttm timestamp without time zone DEFAULT sysdate NOT NULL,
entry_sbj_amt numeric(15,0) DEFAULT 0 NOT NULL,
recr_num numeric(5,0) DEFAULT 1 NOT NULL,
gvwy_sate character varying(50),
win_dttm timestamp without time zone,
entry_stat_cd character varying(1) DEFAULT '1'::character varying NOT NULL
)
PARTITION BY RANGE(event_cd)

(
PARTITION pt_201806 VALUES LESS THAN (2018070000),
PARTITION pt_201812 VALUES LESS THAN (2019010000),
PARTITION pt_max VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE tm_ap_event_entry_sate SET (autovacuum_vacuum_scale_factor = 0.0);

ALTER TABLE tm_ap_event_entry_sate SET (autovacuum_vacuum_threshold = 10000);

ALTER TABLE tm_ap_event_entry_sate SET (autovacuum_analyze_scale_factor = 0.0);

ALTER TABLE tm_ap_event_entry_sate SET (autovacuum_analyze_threshold = 10000);

ALTER TABLE tm_ap_event_entry_sate SET (fillfactor=50);

ALTER TABLE tm_ap_event_entry_sate_pt_201806 SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE tm_ap_event_entry_sate_pt_201806 SET (autovacuum_vacuum_threshold = 10000);
ALTER TABLE tm_ap_event_entry_sate_pt_201806 SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE tm_ap_event_entry_sate_pt_201806 SET (autovacuum_analyze_threshold = 10000);
ALTER TABLE tm_ap_event_entry_sate_pt_201806 SET (fillfactor=50);

ALTER TABLE tm_ap_event_entry_sate_pt_201812 SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE tm_ap_event_entry_sate_pt_201812 SET (autovacuum_vacuum_threshold = 10000);
ALTER TABLE tm_ap_event_entry_sate_pt_201812 SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE tm_ap_event_entry_sate_pt_201812 SET (autovacuum_analyze_threshold = 10000);
ALTER TABLE tm_ap_event_entry_sate_pt_201812 SET (fillfactor=50);

[chaitalirs · Accepted solution]

Hi hellower,

We have escalated this issue, we will keep you updating on the status.

[slonkar · Accepted solution]

Hi @hellower,

The reported bug is fixed in the latest minor version of the EPAS 10.

We have tested it in our local environment and it is working fine.

Could you please install the latest version of EPAS 10 (10.6.13) and then try to upgrade the database cluster.

Please let us know in case of any issues/queries.

Regards,

Sudhir

[hellower]

Thanks!