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':g_catalog.oid);
...............................
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
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
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_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);
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
Thanks!