이번 포스팅에서는 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 user_id@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
 




 

 

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

 

728x90
반응형
Posted by Rfriend
,