Docker - Postgresql 설치

Docker에서 Postresql 설치하는 방법 설명드립니다.
볼륨은 따로 마운트 해서, docker 장애시에도 안정성있게 하도록 설명드리겠습니다.(볼륨 생성 연결)

# postgresql image 다운로드 후 확인
sudo docker pull postgres:latest
sudo docker images | grep postgres
postgres              latest    eb634efa7ee4   2 weeks ago   431MB

# 볼륨 생성
sudo docker volume create psql-data
sudo docker volume ls
DRIVER    VOLUME NAME
local     a210b1f888dd5a24a6d0617f8b9c9733fc8345741a9f8374235cf93d2b973a25
local     cafd5373fadda57ac195dbd0146b161091bc0d66113706ffbc65ea640c924fb2
local     psql-data

# postgresql 실행
sudo docker run -p 5432:5432 --name postgres \
-e POSTGRES_PASSWORD=postgres \
-e TZ=Asia/Seoul \
-v psql-data:/var/lib/postgresql/data \
-d postgres:latest

# 확인
sudo docker ps
CONTAINER ID   IMAGE                 COMMAND                  CREATED          STATUS          PORTS                                       NAMES
3a7b6063662d   postgres:latest       "docker-entrypoint.s…"   29 seconds ago   Up 28 seconds   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   postgres

# postgres 컨테이너 접속
sudo docker exec -it postgres bash
------------------------------------------------------------------
# 컨테이너 접속 후 로그인
psql -U postgres

# db 생성
create database test_db;

# 조회
\l
                                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 test_db   | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
(4 rows)

# 계정 생성 및 권한 설정
create role test_user with login password 'test12!#';
alter user test_user with createdb;
alter user test_user with superuser;

# 조회
\du
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 test_user | Superuser, Create DB

# test_db 에 접근 권한 부여
grant all privileges on database test_db to test_user;

------------------------------------
# 도커 볼륨 정보 확인
sudo docker inspect --format="{{ .Mountpoint }}" psql-data
/data/docker/volumes/psql-data/_data

# 호스트 경로에서 확인
sudo tree /data/docker/volumes/psql-data/_data -L 1
├── base
├── global
├── pg_commit_ts
├── pg_dynshmem
├── pg_hba.conf
├── pg_ident.conf
├── pg_logical
├── pg_multixact
├── pg_notify
├── pg_replslot
├── pg_serial
├── pg_snapshots
├── pg_stat
├── pg_stat_tmp
├── pg_subtrans
├── pg_tblspc
├── pg_twophase
├── PG_VERSION
├── pg_wal
├── pg_xact
├── postgresql.auto.conf
├── postgresql.conf
├── postmaster.opts
└── postmaster.pid

sudo ls -al /data/docker/volumes/psql-data/_data/base/
total 52
drwx------  6 systemd-coredump systemd-coredump   46 Mar  7 12:39 .
drwx------ 19 systemd-coredump systemd-coredump 4096 Mar  7 12:35 ..
drwx------  2 systemd-coredump systemd-coredump 8192 Mar  7 12:37 1
drwx------  2 systemd-coredump systemd-coredump 8192 Mar  7 12:39 16388
drwx------  2 systemd-coredump systemd-coredump 8192 Mar  7 12:35 4
drwx------  2 systemd-coredump systemd-coredump 8192 Mar  7 12:37 5

---

• 장애 재현 : 현재 컨테이너를 강제로 삭제 후 신규 컨테이너 재실행 시 도커 볼륨 연결 후 확인

# 컨테이너 삭제 후 확인
sudo docker rm -f postgres
sudo docker ps
sudo ls -al /data/docker/volumes/psql-data/_data/base/
total 52
drwx------  6 systemd-coredump systemd-coredump   46 Mar  7 12:39 .
drwx------ 19 systemd-coredump systemd-coredump 4096 Mar  7 12:35 ..
drwx------  2 systemd-coredump systemd-coredump 8192 Mar  7 12:37 1
drwx------  2 systemd-coredump systemd-coredump 8192 Mar  7 12:39 16388
drwx------  2 systemd-coredump systemd-coredump 8192 Mar  7 12:35 4
drwx------  2 systemd-coredump systemd-coredump 8192 Mar  7 12:37 5

# postgres 컨테이너 다시 생성 : 기존 도커 자체 볼륨 연결
sudo docker run -p 5432:5432 --name postgres \
-e POSTGRES_PASSWORD=postgres \
-e TZ=Asia/Seoul \
-v psql-data:/var/lib/postgresql/data \
-d postgres:latest

# psql 접속
sudo docker exec -it postgres psql -U postgres

# 데이터 조회
\l
                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+------------+------------+------------+-----------+------------------------
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres           +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres           +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 test_db   | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =Tc/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres +
           |          |          |                 |            |            |            |           | test_user=CTc/postgres
(4 rows)

\du
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 test_user | Superuser, Create DB

# 자원 삭제
sudo docker rm -f postgres
sudo docker volume rm psql-data