Ubuntu 18.04, Docker에서 Postgresql, Pgadmin4 활용하기 (외부 csv.gz 파일 import하기)

제곧내입니다. 바로 시작하겠습니다!

 

하고자 하는 작업은 다음과 같습니다.

 

• Docker 컨테이너에서 Postgresql 서버 열기
• 해당 Postgresql 서버에서 Database, Table 만들기
• 해당 Table에 외부 csv.gz 파일 import 하기
• 웹 기반 Pgadmin4에서 작업하기

 

 

 Docker Container에서 Postgresql 서버 열기

 

먼저 postgres 이미지를 다운 받습니다.

docker pull postgres

 

아래 명령어로 임의의 빈 폴더(pguser) 두 개를 만들고, 컨테이너를 생성합니다.

mkdir pguser # 작업할 파일들을 위치시킴
mkidr pgbase # postgres 실행의 베이스가 되는 폴더
docker run -it -v pgbase:/var/lib/postgresql/data -v pguser:/jmdata (원하는 폴더명) \ 
	-e POSTGRES_PASSWORD=[사용할 패스워드] --name [컨테이너 이름] -p 5432:5432 postgres

 

저는 jmkim_postgres 라는 이름의 컨테이너를 생성했습니다.

새 터미널을 열고, 아래 명령어로 해당 컨테이너에 접근할 수 있습니다.

docker exec -it jmkim_postgres bash

 

아래 명령어들을 순서대로 입력합니다.

apt-get update
apt-get install sudo
sudo -u  postgres -i
psql -U postgres

 

이제 여기에 SQL 문을 입력하면 됩니다. 

 

잘 나오는 것을 확인할 수 있습니다!

 

 

 

 해당 Postgresql 서버에서 Database, Table 만들기

 

postgres=# CREATE USER junmokim PASSWORD 'junmokim' SUPERUSER;
CREATE ROLE

postgres=# CREATE DATABASE mimic3db OWNER junmokim;
CREATE DATABASE

postgres=# \c mimic3db junmokim
You are now connected to database "mimic3db" as user "junmokim".

mimic3db=# CREATE TABLE PUBLIC.MICROBIOLOGYEVENTS (
ROW_ID bigint, SUBJECT_ID bigint, HADM_ID bigint, CHARTDATE timestamp, 
CHARTTIME timestamp, SPEC_ITEMID float, SPEC_TYPE_DESC varchar(255), 
ORG_ITEMID float, ORG_NAME varchar(255), ISOLATE_NUM float, 
AB_ITEMID float, AB_NAME varchar(255), DILUTION_TEXT varchar(255), 
DILUTION_COMPARISON varchar(255), DILUTION_VALUE float, INTERPRETATION varchar(255)
);
CREATE TABLE

 

저는 MIMIC-III Clinical Database를 postgres 서버에 옮기려고 하고 있습니다.

이 데이터베이스의 한 테이블을 만든 코드입니다.

 

mimic3db 라는 이름의 데이터베이스를, microbiologyevents 라는 이름의 테이블을 생성했습니다.

 

 

 

 해당 Table에 외부 csv.gz 파일 import 하기

 

다음 명령어로 간단하게 옮길 수 있습니다.

mimic3db=# \copy TABLE_NAME FROM PROGRAM 'gzip -dc [파일경로]' DELIMITER ',' CSV HEADER NULL ''

 

그런데 마운팅한 폴더 pguser가 권한 때문에 접근이 안되므로 chmod 명령어로 권한을 조정해주고 위 명령어를 실행해주면 데이터가 잘 옮겨집니다.

 

 

 

 웹 기반 Pgadmin4에서 작업하기

 

pgadmin4 이미지를 다운받습니다.

docker pull dpage/pgadmin4

 

아래의 명령어로 pgadmin4의 컨테이너를 생성해줍니다.

docker run --name [컨테이너명] -e "PGADMIN_DEFAULT_EMAIL=[사용자 이메일]" \
	-e "PGADMIN_DEFAULT_PASSWORD=[사용자 비밀번호]" -p [포워딩할 포트번호]:80 -d dpage/pgadmin4

 

이제 크롬 등의 브라우저에 [localhost 또는 서버 IP]:[포트번호] 를 입력하면 아래와 같은 창이 나오게 됩니다.

 

 

컨테이너 생성 시에 입력했던 메일과 비밀번호를 입력하여 접속하고, DB 모양의 Add New Server를 클릭합니다.

 

 

General 탭에서의 Name은 원하는대로 설정하면 됩니다.

 

 

Connection 탭에서는 Host name/address에 localhost 또는 서버 IP 주소를 입력해주고, Username에는 postgres를,

Password에는 postgres 컨테이너를 만들 때 설정한 비밀번호를 입력하면 됩니다.

 

 

그리고 Save를 눌러주면 아래와 같은 창으로 넘어가게 됩니다.

 

 

왼쪽에 제가 먼저 만들어뒀던 mimic3db 데이터베이스와, microbiologyevents 테이블이 잘 있는 것을 확인할 수 있습니다.

 

 

---

 

여기까지 Docker에서 postgresql과 pgadmin을 사용하는 방법, 외부 데이터를 import 하는 방법까지 살펴보았습니다!

 

감사합니다!

 

 

 

출처: 

https://physionet.org/content/mimiciii/1.4/

https://dev.to/steadylearner/how-to-set-up-postgresql-and-pgadmin-with-docker-51h

https://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist

https://judo0179.tistory.com/96

https://blog.knowledgebox.online/linux/lnx-postgres-with-docker/

https://codehunter.cc/a/postgresql/loading-zipped-csv-filename-csv-gz-file-into-postgresql-table

https://ehdvudee.tistory.com/25#drawer