PostgreSQL 데이터베이스 유지 관리

소개

이 랩에서는 필수적인 PostgreSQL 데이터베이스 유지 관리 작업을 배우게 됩니다. 목표는 데이터베이스 객체 크기를 모니터링하고, 성능 최적화를 위한 정기적인 유지 관리를 수행하며, 연결 및 로그를 확인하여 잠재적인 문제를 해결하는 방법을 이해하는 것입니다.

먼저 PostgreSQL 데이터베이스에 연결하고 테이블 및 인덱스의 크기를 쿼리하는 것부터 시작합니다. 그런 다음 데이터베이스 통계를 업데이트하기 위해 ANALYZE를 실행하고 저장 공간을 회수하기 위해 VACUUM을 실행하는 방법을 배우게 됩니다. 마지막으로 활성 클라이언트 연결을 나열하고 오류에 대한 서버 로그를 확인하여 데이터베이스 문제를 진단하고 해결하는 방법을 다룹니다.

데이터베이스 객체 크기 쿼리

이 단계에서는 PostgreSQL 데이터베이스에 연결하고 테이블 및 해당 인덱스의 크기를 쿼리합니다. 데이터베이스 객체의 크기를 이해하는 것은 성능 튜닝 및 용량 계획에 매우 중요합니다.

먼저 터미널을 엽니다. postgres 사용자로 PostgreSQL 서버에 연결하고 mydatabase 데이터베이스에 액세스하려면 다음 명령을 실행합니다.

sudo -u postgres psql mydatabase

psql 프롬프트 (mydatabase=#) 가 표시되어 mydatabase 데이터베이스에 연결되었음을 나타냅니다. 이 랩의 후속 SQL 명령은 별도로 지정하지 않는 한 이 psql 셸에서 실행해야 합니다.

이제 mytable의 크기를 결정해 보겠습니다. pg_size_pretty 및 pg_relation_size 함수를 사용합니다. pg_relation_size는 테이블 크기를 바이트 단위로 반환하고, pg_size_pretty는 이를 사람이 읽을 수 있는 형식 (예: KB, MB) 으로 포맷합니다.

mytable의 크기를 얻으려면 다음 SQL 쿼리를 실행합니다.

SELECT pg_size_pretty(pg_relation_size('mytable'));

테이블 데이터가 사용하는 디스크 공간을 보여주는 다음과 유사한 출력이 표시됩니다.

 pg_size_pretty
----------------
 56 kB
(1 row)

다음으로 name 열에 생성된 인덱스 idx_mytable_name의 크기를 확인합니다.

SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));

출력은 인덱스가 소비하는 공간을 보여줍니다.

 pg_size_pretty
----------------
 48 kB
(1 row)

마지막으로 테이블과 모든 인덱스를 포함한 총 크기를 얻으려면 pg_total_relation_size 함수를 사용합니다.

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

이 출력은 테이블과 해당 인덱스의 결합된 크기를 보여줍니다.

 pg_size_pretty
----------------
 176 kB
(1 row)

ANALYZE 를 사용한 최적화

이 단계에서는 좋은 쿼리 성능을 유지하는 데 필수적인 ANALYZE 명령에 대해 배우게 됩니다.

ANALYZE 이해하기

ANALYZE 명령은 데이터베이스 내 테이블 내용에 대한 통계를 수집합니다. PostgreSQL 쿼리 플래너는 이러한 통계를 사용하여 쿼리에 대한 가장 효율적인 실행 계획을 선택합니다. 정확한 통계가 없으면 플래너가 잘못된 선택을 하여 느린 쿼리 성능으로 이어질 수 있습니다. 특히 테이블 데이터에 상당한 변경이 있은 후에는 주기적으로 ANALYZE를 실행하는 것이 좋습니다.

여전히 psql 셸에 있는 상태에서 mytable 테이블에 대해 ANALYZE를 실행합니다.

ANALYZE mytable;

이 명령은 mytable을 분석하고 해당 통계를 업데이트합니다. 명령이 성공적으로 실행되었음을 확인하는 다음 출력이 표시됩니다.

ANALYZE

명령은 단순히 ANALYZE를 반환하지만 백그라운드에서 mytable의 내부 통계를 업데이트했습니다.

VACUUM 을 사용한 스토리지 회수

이 단계에서는 또 다른 중요한 유지 관리 작업인 스토리지 회수를 수행하기 위해 VACUUM 명령을 사용합니다.

VACUUM 이해하기

PostgreSQL 에서 행이 업데이트되거나 삭제될 때, 해당 행의 이전 버전 ("죽은 튜플") 은 디스크에서 즉시 제거되지 않습니다. VACUUM은 이러한 죽은 튜플이 차지하는 스토리지를 회수하여 해당 공간을 재사용 가능하게 만듭니다. 또한 데이터 가시성 정보를 업데이트하여 쿼리 성능을 개선하는 데 도움이 됩니다.

psql 셸에서 mytable 테이블에 대해 VACUUM을 실행해 보겠습니다.

VACUUM mytable;

이 명령은 테이블을 처리하고 확인 메시지를 반환합니다.

VACUUM

VACUUM과 ANALYZE를 하나의 효율적인 명령으로 결합할 수도 있습니다. 이는 일반적인 유지 관리 방법입니다.

VACUUM ANALYZE mytable;

이 명령은 먼저 스토리지를 회수하고 테이블에 대한 통계를 업데이트한 후 완료 시 VACUUM을 반환합니다.

참고: VACUUM FULL 명령도 있습니다. 이 명령은 스토리지를 더 적극적으로 회수하고 디스크 파일 크기를 줄입니다. 그러나 전체 테이블을 잠그므로 작업 중에 읽기 또는 쓰기가 불가능해지므로 주의해서 필요할 때만 사용해야 합니다.

연결 및 로그 모니터링

이 마지막 단계에서는 활성 데이터베이스 연결을 모니터링하고 오류에 대해 서버 로그를 확인하는 방법을 배우게 됩니다. 이는 문제 해결에 핵심적인 기술입니다.

먼저, 여전히 psql 셸에 있는 상태에서 pg_stat_activity 뷰를 쿼리하여 서버에 대한 모든 활성 연결을 볼 수 있습니다.

다음 SQL 쿼리를 실행합니다.

SELECT datname, usename, client_addr, state FROM pg_stat_activity;

이 쿼리는 데이터베이스 이름 (datname), 사용자 이름 (usename), 클라이언트 IP 주소 (client_addr), 연결의 현재 상태 (state) 를 보여줍니다. 출력에서 자신의 연결이 다음과 유사하게 나열된 것을 볼 수 있습니다.

  datname  | usename  | client_addr | state
-----------+----------+-------------+--------
 mydatabase| postgres |             | active
 postgres  | postgres |             |
           | labex    |             |
(3 rows)

다음으로 서버 로그를 확인합니다. 이를 위해 먼저 psql 셸을 종료해야 합니다. \q를 입력하고 Enter 키를 누릅니다.

\q

이제 표준 Linux 터미널 프롬프트로 돌아왔습니다. PostgreSQL 로그는 /var/log/postgresql/ 디렉토리에 있습니다. 이 시스템에서는 로그 파일이 postgresql-14-main.log입니다.

grep 명령을 사용하여 로그 파일에서 "ERROR"를 포함하는 모든 줄을 검색합니다.

grep ERROR /var/log/postgresql/postgresql-14-main.log

오류가 없으면 이 명령은 출력을 생성하지 않습니다. 이 실험에서는 오류를 유발할 만한 작업을 수행하지 않았으므로 예상되는 결과입니다. 서버 로그를 검사하는 것은 데이터베이스 문제를 진단하는 기본적인 단계입니다.

요약

이 실험에서는 필수적인 PostgreSQL 데이터베이스 유지 관리 작업을 수행하는 방법을 배웠습니다. 이제 테이블과 인덱스의 크기를 모니터링하고, 쿼리 성능을 최적화하기 위해 ANALYZE를 실행하며, 스토리지 공간을 회수하기 위해 VACUUM을 사용하는 방법을 이해했습니다. 또한 활성 클라이언트 연결을 나열하고 오류에 대해 서버 로그를 확인하는 연습도 했습니다. 이러한 기술은 PostgreSQL 데이터베이스의 상태, 성능 및 안정성을 보장하는 데 매우 중요합니다.