SpringBoot에 JDBC로 Postgresql 연동하기

망7H·2021년 4월 9일

SpringBoot로 여러가지 프로젝트를 진행해보기 위해, 그 첫걸음인 Postgresql을 연동해보려 합니다.
이번 포스팅의 목표는 아래와 같습니다.

Local에 Postgresql 설치하고 사용하기.
SpringBoot에 Postgresql를 JDBC로 연동해서 데이터 INSERT.
SpringBoot로 이중화된 Postgresql에 데이터 전달하기.
AWS EC2에서 실행되고 있는 Postgresql(Master)에 데이터 전달해서 Postgresql(Slave)에서 데이터 복제된 것 확인하기.

0. 작업 환경 및 서버 정보

Name	Main Info	기타
IDE	IntelliJ	-
Local Postgresql	IP: 127.0.0.1 (localhost) PORT: 5432 version: 13.2	-
Local OS	macOS Big Sur	-
Postgresql (Master)	IP: 3.34.124.72 PORT: 5432	AWS EC2 인스턴스
Postgresql (Slave)	IP: 54.180.97.157 PORT: 5432	AWS EC2 인스턴스

1. Mac Postgresql 세팅하기

1) Postgresql 설치하기

※ Windows에서 테스트 하시는 경우에는 직접 구글링하시거나 이 포스팅을 참고하시면 됩니다.
brew install postgresql 명령으로 Postgresql를 설치합니다.
설치가 완료되고 나면 센스있게 버전을 확인해봅니다. postgres --version

2) 로컬 사용자 생성

주의! 여기서부터는 postgresql에 직접 접속한 상태로 진행합니다.
로컬 환경에서 psql postgres 명령으로 Postgresql에 접속해서 계정별 권한을 보겠습니다.
\du 명령으로 계정별 권한을 확인할 수 있는데 별다른 작업을 하지 않았다면 계정이 하나만 존재할 겁니다.
mac에서 Postgresql을 설치해보니, 제 pc의 사용자명(jaewonpark)이 Postgresql의 모든 권한을 가진 계정으로 생성되어 있네요.
권한을 설정하기 전에 이 계정의 패스워드를 설정해주어야 합니다.

(1) 루트 계정 비밀번호 설정

\password jaewonpark 명령으로 비밀번호를 설정해줍니다.

(2) SUPERUSER 권한을 가지는 계정 생성

CREATE ROLE admin_jwpark06 WITH LOGIN PASSWORD 'admin_jwpark06'; 명령으로 계정을 생성하면서 LOGIN 권한을 주고, PASSWORD는 admin_jwpark06으로 지정합니다.
ALTER USER admin_jwpark06 WITH CREATEDB; 명령으로 DB를 생성할 수 있는 권한을 주고,
ALTER USER admin_jwpark06 WITH SUPERUSER; 명령으로 admin_jwpark06 계정에 강해보이는 권한도 넣어주고 확인해보면 정상적으로 권한이 들어갔음을 확인할 수 있습니다.
※ LOGIN 권한은 들어가있지 않으면 'Cannot login'으로 표기됩니다.

3) 데이터베이스 생성과 권한 부여

(1) 데이터베이스 생성

CREATE DATABASE shop 명령으로 shop 이라는 데이터베이스를 만듭니다.
(쇼핑몰 데이터베이스인 것처럼 테스트 하기 위해 shop 으로 이름을 지었습니다.)

(2) 특정 유저에게 데이터베이스에 접근가능한 권한 부여

GRANT ALL PRIVILEGES ON DATABASE shop TO admin_jwpark06;
여기까지 작업이 되었다면 db에 접근할 계정의 생성을 만드는 작업은 끝났습니다.
\l 명령으로 현재 생성되어 있는 데이터베이스 목록을 확인할 수 있습니다.

2. SpringBoot 프로젝트 생성 및 설정하기

1) Project 생성

New Project > Spring Initializer에서
선택한 뒤, Dependencies는 Web 하위의 Spring Web만 체크해주고 Next ~ Finish ~ 해서 프로젝트를 생성합니다.
프로젝트 생성 초기에 빌드해주는 작업이 모두 끝났다면, Project 생성은 완료된것입니다.

2) build.gradle에 PostgreSQL JDBC Driver 추가

각종 빌드툴에 맞는 Postgresql jdbc driver를 설치하기 위해 여기로 들어갑니다.

postgresql jdbc를 검색어로 입력하고, 가장 첫번째에 있는 걸 선택합니다. (진리)
원하는 버전을 선택하시고, 아래의 사진의 textarea에 있는 내용을 몽땅 복사합니다.
SpringBoot로 생성된 프로젝트의 build.gradle 파일에 dependencies에 해당 Postgresql jdbc driver를 넣어줍니다.

3) build.gradle에 log4jdbc-log4j2-jdbc4.1 Driver 추가

스프링에서 SQL문을 실행한 로그를 효과적이고 직관적으로 볼 수 있도록 해주는 라이브러리라고 합니다. 상세한 내용은 여기를 참고해주세요.
2번과 같이 build.gradle에 추가해주시면 최종적으로 아래와 같은 모습이 됩니다.
여기까지 끝났다면 변경된 디펜던시 정보를 받아올 수 있게 실행하여 줍니다.
오류가 난다면, 콘솔에 찍힌 에러로그를 보고 해결하시면 됩니다. ~~무책임...~~

4) application.properties에 spring.datasource 설정 추가

spring.datasource에는 이번 포스팅에서 사용할 Postgresql 접속 정보를 입력합니다.

#Datasource Configuration
spring.datasource.hikari.maximum-pool-size=4
spring.datasource.url=jdbc:postgresql://localhost:5432/shop
spring.datasource.username=admin_jwpark06
spring.datasource.password=admin_jwpark06
spring.datasource.platform=postgres

현재 테스트는 로컬에서 진행중이니 url은 localhost:5432 로 Postgresql 기본 포트 5432를 입력했고, shop db를 datasource로 사용할 것임을 명시해두었습니다.
각자 생성한 사용자명과 password를 입력해주시면 됩니다.

3. PostgresSQLRunner Class 작성

1) 소스코드

/src/main/java/com/example/postgrestest/PostgresSQLRunner.java 파일을 생성합니다.

@Component
public class PostgreSQLRunner implements ApplicationRunner {
    @Autowired
    DataSource dataSource;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public void run(ApplicationArguments args) throws Exception {
        try (Connection connection = dataSource.getConnection()){
            System.out.println(dataSource.getClass());
            System.out.println(connection.getMetaData().getURL());
            System.out.println(connection.getMetaData().getUserName());

            Statement statement = connection.createStatement();
            String sql = "CREATE TABLE t_product(product_no INTEGER NOT NULL, product_name VARCHAR(255), PRIMARY KEY (product_no))";
            statement.executeUpdate(sql);
        }
        jdbcTemplate.execute("INSERT INTO t_product VALUES (1, 'Big shirt')");
    }
}

위 소스를 그대로 복사하시면 spring-boot-starter-jdbc를 build.gradle에 추가하겠다고 나오니 순순히 추가 받으시면 됩니다.

2) 실행

정상적으로 실행되었다면 아래와 같이 콘솔에 출력됩니다.
그럼, Postgresql의 shop DB, t_product TABLE에 데이터가 정상적으로 들어갔는지 확인해보겠습니다.

정상적으로 들어간 것을 확인할 수 있었습니다.

3) 한번 더 실행하면?

primary_key가 동일해지므로 DB 에러가 발생합니다.
product_no 부분을 다른 숫자로 바꿔주고 실행하더라도, 이미 생성된 테이블이라는 DB 에러가 발생합니다.
아무튼, 저희가 만든 shop DB의 t_product에는 데이터가 들어갔음을 확인할 수 있었습니다.

★아래 과정은 Postgresql 이중화하기를 읽고 오시면 좋습니다.★

4. 이중화된 DB에 적용해보기

※ 이중화된 DB가 이미 있음을 가정하고 진행하였습니다.

SpringBoot에서는 admin_jwpark06 계정을 이용해서 Postgresql(Master)에 데이터를 INSERT 하고, Postgresql(Slave)에서는 Postgresql(Master)의 replication 계정을 사용하여 데이터를 복제합니다.

1) Postgresql 실행하기 - Master & Slave

Postgresql(Master), Postgresql(Slave) 두 서버에서 sudo service postgresql start 명령을 사용하여 각각 Postgresql을 실행시켜서 복제(replication)가 일어나도록 합니다.

2) Postgresql 세팅하기 - Master

1-2-2 SUPERUSER 권한을 가지는 계정 생성부터
1-3-2 특정 유저에게 데이터베이스에 접근가능한 권한 부여까지 Master에서 동일한 작업을 진행하면서 데이터를 넣을 계정(admin_jwpark06)과 데이터베이스(shop)를 생성하겠습니다.

정상적으로 이중화가 일어났다면 아래 그림과 같이 Postgresql(Slave)에 데이터베이스(shop)이 생성되어서 접속이 될 겁니다.

3) SpringBoot의 application.properties 수정

spring.datasource.url=jdbc:postgresql://3.34.124.72:5432/shop

다른 부분은 수정할 필요 없으며, url에서 localhost 였던 부분을 Postgresql(Master)의 ip로 변경합니다.

4) 테스트하기

이제, PostgresTestApplication을 run 시켜봅니다.
Oops... 오류가 발생했습니다.
pg_hba.conf가 클라이언트의 권한에 대한 설정을 가지고 있는 파일이라는 특징을 생각해볼 때, 이 오류는 제 IP가 admin_jwpark06이라는 계정에 접근하기에는 권한이 없다는 것 같네요.

Postgresql(Master)의 /var/lib/pgsql/data/pg_hba.conf에 접근하여 아래와 같이 admin_jwpark06 에 대한 접근을 모두 허용으로 바꾸었습니다.

5) 다시 테스트하기

다시 PostgresTestApplication을 run 시켜봅니다.
에러메시지가 나오지 않았습니다. 느낌이 좋네요.

먼저, Postgresql(Master)에 데이터가 정상적으로 들어갔는지 확인해보았습니다.
정상적으로 들어간걸 확인할 수 있었습니다.
이제, 하이라이트인 이중화로 Postgresql(Slave)에 데이터가 들어갔는지 확인해보겠습니다.
정상적으로 데이터가 복제된 것을 볼 수 있습니다.

이번 포스팅은 평소 잘 쓰지 않던 SpringBoot를 사용하다보니, 손에 익지 않은 점들이 여러모로 있었습니다. 쉽게 보였던 SpringBoot에서의 Postgresql 연동이 생각보다 오래 걸렸습니다.
그래도, 이전 포스팅과 연계하여 데이터 전달을 서비스해보니 감개무량하네요.
이번 글에서는 데이터베이스를 연결하는 고전 방식의 Jdbc를 사용하였는데, 기회가 된다면 SpringBoot에서의 모든 데이터베이스 접근 기술(Jdbc, JdbcTemplate, JPA, Spring-data-jpa)을 테스트 해보면 좋을 것 같습니다.

추천!
인프런 강좌 중 스프링 입문로 올려진 무료 강좌를 보시면 SpringBoot에서 사용하는 데이터베이스 접근 기술을 모두를 다뤄주는 강의가 있습니다. 강좌 보러가기