이번 포스팅에서는 Linux 위에 설치된 DB에 접속해서 분석을 하려고 했을 때 자주 사용하는 가장 기본적인 Linux Shell Script 를 소개하겠습니다. 


참고로, 저는 MacBook Pro, MacOS sierra를 사용하고 있구요, VMware에 Linux CentOS 6.6 version을 설치한 후, CentOS에 Greenplum Database 4.3.18 버전 (Master node 1, Segment node 1, Segment node 2)를 설치하여 사용하고 있습니다. 


아래의 예시는 Greenplum DB 에 연결해서 사용하는 Linux script 간단 예제가 되겠습니다. 



1. 파일 리스트 확인하기: ls


script

description 

  ls

  list files 

  ls -l

  list more info

  ls -a

  list all

  ls -t

  list by time

  ls -lat

  combine (list more info & all & by time)

  ls *sh

  파일 이름의 끝부분에 'sh'를 포함한 모든 파일 list




2. Terminal Console 창 지우기: clear




3. 파일의 텍스트 내용 확인하기: cat file_name



[gpadmin@mdw ~]$ ls -al

합계 544856

drwx------  8 gpadmin gpadmin      4096 2017-12-06 19:57 .

drwxr-xr-x. 4 root    root         4096 2017-11-08 20:04 ..

-rw-------  1 gpadmin gpadmin      4462 2017-12-09 00:21 .bash_history

-rw-r--r--  1 gpadmin gpadmin        18 2014-10-16 22:56 .bash_logout

-rw-r--r--  1 gpadmin gpadmin       398 2017-12-04 13:45 .bash_profile

-rw-r--r--  1 gpadmin gpadmin       124 2014-10-16 22:56 .bashrc

-rw-rw-r--  1 gpadmin gpadmin        28 2017-11-08 20:12 .gphostcache

drwxrwxr-x  2 gpadmin gpadmin      4096 2017-12-04 13:43 .oracle_jre_usage

-rw-------  1 gpadmin gpadmin        32 2017-11-08 20:21 .pgpass

-rw-rw-r--  1 gpadmin gpadmin         0 2017-11-08 20:21 .pgpass.1510140115

-rw-------  1 gpadmin gpadmin      2066 2017-12-09 00:21 .psql_history

drwx------  2 gpadmin gpadmin      4096 2017-11-08 20:05 .ssh

drwxrwxr-x  2 gpadmin gpadmin      4096 2017-12-06 20:10 command

drwxrwxr-x  2 gpadmin gpadmin      4096 2017-12-08 09:45 gpAdminLogs

drwxr-xr-x  2 gpadmin gpadmin      4096 2017-11-08 20:14 gpconfigs

drwxrwxr-x  2 gpadmin gpadmin      4096 2017-12-07 20:29 gptext

-rwxr-xr-x  1 gpadmin gpadmin      2916 2017-12-04 13:42 gptext_install_config

-rwxr-xr-x  1 gpadmin gpadmin 192264305 2017-09-19 09:20 greenplum-text-2.1.3-rhel6_x86_64.bin

-rw-r--r--  1 gpadmin gpadmin 191435368 2017-12-04 12:36 greenplum-text-2.1.3-rhel6_x86_64.tar.gz

-rw-r--r--  1 gpadmin gpadmin 174157387 2017-12-04 13:28 jdk-8u161-linux-x64.rpm

[gpadmin@mdw ~]$

[gpadmin@mdw ~]$

[gpadmin@mdw ~]$ cd command

[gpadmin@mdw command]$ ls -al

합계 32

drwxrwxr-x 2 gpadmin gpadmin 4096 2017-12-06 20:10 .

drwx------ 8 gpadmin gpadmin 4096 2017-12-06 19:57 ..

-rw-rw-r-- 1 gpadmin gpadmin  830 2017-12-06 20:04 check_tb.txt

-rw-rw-r-- 1 gpadmin gpadmin   41 2017-12-06 20:10 gp_command.txt

[gpadmin@mdw command]$

[gpadmin@mdw command]$

[gpadmin@mdw command]$ cat gp_command.txt

# starting gpdb

gpstart -a

# stopping gpdb

gpstop -af


[gpadmin@mdw command]$ 

[gpadmin@mdw command]$ 

 



>> CentOS 버전 확인, Local Host 확인 하기: cat /etc/redhat-release, cat /etc/hosts


[root@4e6b947be0d6 setup]# su - gpadmin

-bash-4.1$ cat /etc/redhat-release

CentOS release 6.7 (Final)


-bash-4.1$ cat /etc/hosts

127.0.0.1       localhost

::1      localhost ip6-localhost ip6-loopback

fe00::0 ip6-localnet

ff00::0 ip6-mcastprefix

ff02::1 ip6-allnodes

ff02::2 ip6-allrouters

172.17.0.2     4e6b947be0d6

127.0.0.1 72ba20be3774


-bash-4.1$ exit

logout

 


>> 권한 부여: chown


[root@4e6b947be0d6 setup]# chown gpadmin:gpadmin plr-2.3.1-GPDB4.3-rhel6-x86_64.gppkg

 




4. 파일을 열고 편집하기: vi


script

description 

  vi filename

  편집창에 file 열기 

  esc 을 누른 후에 :q!

  저장하지 않고 vi 편집창 닫기 (not save and exit)

  esc 을 누른 후에 :wq!

  변경사항을 저장하고 vi 편집창 닫기 (save and exit)




5. Greenplum Database에 연결하기: ssh gpadmin@ip



[MacBook-Pro:~ rfriend$ ssh gpadmin@192.168.188.131

gpadmin@192.168.188.131's password: xxxxxxxx 

Last login: Fri Dec  8 22:23:24 2017 from 192.168.188.1

[gpadmin@mdw ~]$ 

 




6. con.sh 파일에 저장해놓고 파일 실행으로 Greenplum Database에 연결하기


여러개의 DB에 바꾸어 가면서 접속해야 하는 경우 IP를 모두 외우고 일일이 치기 힘들 때 유용합니다. 아래 예제는 gpdb, gpdb2, gpdb3 의 3개 DB에 접속하기 편하도록 DB별로 alias를 준 예제입니다. 


chmod +x con.sh 는 con.sh 파일에 들어있는 script가 나중에 실행될 수 있도록 파일 형태를 바꾸어 줍니다. (chmod: change mode)


./con.sh gpdb 는 현재 경로의 con.sh 파일 안의 gpdb 라는 이름 아래에 있는 script (즉, ssh gpadmin@192.168.188.131)를 실행하라는 뜻입니다. 



[MacBook-Pro:~ rfriend$ cd


[MacBook-Pro:~ rfriend$ ls *.sh

con.sh


[MacBook-Pro:~ rfriend$ vi con.sh


#!/bin/bash

  

case $1 in

gpdb)

ssh gpadmin@192.168.188.131

;;


gpdb2)

ssh gpadmin@192.168.188.141

;;


gpdb3)

ssh gpadmin@192.168.188.151

;;


*)


echo $"Usage: $0 target"

exit

esac

~

~

:wq!



[MacBook-Pro:~ rfriend$ chmod +x con.sh


[MacBook-Pro:~ rfriend$ ./con.sh gpdb

gpadmin@192.168.188.131's password:xxxxxxxx 

Last login: Fri Dec  8 23:05:01 2017 from 192.168.188.1

[gpadmin@mdw ~]$ 





7. Greenplum DB 시작하기, 멈추기: gpstart, spstop


script

descriptionn 

  gpstart

  Greenplum Database 시작하기

  gpstart -a

  Greenplum Database 시작할 때 자동으로 YES 입력하게 해서 시작하기

  gpstart -m

  Greenplum Database 마스터 노드만 maintenance mode로 시작하기

  gpstop -af

  실행 중인 모든 코드를 멈추고 Greenplum Database 내리기(중단하기)

  gpstop -r

  Greenplum Database를 내렸다가 다시 시작하기

  gpstop -u

  Greenplum Database 시스템을 방해하는 것 없이 

  configuration file 변경 사항만 다시 로딩하기 




8. Greenplum DB 상태 확인하기: gpstate


script

description 

  gpstate

  Greenplum Database 작동 상태 확인하기

  : PostgreSQL, Greenplum DB의 version 확인 가능함

  gpstate -e

  Greenplum Database primary/segment mirror 세부 이슈 확인하기


Show details on primary/mirror segment pairs that have potential issues such as 1) the active segment is running in change tracking mode, meaning a segment is down 2) the active segment is in resynchronization mode, meaning it is catching up changes to the mirror 3) a segment is not in its preferred role, for example, a segment that was a primary at system initialization time is now acting as a mirror, meaning you may have one or more segment hosts with unbalanced processing load.



[MacBook-Pro:~ rfriend$ ssh gpadmin@192.168.188.131

gpadmin@192.168.188.131's password: xxxxxxxx 

Last login: Fri Dec  8 23:48:08 2017 from 192.168.188.1


[gpadmin@mdw ~]$ gpstate

20171208:23:50:17:109233 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: 

20171208:23:50:17:109233 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.18.0 build 1'

20171208:23:50:17:109233 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.18.0 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Nov 22 2017 18:54:31'

20171208:23:50:17:109233 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...

20171208:23:50:17:109233 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments...

. 

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-Greenplum instance status summary

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-----------------------------------------------------

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Master instance                                = Active

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Master standby                                 = No master standby configured

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Total segment instance count from metadata     = 4

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-----------------------------------------------------

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Primary Segment Status

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-----------------------------------------------------

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Total primary segments                         = 4

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Total primary segment valid (at master)        = 4

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Total primary segment failures (at master)     = 0

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Total number of postmaster.pid files missing   = 0

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Total number of postmaster.pid files found     = 4

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Total number of postmaster.pid PIDs missing    = 0

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Total number of postmaster.pid PIDs found      = 4

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Total number of /tmp lock files missing        = 0

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Total number of /tmp lock files found          = 4

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Total number postmaster processes missing      = 0

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Total number postmaster processes found        = 4

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-----------------------------------------------------

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Mirror Segment Status

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-----------------------------------------------------

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-   Mirrors not configured on this array

20171208:23:50:18:109233 gpstate:mdw:gpadmin-[INFO]:-----------------------------------------------------

[gpadmin@mdw ~]$ 

[gpadmin@mdw ~]$ 

[gpadmin@mdw ~]$ gpstate -e

20171208:23:50:28:109293 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e

20171208:23:50:28:109293 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.18.0 build 1'

20171208:23:50:28:109293 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.18.0 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Nov 22 2017 18:54:31'

20171208:23:50:28:109293 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...

20171208:23:50:28:109293 gpstate:mdw:gpadmin-[INFO]:-Physical mirroring is not configured

[gpadmin@mdw ~]$ 

[gpadmin@mdw ~]$ 

 




9. Greenplum DB에서 ZooKeeper, GPText 시작하기


script

description 

  zkManager start

  ZooKeeper 시작하기 

  (아래의 gptext-start 를 하기 전에 ZooKeeper를 먼저 실행시켜야 함)

  gptext-start

  GPText 시작하기




10. Greenplum DB에서 나오기: exit



[gpadmin@mdw ~]$ 

[gpadmin@mdw ~]$ exit

logout

Connection to 192.168.188.131 closed.

[MacBook-Pro:~ rfriend$ 

 




11. 터미널에서 psql 로 Schema, Table 조회하기, SQL로 조회하기, psql 종료


script

description 

  $ psql

  터미널에서 psql 시작하기

  # \l
  Database 리스트 보기

  # \dn

  Schema 리스트 보기

  # \dt

  Table 리스트 보기

  # \dt public.e*

  public Schema안의 Table 중에서

  'e'로 시작하는 모든 Table 리스트 보기

  # select * from table_name

  table에서 모든 변수의 값 조회하기

  # alter role gpadmin password 'newpassword';

  Password 변경

  # \q

  psql 종료



[MacBook-Pro:~ rfriend$ ssh gpadmin@192.168.188.131

gpadmin@192.168.188.131's password: xxxxxxxx

Last login: Fri Dec  8 23:50:12 2017 from 192.168.188.1

[gpadmin@mdw ~]$ psql

psql (8.2.15)

Type "help" for help.


testdb=# \l
                  List of databases
   Name    |  Owner  | Encoding |  Access privileges 
-----------+---------+----------+---------------------
 gpperfmon | gpadmin | UTF8     | gpadmin=CTc/gpadmin
                                : =c/gpadmin
 postgres  | gpadmin | UTF8     |
 template0 | gpadmin | UTF8     | =c/gpadmin         
                                : gpadmin=CTc/gpadmin
 template1 | gpadmin | UTF8     | =c/gpadmin         
                                : gpadmin=CTc/gpadmin
 testdb    | gpadmin | UTF8     |
(5 rows)


testdb=# \dn

       List of schemas

        Name        |  Owner  

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

 gp_toolkit         | gpadmin

 gpdemo             | gpadmin

 gptext             | gpadmin

 information_schema | gpadmin

 madlib             | gpadmin

 pg_aoseg           | gpadmin

 pg_bitmapindex     | gpadmin

 pg_catalog         | gpadmin

 pg_toast           | gpadmin

 plp                | gpadmin

 public             | gpadmin

 test               | gpadmin

(12 rows)


testdb=# \dt public.e*

                 List of relations

 Schema |     Name     | Type  |  Owner  | Storage 

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

 public | edge         | table | gpadmin | heap

 public | employeelist | table | gpadmin | heap

 public | enron        | table | gpadmin | heap

(3 rows)



testdb=# select eid, firstname, lastname, email_id from employeelist limit 5;

 eid | firstname | lastname |          email_id          

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

  19 | Lindy     | Donoho   | lindy.donoho@enron.com

 115 | Lisa      | Gang     | lisa.gang@enron.com

 127 | Kenneth   | Lay      | kenneth.lay@enron.com

 107 | Louise    | Kitchen  | louise.kitchen@enron.com

  43 | Larry     | Campbell | larry.f.campbell@enron.com

(5 rows)

testdb=# 

testdb=#


testdb=# alter role gpadmin password 'newpassword';


testdb=# \q

[gpadmin@mdw ~]$ 







12. IP, 네트워크 인터페이스 확인하기: ifconfig



[gpadmin@mdw ~]$ ifconfig
eth0      Link encap:Ethernet  HWaddr 00:0C:29:4E:9B:86  
          inet addr:192.168.188.131  Bcast:192.168.188.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:fe4e:9b86/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1069849 errors:0 dropped:0 overruns:0 frame:0
          TX packets:1586899 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:973723679 (928.6 MiB)  TX bytes:1690761826 (1.5 GiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:3683 errors:0 dropped:0 overruns:0 frame:0
          TX packets:3683 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:519553 (507.3 KiB)  TX bytes:519553 (507.3 KiB)

 





13. Downloads 폴더에 있는 파일을 GPDB 폴더 안으로 원격 복사하기

      : scp file_name gpadmin@ip:directory


pwd 로 현재 작업 경로 (current working directory)를 확인하고, 

cd Downloads 로 파일을 다운로드 받아놓은 Downloads 폴더로 작업 디렉토리를 변경 후에, 

scp file_name directory 로 파일을 복사하는 예제입니다. 



[MacBook-Pro:~ rfriend$ pwd

/Users/rfriend

[MacBook-Pro:~ rfriend$ cd Downloads/

[MacBook-Pro:Downloads rfriend$ scp file_name gpadmin@192.168.188.131:/home/gpadmin/gpdb

gpadmin@192.168.188.131's password: xxxxxxxx 

file_name                                         100%   72MB  79.0MB/s   00:00    

[MacBook-Pro:Downloads rfriend$ 

 




14. 문자열 패턴 검색: grep

은 다음번 포스팅에서 예를 들어서 설명하겠습니다. 




15. GPDB 버전, master, segment node configuration, MADlib 버전, PL/Language 확인하기



-- GPDB 버전 확인


# select version()


PostgreSQL 8.2.15 (Greenplum Database 4.3.18.0 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Nov 22 2017 18:54:31




-- GPDB master, segment node configuration 확인

# select * from pg_catalog.gp_segment_configuration

 




-- MADlib 버전 확인


# select madlib.version()

MADlib version: 1.12, git revision: rc/1.12-rc1, cmake configuration time: Wed Aug 23 22:33:09 UTC 2017, build type: Release, build system: Linux-2.6.18-238.27.1.el5.hotfix.bz516490, C compiler: gcc 4.4.0, C++ compiler: g++ 4.4.0


select * from madlib.migrationhistory





-- PL/Language 확인


# select * from pg_catalog.pg_language







많은 도움이 되었기를 바랍니다. 




Posted by R Friend R_Friend