20.Solutions / 40.Postgresql
프로젝트: astro-sub1
원본 경로: installguide/20.Solutions/40.Postgresql.md
PostgreSQL
1. 개요
PostgreSQL를 AKS내 Container DB 로 설치한다.
2. PostgreSQL
1) Helm Install
$ helm repo add bitnami https://charts.bitnami.com/bitnami
$ helm search repo postgresql
NAME CHART VERSION APP VERSION DESCRIPTION
bitnami/postgresql 16.3.4 17.2.0 PostgreSQL (Postgres) is an open source object-...
bitnami/postgresql-ha 15.1.4 17.2.0 This PostgreSQL cluster solution includes the P...
runix/pgadmin4 1.34.0 8.14 pgAdmin4 is a web based administration tool for...
$ cd ~/helm/charts/postgresql
# Helm install
$ helm -n atportal install postgresql bitnami/postgresql \
--set global.postgresql.auth.postgresPassword=postpass! \
--set global.postgresql.auth.username=atportal \
--set global.postgresql.auth.password=New1234! \
--set global.postgresql.auth.database=atportal \
--dry-run=true
NAME: mypost
LAST DEPLOYED: Sun Jun 2 13:25:17 2024
NAMESPACE: postgressql
STATUS: deployed
REVISION: 1
TEST SUITE: None
NOTES:
CHART NAME: postgresql
CHART VERSION: 15.5.1
APP VERSION: 16.3.0
# 확인
$ helm -n atportal ls
NAME NAMESPACE REVISION UPDATED STATUS CHART APP VERSION
postgresql atportal 1 2025-03-27 18:04:21.246162 +0900 KST deployed postgresql-16.3.4 17.2.0
# 삭제시...
$ helm -n atportal delete postgresql
2) 확인
$ kubectl -n atportal get all
NAME READY STATUS RESTARTS AGE
pod/postgresql-0 1/1 Running 0 62s
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
service/postgresql ClusterIP 10.0.184.138 <none> 5432/TCP 62s
service/postgresql-hl ClusterIP None <none> 5432/TCP 62s
NAME READY AGE
statefulset.apps/postgresql 1/1 62s
3. pgadmin(WebUI)
1) pgadmin install with helm
(1) install
$ helm repo add runix https://helm.runix.net
$ helm search repo pgadmin
NAME CHART VERSION APP VERSION DESCRIPTION
runix/pgadmin4 1.24.0 8.4 pgAdmin4 is a web based administration tool for...
stable/pgadmin 1.2.3 4.18.0 DEPRECATED - moved to new repo, see source for ...
---
NAME CHART VERSION APP VERSION DESCRIPTION
runix/pgadmin4 1.25.3 8.6 pgAdmin4 is a web based administration tool for...
---
NAME CHART VERSION APP VERSION DESCRIPTION
runix/pgadmin4 1.34.0 8.14 pgAdmin4 is a web based administration tool for...
# values yaml 확인
$ helm show values runix/pgadmin4
# Helm install
$ helm -n atportal install pgadmin runix/pgadmin4 \
--set ingress.enabled=true \
--set ingress.ingressClassName=nginx \
--set ingress.hosts[0].host=pgadmin.cbiz.kubepia.net \
--set ingress.hosts[0].paths[0].path=/ \
--set ingress.hosts[0].paths[0].pathType=Prefix \
--set env.email=atportal@ktds.co.kr \
--set env.password=Pgadmin1234! \
--set persistentVolume.enabled=false \
--dry-run=true
# 확인
$ helm -n atportal ls
NAME NAMESPACE REVISION UPDATED STATUS CHART APP VERSION
pgadmin atportal 1 2025-03-27 18:10:41.204009 +0900 KST deployed pgadmin4-1.34.0 8.14
postgresql atportal 1 2025-03-27 18:04:21.246162 +0900 KST deployed postgresql-16.3.4 17.2.0
# 삭제시...
$ helm -n atportal delete pgadmin
(2) 확인
http://pgadmin.cbiz.kubepia.net
2) 서버추가
- 메뉴 : Add New Server
- 서버 : abclab-svr
- Host Addreee : postgresql.postgresql.svc
- database: postgres or. abclab
- username: abclab
- password: New1234!
4. NodeSelector 지정(설정전)
해당 POD들이 InfraNode에 스케쥴링되도록 Node Selector를 지정한다.
Deployment와 Statefulset 에 아래와 같이 추가한다.
apiVersion: apps/v1
kind: Deployment
metadata:
spec:
template:
spec:
containers:
nodeSelector: <-- nodeSelector 셋팅한다.
nodepool: infra <--
...
5. External Access Setting(설정전)
Dev 환경의 경우 개발자의 local PC에서 DB에 접근할 수 있어야 한다.
Dev용 Kubernetes Cluster 의 경우 외부에서 접속 할 수 있도록 Node Port 로 Service 를 생성 하였다.
Node port 로 접속할 수 있도록 아래 설정이 필요하다.
1) 방화벽셋팅(NSG)
Inbound 방화벽에서 Node Port 를 오픈한다.
- 메뉴 : Portal > NSG > aks-agentpool
- Settings
- Inbouned security rules > Add
- Source : Any
- Service : Custom
- Protocol : TCP
- Port : 31110
2) Load Balancer
Node Port 로 접근할 수 있도록 Load Balancer 를 설정한다.
메뉴 : Portal > Load Balancer >
abclab-dev-aks-mc-rg > kubernetes
Settings > Load Balancing rules > Add
Name : postgresNodePort
TCP
Port : 31110
Health Probe : TCP31110
6. Client 실행 및 테스트(설정전)
다양한 방법으로 Client 를 실행하여 테스트 할 수 있다.
1) docker 로 실행
Local PC 에 Docker 로 PSQL Client를 설치 후 접근한다.
# postgres-client 설치된 이미지를 사용하여 접속테스트
docker pull bledig/alpine-psqlclient:3
docker run -d --name psql bledig/alpine-psqlclient:3 sleep 365d
docker exec -it psql sh
# db 접속
$ psql -h [hostname] -p [port] -U [userid]
Password for user postgres:
# database 목록 확인(예시)
[database]=# \l
psql Connect Test
psql -h 20.249.129.204 --username=abclab --dbname=abclab --port=31110 abclab=> abclab=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- abclab | abclab | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/abclab + | | | | | abclab=CTc/abclab postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)
2) Kube pod 로 실행
Kubernetes POD 로 Client 를 실행하여 DB 에 접속한다.
psql CLI tool 설치된 이미지로 pod에서 접속 테스트 확인 예시
# postgres-client 설치된 이미지를 사용하여 접속테스트 $ kubectl -n postgres run -it postgres-client --image=bledig/alpine-psqlclient:3 -- sh # db 접속 $ psql -h [hostname] -p [port] -U [userid] Password for user postgres: # database 목록 확인(예시) [database]=# \lpsql Connect Test
$ psql -h postgresql.postgresql.svc --username=abclab --dbname=abclab --port=31110 abclab=> abclab=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- abclab | abclab | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/abclab + | | | | | abclab=CTc/abclab postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)