Greenplum Database: 12월 2024

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
      
      

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

2024년 12월 15일 일요일

Greenplum 6 마스터 Port 변경

2024년 12월 11일 수요일

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

Greenplum 6 마스터 Port 변경

블로그 보관함

태그