Greenplum Database

2025년 3월 23일 일요일

Rockey 9.x에서 Greenplum TPC-DS 컴파일시 에러 조치

1. 개요

- Rockey 8.x 에서는 gcc 8.x를 이용하여 컴파일하는데, Rockey 9.5 에서는 gcc 11.5를 이용하여 컴파일

- TPCDS 실행시 데이터 생성하는 dsdgen 컴파일시 에러 발생

- gcc 9.5 소스를 빌드하고 난뒤 gcc 9.5로 TPCDS를 컴파일하면 이슈 해결

- 주의 사항

* gcc 9.5를 설치하고, TPCDS를 수행 한 후에 gcc 파일명을 gcc-9로 이름 변경

- Greenplum TPCDS 관련 링크

* https://github.com/RunningJon/TPC-DS

* https://github.com/gpdbkr/gpmixedworkload

2. TPCDS 수행시 에러

[gpadmin@cdw TPC-DS]$ ./tpcds.sh

...

collect2: error: ld returned 1 exit status

make: *** [Makefile:234: dsdgen] Error 1

3.gcc 9.5 설치

1) 설치

- root 계정으로 설치

[root@cdw local]# cd /usr/local

[root@cdw local]# wget http://ftp.gnu.org/gnu/gcc/gcc-9.5.0/gcc-9.5.0.tar.gz

[root@cdw local]# tar zxf gcc-9.5.0.tar.gz

[root@cdw local]# cd gcc-9.5.0

[root@cdw gcc-9.5.0]# ./contrib/download_prerequisites

[root@cdw gcc-9.5.0]# mkdir build

[root@cdw gcc-9.5.0]# ./configure --prefix=/usr/local/gcc-9.5.0 --enable-checking=release -disable-multilib --enable-languages=c,c++

[root@cdw gcc-9.5.0]# make -j$(nproc)

[root@cdw gcc-9.5.0]# make install

[root@cdw gcc-9.5.0]# /usr/local/gcc-9.5.0/bin/gcc --version

gcc (GCC) 9.5.0

This is free software; see the source for copying conditions. There is NO

warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

#### gcc파일을 /usr/local/bin/ 하위디렉로리에 복사

[root@scdw ~]# env | grep PATH

PATH=/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin

[root@scdw ~]#

[root@scdw bin]# cp /usr/local/gcc-9.5.0/bin/gcc /usr/local/bin/gcc

2) 설치 도중 화면 출력

#### download_prerequisites 수행 후 output

[root@scdw gcc-9.5.0]# ./contrib/download_prerequisites

2025-03-13 12:27:37 URL: ftp://gcc.gnu.org/pub/gcc/infrastructure/gmp-6.1.0.tar.bz2 [2383840] -> "./gmp-6.1.0.tar.bz2" [1]

2025-03-13 12:27:42 URL: ftp://gcc.gnu.org/pub/gcc/infrastructure/mpfr-3.1.4.tar.bz2 [1279284] -> "./mpfr-3.1.4.tar.bz2" [1]

2025-03-13 12:27:47 URL: ftp://gcc.gnu.org/pub/gcc/infrastructure/mpc-1.0.3.tar.gz [669925] -> "./mpc-1.0.3.tar.gz" [1]

2025-03-13 12:27:52 URL: ftp://gcc.gnu.org/pub/gcc/infrastructure/isl-0.18.tar.bz2 [1658291] -> "./isl-0.18.tar.bz2" [1]

gmp-6.1.0.tar.bz2: OK

mpfr-3.1.4.tar.bz2: OK

mpc-1.0.3.tar.gz: OK

isl-0.18.tar.bz2: OK

All prerequisites downloaded successfully.

[root@scdw gcc-9.5.0]#

#### configure 수행 후 output

libtool: link: ranlib .libs/libtsan.a

libtool: link: rm -fr .libs/libtsan.lax

libtool: link: ( cd ".libs" && rm -f "libtsan.la" && ln -s "../libtsan.la" "libtsan.la" )

make[4]: Leaving directory '/gcc-9.5.0/x86_64-pc-linux-gnu/libsanitizer/tsan'

make[4]: Entering directory '/gcc-9.5.0/x86_64-pc-linux-gnu/libsanitizer'

true "AR_FLAGS=rc" "CC_FOR_BUILD=gcc" "CFLAGS=-g -O2" "CXXFLAGS=-g -O2 -D_GNU_SOURCE" "CFLAGS_FOR_BUILD=-g -O2" "CFLAGS_FOR_TARGET=-g -O2" "INSTALL=/usr/bin/install -c" "INSTALL_DATA=/usr/bin/install -c -m 644" "INSTALL_PROGRAM=/usr/bin/install -c" "INSTALL_SCRIPT=/usr/bin/install -c" "JC1FLAGS=" "LDFLAGS=" "LIBCFLAGS=-g -O2" "LIBCFLAGS_FOR_TARGET=-g -O2" "MAKE=make" "MAKEINFO=/gcc-9.5.0/missing makeinfo --split-size=5000000 --split-size=5000000 " "PICFLAG=" "PICFLAG_FOR_TARGET=" "SHELL=/bin/sh" "RUNTESTFLAGS=" "exec_prefix=/usr/local/gcc-9.5.0" "infodir=/usr/local/gcc-9.5.0/share/info" "libdir=/usr/local/gcc-9.5.0/lib" "prefix=/usr/local/gcc-9.5.0" "includedir=/usr/local/gcc-9.5.0/include" "AR=ar" "AS=/gcc-9.5.0/host-x86_64-pc-linux-gnu/gcc/as" "LD=/gcc-9.5.0/host-x86_64-pc-linux-gnu/gcc/collect-ld" "LIBCFLAGS=-g -O2" "NM=/gcc-9.5.0/host-x86_64-pc-linux-gnu/gcc/nm" "PICFLAG=" "RANLIB=ranlib" "DESTDIR=" DO=all multi-do # make

make[4]: Leaving directory '/gcc-9.5.0/x86_64-pc-linux-gnu/libsanitizer'

make[3]: Leaving directory '/gcc-9.5.0/x86_64-pc-linux-gnu/libsanitizer'

make[2]: Leaving directory '/gcc-9.5.0/x86_64-pc-linux-gnu/libsanitizer'

make[1]: Leaving directory '/gcc-9.5.0'

[root@cdw gcc-9.5.0]#

#### make 수행 후 output

make[4]: Entering directory '/usr/local/gcc-9.5.0/x86_64-pc-linux-gnu/libatomic/testsuite'

make[4]: Nothing to be done for 'all'.

make[4]: Leaving directory '/usr/local/gcc-9.5.0/x86_64-pc-linux-gnu/libatomic/testsuite'

make[4]: Entering directory '/usr/local/gcc-9.5.0/x86_64-pc-linux-gnu/libatomic'

Makefile:867: warning: overriding recipe for target 'all-multi'

Makefile:861: warning: ignoring old recipe for target 'all-multi'

true DO=all multi-do # make

make[4]: Leaving directory '/usr/local/gcc-9.5.0/x86_64-pc-linux-gnu/libatomic'

make[3]: Leaving directory '/usr/local/gcc-9.5.0/x86_64-pc-linux-gnu/libatomic'

make[2]: Leaving directory '/usr/local/gcc-9.5.0/x86_64-pc-linux-gnu/libatomic'

make[1]: Leaving directory '/usr/local/gcc-9.5.0'

[root@scdw gcc-9.5.0]#

#### make install 수행 후 output

See any operating system documentation about shared libraries for

more information, such as the ld(1) and ld.so(8) manual pages.

----------------------------------------------------------------------

make[4]: Nothing to be done for 'install-data-am'.

make[4]: Leaving directory '/usr/local/gcc-9.5.0/x86_64-pc-linux-gnu/libatomic'

make[3]: Leaving directory '/usr/local/gcc-9.5.0/x86_64-pc-linux-gnu/libatomic'

make[2]: Leaving directory '/usr/local/gcc-9.5.0/x86_64-pc-linux-gnu/libatomic'

make[1]: Leaving directory '/usr/local/gcc-9.5.0'

[root@scdw gcc-9.5.0]#

4. 주의 및 기타 사항

1) gcc 빌드, TPCDS 컴파일 후 gcc 파일명 변경

- PATH의 순위가 /usr/local/bin가 /usr/bin 보다 상위에 있기 때문에 Rockey 9.5 GCC가 변경되므로

/usr/local/bin/gcc를 gcc-9으로 파일명 변경

- gcc 버전 확인 - 반드시 세션 종료 후 재접속 필요

2) TPCDS 컴파일 이후 TPCDS 실행 환경에 다시 컴파일 하지 않도록 파라미터 수정

- TPC-DS/tpcds_variables.sh의 파일에서 true를 false로 변경하면 재 컴파일 하지 않음.

export RUN_COMPILE_TPCDS="true" => export RUN_COMPILE_TPCDS="false"

3) 경로별 버전 확인

[root@cdw ~]# /usr/bin/gcc --version

gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5)

This is free software; see the source for copying conditions. There is NO

warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

## TPCDS 컴파일 도중

[root@cdw ~]# /usr/local/bin/gcc --version

gcc-9 (GCC) 9.5.0

This is free software; see the source for copying conditions. There is NO

warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

## TPCDS 컴파일 이후 파일명 변경

[root@cdw ~]# mv /usr/local/bin/gcc /usr/local/bin/gcc-9

[root@cdw ~]# /usr/local/bin/gcc-9 --version

gcc-9 (GCC) 9.5.0

This is free software; see the source for copying conditions. There is NO

warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

2025년 2월 16일 일요일

Greenplum 제품 알람 공지 수신

Greenplum Product Notifications

* Critical Alerts - 제품 작동에 중요하고 고객 환경에 큰 영향을 미치거나, 많은 고객에게 부정적인 영향을 미칠 수 있는 큰 영향 및/또는 광범위한 소프트웨어 문제.

* Product Advisories - 하나 이상의 고객 환경에서 제품 작동에 영향을 미치는 것으로 확인된 보통 또는 낮은 영향의 비전반적인 소프트웨어 문제입니다.

* Release Announcements - Product lifecycle 공지 (GA, EOL, EOS, compatibility matrix, feature releases)

* Security Advisories - 잠재적인 보안 위험, 취약성 및 사용 가능한 해결 방법

* Legal Notices

Greenplum Product Notifications 수신 설정 방법

* https://knowledge.broadcom.com/external/article/142615/subscribing-to-broadcom-product-notifica.html

Greenplum Product Notifications 수신 설정 후 화면

- Greenplum으로 검색 후 개별 항목에 대해서 클릭

- 주의 : Product All에서 선택할 경우 모든 Broadcom 제품을 선택하는 것과 동일하기 때문에 주의 필요

2025년 1월 19일 일요일

Greenplum 6에서 Table SKEW 확인 방법

소스

- https://github.com/gpdbkr/gpkrutil/blob/main/mngdb/chk_table_skew.sql

- https://knowledge.broadcom.com/external/article?articleNumber=295161

- https://knowledge.broadcom.com/external/article?articleNumber=295215

1. 히든 컬럼(gp_segment_id )을 이용한 방법

- 테이블의 세그먼트 인스턴스별로 row수를 확인

- 장점: 매우 직관적, 쉽게 확인 가능

- 단점: 테이블별/파티션별로 수행 필요, 수행시점에 Table Full 스캔

- 예시

SELECT gp_segment_id, COUNT(*)

FROM <schema_name>.<table_name>

GROUP BY gp_segment_id

ORDER BY 1;

2. Greenplum의 운영을 위한 view로 확인

- view 조회 시점, 내부적으로 gp_segment_id를 수행하여 결과를 보여 줌

- 관련 view: gp_toolkit.gp_skew_coefficients, gp_toolkit.gp_skew_idle_fractions

- 장점: DB내의 모든 테이블의 SKEW를 확인 가능

- 단점: 부하가 많고, 시간이 많이 걸림(view 조회 시점에 full 스캔을 하기에 실 운영환경에서는 비권고)

- 예시

SELECT *

FROM gp_toolkit.gp_skew_coefficients;

SELECT *

FROM gp_toolkit.gp_skew_idle_fractions;

3. 모든 세그먼트에서 테이블의 OS 파일 사이즈로 확인 방법

- 모든 세그먼트의 OS 파일을 external table로 만든 다음 사이즈 체크

- 장점: 전체 DB 확인시 매우 빠름

- 단점: DML 발생시에는 해당 테이블 반영안될수 있음. 함수 생성 필요

- 예시

1) 함수 생성 - Greenplum 6

DROP FUNCTION IF EXISTS public.greenplum_check_skew();

CREATE FUNCTION public.greenplum_check_skew()

RETURNS TABLE (

relation text,

vtotal_size_gb numeric,

vseg_min_size_gb numeric,

vseg_max_size_gb numeric,

vseg_avg_size_gb numeric,

vseg_gap_min_max_percent numeric,

vseg_gap_min_max_gb numeric,

vnb_empty_seg bigint)

LANGUAGE plpgsql

AS $$

DECLARE

v_function_name text := 'greenplum_check_skew';

v_location int;

v_sql text;

v_db_oid text;

BEGIN

The function checks the skew on greenplum table via OS file size

* The table "greenplum_get_refilenodes" collects all the relfilenodes found on the database from all segments

* The external "greenplum_get_db_file_ext" tables collects all the files that are on the base directory

* The view "greenplum_get_file_statistics" combines the relfilenodes and displays the size of the relation by segments

* The view "greenplum_get_skew_report" provide high level overview of the skew

-- Set the client min messages to just warning

v_location := 1000;

SET client_min_messages TO WARNING;

-- Get the database oid

v_location := 2000;

SELECT d.oid INTO v_db_oid

FROM pg_database d

WHERE datname = current_database();

-- Drop the temp table if it exists

v_location := 3000;

v_sql := 'DROP TABLE IF EXISTS public.greenplum_get_refilenodes CASCADE';

v_location := 3100;

EXECUTE v_sql;

-- Temp table to temporary store the relfile records

v_location := 4000;

v_sql := 'CREATE TABLE public.greenplum_get_refilenodes ('

' segment_id int,'

' o oid,'

' relname name,'

' relnamespace oid,'

' relkind char,'

' relfilenode bigint'

')';

v_location := 4100;

EXECUTE v_sql;

-- Store all the data related to the relfilenodes from all

-- the segments into the temp table

v_location := 5000;

v_sql := 'INSERT INTO public.greenplum_get_refilenodes SELECT '

' s.gp_segment_id segment_id, '

' s.oid o, '

' s.relname, '

' s.relnamespace,'

' s.relkind,'

' s.relfilenode '

'FROM '

' gp_dist_random(''pg_class'') s ' -- all segment

'UNION '

' SELECT '

' m.gp_segment_id segment_id, '

' m.oid o, '

' m.relname, '

' m.relnamespace,'

' m.relkind,'

' m.relfilenode '

'FROM '

' pg_class m '; -- relfiles from master

v_location := 5100;

EXECUTE v_sql;

-- Drop the external table if it exists

v_location := 6000;

v_sql := 'DROP EXTERNAL WEB TABLE IF EXISTS public.greenplum_get_db_file_ext';

v_location := 6100;

EXECUTE v_sql;

-- Create a external that runs a shell script to extract all the files

-- on the base directory

v_location := 7000;

v_sql := 'CREATE EXTERNAL WEB TABLE public.greenplum_get_db_file_ext ' ||

'(segment_id int, relfilenode text, filename text, ' ||

'size numeric) ' ||

'execute E''ls -l $GP_SEG_DATADIR/base/' || v_db_oid ||

' | ' ||

'grep gpadmin | ' ||

E'awk {''''print ENVIRON["GP_SEGMENT_ID"] "\\t" $9 "\\t" ' ||

'ENVIRON["GP_SEG_DATADIR"] "/base/' || v_db_oid ||

E'/" $9 "\\t" $5''''}'' on all ' || 'format ''text''';

v_location := 7100;

EXECUTE v_sql;

-- Drop the datafile statistics view if exists

v_location := 8000;

v_sql := 'DROP VIEW IF EXISTS public.greenplum_get_file_statistics';

v_location := 8100;

EXECUTE v_sql;

-- Create a view to get all the datafile statistics

v_location := 9000;

v_sql :='CREATE VIEW public.greenplum_get_file_statistics AS '

'SELECT '

' n.nspname || ''.'' || c.relname relation, '

' osf.segment_id, '

' split_part(osf.relfilenode, ''.'' :: text, 1) relfilenode, '

' c.relkind, '

' sum(osf.size) size '

'FROM '

' public.greenplum_get_db_file_ext osf '

' JOIN public.greenplum_get_refilenodes c ON ('

' c.segment_id = osf.segment_id '

' AND split_part(osf.relfilenode, ''.'' :: text, 1) = c.relfilenode :: text'

' ) '

' JOIN pg_namespace n ON c.relnamespace = n.oid '

'WHERE '

' osf.relfilenode ~ ''(\d+(?:\.\d+)?)'' '

-- ' AND c.relkind = ''r'' :: char '

' AND n.nspname not in ('

' ''pg_catalog'', '

' ''information_schema'', '

' ''gp_toolkit'' '

' ) '

' AND not n.nspname like ''pg_temp%'' '

' GROUP BY 1,2,3,4';

v_location := 9100;

EXECUTE v_sql;

-- Drop the skew report view view if exists

v_location := 10000;

v_sql := 'DROP VIEW IF EXISTS public.greenplum_get_skew_report';

v_location := 10100;

EXECUTE v_sql;

-- Create a view to get all the table skew statistics

v_location := 11100;

v_sql :='CREATE VIEW public.greenplum_get_skew_report AS '

'SELECT '

' sub.relation relation,'

' (sum(sub.size)/(1024^3))::numeric(15,2) AS vtotal_size_GB,' --Size on segments

' (min(sub.size)/(1024^3))::numeric(15,2) AS vseg_min_size_GB,'

' (max(sub.size)/(1024^3))::numeric(15,2) AS vseg_max_size_GB,'

' (avg(sub.size)/(1024^3))::numeric(15,2) AS vseg_avg_size_GB,' --Percentage of gap between smaller segment and bigger segment

' (100*(max(sub.size) - min(sub.size))/greatest(max(sub.size),1))::numeric(6,2) AS vseg_gap_min_max_percent,'

' ((max(sub.size) - min(sub.size))/(1024^3))::numeric(15,2) AS vseg_gap_min_max_GB,'

' count(sub.size) filter (where sub.size = 0) AS vnb_empty_seg '

'FROM '

'public.greenplum_get_file_statistics sub'

' GROUP BY 1';

v_location := 11100;

EXECUTE v_sql;

-- Return the data back

RETURN query (

SELECT

FROM public.greenplum_get_skew_report a);

-- Throw the exception whereever it encounters one

EXCEPTION

WHEN OTHERS THEN

RAISE EXCEPTION '(%:%:%)', v_function_name, v_location, sqlerrm;

END;

$$;

2) SKEW 리포트 함수 실행

SELECT * FROM public.greenplum_check_skew()

where vseg_gap_min_max_percent > 10;

----------------------------------+----------------+------------------+------------------+------------------+--------------------------+---------------------+---------------

public.order_log_1_prt_p2002 | 0.00 | 0.00 | 0.00 | 0.00 | 15.50 | 0.00 | 0

public.greenplum_get_refilenodes | 0.00 | 0.00 | 0.00 | 0.00 | 42.86 | 0.00 | 0

public.order_log_1_prt_p2004 | 0.00 | 0.00 | 0.00 | 0.00 | 12.80 | 0.00 | 0

public.order_log_1_prt_p2006 | 0.00 | 0.00 | 0.00 | 0.00 | 19.53 | 0.00 | 0

public.order_log_1_prt_p2001 | 0.00 | 0.00 | 0.00 | 0.00 | 14.63 | 0.00 | 0

public.test_toast2 | 0.00 | 0.00 | 0.00 | 0.00 | 100.00 | 0.00 | 1

public.order_log_1_prt_p2005h1 | 0.00 | 0.00 | 0.00 | 0.00 | 24.32 | 0.00 | 0

gpkrtpch.region_ix | 0.00 | 0.00 | 0.00 | 0.00 | 50.00 | 0.00 | 0

gpkrtpch.region_pkey | 0.00 | 0.00 | 0.00 | 0.00 | 50.00 | 0.00 | 0

pg_toast.pg_toast_182074 | 0.00 | 0.00 | 0.00 | 0.00 | 100.00 | 0.00 | 1

pg_toast.pg_toast_182074_index | 0.00 | 0.00 | 0.00 | 0.00 | 50.00 | 0.00 | 0

gpkrtpch.region | 0.00 | 0.00 | 0.00 | 0.00 | 100.00 | 0.00 | 1

public.test_toast2_pkey | 0.00 | 0.00 | 0.00 | 0.00 | 50.00 | 0.00 | 0

gpkrtpch.supplier_pkey | 0.00 | 0.00 | 0.00 | 0.00 | 20.00 | 0.00 | 0

(14 rows)

Time: 210.008 ms

gpkrtpch=#

3) 각 테이블의 각 세그먼트의 사이즈 확인

SELECT * FROM public.greenplum_get_file_statistics

where relation = 'public.order_log_1_prt_p2002';

relation | segment_id | relfilenode | relkind | size

------------------------------+------------+-------------+---------+------

public.order_log_1_prt_p2002 | 0 | 209050 | r | 896

public.order_log_1_prt_p2002 | 3 | 255681 | r | 944

public.order_log_1_prt_p2002 | 1 | 161093 | r | 872

public.order_log_1_prt_p2002 | 2 | 161093 | r | 1032

(4 rows)

2024년 12월 15일 일요일

Greenplum 6 마스터 Port 변경

Greenplum 마스터 노드 Port 변경
  - 이미 설치된 Greenplum 클러스터에서 마스터 port 변경 절차
  - https://knowledge.broadcom.com/external/article?articleNumber=296803

1. 테스트 환경
   - Greenplum Version: Greenplum 6.x
   - 2 Master nodes, 4 Segment nodes


2. 변경할 설정
  1) DB 설정 파일 변경 ( Master node/ Standby Master node) 
     $ vi $MASTER_DATA_DIRECTORY/postgresql.conf 
     - port=5432 ##port 5432 ==> port=5433
  2) DB 환경 변경 
     =# select * from gp_segment_configuration ;
     - gp_segment_configuration.port 5432 => 5433
  3) DB 접속 환경 변경
     $ vi ~/.bashrc
     export PGPORT=5433   

3. Greenplum Master Port 변경
  1) 현재 DB 환경 확인
[gpadmin@mdw gpseg-1]$ psql
Timing is on.
psql (9.4.26)
Type "help" for help.

gpadmin=# select * from gp_segment_configuration order by 1;
 dbid | content | role | preferred_role | mode | status | port | hostname | address |       datadir
------+---------+------+----------------+------+--------+------+----------+---------+----------------------
    1 |      -1 | p    | p              | n    | u      | 5432 | mdw      | mdw     | /data/master/gpseg-1
    2 |       0 | p    | p              | n    | u      | 6000 | sdw1     | sdw1    | /data/primary/gpseg0
    3 |       1 | p    | p              | n    | u      | 6000 | sdw2     | sdw2    | /data/primary/gpseg1
    4 |       2 | p    | p              | n    | u      | 6000 | sdw3     | sdw3    | /data/primary/gpseg2
    5 |       3 | p    | p              | n    | u      | 6000 | sdw4     | sdw4    | /data/primary/gpseg3
    6 |       0 | m    | m              | n    | u      | 7000 | sdw2     | sdw2    | /data/mirror/gpseg0
    7 |       1 | m    | m              | n    | u      | 7000 | sdw3     | sdw3    | /data/mirror/gpseg1
    8 |       2 | m    | m              | n    | u      | 7000 | sdw4     | sdw4    | /data/mirror/gpseg2
    9 |       3 | m    | m              | n    | u      | 7000 | sdw1     | sdw1    | /data/mirror/gpseg3
   10 |      -1 | m    | m              | s    | u      | 5432 | smdw     | smdw    | /data/master/gpseg-1
(10 rows)

Time: 3.192 ms
gpadmin=#

   2) DB stop
[gpadmin@mdw gpseg-1]$ gpstop -af

   3) Greenplum 환경 설정 파일 port 변경 ($MASTER_DATA_DIRECTORY/postgresql.conf ) 
[gpadmin@mdw gpseg-1]$ grep 5432 $MASTER_DATA_DIRECTORY/postgresql.conf
port=5432 ##port 5432       # sets the database listener port for
[gpadmin@mdw gpseg-1]$ vi $MASTER_DATA_DIRECTORY/postgresql.conf  ## change port from 5432 to 5433
[gpadmin@mdw gpseg-1]$ grep 5433 $MASTER_DATA_DIRECTORY/postgresql.conf
port=5433 ##port 5433       # sets the database listener port for
[gpadmin@mdw gpseg-1]$ ssh smdw
[gpadmin@smdw ~]$ vi $MASTER_DATA_DIRECTORY/postgresql.conf       ## change port from 5432 to 5433
[gpadmin@smdw ~]$ grep 5433 $MASTER_DATA_DIRECTORY/postgresql.conf
port=5433 ##port 5433       # sets the database listener port for
[gpadmin@smdw ~]$


   4)DB start
[gpadmin@mdw gpseg-1]$ gpstart -a

   5) DB 설정 테이블의 포트 변경 (gp_segment_configuration)
[gpadmin@mdw gpseg-1]$ psql -p 5433
Timing is on.
psql (9.4.26)
Type "help" for help.

gpadmin=# begin;
BEGIN
Time: 0.686 ms
gpadmin=# set allow_system_table_mods=true;
SET
Time: 13.477 ms
gpadmin=# UPDATE gp_segment_configuration SET port=5433 WHERE port=5432 and content = -1;
UPDATE 2
Time: 10.805 ms
gpadmin=# select * from gp_segment_configuration where content=-1;
 dbid | content | role | preferred_role | mode | status | port | hostname | address |       datadir
------+---------+------+----------------+------+--------+------+----------+---------+----------------------
    1 |      -1 | p    | p              | n    | u      | 5433 | mdw      | mdw     | /data/master/gpseg-1
   10 |      -1 | m    | m              | s    | u      | 5433 | smdw     | smdw    | /data/master/gpseg-1
(2 rows)

Time: 2.368 ms
gpadmin=# commit;
COMMIT
Time: 1.953 ms
gpadmin=#
gpadmin=# create table public.test ( a int);
NOTICE:  Table does not have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
Time: 31.949 ms
gpadmin=# insert into public.test select i from generate_series(1, 100) i;
INSERT 0 100
Time: 17.722 ms
gpadmin=# select count(*) from public.test;
 count
-------
   100
(1 row)

Time: 5.914 ms
gpadmin=# \q

   5) Greenplum 접속 port 설정 변경 (~/.bashrc) 

[gpadmin@mdw gpseg-1]$ psql
psql: could not connect to server: 그런 파일이나 디렉터리가 없습니다       ## port 변경되었기 때문에, 새로운 port로 접속 필요
  Is the server running locally and accepting
  connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

[gpadmin@mdw gpseg-1]$ psql -p 5433
Timing is on.
psql (9.4.26)
Type "help" for help.

gpadmin=# \q
[gpadmin@mdw gpseg-1]$
[gpadmin@mdw gpseg-1]$ vi ~/.bashrc
export PGPORT=5433            ## port 설정이 없으면 5432접속, port 번호 변경시 PGPORT 환경 설정 
[gpadmin@mdw gpseg-1]$ source ~/.bashrc
[gpadmin@mdw gpseg-1]$ psql
Timing is on.
psql (9.4.26)
Type "help" for help.

gpadmin=# show port;
 port
------
 5433
(1 row)

Time: 0.885 ms
gpadmin=# select count(*) From public.test;
 count
-------
   100
(1 row)

Time: 30.654 ms
gpadmin=# \q

   6) Greenplum 상태 확인  

[gpadmin@mdw gpseg-1]$ gpstate -f
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -f
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.23.3 build commit:0eb759d759987e82ba3bf910b89ed3057bad0416'
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.23.3 build commit:0eb759d759987e82ba3bf910b89ed3057bad0416) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Mar  3 2023 21:50:52'
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-Standby master details
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-----------------------
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-   Standby address          = smdw
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-   Standby data directory   = /data/master/gpseg-1
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-   Standby port             = 5433
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-   Standby PID              = 110197
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-   Standby status           = Standby host passive
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:--------------------------------------------------------------
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:--pg_stat_replication
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:--------------------------------------------------------------
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:-No entries found.
20241216:10:27:59:037230 gpstate:mdw:gpadmin-[INFO]:--------------------------------------------------------------
[gpadmin@mdw gpseg-1]$ gpstate -e
20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e
20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.23.3 build commit:0eb759d759987e82ba3bf910b89ed3057bad0416'
20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.23.3 build commit:0eb759d759987e82ba3bf910b89ed3057bad0416) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Mar  3 2023 21:50:52'
20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...
20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments...
20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-----------------------------------------------------
20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-Segment Mirroring Status Report
20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-----------------------------------------------------
20241216:10:28:15:037479 gpstate:mdw:gpadmin-[INFO]:-All segments are running normally
[gpadmin@mdw gpseg-1]$


4. 기타 사항
   1) 세그먼트 port 변경시 (broadcom knowledge 참조)
      - https://knowledge.broadcom.com/external/article?articleNumber=382981
      
      

2024년 12월 11일 수요일

일반 유저로 pl/python 함수생성하기

Greenplum은 DB로서의 역활 뿐만 아니라 유용한 확장 패키지들이 있습니다.

이중 pl/python으로 함수를 만들면, In-DB 분석을 할 수 있을 뿐만 아니라 DB 안에서 다양한 유틸리티를 수행할 수 있습니다.

다만, pl/python으로 사용하는 경우, OS 커멘드까지 수행할 수 있어서, 슈퍼유저의 권한이 필요로 합니다.

슈퍼유저 권한 없이 일반 유저가 pl/python 을 생성/수정할 수 있도록 설정하는 방법은 아래와 같습니다.

1. 일반 유저가 pl/python 수행시 발생하는 에러

[gpadmin@r8g6single mngdb]$ psql -U udba
Timing is on.
psql (9.4.26)
Type "help" for help.
gpkrtpch=> CREATE FUNCTION pymax (a integer, b integer)
RETURNS integer
AS $$
if a > b:
return a
return b
$$ LANGUAGE plpythonu;
ERROR: permission denied for language plpythonu
Time: 0.492 ms
gpkrtpch=>

2. pl/python 권한 주기 위한 내용 요약

1) Greenplum에서는 plpythonu를 생성하면, lanpltrusted 필드가 false로 기본 셋팅 됩니다.

이를 일반 유저가 사용할 수 있도록 true로 설정하면, 모든 유저가 일반 프로시저를 생성하는 것과 같이 pl/python을 사용할 수 있습니다.

2) 해당 시스템 카탈로그를 업데이트하려면, 마스터 인스턴스 뿐만 아니라 모든 인스턴스에서 true로 업데이트가 필요합니다.

gpadmin=# create language plpythonu;
CREATE LANGUAGE
Time: 125.521 ms
gpadmin=# select * from pg_language;
lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
-----------+----------+---------+--------------+---------------+-----------+--------------+--------
internal | 10 | f | f | 0 | 0 | 2246 |
c | 10 | f | f | 0 | 0 | 2247 |
sql | 10 | f | t | 0 | 0 | 2248 |
plpgsql | 10 | t | t | 12332 | 12333 | 12334 |
plpythonu | 10 | t | f =>t 변경필요 | 26231 | 26232 | 26233 |
gpadmin=#

2. pl/python 권한 주기

1) 사전 현재 환경 확인 (콘솔/gpadmin 계정)

$ psql -c "select * from pg_language;"

lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
-----------+----------+---------+--------------+---------------+-----------+--------------+--------
internal | 10 | f | f | 0 | 0 | 2246 |
c | 10 | f | f | 0 | 0 | 2247 |
sql | 10 | f | t | 0 | 0 | 2248 |
plpgsql | 10 | t | t | 12332 | 12333 | 12334 |
plpythonu | 10 | t | f | 26231 | 26232 | 26233 |

2) pl/python 권한

- 첨부 파일은 https://github.com/gpdbkr/gpkrutil/blob/main/mngdb/trusting_plpython.sh 에도 올려 놓았습니다.

- trusting_plpython.sh 내용 : 모든 세그먼트에 plpythonu의 lanpltrusted 필드를 false에서 true로 업데이트

$ su - gpadmin

$ export PGDATABASE=databasename ## 현재 사용하는 databasename

$ sh trusting_plpython.sh

$ cat trusting_plpython.sh ## 소스
#!/bin/bash

### This script for GPDB 6.x
### Get segments host and port
sql_segments="select hostname || ' ' || port from gp_segment_configuration where preferred_role = 'p';"

### Loop over segments
psql -Atc "${sql_segments}" | while read host port;
do
echo "PROCESSING ${host}, ${port}";
PGOPTIONS="-c gp_session_role=utility" psql -a -h ${host} -p ${port} <<EOF
set allow_system_table_mods=on;
update pg_language set lanpltrusted = true where lanname = 'plpythonu';
EOF
done
$

3) pl/python 업데이트 확인

$ psql -c "select * from pg_language;"

lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
-----------+----------+---------+--------------+---------------+-----------+--------------+--------
internal | 10 | f | f | 0 | 0 | 2246 |
c | 10 | f | f | 0 | 0 | 2247 |
sql | 10 | f | t | 0 | 0 | 2248 |
plpgsql | 10 | t | t | 12332 | 12333 | 12334 |
plpythonu | 10 | t | t | 26231 | 26232 | 26233 |

3. 일반 유저로 pl/python 함수 생성

1) 일반 유저로 DB 접속

$ psql -U 일반유저

[gpadmin@r8g6single mngdb]$ psql -U udba
Timing is on.
psql (9.4.26)
Type "help" for help.

gpkrtpch=> CREATE FUNCTION pymax (a integer, b integer)
RETURNS integer
AS $$
if a > b:
return a
return b
$$ LANGUAGE plpythonu;
CREATE FUNCTION
Time: 53.240 ms
gpkrtpch=> gpkrtpch=> select pymax(1, 2);
pymax
-------
2
(1 row)

Time: 11.096 ms
gpkrtpch=>

Greenplum Database

2025년 3월 23일 일요일

Rockey 9.x에서 Greenplum TPC-DS 컴파일시 에러 조치

2025년 2월 16일 일요일

Greenplum 제품 알람 공지 수신

2025년 1월 19일 일요일

Greenplum 6에서 Table SKEW 확인 방법

2024년 12월 15일 일요일

Greenplum 6 마스터 Port 변경

2024년 12월 11일 수요일

일반 유저로 pl/python 함수생성하기

Rockey 9.x에서 Greenplum TPC-DS 컴파일시 에러 조치

블로그 보관함

태그