일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
- JavaScript
- Spring Boot
- Python
- IntelliJ
- Tomcat
- 설정
- error
- SpringBoot
- Docker
- spring
- Core Java
- STS
- Source
- Eclipse
- Thymeleaf
- Open Source
- jpa
- maven
- git
- MSSQL
- 오픈소스
- ubuntu
- MySQL
- oracle
- PostgreSQL
- 문서
- myBatis
- AJAX
- Exception
- JDBC
- Today
- Total
헤르메스 LIFE
[SpringBoot] Docker에 PostgreSQL 설치 / 연결하기 본문
IDE : IntelliJ
JDK : OpenJDK 11
Framework : Spring Boot 2.5.2
Database : PostgreSQL 최신버전 ( 라이센스도 소스공개의무도 없음 )
첨부파일이 MySqlRunner 로 되어있는데.. MySQL 접속테스트 중 소스만 바뀐거라.. ㅎㅎ
Docker 설치
https://hermeslog.tistory.com/497?category=302346
Docker에 PostgreSQL 설치 ( 영문버전 )
? postgresql 을 다운로드
C:\>docker pull postgres
? 다운로드된 Images 확인
C:\>docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql latest d1dc36cf8d9e 9 months ago 519MB
postgres <none> e94a3bb61224 11 months ago 374MB
? Docker postgres 컨테이너 생성
? -d : 백그라운드 실행
? -p 포트 포딩 5432번 포트로 들어온 내용은 5432 번 포트로 포딩을 해주겟다는 내용
? -e 환경변수 설정 POSTRGRES_PASSWORD= 내용을 password로 설정
? --name Container 이름을 설정해줍니다.
? docker exec -> run a command on an existing/running container
? docker run -> create a new container from an image, and run the command there
C:\>docker run -p 5432:5432 --name postgres_boot -e POSTGRES_DB=springboot -e POSTGRES_USER=hermeswing -e POSTGRES_PASSWORD=pass -d postgres
? docker에 설치된 컨테이너 목록 조회
C:\>docker ps -a
? Docker 접속
C:\>docker exec -it postgres_boot bash
root@38b74b264559:/#
? Docker OS 환경 확인
? grep -n . /etc/*-release
root@38b74b264559:/# grep -n . /etc/*-release
1:PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"
2:NAME="Debian GNU/Linux"
3:VERSION_ID="11"
4:VERSION="11 (bullseye)"
5:VERSION_CODENAME=bullseye
6:ID=debian
7:HOME_URL="https://www.debian.org/"
8:SUPPORT_URL="https://www.debian.org/support"
9:BUG_REPORT_URL="https://bugs.debian.org/"
? locale 설치하기
? apt-get -y update
? apt-get install -y locales
root@38b74b264559:/# apt-get -y update
Get:1 http://security.debian.org/debian-security bullseye-security InRelease [48.4 kB]
Get:2 http://deb.debian.org/debian bullseye InRelease [116 kB]
Get:3 http://deb.debian.org/debian bullseye-updates InRelease [44.1 kB]
Get:4 http://security.debian.org/debian-security bullseye-security/main amd64 Packages [237 kB]
Get:5 http://deb.debian.org/debian bullseye/main amd64 Packages [8,183 kB]
Get:6 http://deb.debian.org/debian bullseye-updates/main amd64 Packages [14.6 kB]
Get:7 http://apt.postgresql.org/pub/repos/apt bullseye-pgdg InRelease [116 kB]
Get:8 http://apt.postgresql.org/pub/repos/apt bullseye-pgdg/main amd64 Packages [269 kB]
Get:9 http://apt.postgresql.org/pub/repos/apt bullseye-pgdg/14 amd64 Packages [2,577 B]
Fetched 9,031 kB in 5s (1,688 kB/s)
Reading package lists... Done
root@38b74b264559:/# apt-get install -y locales
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following packages will be upgraded:
locales
1 upgraded, 0 newly installed, 0 to remove and 56 not upgraded.
Need to get 4,086 kB of archives.
After this operation, 0 B of additional disk space will be used.
Get:1 http://deb.debian.org/debian bullseye/main amd64 locales all 2.31-13+deb11u5 [4,086 kB]
Fetched 4,086 kB in 0s (9,814 kB/s)
debconf: delaying package configuration, since apt-utils is not installed
(Reading database ... 12073 files and directories currently installed.)
Preparing to unpack .../locales_2.31-13+deb11u5_all.deb ...
Unpacking locales (2.31-13+deb11u5) over (2.31-13+deb11u2) ...
Setting up locales (2.31-13+deb11u5) ...
debconf: unable to initialize frontend: Dialog
debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 78.)
debconf: falling back to frontend: Readline
Generating locales (this might take a while)...
Generation complete.
? 현재 사용할 수 있는 locale 확인
? locale -a
root@38b74b264559:/# locale -a
locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_MESSAGES to default locale: No such file or directory
locale: Cannot set LC_COLLATE to default locale: No such file or directory
C
C.UTF-8
POSIX
? debian OS 에서 locale 생성
? localedef -f UTF-8 -i ko_KR ko_KR.UTF-8
root@38b74b264559:/# localedef -f UTF-8 -i ko_KR ko_KR.UTF-8
root@38b74b264559:/# locale -a
C
C.UTF-8
POSIX
ko_KR.utf8
? Docker 컨테이너 실행 / 접속
C:\>docker exec -it postgres_boot bash
root@38b74b264559:/# >su - postgres
root@38b74b264559:/# >psql --username hermeswing --dbname springboot
? Docker 컨테이너 멈춤
C:\>docker stop postgres_boot
? Docker 컨테이너 시작
C:\>docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
68a29cd6a49a redis "docker-entrypoint.s…" 21 hours ago Exited (0) 17 hours ago redis_boot
8870b3eecebd postgres-ko:14.1 "docker-entrypoint.s…" 21 hours ago Exited (0) 17 hours ago postgres_boot
? Docker 컨테이너 시작
# docker start -a [CONTAINER ID 또는 NAMES]
C:\>docker start -a postgres_boot
? Docker 컨테이너 삭제
C:\>docker rm postgres_boot
? 테이블 목록조회 : \l, \list
springboot=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+------------+----------+------------+------------+---------------------------
postgres | hermeswing | UTF8 | en_US.utf8 | en_US.utf8 |
springboot | hermeswing | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | hermeswing | UTF8 | en_US.utf8 | en_US.utf8 | =c/hermeswing +
| | | | | hermeswing=CTc/hermeswing
template1 | hermeswing | UTF8 | en_US.utf8 | en_US.utf8 | =c/hermeswing +
| | | | | hermeswing=CTc/hermeswing
(4 rows)
Default 설정으로 설치된 PostgreSQL에서도 한글은 잘 등록됩니다.
PostgreSQL에서 EUC-KR 을 지원하기 때문에 한글에 문제가 없으리라 생각됩니다. 하지만, Locale 문제는 어디서 문제가 생길지 모르기 때문에 ko_KR.UTF-8로 설정하는게 좋을 것 같습니다.
생성하는 방법은 여기를 참고했습니다.
Docker에 PostgreSQL 설치 ( 한글버전 )
Dockerfile 생성 ( C:\Temp 폴더에 Dockerfile 파일을 생성했습니다. )
# 한글 Docker 파일을 생성할 폴더로 이동
$ cd C:\Temp
# 한글 Docker 파일이 생성될 폴더 생성
$ copy con Dockerfile
# 사설저장소를 사용하는 경우 아래 FROM 수정 필요
# FROM 사설저장소주소:포트/postgres:14.1
FROM postgres:14.1
RUN ln -sf /usr/share/zoneinfo/Asia/Seoul /etc/localtime && \
sed -i 's/# ko_KR.UTF-8 UTF-8/ko_KR.UTF-8 UTF-8/' /etc/locale.gen && \
locale-gen
ENV LANG=ko_KR.utf8 \
LC_COLLATE=ko_KR.utf8 \
POSTGRES_INITDB_ARGS=--data-checksums
^z
Docker 이미지 생성 ( Dockerfile 이 존재하는 폴더에서 명령을 실행해야 합니다. )
C:\Temp>docker build -t postgres-ko:14.1 .
[+] Building 9.6s (6/6) FINISHED
=> [internal] load build definition from Dockerfile 0.0s
=> => transferring dockerfile: 404B 0.0s
=> [internal] load .dockerignore 0.0s
=> => transferring context: 2B 0.0s
=> [internal] load metadata for docker.io/library/postgres:14.1 3.6s
=> [1/2] FROM docker.io/library/postgres:14.1@sha256:3162a6ead070474b27289f09eac4c865e75f93847a2d7098f718ee5a721637c4 4.7s
=> => resolve docker.io/library/postgres:14.1@sha256:3162a6ead070474b27289f09eac4c865e75f93847a2d7098f718ee5a721637c4 0.0s
=> => sha256:daa0467a6c4883c02b241fe5f4f1703245f43ccbe5bcd56a3dceddef285bf31e 4.41MB / 4.41MB 0.6s
=> => sha256:3162a6ead070474b27289f09eac4c865e75f93847a2d7098f718ee5a721637c4 1.86kB / 1.86kB 0.0s
=> => sha256:5eb5b503b37671af16371272f9c5313a3e82f1d0756e14506704489ad9900803 31.37MB / 31.37MB 1.0s
=> => sha256:da2cb49d7a8d1416cfc2ec6fb47b60112b3a2f276bcf7439ef18e7c505b83fc6 10.23kB / 10.23kB 0.0s
=> => sha256:7cf625de49eff935274ddf0944444341ef8721bb01b69c991c67924dfd76b1e4 1.80kB / 1.80kB 1.0s
=> => sha256:043c256b5dc621860539d8036d906eaaef1bdfa69a0344b4509b483205f14e63 3.04kB / 3.04kB 0.0s
=> => sha256:bb8afcc973b2a3de0135018fd8bd12fc2f56ef30a64e6e503d1a20cf35e340f3 1.42MB / 1.42MB 0.9s
=> => extracting sha256:5eb5b503b37671af16371272f9c5313a3e82f1d0756e14506704489ad9900803 0.5s
=> => sha256:c74bf40d29ee27431deec24f6d21d1a09f178335b7c25aa6fd26850bec90252a 8.05MB / 8.05MB 1.3s
=> => sha256:2ceaf201bb22921a17d7d85257c21ea29ff1da9b269e204d7a40fe2175b58913 441.57kB / 441.57kB 1.2s
=> => sha256:1255f255c0eb861462fc3bb1e26bebe7300bc593e6cba7389e9b449076993e2f 149B / 149B 1.2s
=> => sha256:d27501cd0cca7a8f831bed5caf1277ce9f6c19a2b2bc0ada26f27c875151b01d 3.06kB / 3.06kB 1.5s
=> => sha256:ff5b6d09a5d0eac1f5a838c5bb50e99529d26ae7caab8d3d9b5bcba447fc2d4f 91.23MB / 91.23MB 3.1s
=> => sha256:f635aec276456d522483a6c3428efa6706aa3eb3e3a865aae368e30aea1902d5 9.53kB / 9.53kB 1.9s
=> => sha256:a165c6729250ae2a77f7504bf7d67209aa3518643019f3971aef7bae6cc7c768 129B / 129B 1.7s
=> => extracting sha256:daa0467a6c4883c02b241fe5f4f1703245f43ccbe5bcd56a3dceddef285bf31e 0.1s
=> => extracting sha256:7cf625de49eff935274ddf0944444341ef8721bb01b69c991c67924dfd76b1e4 0.0s
=> => extracting sha256:bb8afcc973b2a3de0135018fd8bd12fc2f56ef30a64e6e503d1a20cf35e340f3 0.0s
=> => extracting sha256:c74bf40d29ee27431deec24f6d21d1a09f178335b7c25aa6fd26850bec90252a 0.2s
=> => sha256:b0aa4f86b6117692983b4129ac41216e463032962bdb5cc3b9611c7a600a6a75 199B / 199B 2.0s
=> => sha256:9efc4664d9d2ce6f8174a6ebad93ad74fe65e8337df08c642b6fe2841e388fbe 4.72kB / 4.72kB 2.1s
=> => extracting sha256:2ceaf201bb22921a17d7d85257c21ea29ff1da9b269e204d7a40fe2175b58913 0.0s
=> => extracting sha256:1255f255c0eb861462fc3bb1e26bebe7300bc593e6cba7389e9b449076993e2f 0.0s
=> => extracting sha256:d27501cd0cca7a8f831bed5caf1277ce9f6c19a2b2bc0ada26f27c875151b01d 0.0s
=> => extracting sha256:ff5b6d09a5d0eac1f5a838c5bb50e99529d26ae7caab8d3d9b5bcba447fc2d4f 1.2s
=> => extracting sha256:f635aec276456d522483a6c3428efa6706aa3eb3e3a865aae368e30aea1902d5 0.0s
=> => extracting sha256:a165c6729250ae2a77f7504bf7d67209aa3518643019f3971aef7bae6cc7c768 0.0s
=> => extracting sha256:b0aa4f86b6117692983b4129ac41216e463032962bdb5cc3b9611c7a600a6a75 0.0s
=> => extracting sha256:9efc4664d9d2ce6f8174a6ebad93ad74fe65e8337df08c642b6fe2841e388fbe 0.0s
=> [2/2] RUN ln -sf /usr/share/zoneinfo/Asia/Seoul /etc/localtime && sed -i 's/# ko_KR.UTF-8 UTF-8/ko_KR.UTF-8 UTF-8/' /etc/locale.gen && locale-gen 1.2s
=> exporting to image 0.0s
=> => exporting layers 0.0s
=> => writing image sha256:5c20638621266572abb6622a7f1a33cf111b09f92b77003b8212d5df28c002d7 0.0s
=> => naming to docker.io/library/postgres-ko:14.1 0.0s
Use 'docker scan' to run Snyk tests against images to find vulnerabilities and learn how to fix them
C:\Temp>docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
postgres-ko 14.1 5c2063862126 About an hour ago 375MB
ubuntu latest 08d22c0ceb15 5 weeks ago 77.8MB
redis latest 3358aea34e8c 5 months ago 117MB
mysql latest d1dc36cf8d9e 14 months ago 519MB
postgres <none> e94a3bb61224 16 months ago 374MB
postgres-ko Images가 생성된걸 확인 할 수 있습니다.
postgres-ko 설치
C:\Temp>docker run -p 5432:5432 --name postgres_boot -e POSTGRES_DB=springboot -e POSTGRES_USER=hermeswing -e POSTGRES_PASSWORD=pass -d postgres-ko:14.1
36e399118658fa438bba67805c3dea453f6be859b9e1fa65bd09ec16b3bb369f
C:\Temp>docker exec -it postgres_boot bash
root@36e399118658:/# grep -n . /etc/*-release
1:PRETTY_NAME="Debian GNU/Linux 11 (bullseye)"
2:NAME="Debian GNU/Linux"
3:VERSION_ID="11"
4:VERSION="11 (bullseye)"
5:VERSION_CODENAME=bullseye
6:ID=debian
7:HOME_URL="https://www.debian.org/"
8:SUPPORT_URL="https://www.debian.org/support"
9:BUG_REPORT_URL="https://bugs.debian.org/"
root@36e399118658:/# locale -a
C
C.UTF-8
POSIX
ko_KR.utf8
root@36e399118658:/# psql --username hermeswing --dbname springboot
psql (14.1 (Debian 14.1-1.pgdg110+1))
도움말을 보려면 "help"를 입력하십시오.
springboot=# \l
데이터베이스 목록
이름 | 소유주 | 인코딩 | Collate | Ctype | 액세스 권한
------------+------------+--------+------------+------------+---------------------------
postgres | hermeswing | UTF8 | ko_KR.utf8 | ko_KR.utf8 |
springboot | hermeswing | UTF8 | ko_KR.utf8 | ko_KR.utf8 |
template0 | hermeswing | UTF8 | ko_KR.utf8 | ko_KR.utf8 | =c/hermeswing +
| | | | | hermeswing=CTc/hermeswing
template1 | hermeswing | UTF8 | ko_KR.utf8 | ko_KR.utf8 | =c/hermeswing +
| | | | | hermeswing=CTc/hermeswing
(4개 행)
springboot=#
ko_KR.utf8로 설치된걸 확인할 수 있습니다.
오류메시지도 한글로 보여지네요.
org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #2 of URL [file:/C:/Octopus/workspace/octopus_bbs/bin/main/initdata/schema-postgresql.sql]: CREATE TABLE IF NOT EXISTS T_BOARD_M ( ID SERIAL NOT NULL, TITLE VARCHAR(200) NOT NULL, CONTENTS TEXT NOT NULL, READ_CNT INTEGER NOT NULL DEFAULT 0 , CRT_ID VARCHAR(20), CRT_DT TIMESTAMP, MDF_ID VARCHAR(20), MDF_DT TIMESTAMP, CONSTRAINT T_BOARD_M PRIMARY KEY (ID) ); nested exception is org.postgresql.util.PSQLException: 오류: "t_board_m" 이름의 릴레이션(relation)이 이미 있습니다
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.2</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.mysql</groupId>
<artifactId>MySqlRunner</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>MySqlRunner</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
server.port=9090
spring.datasource.hikari.maximum-pool-size=4
# PostgreSQL DB 설정
spring.datasource.url=jdbc:postgresql://localhost:5432/springboot
spring.datasource.username=hermeswing
spring.datasource.password=pass
package com.mysql.mysqlrunner.runner;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
@Component
public class PostgresRunner implements ApplicationRunner {
@Autowired
DataSource dataSource;
@Autowired
JdbcTemplate jdbcTemplate;
@Override
public void run( ApplicationArguments args ) {
try {
Connection connection = dataSource.getConnection();
System.out.println( connection.getMetaData().getClass() );
System.out.println( connection.getMetaData().getDriverName() );
System.out.println( connection.getMetaData().getURL() );
System.out.println( connection.getMetaData().getUserName() );
Statement statement = connection.createStatement();
String sql = "CREATE TABLE ACCOUNT(ID INTEGER NOT NULL, NAME VARCHAR(255), PRIMARY KEY(ID))";
statement.executeUpdate( sql );
jdbcTemplate.execute( "INSERT INTO ACCOUNT VALUES (1, 'hermeswing')" );
} catch( Exception e ) {
e.printStackTrace();
}
}
}
#>psql --username hermeswing --dbname springboot
psql (13.3 (Debian 13.3-1.pgdg100+1))
Type "help" for help.
springboot=#
? 테이블 목록조회 : \l, \list
springboot=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+------------+----------+------------+------------+---------------------------
postgres | hermeswing | UTF8 | en_US.utf8 | en_US.utf8 |
springboot | hermeswing | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | hermeswing | UTF8 | en_US.utf8 | en_US.utf8 | =c/hermeswing +
| | | | | hermeswing=CTc/hermeswing
template1 | hermeswing | UTF8 | en_US.utf8 | en_US.utf8 | =c/hermeswing +
| | | | | hermeswing=CTc/hermeswing
(4 rows)
? 테이블 목록조회 : \dt
springboot=# \dt
Did not find any relations.
? 실행시킨 후 테이블 목록조회 : \dt
springboot=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+------------
public | account | table | hermeswing
(1 row)
springboot=# select *
springboot-# from account;
id | name
----+------------
1 | hermeswing
(1 row)
springboot=#
POSTGRES_DB, POSTGRES_USER, POSTGRES_PASSWORD 등등에 오타가 있을 때 아래와 같은 오류를 만날 수 있습니다.
? POSTGRES_DB, POSTGRES_USER, POSTGRES_PASSWORD 등등에 오타가 있을 때 아래와 같은 오류를 만날 수 있습니다.
? 오타가 있어서 생성이 안된겁니다.
$>docker exec -it postgres_boot bash
Error response from daemon: Container 999323c0e0b300b181c91b2b4e47ad6e0e00aca34e76b5f85e1629a6028af363 is not running
?POSTGRES_PASSWORD 에 오타가 있을 때 아래와 같은 오류를 만날 수 있습니다.
? 일단 실행은 되지만 권한이 없는거죠.
$>docker run -it postgres_boot bash
Unable to find image 'postgres_boot:latest' locally
docker: Error response from daemon: pull access denied for postgres_boot, repository does not exist or may require 'docker login': denied: requested access to the resource is denied.
See 'docker run --help'.
? POSTGRESQL에 접속이 안될 경우가 발생합니다. 최신버전이라 명시적으로 명령을 처리해야 합니다.
? #>psql --username hermeswing --dbname springboot
$>psql springboot
psql: error: FATAL: role "postgres" does not exist
참고 : 백기선님의 스프링 부트 개념과 활용
'Spring Framework' 카테고리의 다른 글
[Spring Cloud Netflix] Eureka Service 샘플 (2) | 2024.03.27 |
---|---|
[Log] Log4Jdbc 설정 (0) | 2024.02.23 |
[Redis] Docker에 Redis 설치하기 (0) | 2024.01.30 |
[전자정부프레임워크] 샘플 프로젝트 생성 dev4.1 (0) | 2023.06.27 |
[전자정부프레임워크] 전자정부프레임워크 설치 (0) | 2023.06.22 |