이번 포스팅에서는 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
반응형
