PostgreSQL 서버 환경

#핵심 요약

Oracle → PostgreSQL 이행(PL/SQL → PL/pgSQL 변환)에 대비해, Oracle이 도는 Oracle Linux 7 서버에 PostgreSQL 15를 함께 설치함
Oracle 리스너는 1521, PostgreSQL은 5432라 포트가 겹치지 않음

OL 7은 dnf가 없어 yum을 쓰고, OS 기본 PostgreSQL(9.2) 대신 PGDG 저장소의 15 버전을 설치함

initdb는 데이터 디렉터리를 처음 만드는 단계로 Oracle의 DBCA에 해당하며, Oracle AL32UTF8에 맞춰 UTF8로 초기화함

이행 대상과 같은 이름의 롤(<USERNAME>)을 소유자로 DB(<TARGET_DB_NAME>)와 같은 이름의 스키마를 만들어 Oracle의 "계정 = 스키마" 구조를 흉내 냄

#개념과 동작 원리

psql은 기본이 자동 커밋이라 INSERT가 별도 COMMIT 없이 반영됨
Oracle PL/SQL의 COMMIT, ROLLBACK을 옮길 때 트랜잭션 처리 차이를 검토해야 함

#필수 명령어 또는 SQL

#설치 전 확인과 패키지 설치

cat /etc/os-release | grep -E "^NAME|^VERSION_ID"
rpm -E %{rhel}
ss -lntp | grep -E ":1521|:5432"
free -m

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum repolist | grep pgdg15
yum install -y postgresql15-server postgresql15-contrib
rpm -qa | grep postgresql15
id postgres

결과 해석:

rpm -E %{rhel}이 7이면 RHEL 7 계열이며, 5432가 비어 있어야 함

설치는 root에서 하며(oracle 계정은 설치 권한 없음) 설치 과정에서 postgres OS 계정이 자동으로 생김

#기존 클러스터 보관과 UTF8 초기화

cat /var/lib/pgsql/15/data/PG_VERSION
ls -l /var/lib/pgsql/15/data
systemctl status postgresql-15 --no-pager

systemctl stop postgresql-15
mv /var/lib/pgsql/15/data /var/lib/pgsql/15/data_old_$(date +%Y%m%d)
mkdir /var/lib/pgsql/15/data
chown postgres:postgres /var/lib/pgsql/15/data
chmod 700 /var/lib/pgsql/15/data
ls -ld /var/lib/pgsql/15/data*

PGSETUP_INITDB_OPTIONS="-E UTF8 --locale=C --data-checksums --auth-local=peer --auth-host=scram-sha-256" \
  /usr/pgsql-15/bin/postgresql-15-setup initdb
ls /var/lib/pgsql/15/data
cat /var/lib/pgsql/15/data/PG_VERSION
systemctl enable --now postgresql-15
systemctl status postgresql-15 --no-pager
ps -ef | grep -E "pmon|postgres:" | grep -v grep

결과 해석:

PG_VERSION에 15가 나오면 예전 15 클러스터가 남아 있는 것이며, 그대로 initdb하면 Data directory is not empty!로 멈춤

기존 클러스터는 인코딩, 인증 방식이 다를 수 있어 rm으로 지우지 않고 mv로 날짜를 붙여 보관한 뒤 빈 디렉터리(소유자 postgres, 권한 700)에서 새로 초기화함
기존 디렉터리 확인에서 No such file이면 보관 단계는 건너뜀

마지막 ps에서 Oracle의 ora_pmon_*와 postgres: 프로세스가 함께 보여야 함

#postgres 환경과 첫 접속

cat >> ~/.bash_profile <<'EOF'
export PATH=/usr/pgsql-15/bin:$PATH
export PGDATA=/var/lib/pgsql/15/data
EOF
source ~/.bash_profile
pg_ctl status
psql

SELECT version();
SHOW data_directory;
SHOW port;
SHOW server_encoding;
\l
\du
\q

결과 해석:

PATH에 /usr/pgsql-15/bin을 넣어야 psql 외에 pg_ctl 같은 관리 명령을 바로 쓸 수 있음

peer 인증이라 postgres OS 계정에서는 비밀번호 없이 psql로 들어감

#이행용 롤, DB, 스키마

ALTER USER postgres PASSWORD '<PASSWORD>';
CREATE USER <USERNAME> PASSWORD '<PASSWORD>';
CREATE DATABASE <TARGET_DB_NAME> OWNER <USERNAME> ENCODING 'UTF8';
\c <TARGET_DB_NAME>
CREATE SCHEMA <USERNAME> AUTHORIZATION <USERNAME>;
\dn
\q

결과 해석:

PostgreSQL은 롤을 만들어도 같은 이름의 스키마가 생기지 않으므로 CREATE SCHEMA ... AUTHORIZATION으로 직접 만듦
기본 search_path가 "$user", public이라 <USERNAME>로 접속하면 <USERNAME> 스키마가 먼저 쓰임

#외부 접속 허용

psql -c "ALTER SYSTEM SET listen_addresses = '*';"
echo "host    all    all    <CLIENT_NET>/24    scram-sha-256" >> $PGDATA/pg_hba.conf
exit

systemctl restart postgresql-15
firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --reload
ss -lntp | grep 5432

결과 해석:

listen_addresses 기본값은 localhost라 다른 서버, PC의 접속을 받지 않으며, ALTER SYSTEM은 postgresql.auto.conf에 기록되고 재시작해야 반영됨

수업은 0.0.0.0/0(모든 주소)을 허용했으나, 실제로는 접속할 클라이언트 대역만 적음
pg_hba.conf는 위에서부터 먼저 맞는 줄이 적용됨

방화벽 명령은 firewalld를 쓰는 서버에서만 하며, ss에 0.0.0.0:5432가 보여야 함

#<USERNAME>로 접속해 시험

psql -h localhost -U <USERNAME> -d <TARGET_DB_NAME>

SELECT current_user, current_database(), current_schema();
CREATE TABLE emp_m (empno NUMERIC PRIMARY KEY, ename VARCHAR(20), sal NUMERIC, deptno NUMERIC);
INSERT INTO emp_m VALUES (7839, 'KING', 5000, 10);
SELECT * FROM emp_m;
DROP TABLE emp_m;
\q

결과 해석:

-h localhost로 붙으면 TCP 접속이라 scram-sha-256 비밀번호 인증을 거침

current_schema()가 <USERNAME>로 나오면 계정 = 스키마 구성이 된 것임

#DBeaver 접속

SELECT current_user, current_database(), version();

결과 해석:

새 연결 → PostgreSQL → 위 값 입력 → Test Connection, 드라이버 다운로드 창이 뜨면 Download를 고름

Connection refused면 5432 방화벽과 listen_addresses를, no pg_hba.conf entry면 pg_hba.conf의 허용 규칙을 봄

#사용 조건과 주의사항

설치 완료 확인: systemctl status postgresql-15가 active (running), SHOW server_encoding이 UTF8, Oracle PMON과 PostgreSQL 프로세스 동시 실행, psql -h localhost -U <USERNAME> -d <TARGET_DB_NAME> 비밀번호 접속, systemctl is-enabled postgresql-15가 enabled

Oracle과 한 서버에 두면 포트는 겹치지 않아도 메모리, 디스크, CPU를 나눠 쓰므로 free -m, df -h로 여유를 확인함

실습 비밀번호를 계정 이름과 같게 두는 것은 실습 VM에서만 하며, 운영에서는 정책에 맞는 비밀번호와 좁은 허용 대역을 씀