[PostgreSQL] 운영자를 위한 필수 쿼리 모음

 

PostgreSQL을 운영하다 보면 자주 반복하게 되는 쿼리들이 있습니다. 이 글에서는 권한 확인, 테이블/컬럼 정보 조회, 함수 검색, DB Lock 처리 등 실무에서 바로 쓸 수 있는 운영 쿼리들을 정리했습니다.

 

 

---

 

🔐 테이블 권한 조회

특정 테이블에 어떤 권한이 부여되어 있는지 확인할 수 있습니다.

SELECT
   grantee, 
   privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name = '[테이블명]';

 

---

➕ 테이블 권한 부여

계정에 대해 INSERT, SELECT 등 특정 권한을 부여할 때 사용합니다.

GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
ON [테이블명] 
TO [계정];

 

---

📋 컬럼 목록  조회

테이블에 정의된 컬럼의 이름, 데이터 타입, 길이 정보를 확인합니다.

SELECT
   column_name, 
   data_type,
   character_maximum_length 
FROM information_schema.columns
WHERE table_name = '[테이블명]';

 

---

📂 테이블 목록 조회

데이터베이스 내 존재하는 테이블 리스트를 확인할 수 있습니다.

-- 전체 테이블 조회
SELECT * FROM pg_tables;

-- 특정 스키마(예: 본인 생성 스키마) 테이블 조회
SELECT tablename 
FROM pg_tables 
WHERE schemaname = '[스키마명]';

 

---

⚙️ 함수 및 프로시저 내용 검색

 

등록된 함수나 프로시저 중 특정 키워드를 포함한 내용을 검색할 수 있습니다.

SELECT 
   ROUTINE_TYPE, 
   ROUTINE_NAME, 
   ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE IN ('FUNCTION', 'PROCEDURE')
   AND ROUTINE_DEFINITION LIKE '%[키워드]%'
   AND ROUTINE_SCHEMA = '[스키마명]';

 

---

🔒 DB Lock 조회 및 해제

 

세션에 걸린 Lock 현황을 조회하고, 필요 시 세션을 종료할 수 있습니다.

-- DB Lock 조회
SELECT 
   t.relname,
   l.locktype,
   page,
   virtualtransaction,
   pid,
   mode,
   granted
FROM pg_locks l
JOIN pg_stat_all_tables t ON l.relation = t.relid
ORDER BY relation ASC;

-- Lock 해제 (⚠️주의: 실행 중 트랜잭션이 종료됩니다)
SELECT pg_cancel_backend([pid]);
SELECT pg_terminate_backend([pid]);

 

---

📝 정리

위 쿼리들을 정리해두면 PostgreSQL 운영중 발생하는 다양한 상황에서 신속하게 대응할 수 있습니다.