Greenplum upgrade from 4.3.4.1 to 4.3.30.0 准备工作

2015年升级后，最近几年没有进行升级，最近查看官网看到已经升级到了4.3.30.0版本，对一些工具都打了补丁，具体可见链接https://gpdb.docs.pivotal.io/43300/relnotes/GPDB_43300_README.html

升级前的准备工作

1.保证升级可以回滚

2.按官方文档提示的升级相关视图，修改相关参数

第一步首先保证greenplum 4.3.4.1的安装目录文件不做任何变动,我的目录为/opt/greenplum-db-4.3.4.1

第二步由于我是从4.3.4.1升级到4.3.30.0，所以只升级相关视图，修改参数即可

1.修改参数

gpconfig -c gp_interconnect_type -v UDPIFC -m UDPIFC 如果之前是UDP一定要修改为UDPIFC,否则节点之间无法通信

2.升级视图gp_toolkit.gp_bloat_expected_pages

因为有可能在分区表中，主表即使没有数据，也会报告主分区表膨胀，而导致主表也进行vacuum full.
查看视图定义
\d+ gp_toolkit.gp_bloat_expected_pages
更新的视图新增了以下谓词
AND NOT EXISTS
( SELECT parrelid
     FROM pg_partition
     WHERE parrelid = pgc.oid )

升级gp_toolkit.gp_bloat_expected_pages 
1.在master复制脚本到文本文件
2.在master每一个库执行文本文件，这里假设复制到了gp_bloat_expected_pages.sql文件中，在mytest库执行
psql -f /home/gpadmin/gp_bloat_expected_pages.sql -d mytest

gp_bloat_expected_pages.sql脚本内容如下:
BEGIN;
CREATE OR REPLACE VIEW gp_toolkit.gp_bloat_expected_pages
AS
    SELECT
      btdrelid,
      btdrelpages,
       CASE WHEN btdexppages < numsegments
          THEN numsegments
          ELSE btdexppages
        END as btdexppages
    FROM
    ( SELECT
        oid as btdrelid,
        pgc.relpages as btdrelpages,
        CEIL((pgc.reltuples * (25 + width))::numeric / current_setting('block_size')::numeric) AS btdexppages,
        (SELECT numsegments FROM gp_toolkit.__gp_number_of_segments) AS numsegments
        FROM
        ( SELECT pgc.oid, pgc.reltuples, pgc.relpages
            FROM pg_class pgc
            WHERE NOT EXISTS
            ( SELECT iaooid
                FROM gp_toolkit.__gp_is_append_only
                WHERE iaooid = pgc.oid AND iaotype = 't' )
            AND NOT EXISTS
            ( SELECT parrelid
                FROM pg_partition
                WHERE parrelid = pgc.oid )) AS pgc
        LEFT OUTER JOIN
          ( SELECT  starelid, SUM(stawidth * (1.0 - stanullfrac)) AS width
              FROM pg_statistic pgs
              GROUP BY 1) AS btwcols
        ON pgc.oid = btwcols.starelid
        WHERE starelid IS NOT NULL) AS subq;

GRANT SELECT ON TABLE gp_toolkit.gp_bloat_expected_pages TO public;
COMMIT;

 

3.升级视图gp_toolkit.gp_bloat_diag

检查是否需要更新，这里显示btdexppages integer，所以需要更新，如果是numeric则不需要
\df gp_toolkit.gp_bloat_diag
List of functions
-[ RECORD 1 ]-------+------------------------------------------------------------------------------------------------
Schema              | gp_toolkit
Name                | gp_bloat_diag
Result data type    | record
Argument data types | btdrelpages integer, btdexppages integer, aotable boolean, OUT bltidx integer, OUT bltdiag text
Type                | normal

1.拷贝文件到一个文本文件
2.在每个数据库运行该脚本文件
注意在GP低负载的时候做相关操作，虽然不影响功能使用，但是有可能会影响性能

脚本代码如下：
BEGIN;
CREATE OR REPLACE FUNCTION gp_toolkit.gp_bloat_diag(btdrelpages int, btdexppages numeric, aotable bool,
    OUT bltidx int, OUT bltdiag text)
AS
$$
    SELECT
        bloatidx,
        CASE
            WHEN bloatidx = 0
                THEN 'no bloat detected'::text
            WHEN bloatidx = 1
                THEN 'moderate amount of bloat suspected'::text
            WHEN bloatidx = 2
                THEN 'significant amount of bloat suspected'::text
            WHEN bloatidx = -1
                THEN 'diagnosis inconclusive or no bloat suspected'::text
        END AS bloatdiag
    FROM
    (
        SELECT
            CASE
                WHEN $3 = 't' THEN 0
                WHEN $1 < 10 AND $2 = 0 THEN -1
                WHEN $2 = 0 THEN 2
                WHEN $1 < $2 THEN 0
                WHEN ($1/$2)::numeric > 10 THEN 2
                WHEN ($1/$2)::numeric > 3 THEN 1
                ELSE -1
            END AS bloatidx
    ) AS bloatmapping

$$
LANGUAGE SQL READS SQL DATA;

GRANT EXECUTE ON FUNCTION gp_toolkit.gp_bloat_diag(int, numeric, bool, OUT int, OUT text) TO public;

CREATE OR REPLACE VIEW gp_toolkit.gp_bloat_diag
AS
    SELECT
        btdrelid AS bdirelid,
        fnnspname AS bdinspname,
        fnrelname AS bdirelname,
        btdrelpages AS bdirelpages,
        btdexppages AS bdiexppages,
        bltdiag(bd) AS bdidiag
    FROM
    (
        SELECT
            fn.*, beg.*,
            gp_toolkit.gp_bloat_diag(btdrelpages::int, btdexppages::numeric, iao.iaotype::bool) AS bd
        FROM
            gp_toolkit.gp_bloat_expected_pages beg,
            pg_catalog.pg_class pgc,
            gp_toolkit.__gp_fullname fn,
            gp_toolkit.__gp_is_append_only iao

        WHERE beg.btdrelid = pgc.oid
            AND pgc.oid = fn.fnoid
            AND iao.iaooid = pgc.oid
    ) as bloatsummary
    WHERE bltidx(bd) > 0;

GRANT SELECT ON TABLE gp_toolkit.gp_bloat_diag TO public;
COMMIT;

参考：

https://gpdb.docs.pivotal.io/43300/relnotes/GPDB_43300_README.html#topic_wzq_5s2_kcb

https://community.pivotal.io/s/article/How-to-Upgrade-Pivotal-Greenplum-inside-family-from-43-to-later