DBA 참조
사용 규칙: 아래 명령어는 안정적이고 잘 알려진 형태입니다. 버전 및 에디션(라이선스!)에 따라 동작이 다릅니다. "사용 중인 버전에서 확인"이라고 표시된 줄은 명령어에 의존하기 전에 정확한 릴리스에 대한 벤더 문서를 확인하세요. 변경 기록 없이 프로덕션 환경에서 데이터를 수정하는 진단 문을 실행하지 마십시오.
1. 엔진 간략 비교
| 항목 | Oracle | PostgreSQL | SQL Server | Db2 LUW | MySQL / MariaDB (InnoDB) |
|---|---|---|---|---|---|
| 동시성 모델 | MVCC (undo); 읽기는 쓰기를 차단하지 않음 | MVCC (테이블의 행 버전; VACUUM) | 기본적으로 잠금; RCSI/스냅샷이 활성화된 경우 MVCC | 잠금; "현재 커밋됨" 읽기 (CUR_COMMIT) | MVCC (undo) + REPEATABLE READ(기본값)의 next-key/gap 잠금 |
| 트랜잭션 DDL | 아니요 (암시적 커밋 전/후) | 예 (CREATE INDEX CONCURRENTLY, CREATE DATABASE, VACUUM 등 제외) | 대부분 DDL에 대해 예 | 대부분 DDL에 대해 예 (문별로 확인) | 아니요 (암시적 커밋; MySQL 8 DDL은 문당 원자적) |
| 잠금 에스컬레이션 | 없음 | 없음 | 예 (행/페이지 -> 테이블, 객체 및 문당 ~5000 잠금) | 예 (LOCKLIST/MAXLOCKS) | 아니요 (단, gap 잠금은 있음) |
| 쿼리 통계 | AWR/ASH (Diagnostics Pack), Statspack | pg_stat_statements | Query Store, DMV | MON_GET_* 테이블 함수 | performance_schema, sys 스키마, 슬로우 로그 |
| 세션 잠금 타임아웃 | ALTER SESSION SET DDL_LOCK_TIMEOUT = 30; (DDL, 초) | SET lock_timeout = '5s'; | SET LOCK_TIMEOUT 5000; (ms) | SET CURRENT LOCK TIMEOUT 30; (초) | SET SESSION lock_wait_timeout = 30; (메타데이터 잠금) + innodb_lock_wait_timeout (행 잠금) |
2. 성능 진단
방법: (1) 증상 정의 (단일 쿼리, 특정 시간대, 전체 인스턴스); (2) SQL 튜닝 전에 대기/시간 모델 확인; (3) 실제 실행 계획 확인 (예상 계획만 보지 말 것); (4) 예상 행 수와 실제 행 수 비교 - 카디널리티 오추정이 일반적인 근본 원인 (오래된 통계, 데이터 쏠림, 상관된 조건자, 파라미터 스니핑/바인드 피킹); (5) 한 번에 하나씩 변경하고 측정.
Oracle
- 예상 계획:
EXPLAIN PLAN FOR <문장>;그런 다음SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); - 실제 계획:
/*+ GATHER_PLAN_STATISTICS */힌트와 함께 실행한 후SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>', NULL, 'ALLSTATS LAST')); - AWR/ASH/ADDM (
@?/rdbms/admin/awrrpt.sql,ashrpt.sql,addmrpt.sql,V$ACTIVE_SESSION_HISTORY,DBA_HIST_*)은 Diagnostics Pack(Enterprise Edition)이 필요합니다. SQL Monitor 및 SQL Tuning Advisor는 Tuning Pack이 필요합니다.CONTROL_MANAGEMENT_PACK_ACCESS를 확인하세요. 라이선스 없음: Statspack. - 실시간 대기:
V$SESSION(event,wait_class,sql_id,blocking_session),V$SYSTEM_EVENT,V$SQL/V$SQLSTATS. - 일반적인 대기:
db file sequential read(단일 블록, 인덱스 액세스),db file scattered read/direct path read(전체 스캔),log file sync(커밋 빈도 / 리두 I/O),enq: TX - row lock contention(애플리케이션 잠금),enq: TM - contention(종종 인덱싱되지 않은 외래 키),latch: cache buffers chains(핫 블록),gc *(RAC 상호 연결). - 계획 안정성: SQL Plan Management (베이스라인); SQL 프로파일은 Tuning Pack이 필요합니다.
PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) <문장>;- ANALYZE는 문장을 실행합니다: DML을BEGIN; ... ROLLBACK;으로 감싸세요.pg_stat_statements(shared_preload_libraries필요):total_exec_time기준 상위 SQL (PG13에서 열 이름 변경:total_exec_time,mean_exec_time).SELECT queryid, calls, round(total_exec_time) total_ms, round(mean_exec_time::numeric,1) mean_ms, rows, left(query,80) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;- 실시간:
pg_stat_activity(state,wait_event_type,wait_event,xact_start,query);idle in transaction세션은 잠금을 보유하고 VACUUM을 차단합니다. - 로그의 느린 계획을 위한
auto_explain모듈; I/O 타이밍을 위한track_io_timing = on. - 상태:
pg_stat_user_tables(seq_scan,n_dead_tup,last_autovacuum,last_autoanalyze); 블로트; 트랜잭션 ID 랩어라운드:SELECT datname, age(datfrozenxid) FROM pg_database;. - 강제 종료:
pg_cancel_backend(pid)(쿼리) 그 다음pg_terminate_backend(pid)(세션).
SQL Server
- 실제 계획: SSMS "실제 실행 계획 포함" 또는
SET STATISTICS XML ON;; 읽기/CPU를 위한SET STATISTICS IO, TIME ON;. - Query Store (2016+; 2022에서 새 데이터베이스에 대해 기본적으로 ON):
sys.query_store_query,sys.query_store_query_text,sys.query_store_plan,sys.query_store_runtime_stats,sys.query_store_wait_stats(2017+); 회귀 -> 계획 강제 적용EXEC sp_query_store_force_plan @query_id = <q>, @plan_id = <p>;. - DMV:
sys.dm_exec_requests(wait_type,blocking_session_id),sys.dm_os_wait_stats,sys.dm_exec_query_stats+CROSS APPLY sys.dm_exec_sql_text(sql_handle)+sys.dm_exec_query_plan(plan_handle). - 일반적인 대기:
PAGEIOLATCH_*(데이터 읽기),LCK_M_*(차단),WRITELOG(로그 I/O / 커밋 속도),CXPACKET/CXCONSUMER(병렬 처리 - MAXDOP/비용 임계값 확인),SOS_SCHEDULER_YIELD(CPU),ASYNC_NETWORK_IO(클라이언트가 결과를 소비하지 않음),RESOURCE_SEMAPHORE(메모리 부여). - 파라미터 스니핑: 파라미터별 계획 비교; 옵션
OPTION (RECOMPILE),OPTIMIZE FOR, Query Store 힌트 (2022) - 경우에 따라 선택. - 확장 이벤트;
system_health세션은 최근 데드락 그래프를 유지합니다.
Db2 LUW
- Explain: 설명 테이블 생성 (
SYSPROC.SYSINSTALLOBJECTS또는EXPLAIN.DDL),EXPLAIN PLAN FOR <문장>,db2exfmt -d <DB> -1 -o plan.txt로 포맷; 빠른 보기db2expln. - 모니터링 테이블 함수:
MON_GET_PKG_CACHE_STMT(상위 SQL:STMT_EXEC_TIME,NUM_EXECUTIONS,ROWS_READ),MON_GET_CONNECTION,MON_GET_ACTIVITY,MON_GET_TABLE,MON_GET_BUFFERPOOL. - 잠금:
SYSIBMADM.MON_LOCKWAITS,db2pd -db <DB> -wlocks,db2pd -db <DB> -locks. - 통계:
RUNSTATS ON TABLE <스키마>.<테이블> WITH DISTRIBUTION AND DETAILED INDEXES ALL;-REORGCHK로 재구성 필요성 평가; 인덱스 조언을 위한db2advis.
MySQL / MariaDB
EXPLAIN <문장>; 실제: MySQL 8.0.18+EXPLAIN ANALYZE <문장>; MariaDBANALYZE <문장>(둘 다 문장을 실행합니다).- 상위 SQL:
sys.statement_analysis,performance_schema.events_statements_summary_by_digest; 슬로우 쿼리 로그 (slow_query_log,long_query_time) +pt-query-digest. - InnoDB:
SHOW ENGINE INNODB STATUS\G(최신 데드락, 히스토리 리스트 길이),information_schema.INNODB_TRX,sys.innodb_lock_waits; MySQL 8:performance_schema.data_locks/data_lock_waits; MariaDB:information_schema.INNODB_LOCKS/INNODB_LOCK_WAITS및METADATA_LOCK_INFO플러그인; MySQL 메타데이터 잠금:performance_schema.metadata_locks.
3. 잠금 및 동시성 - 함정
| 엔진 | 함정 | 완화 방법 |
|---|---|---|
| 모든 엔진 | 긴 트랜잭션 + DDL: DDL이 대기하고, 이후 모든 쿼리가 그 뒤에서 대기 | DDL에 대한 짧은 잠금 타임아웃, 재시도 루프, 긴 트랜잭션이 없을 때 실행; 사전 확인 (활성 세션) |
| Oracle | 인덱싱되지 않은 외래 키 -> 상위 행이 삭제/기본 키가 업데이트될 때 하위 테이블에 TM 잠금 | 외래 키 열 인덱싱 |
| Oracle | 사용 중인 객체에 대한 DDL 실패 ORA-00054 | DDL_LOCK_TIMEOUT; 온라인 변형 (CREATE INDEX ... ONLINE, ALTER TABLE ... MOVE ONLINE 12.2+, DBMS_REDEFINITION) - 모두 Enterprise Edition 전용 (SE2에는 없음) |
| Oracle | 열린 커서 위에서 루프 내부 커밋 -> ORA-01555 snapshot too old | 키/로우ID 범위로 청크 (DBMS_PARALLEL_EXECUTE), 언두 보유 시간 설정 |
| PostgreSQL | 대부분의 ALTER TABLE은 ACCESS EXCLUSIVE를 획득; 이를 기다리면 모든 읽기가 차단됨 | SET lock_timeout; CREATE INDEX CONCURRENTLY; FK/CHECK NOT VALID 후 VALIDATE CONSTRAINT |
| PostgreSQL | 열 유형 변경은 테이블을 다시 작성합니다 (varchar 길이 증가와 같은 안전한 경우 제외) | 새 열 + 백필로 확장/축소 |
| PostgreSQL | idle in transaction 세션 | idle_in_transaction_session_timeout, 애플리케이션 수정 |
| SQL Server | 기본 READ COMMITTED에서 읽기가 쓰기를 차단 | READ_COMMITTED_SNAPSHOT ON 평가 (tempdb 버전 저장소 영향; 테스트) |
| SQL Server | 대량 DML에서 테이블로의 잠금 에스컬레이션 | 약 5000행 미만의 배치; 주의하여 ALTER TABLE ... SET (LOCK_ESCALATION = ...) |
| SQL Server | "수정"으로 NOLOCK 사용 | 더티 읽기, 누락/중복 행 - 리뷰에서 거부 |
| Db2 | LOCKTIMEOUT = -1 (무한 대기) 기본값 | 릴리스 세션에서 CURRENT LOCK TIMEOUT 설정 |
| Db2 | ALTER TABLE ... DROP COLUMN, 대부분의 ALTER COLUMN SET DATA TYPE, SET NOT NULL/DROP NOT NULL은 행 구성 테이블을 재구성 보류 상태로 만듦 (SQLSTATE 57007): REORG TABLE까지는 스캔만 허용, DROP/RENAME/TRUNCATE 및 대부분의 다른 ALTER는 허용됨; 이러한 작업의 최대 31개 작업 단위 후에 REORG가 필수가 됨 | 창 내에서 REORG TABLE 계획; SYSIBMADM.ADMINTABINFO에서 REORG_PENDING / NUM_REORG_REC_ALTERS 확인 |
| MySQL | 메타데이터 잠금(MDL) 대기: 열린 트랜잭션에 의해 DDL이 차단된 후 모든 사람을 차단 | 릴리스 세션에서 lock_wait_timeout을 낮게 설정; 유휴 상태의 긴 트랜잭션 종료 |
| MySQL | 온라인 DDL이 자동으로 테이블 복사로 대체됨 | 오류가 발생하도록 ALGORITHM=INSTANT (또는 INPLACE)를 LOCK=NONE과 함께 항상 명시; 또는 gh-ost / pt-online-schema-change |
| MySQL | REPEATABLE READ에서 범위 업데이트 시 Gap/next-key 잠금 | 조건자 인덱싱; 허용 가능한 경우 READ COMMITTED 고려 |
데드락: 항상 설계 문제입니다 (일관되지 않은 액세스 순서, 누락된 인덱스). 캡처: Oracle 추적 파일 / 알림 로그 (ORA-00060), PostgreSQL 로그 (log_lock_waits = on for waits > deadlock_timeout), SQL Server system_health XE, Db2 잠금용 이벤트 모니터, MySQL innodb_print_all_deadlocks.
4. HA / DR
| 엔진 | 로컬 HA | DR / 복제본 | 주요 작업 및 확인 |
|---|---|---|---|
| Oracle | RAC (공유 스토리지, 액티브-액티브, 추가 라이선스) | Data Guard 물리적 대기 (리두 적용); Active Data Guard = 적용과 함께 읽기 전용 열기 (추가 라이선스) | 보호 모드: Maximum Performance (ASYNC, 기본값), Maximum Availability (SYNC, 대체), Maximum Protection (SYNC, 대기 없으면 기본 중지). Broker dgmgrl: SHOW CONFIGURATION;, VALIDATE DATABASE <stby>;, SWITCHOVER TO <stby>;, FAILOVER TO <stby>;. 지연: V$DATAGUARD_STATS. 기본에서 FORCE LOGGING. Fast-Start Failover는 관찰자(이상적으로는 세 번째 사이트)가 필요합니다. |
| PostgreSQL | Patroni (DCS: etcd/Consul/ZooKeeper) + HAProxy/VIP | 스트리밍 복제 (물리적, WAL); 논리적 복제 (발행/구독, PG10+) | pg_stat_replication (sent_lsn, write_lsn, flush_lsn, replay_lsn), pg_is_in_recovery(), pg_promote() (PG12+). 동기: synchronous_standby_names + synchronous_commit. 복제 슬롯은 복제본이 다운된 경우 WAL을 보유 -> 디스크 가득 참: 모니터링, max_slot_wal_keep_size (PG13+)로 제한. Patroni: patronictl -c <cfg> list / switchover / failover; 로드 밸런서용 REST 상태 엔드포인트 (/primary, /replica). 논리적 복제는 DDL이나 시퀀스 값을 복제하지 않습니다 (PG 18 기준): 구독자에 가산적 스키마 변경을 먼저 적용하고 구독자로 장애 조치하기 전에 시퀀스를 재설정합니다. |
| SQL Server | 장애 조치 클러스터 인스턴스 (공유 스토리지, WSFC) | Always On 가용성 그룹 (WSFC, Linux에서는 Pacemaker); 로그 전달 | 동기 커밋 복제본 간에만 자동 장애 조치. 클라이언트용 수신기 (MultiSubnetFailover=True). 로그인, 에이전트 작업, 연결된 서버, 자격 증명은 복제되지 않습니다 (포함된 AG, SQL Server 2022 Enterprise, 도움됨). 전체 AG는 Enterprise가 필요함; Standard Edition의 Basic AG (1 DB, 2 복제본). sys.dm_hadr_database_replica_states 확인. 계획된 장애 조치: 대상 동기 보조에서 ALTER AVAILABILITY GROUP [<AG>] FAILOVER;. 읽기 가능한 보조는 라이선스에 영향을 미칩니다. |
| Db2 LUW | pureScale (액티브-액티브) 또는 HADR + Pacemaker/TSA 자동화 | HADR (최대 3개 대기, HADR_TARGET_LIST) | HADR_SYNCMODE: SYNC, NEARSYNC, ASYNC, SUPERASYNC. 모니터 db2pd -db <DB> -hadr 또는 MON_GET_HADR. 대기에서 역할 전환: TAKEOVER HADR ON DATABASE <DB> (BY FORCE for failover). 대기에서 읽기 via DB2_HADR_ROS (제한 사항 있음). |
| MySQL / MariaDB | InnoDB Cluster (Group Replication + MySQL Router); Galera (MariaDB) | 비동기/세미싱크 복제, GTID 기반 | SHOW REPLICA STATUS (MySQL 8.0.22+; MariaDB 10.5.1+는 REPLICA를 SLAVE의 동의어로 허용함; 이전 버전: SHOW SLAVE STATUS). 복제 지연 및 잘못된 GTID 관찰; Galera는 기본 구성 요소 쿼럼이 필요합니다 (3개 노드 또는 중재자). |
설계 규칙: 동기/비동기를 선택하기 전에 애플리케이션별 RPO/RTO 정의; 최소한 연 1회 DR 테스트를 문서화된 결과와 함께 수행 (DORA는 금융 기관에 ICT 연속성 테스트를 요구함); 클라이언트는 투명하게 재연결해야 함 (다중 호스트/리스너/SCAN이 있는 연결 문자열, 재시도 로직); 지연 모니터링 및 알림; 스플릿 브레인 방지 (쿼럼, 퓨징, 세 번째 사이트의 관찰자/증인).
5. 백업, 복원, PITR
| 엔진 | 전체 / 증분 | PITR | 검증 |
|---|---|---|---|
| Oracle (RMAN) | BACKUP DATABASE PLUS ARCHIVELOG; 증분 레벨 0/1; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS; | ARCHIVELOG 모드 필요. RUN { SET UNTIL TIME "TO_DATE('2026-10-01 12:00','YYYY-MM-DD HH24:MI')"; RESTORE DATABASE; RECOVER DATABASE; } 그런 다음 ALTER DATABASE OPEN RESETLOGS; | RESTORE DATABASE VALIDATE;, CROSSCHECK BACKUP;, LIST BACKUP SUMMARY;, REPORT NEED BACKUP;, DELETE OBSOLETE; |
| PostgreSQL | pg_basebackup -D <dir> -Ft -z -X stream -P; pgBackRest --type=full / diff / incr backup; Barman. pg_dump는 논리적 전용 (PITR 불가) | WAL 아카이빙 (archive_mode, archive_command); 베이스 복원 + restore_command + recovery_target_time + recovery.signal (PG12+). pgBackRest: pgbackrest --stanza=<s> --type=time "--target=2026-10-01 12:00:00+02" restore | pgbackrest --stanza=<s> check, pgbackrest --stanza=<s> info; 정기적인 실제 복원 테스트 |
| SQL Server | 복구 모델 FULL; BACKUP DATABASE [<db>] TO DISK = N'<path>' WITH CHECKSUM, COMPRESSION; 차등; BACKUP LOG | 전체 복원 WITH NORECOVERY, 차등 WITH NORECOVERY, 로그 WITH NORECOVERY ..., 최종 RESTORE DATABASE [<db>] WITH RECOVERY; 로그 백업 체인 중단 방지 (FULL 또는 DIFF 후 첫 번째 LOG 백업은 부분적임); 타임스탬프: STOPAT = '2026-10-01 12:00:00' | RESTORE VERIFYONLY FROM DISK = '<백업 파일 경로>' WITH CHECKSUM;; 정기적인 실제 복원 테스트 (다른 환경) |
| Db2 LUW | BACKUP DATABASE <DB> TO <path> COMPRESS EXCLUDE LOGS WITHOUT PROMPTING; 증분 (INCREMENTAL DELTA)은 트랜잭션 로그 전달이 필요함 | 롤포워드: 로그 아카이빙 (LOGARCHMETH1); RESTORE DATABASE <DB> FROM <path> TAKEN AT <timestamp> ...; ROLLFORWARD DATABASE <DB> TO 2026-10-01-12.00.00.000000 USING LOCAL TIME AND COMPLETE; | RESTORE DATABASE <DB> ... WITHOUT ROLLING FORWARD;로 복원성 확인; LIST HISTORY BACKUP ALL FOR <DB>; |
| MySQL / MariaDB | mysqldump --all-databases --single-transaction --routines --events --triggers --master-data=2 > dump.sql (논리적); XtraBackup (물리적, PITR 가능); MariaDB mariabackup | 바이너리 로그 필요 (log_bin); 전체 백업 + 바이너리 로그; `mysqlbinlog --stop-datetime='2026-10-01 12:00:00' | mysql -u root -p (증분 적용); XtraBackup은 내장 PITR 지원 (--apply-log-only가 있는 --incremental`) |
보관 정책: 엔진 백업 명령과 공급업체 솔루션(pgBackRest, XtraBackup, RMAN, ...)을 분리하십시오. 툴링은 엔진의 API를 사용합니다. 환경 간에 정책(보관 기간, 빈도, 암호화, 위치)을 문서화하고 테스트된 실제 복원 절차를 보관하십시오. 첫 번째 전체 백업 + 모든 로그/보관: 모든 PITR은 체인의 첫 번째 전체 백업이 필요합니다(diff/incr가 있는 Oracle RMAN: 레벨 0 기본).
6. 보안 및 GDPR
접근 제어
- Oracle:
CREATE USER ... IDENTIFIED BY ...;GRANT CONNECT, RESOURCE, CREATE SESSION TO <user>;; 역할;V$SESSION(username,schemaname,osuser,machine,program). - PostgreSQL:
CREATE USER ... PASSWORD '...';(MD5 또는 SCRAM-SHA-256);GRANT CONNECT ON DATABASE ... TO <user>;;pg_hba.conf; 역할 멤버십 (GRANT <role> TO <user>). - SQL Server:
CREATE LOGIN ... WITH PASSWORD = '...';;CREATE USER ... FROM LOGIN ...;;ALTER ROLE ... ADD MEMBER ...;; 포함된 데이터베이스 사용자 (CREATE USER ... WITH PASSWORD = '...';). - Db2 LUW:
CREATE USER는 OS 수준;CONNECT TO <DB> USER <user> USING <pwd>;(GRANT CONNECT ON DATABASE TO ...;). - MySQL / MariaDB:
CREATE USER 'user'@'host' IDENTIFIED BY '...';;GRANT ALL PRIVILEGES ON <db>.* TO 'user'@'host';;FLUSH PRIVILEGES;.
감사
- Oracle: 표준 감사 (
AUDIT ... BY <user> BY ACCESS;), 통합 감사 (12c+,ADMINISTER KEY MANAGEMENT,AUDIT POLICY).DBA_AUDIT_TRAIL,UNIFIED_AUDIT_TRAIL. 감사 레코드 보존 정책. - PostgreSQL:
pg_audit확장 (공유 객체 필요);log_statement = 'all'(성능 영향);log_line_prefix; 로그 회전 (log_rotation_age,log_rotation_size).hba_file을 통한 연결 감사. - SQL Server: 서버 감사 사양 + 데이터베이스 감사 사양;
sys.fn_get_audit_file; 기본 감사는 구성해야 함. - Db2 LUW:
db2audit; 구획(db2audit configure scope all status yes);db2audit extract. - MySQL / MariaDB: 감사 로그 플러그인 (MySQL Enterprise / MariaDB Audit Plugin);
audit_log; 정책 기반 구성.
암호화
- Oracle: TDE (Transparent Data Encryption, Enterprise Edition, Advanced Security 옵션);
ADMINISTER KEY MANAGEMENT; 열 수준 (ENCRYPT); 테이블스페이스 수준. - PostgreSQL:
pgcrypto확장 (열 수준, 애플리케이션 측); TDE는 PG 16 이상에서 사용 가능 (선택 사항). - SQL Server: TDE (암호화 키, 인증서, DEK);
CREATE DATABASE ENCRYPTION KEY;ALTER DATABASE SET ENCRYPTION ON; 열 수준 (ENCRYPTBYKEY,DECRYPTBYKEY). - Db2 LUW: NATIVE_DLL_CIPHER (암호화);
db2pd -db <DB> -encryption. - MySQL / MariaDB: TDE (InnoDB 테이블스페이스 암호화, MySQL 8.0.13+, MariaDB 10.1+);
innodb_undo_log_encrypt,innodb_redo_log_encrypt.
GDPR 영향 (일반 사항)
- 개인 식별 정보(PII) 데이터 분류: 스키마 문서화, 알려진 PII 열에 메타데이터 태깅.
- 액세스 기록: 감사 로그, 연결 소스 IP.
- 데이터 보존: 자동 제거 정책, 보존 기간이 지난 백업 파기.
- 삭제 권리(Right to Erasure): 식별된 PII 열
UPDATE .. SET <pii> = NULL또는 의사 익명화; 백업에서도 제거를 위해 별도의 파기 정책이 필요할 수 있음. - 데이터 이동성: 표준 내보내기 기능 (
expdp,pg_dump,bcp,db2move,mysqldump). - 침해 통지: 사전 준비된 침해 대응 플레이북, 알림 템플릿.
일반적인 취약점
- 기본 포트: 변경 (선택 사항이지만 스캐닝 노출 감소).
- 기본 계정: Oracle(
SYS,SYSTEM), PostgreSQL(postgres), SQL Server(sa), MySQL(root), MariaDB(root), Db2(db2inst1).SYS/postgres/sa/root를 직접 사용하지 말고, 더 적은 권한의 관리자 사용자를 만드십시오.postgres/sa/root비밀번호를 회전하고 가능하면 연결을 제한하십시오. - 장기간 사용되지 않은 사용자: 검토 및 삭제/비활성화.
- 과도한 권한: 정기적인 권한 검토 (예: Oracle
DBA가 아닌CONNECT+RESOURCE, PostgreSQLSUPERUSER피하기, SQL Serversysadmin에서db_owner로 전환).
7. Oracle에서 PostgreSQL로 마이그레이션 - 함정
| Oracle 기능 | PostgreSQL 동등 | 함정 |
|---|---|---|
SYSDATE, SYSTIMESTAMP | NOW(), CURRENT_TIMESTAMP | SYSDATE는 Oracle에서 트랜잭션 중에 고정되지만 PostgreSQL NOW()는 고정됩니다 (트랜잭션 시작 시간). CLOCK_TIMESTAMP()는 실시간을 제공합니다. 트랜잭션 내부에서 차이가 발생합니다. |
NVL(), NVL2() | COALESCE() (ANSI 표준을 선호) | Oracle DECODE() -> CASE 표현식 또는 COALESCE(). |
ROWNUM | LIMIT / OFFSET 또는 ROW_NUMBER() 창 함수 | 정렬 없는 LIMIT는 임의의 행을 반환합니다. ROWNUM = 1은 Oracle이 첫 번째 적중에서 중지(임의)하지만 PostgreSQL LIMIT 1은 전체 결과를 가져온 다음 자릅니다. 더 빠른 최적화를 위해 서브쿼리에 ORDER BY를 사용하십시오. |
CONNECT BY (계층적) | 재귀적 CTE (WITH RECURSIVE ...) | 구문이 많이 다릅니다. 루프 방지가 필요합니다. |
시퀀스 (<seq>.NEXTVAL) | NEXTVAL('<seq>') | PostgreSQL 시퀀스는 트랜잭션 롤백 시 롤백되지 않습니다. 구분 기호: 점 대신 '<seq>', SELECT NEXTVAL('my_seq'). |
MERGE (UPSERT) | INSERT ... ON CONFLICT DO UPDATE/SET (PostgreSQL 9.5+) | Oracle MERGE는 더 많은 절 (예: WHEN NOT MATCHED)이 있으며 PostgreSQL에서 지원되지 않거나 다르게 동작합니다. |
MATERIALIZED VIEW | MATERIALIZED VIEW (리프레시 CONCURRENTLY) | Oracle MV는 증분 리프레시가 가능합니다. PostgreSQL MV는 전체 리프레시(기본값) 또는 동시 리프레시(인덱스 필요)를 지원합니다. |
FLASHBACK | 없음 (타임 트래블 확장, pg_dirtyread) | Oracle Flashback Query (AS OF TIMESTAMP)는 내장되어 있습니다. PostgreSQL은 복제/확장이 필요합니다. |
SAMPLE | TABLESAMPLE SYSTEM / BERNOULLI (9.5+) | 구문이 다릅니다. |
ROWNUM + ORDER BY (페이징) | OFFSET / FETCH NEXT ROWS ONLY (SQL:2008) | Oracle은 ROWNUM에 대해 중첩 서브쿼리가 필요합니다. |
VARCHAR2(4000) / VARCHAR2(32767) | TEXT / VARCHAR(n) (n <= 10,485,760) | Oracle VARCHAR2(4000) 기본값은 4000자; 최대 32767은 MAX_SQL_STRING_SIZE 확장이 필요합니다. PostgreSQL TEXT / VARCHAR는 거의 무제한입니다. 그러나 성능 오해: TEXT와 VARCHAR(n)은 내부적으로 동일합니다. |
| 가상 열 | 생성된 열 (12+, 저장됨) | PostgreSQL 12+는 가상(STORED) 생성 열을 지원합니다. |
| 부분 인덱스 | CREATE INDEX ... ON <table> WHERE <condition>; | Oracle과 유사하지만 구문이 약간 다릅니다. |
| 함수 기반 인덱스 | 표현식 인덱스 | Oracle: CREATE INDEX ... ON <table> (UPPER(col));; PostgreSQL: CREATE INDEX ... ON <table> (UPPER(col)); (유사). |
WITH (NOLOCK) 힌트 (SQL Server) | PostgreSQL에는 해당 사항 없음 | PostgreSQL은 잠금 없는 읽기를 위해 MVCC를 사용합니다. |
AUTOTRACE | EXPLAIN (ANALYZE, BUFFERS) | 쿼리 실행을 위한 SET AUTOTRACE TRACEONLY에 해당하는 것은 없습니다. |
8. 릴리스 스크립트 모범 사례
일반 규칙:
- 반드시 트랜잭션 내에서 (엔진이 지원하는 경우) 실행하고, 하위 호환성을 유지하며, 변경 사항을 검증하고, 변경 기록을 통해 추적합니다.
- 각 릴리스 스크립트는 단일 버전(멱등적)에서 실행되며, 이전 버전에서 업그레이드된 것으로 가정합니다. 롤백 스크립트가 필요합니다.
- 애플리케이션 호환성: 스키마를 변경하기 전/후에 쿼리를 변경합니다(이전 쿼리는 새 스키마와 호환되어야 함).
- 프로덕션에서 DDL을 실행하지 마십시오. 큰 테이블은 스테이징에서 테스트하고 시간을 측정하십시오.
마이그레이션 툴
- Liquibase (XML/YAML/JSON/SQL, changelog,
rollback) - Flyway (
V<number>__<description>.sql,undo상업용) - sqitch (배포 전용, 태그, 되돌리기)
- Alembic (Python/SQLAlchemy)
SQL 릴리스 체크리스트
- 데이터 손실 또는 스키마 변경이 예상됩니까? (원자적, 롤백 가능해야 함)
- 트랜잭션 DDL을 지원하지 않는 엔진에서 DDL이 트랜잭션 외부에 있습니까? (Oracle, MySQL)
- 큰 테이블에서의 DDL:
ALTER TABLE은 오랜 시간 동안 배타적 잠금을 유지합니다. 온라인 변형이나 툴(gh-ost, pt-online-schema-change)을 고려하십시오. - 외래 키 변경: 이전 쿼리와 새 쿼리가 모두 유효한지 확인하십시오.
- 인덱스 변경: 새 인덱스를 먼저 추가하고 성능에 미치는 영향을 확인한 후 이전 인덱스를 삭제하십시오.
- 제약 조건: 새 제약 조건은 기존 데이터에 대해
NOT VALID여야 하며, 오프 피크 시간에VALIDATE해야 합니다. - 권한: 새 객체에 대한
GRANT. - 데이터 마이그레이션: 큰 테이블의 일괄 업데이트, 롤백 스크립트.
- 버전 태그: 마이그레이션 도구를 사용하는 경우 변경 집합에 태그를 지정하십시오.
- 모니터링: 느린 쿼리, 오류, 잠금에 대한 알림을 설정하십시오.
롤백 스크립트
- 각 릴리스에는 롤백 스크립트가 있어야 합니다.
- 데이터 마이그레이션의 경우: 원래 상태로 되돌리기 위해 반대 업데이트를 작성하십시오.
- DDL의 경우: 반대
ALTER/DROP/CREATE. - 외래 키의 경우: 이전 외래 키가 없는 경우, 이전 제약 조건을 정확히 다시 추가하십시오. 이전 쿼리가 오래된 제약 조건을 예상하는 경우 문제가 발생할 수 있습니다.
9. 리뷰 체크리스트
SQL 쿼리 리뷰
-
SELECT *사용 안 함. -
WHERE절이 인덱싱됨 (인덱스 스캔, 스캔된 행 수 예상). - 조인 조건이 있습니다 (의도하지 않은 크로스 조인 없음).
-
DISTINCT가 불필요하게 사용되지 않음 (중복을 숨기지 않음). - 하위 쿼리 성능 프로파일링 (상관 관계, 존재 여부).
-
IN (SELECT ...)대신EXISTS가 더 나은가? -
OR는 인덱스를 깨뜨리지 않음 (결합 인덱스,IN목록). -
UNIONvsUNION ALL? (중복이 예상되는 경우UNION ALL선호). -
ORDER BY가 인덱싱됨. -
LIMIT/OFFSET이 의도적임. -
HAVING을WHERE로 이동 가능한가? - 함수가 인덱스를 깨뜨리지 않음 (예:
WHERE UPPER(col)대신 표현식 인덱스 사용). - 매개변수가 올바르게 바인딩됨 (SQL 주입 방지).
DDL 리뷰
- 새 테이블에 기본 키가 있음.
- 외래 키가 인덱싱됨 (Oracle, MySQL).
- 데이터 유형이 적절함 (최대 길이, 정밀도).
-
NOT NULL이 필요하지 않은 경우 사용되지 않음. - 기본값이 올바름.
-
COMMENT ON TABLE/COLUMN이 추가됨. - 파티셔닝 전략이 문서화됨 (파티션 키, 경계).
- 공간 요구 사항이 고려됨 (인덱스 테이블스페이스, 데이터 파일).
- 스토리지 엔진 / 테이블 유형이 지정됨 (MySQL의 InnoDB, PostgreSQL의
WITH (OIDS=FALSE), SQL Server의DATA_COMPRESSION).
권한 리뷰
- 새 객체에 대한
GRANT가 누락되지 않음. - 권한이 필요한 것보다 적음(최소 권한).
-
GRANT ... TO PUBLIC사용 안 함 (데이터 노출). - 감사 요구 사항이 충족됨.
성능 리뷰
-
EXPLAIN/EXPLAIN ANALYZE가 리뷰됨. - 테이블/인덱스 통계가 최신임.
- 버퍼/캐시 히트율이 수용 가능함.
- 느린 쿼리가 캡처됨.
- 동시성 영향이 평가됨 (잠금, 교착 상태).