PostgreSQL 기초

개요

프로젝트에서 평소 사용하던 MySQL 대신 PostgreSQL을 DB로 사용하도록 요구 사항이 있었기 때문에 PostgreSQL에 대한 간단한 사용법을 배우기로 했다.

 

PostgreSQL?

객체-관계형 데이터베이스 시스템(ORDBMS)으로 다른 RDBMS와는 다르게 비관계형 및 관계형 데이터 유형을 모두 지원한다.

 

POSTGRES는 1970년대 초에 시작된 오픈 소스 SQL 관계형 데이터베이스 프로젝트인 INGRES의 후속으로 1986년에 처음 개발되어 1994년 SQL에 대한 지원을 추가한 후 PostgreSQL이 탄생했다.

 

기능 및 제한

RDBMS가 제공하는 기본적 기능인 트랜잭션과 ACID(Atomicity, Consistency, Isolation, Durability) 외에도 많은 기능을 지원하고 있다

• Nested transaction(savepoints)
• Point in time recovery
• Online/hot backups, Parallel restore
• Rules system(query rewrite system)
• B-tree, R-tree, hash, GiST method indexes
• Multi-Version Concurrency Control(MVCC)
• Tablespaces
• Procedural Language
• Information Schema
• I18N, L10N
• Database & Column level collation
• Array, XML, UUID type
• Auto-increment (sequences)
• Asynchronous replication
• LIMIT/OFFSET
• Full text search
• SSL, IPv6
• Key/Value storage
• Table inheritance

기본 제한 사항

 

내부 구조

 

프로세스 구조

 

 

• 클라이언트의 요청이 들어오면 구문 분석과정을 통해 Parse Tree를 생성하고 의미 분석과정을 통해 새로운 트랜잭션을 시작하고 Query Tree를 생성한다.
• 서버에 정의된 Rule에 따라 Query Tree가 재생성되고 실행 가능한 여러 수행 계획 중 가장 최적화된 Plan Tree를 생성한다.
• 서버는 이를 수행하여 요청에 대한 결과를 클라이언트로 전달하게 된다.

 

프로젝트 적용 및 발생한 문제 해결

PostgreSQL을 설치하고 기본 설정을 끝낸 후(설치 및 설정에 대한 내용은 생략) 인텔리제이를 사용해 SpringBoot에서 Data Source로 추가했다.

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/test
    username: yong
    password: 3412
    driver-class-name: org.postgresql.Driver
  jpa:
    show-sql: true
    database: postgresql
    hibernate:
      ddl-auto: create

 

아래 예시와 같이 application.yml 파일을 설정하고 애플리케이션을 실행했더니 아래와 같은 에러가 발생했다.

Error executing DDL "create table p_user (is_deleted BOOLEAN DEFAULT false not null, created_at TIMESTAMP not null, deleted_at TIMESTAMP, updated_at TIMESTAMP not null, created_by varchar(255), deleted_by varchar(255), password varchar(255) not null, region_id varchar(255) not null, role varchar(255)
not null check (role in ('CUSTOMER','OWNER','MANAGER','MASTER')), updated_by varchar(255), user_id varchar(255) not null, primary key (user_id))" via JDBC [오류: public 스키마(schema) 접근 권한 없음

 

에러 내용을 확인해 보면 현재 연결된 사용자가 public 스키마에 접근할 수 있는 권한이 없어서 table을 만들 수 없다고 한다.

SQL Shell(psql)을 사용해 postgreSQL에 생성된 db 사용 유저에게 권한을 부여하기로 하고 아래의 명령어를 입력했다.

GRANT ALL PRIVILEGES ON DATABASE test TO yong;
-- 특정 스키마 (예: public)에 대한 권한 부여
GRANT ALL ON SCHEMA public TO yong;

-- 데이터베이스의 모든 테이블에 대한 권한 부여
GRANT ALL ON ALL TABLES IN SCHEMA public TO yong;

-- 데이터베이스의 모든 시퀀스에 대한 권한 부여
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO yong;

 

그 후 애플리케이션을 재실행 했지만 방금과 같은 에러가 다시 발생해 SQL Shell을 통해 유저 권한을 확인한 결과 유저의 권한이 제대로 부여되지 않았다.

 

원인을 알 수 없지만 유저를 생성한 이후 권한 부여는 되지 않았지만 유저를 생성할 때 권한을 부여하면 정상적으로 생성된다.

 

 

권한을 가진 유저를 생성한 후 애플리케이션을 동작 시켰더니 Entity를 통해 정의한 Table이 JPA를 통해 정상적으로 생성되었다.

 

이후 기능 테스트를 위해 더미 데이터를 생성해서 Table에 저장하고 주문을 등록하는 기능을 POSTMAN을 사용해 테스트 해보려 했는데 아래와 같은 에러가 발생했다.

{
    "message": "could not execute statement [오류: \"p_order\" 이름의 릴레이션(relation)이 없습니다\n  Position: 13] [insert into p_order (created_at,created_by,deleted_at,deleted_by,delivery_address,is_deleted,order_state,order_type,uuid,request_order,shop_id,updated_at,updated_by,user_entity_user_id) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?) returning order_id]; SQL [insert into p_order (created_at,created_by,deleted_at,deleted_by,delivery_address,is_deleted,order_state,order_type,uuid,request_order,shop_id,updated_at,updated_by,user_entity_user_id) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?) returning order_id]",
    "statusCode": 500
}

 

원인을 파악해보니 OrderEntity에 들어가는 Enum 타입에 @Enumerated 애너테이션을 추가하지 않았었다, 기초적인 실수이지만 처음 사용하는 아키텍처 구조에서 개발을 진행하다보니 간단한 부분도 확인하지 못했었다.

@Enumerated(EnumType.STRING)

 

기술이나 라이브러리를 처음 사용하는 상황에서는 더 꼼꼼하게 코드를 확인하는 습관을 들여야 할 것 같다.

 

추후 개선 사항

• PostgreSQL에 대해 더 공부해야 될 거 같다.
• 런타임에 일어날 수 있는 에러를 최소화 하기 위해 코드를 완성하고 문제점이 없는지 확인하는 습관을 길러야 겠다.

참고
https://mangkyu.tistory.com/71
https://d2.naver.com/helloworld/227936
https://sujinisacat.tistory.com/8

chatGPT