Engineering_S/W & H/W

 이번에는 기존의 docker-compose를 이용하여 server에 postgers를 연결하도록 하겠습니다. 이전글 : docker-compose NodeJS(NestJS) 적용하기 -1 진행하기 전에 Docker와 Docker-Compose가 설치되 있어야 하고 OS는 Ubuntu(Linux)를 사용합니다.  docker install by terminal : ubuntu 20.04 docker-compose Ubuntu Install 먼저 해당 프로젝트에 '@nestjs/typeorm' 'pg'를 yarn으로 설치해 줍니다. $ yarn add @nestjs/typeorm pg typeorm copy 해당 typeorm 및 pg연결은 아래 링크를 참고해 주시기 바랍니다. mysql이라 하더라도 pg(postgres)으로 변경하면 됩니다. 링크1 : nest js -5 Connect to DataBase(mysql) with TypeORM 'docker-compose'파일에 변경사항이 있으니 참고해 주시기 바랍니다. --build를 추가하여 변경된 코드를 반영합니다. $ docker-compose up --build -d copy docker-compose up --build -d 이후 진행을 하면 database와 정상적으로 연결된 것을 알수 있습니다. 해당 gitHub(branch : docker-compose_NodeJS_NestJS_적용하기-1 -> docker-compose_NodeJS_NestJS_적용하기-2) 이후글 : docker-compose NodeJS(NestJS) 적용하기 -3

DB를 조작하다보면 실수하는 경우가 있습니다. 만약 지우거나 수정하면 안되는 Record를 건드리게 되면 사태는 상당히 심각하게 돌아갈수 있습니다.  이때 postgres의 'Begin transaction'을 미리 사용하면 'Begin transation'이전의 상태로 복원할수 있습니다. $ Begin transation copy 'Begin transaction'을 사용하고 coffees Table을 조회했습니다. 이제 id가 4인 커피를 지우도록 하겠습니다. $ DELETE FROM coffees WHERE id=4; copy 위 사진처럼 4를 지웠습니다. 하지만 확인해보니 4를 지우는 것이 아니라 5를 지우는 것이였습니다. 이때 다행히 'Beign transaction'상태이기 때문에 'rollback'를 입력하면 됩니다. $ rollback copy 롤백 된것을 확인할수 있습니다. 4번 커피 Record도 무사한 것을 알수 있습니다. 하지만 정말 4번을 지워햐 하는 것이 맞다면 'commit'를 실행하면 됩니다. $ commit; copy 'commit'를 입력하면 원복이 불가능 합니다. 따라서 'commit'를 입력하기 전에 한번더 생각해 주시기 바랍니다.

 사전에 Docker및 Docker-Compose가 설치되어 있어야 합니다. postgres official docker image link : https://hub.docker.com/_/postgres docker-compose로 컨테이너를 만들기전 .yaml파일을 작성합니다. # ./docker-compose.yaml # services에서는 여러개의 컨테이너를 생성할수 있습니다. services : postgres : # 컨테이너의 베이스가 될 이미지를 받는다. (위 예제에서는 postgres:12) image : "postgres:12" # postgres의 데이터를 관리하기 위해 볼륨을 사용자가 네이밍 해서 관리한다. volumes : - data:/var/lib/postgresql/data # 환경변수(environment)를 직접 작성할수 있다. #environment: # POSTGRES_USER: alex # POSTGRES_PASSWORD: bestpassword # - POSTGRES_USER=max # 각각의 환경변수를 지정하지 않고 파일을 이용하여 지정 할수 있다. env_file : # yaml의 상대경로로 환경변수 파일을 찾는다. - ./.postgres.env # 네트워크는 default로 자동 생성해준다 #networks: # - test1_net # 외부에 포트를 노출시킨다. postgres default포트는 5432이고 4500포트로 외부 노출시킨다. ports : - "4500:5432" # 네이밍 볼륨이 있을시 반드시 루트에 한번더 volumes 안에 해당 네이밍 볼륨 이름을 넣는다. volumes : # postgres는 data라는 네이밍 볼륨을 사용하기 때문에 아래에 추가해 준다. data :...

  postgres에서 해당 테이블을 생성합니다. $ CREATE TABLE users(id INT PRIMARY KEY NOT NULL, name TEXT NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT now()) 이후 아래 레코드를 입력합니다. $ INSERT INTO users(id, name, created_at) VALUES (1,'알렉스', '2022-10-12 01:00:00+09'), (2,'존', '2022-10-12 01:10:00+09'), (3,'피닉스', '2022-10-12 11:20:00+09'), (4,'폴', '2022-10-13 01:00:30+09'), (5,'스카', '2022-10-13 07:30:00+09'), (6,'알폰스', '2022-10-14 01:00:00+09'), (7,'미미', '2022-10-15 01:00:00+09'), (8,'크레이토스', '2022-10-15 01:00:00+09'), (9,'제우스', '2022-10-15 01:10:00+09'), (10,'아테나', '2022-10-16 01:00:00+09') 테이블을 조회하면 아래 사진과 같이 나오는 것을 알수 있습니다. 이제 날짜별로 생성된 유저수를 확인할려고 합니다. 현재는 날짜 기준으로 생성날자를 카운트 할려고한다. 문제는 날짜가 같아도 생성 시간이 달라서 GROUP BY를 하지 못합니다. 이때 DATE_TRUNC를 사용합니다. $ SELECT DATE_TRUNC('day', "created_at") AS date, COUNT(*) FROM users GROUP BY date...

  사진1) SQL 인스턴스 만들기 GCP에서 SQL 인스턴스를 만들기 버튼을 클릭합니다. 사진2) DBMS 선택 해당 데이터베이스에 사용할 DBMS을 선택합니다. (위 글에서는 postgreSQL을 선택했습니다.) 사진3) DBMS 세부설정 DBMS이름 및 사양 등을 설정합니다. 사진4) DBMS 리전 선택 사진5) 영역가용성 선택 사진6) 옵션설정 사진7) 외부에서 접근 가능한 IP 추가 사진8) IP추가 저는 터미널로 작업하는것을 선호하기 때문에 사진 7,8과 같이 승인 네트워크를 추가합니다. 해당 IP주소는 터미널로 DBMS에 접근이 가능합니다. 사진9) 데이터 보호 옵션 사진9에서는 기본적으로 '삭제 보호 사용 설정'이 되어 있습니다. 하지만 이 글에서는 삭제를 보여주기 위해 해제합니다. 사진10) 데이터 베이스 생성 사진11) 데이터 베이스 접근 사진11과 같이 DB가 생성되면 터미널로 접속이 가능합니다. postgres의 터미널 접속방법에 대해서는 아래 링크를 참고해 주시기 바랍니다. 링크 : PostgreSQL CLI 사진12) 데이터 베이스 삭제 선택 사진13) 데이터 베이스 삭제 절차 사진14) 삭제 완료 사진12 ~ 14까지 데이터베이스 삭제 방법입니다. 다만 사진9에서 삭제보호가 활성화 되어 있다면 비활성화 한다음에 삭제가 가능합니다.

 보통 DBMS를 셋팅하는데 많은 시간이 걸립니다. 특히 PostgreSQL을 처음 셋팅할때는 기본이 Local에서만 접속할수 있고 config파일을 수정하고 재시작 해야지만이 외부에서 접속이 가능합니다. 위 작업은 기본적으로 Docker가 설치되 있어야 합니다. 설치방법은 아래 링크를 참고해 주시기 바랍니다. 링크 : docker install by terminal : ubuntu 20.04 1) 도커 컨테이너로 postgres생성하기 - postgresql 이미지를 이용하여 DataBase 컨테이너를 만들기 $ sudo docker run -p [접속할려는 포트]:5432 --name [컨테이너 이름] -e POSTGRES_PASSWORD=[비밀번호] -e TZ=Asia/Seoul -d postgres:12 copy 사진1) postgres 컨테이너 생성 사진2) postgres 접속 TZ=Asia/Seoul은 서울 타임존을 말한다. 필요시 다른곳으로도 옮길수 있습니다. 그리고 '-d postgres:12'은 postgres버전 12를 사용한다는 뜻입니다. 다른버전도 사용할수 있습니다. 그리고 컨테이너 안에 있는 데이터는 '/var/lib/postgresql/data'안에 있습니다. 컨테이너 밖으로 저장해야 할시 '-v [로컬경로]:/var/lib/postgresql/data'를 컨테이너 생성할때 추가하면 됩니다. 더 좋은점은 초기 셋팅과 달리 외부에서도 DB에 접근이 가능하다는 것입니다.

  위 사진처럼 커피(coffees)와 향(flavor) 테이블 사이에 Join 테이블이 있습니다. 해당 Join테이블은 한개의 row에 하나의 flavor만 조회가 가능합니다. 만약 2개 이상의 향이 있는 커피를 찾는다면 아래 쿼리문을 사용해야 합니다. -- SQL Query1  SELECT DISTINCT title   FROM coffees JOIN coffees_flavors_flavor ON coffees.id = "coffeesId"                 JOIN flavor ON flavor.id = "flavorId"  WHERE flavor.name = 'choco' OR flavor.name = 'banana'; Query1을 사용하면 Join, OR문을 이용하여 choco 또는 banana가 있는 레코드를 조회합니다. 문제는 choco는 있지만 banana가 없거나 반대의 경우도 모두 조회를 한다는 것입니다. 이를 위해서 GROUP BY를 사용하며 해당 갯수를 count해서 조회해야 합니다. -- SQL Query2   SELECT DISTINCT title, COUNT(*) as count   FROM coffees JOIN coffees_flavors_flavor ON coffees.id = "coffeesId"                 JOIN flavor ON flavor.id = "flavorId"  WHERE flavor.name = 'choco' OR flavor.name = 'banana'  GROUP BY title  HAVING flavor.count = 2; Query2를 사용하면 구할려고 하는 해당 title을 조회할수 있습니다.

 postgres의 컬럼 설정에서는 배열을 넣을수가 있다.  컬럼을 생성할때 위 사진처럼 컬럼 arr을 숫자형 배열로 지정할수 있다 하나의 레코드를 삽입하면 위 사진처럼 id및 arr이 배열로 저장되게 된다. 이때 동일한 배열을 찾을때 WHERE을 이용하여 찾는다. 하지만 모든 배열을 아는것이 아니면 위방법으로 레코드를 조회할수 없다. arr을 텍스트로 변환한 다음 LIKE을 이용하여 조회를 해야 합낟. 이때 arr은 배열에서 문자열로 조회할때만 전환된다. 문자열 배열또한 만들수 있다. 문자열 배열또한 생성, 수정 삭제, 조회가 가능하며 LIKE을 사용할려면 조회할 배열을 text로 변환후 확인해야 한다.

이번 postgres을 사용하다가 해당 쿼리문을 사용할수 없어서 확인하다가 확장 모듈이 필요하다는 것을 알고 기록을 위해 블로그로 작성하였습니다.  테이블을 생성하는 도중 column의 데이터 타입을 uuid_generate_v4()로 설정을 하고 테이블을 생성할려고 할때 아래 사진과 같이 에러가 발생했습니다. 사진1) 에러 상황 위 에러가 발생하면 가장 먼저 해당 extension이 사용 가능하지 확인을 해야 합니다. psql에서 설치 가능한 extension을 확인한다. $ SELECT * FROM pg_available_extensions; copy 사진2) 설치 가능한 extension 사진2에서 가장 마지막에 있는 'uuid-ossp'이 있습니다. 이 확장 모듈이 uuid생성을 도와줍니다. 이제 'uuid-ossp'를 설치해 보도록 하겠습니다. psql에서 extension가 존재하지 않을때 설치. $ CREATE EXTENSION IF NOT EXISTS "extension name"; copy 위 블로그에서는 uuid를 생성할수 있어야 하기 때문에 'uuid-ossp'를 설치하도록 하겠습니다. 해당 sql문은 CREATE EXTENSION IF NOT EXISTS "uuid-ossp" 으로 실행하면 됩니다. 사진3) "uuis-ossp" 설치 사진4) 생성 완료 원하던 쿼리문이 정상적으로 실행 된것을 확인할수 있었습니다.  아래는 실행한 해당 쿼리문 입니다 CREATE TABLE users (   id UUID NOT NULL DEFAULT uuid_generate_v4(),   email VARCHAR NOT NULL,   password VARCHAR NOT NULL,   username VARCHAR NOT NULL,   socialSignUP Boolean NOT NULL DEFAULT false,   soc...

 PostgreSQL작업을 하다보면 DB를 지워야 할때가 있습니다. 그런데 아래와 같은 에러가 발생할때가 있습니다. 사진1) DB삭제시 발생에러 사진1과 같이 다른곳에서 DB의 세션으로 묶여있으면 그 세션을 종료할때까지 DB를 삭제할수가 없습니다. 그래서 강제 종료를 해야 합니다. PostgreSQL세션 강제 종료하기 $ SELECT      pg_terminate_backend(pid)  FROM      pg_stat_activity  WHERE      -- don't kill my own connection!     pid <> pg_backend_pid()     -- don't kill the connections to other databases     AND datname = 'test2'; copy 사진2) DB강제 접속 종료 이제 접속이 차단된 상태에서 DB를 지우도록 하겠습니다. 사진3) DB 정상 제거 이제 DB를 지울수가 있습니다. 위 코드는 DB 이름이 test1인 경우로 진행한 것입니다. 필요에 따라 변경이 가능합니다.

  Ubunt Terminal을 이용하여 PostgreSQL제어 1. DB기능 1) Dump파일 받기 - 해당 RDS에 설치된 postgres서버의 Dump파일을 받습니다(AWS의 RDS에서 터미널로 받음), sql로 받음 $ pg_dump -h [RDS address] -U [DB user] [DB name] > [file name] copy 2) Dump파일 업로드하기 - 해당 RDS에 설치된 postgres서버의 Dump파일을 업로드 합니다.(AWS의 RDS의 DB는 비어있어야 합니다.), sql로 백업 $ psql -h [RDS address] -U [DB user] -d [upload DB name] -f [file name] copy tar확장자로 daump파일 백업하고 업로드 하기(click) 1) Dump파일 받기 - 해당 RDS에 설치된 postgres서버의 Dump파일을 받습니다(AWS의 RDS에서 터미널로 받음), tar로 받음 $ pg_dump -U [DB user] -h [PostgreSQL address] -p [Port Number] -d [DB name] -f [file path with .tar] -F t -W copy OR Including Password $ PGPASSWORD="password" pg_dump -U [DB user] -h [PostgreSQL address] -p [Port Number] -d [DB name] -f [file path with .tar] -F t -W copy 2) Dump파일 백업 - 해당 RDS에 설치된 postgres서버의 Dump파일을 업로드(AWS의 RDS에서 터미널로 받음), tar로 업로드 주의 : 해당 cli는 기존의 DB의 테이블을 지우고 다시 백업합니다. $ pg_restore -cC -h [Pos...

 안녕하세요. 알렉스 입니다. 이번에 업무를 보다가 해결한 문제를 블로그 글로 작성할려고 합니다. 예전에 가격을 'VARCHAR'로 지정해서 그것을 모두 'Integer'로 변경해야 했습니다. 그런데 이때 데이터의 변환 없이 그대로 바꿔야 했죠.  입력되어있는 데이터는 모두 숫자로 구성된 문자로 단순히 문자를 숫자로 변환해주면 되는 것이였습니다. 사진1) payment table 현재 테이블에서 gamePrice가 있는데 종류가 'character varying'으로 되어있습니다. 즉 'VARCHAR'로 되어 있는 것입니다. 사진2) 2개의 record 사진2에서 2개의 레코드가 DB에 저장된 것을 알수 있습니다. 이제 보통 column의 속성을 바꿀때 쓰는 sql문을 사용해 보겠습니다. 1. Table의 column속성을 변경하기 - 해당 컬럼속성을 변경합니다. 아래는 VARCHAR속성에서 INTEGER로 변경하는 것입니다. ALTER TABLE payment ALTER COLUMN "gamePrice" TYPE integer; copy 예상대로 integer로 전환이 안된다고 나옵니다. 그런데 여기서 힌트가 있는데 "USING "gamePrice"::integer"를 쓰라고 하네요. 이걸 추가해서 사용하도록 하겠습니다. 2. Table의 column속성을 변경하기(기존 데이터 유지) - 해당 컬럼속성을 변경합니다. 아래는 VARCHAR속성에서 INTEGER로 변경하는 것입니다. ALTER TABLE payment ALTER COLUMN "gamePrice" TYPE integer USING "gamePrice"::integer; copy      사진4) 변경되지 않은 record 위 사진 3,4를 보면 column값이 성공적으로 VARCHAR -> INTEGER로 변경...