Database/PostgreSQL

PostgreSQL 에 접속할 때 아래의 오류가 발생했다. psql: SCRAM authentication requires libpq version 10 or above 이러한 오류는 psql 버전이 10 미만이기 때문이다. PostgreSQL 13 버전인가 부터는 클라이언트가 10 버전 이상이어야 한다. # psql --version psql (PostgreSQL) 9.2.24 다른 많은 블로그에서 우분투 환경으로 yum 이나 apt 로 패키지를 다운받아서 하는 방법을 가이드 하고 있지만, 나의 환경은 Amazon Linux 이기 때문에 이 명령어로 설치가 가능하다. # amazon-linux-extras install postgresql10 psql 클라이언트 버전이 변경되고 postgres 에도 접..

PostgreSQL 에서 접속을 관리하는 방법 PostgreSQL 은 postgresql.conf 파일과 pg_hba.conf 파일을 통해 특정 서버에서 특정 사용자에만 접속이 가능하도록 제한할 수 있습니다. 이 오류는 pg_hba.conf 파일에 인증이 적용되어 있지 않기 때문입니다. FATAL: no pg_hba.conf entry for host "192.168.56.103", user "postgres", database "postgres", SSL off error 치명적오류: 호스트 "192.168.56.103", 사용자 "postgres", 데이터베이스 "postgres", SSL 중지 연결에 대한 설정이 pg_hba.conf 파일에 없습니다. 위와같은 오류가 발생하는 원인 pg_hba.co..

PostgreSQL 에서 접속을 관리하는 방법 PostgreSQL 은 postgresql.conf 파일과 pg_hba.conf 파일을 통해 특정 서버에서 특정 사용자에만 접속이 가능하도록 제한할 수 있습니다. 이 오류는 대부분 외부서버에서 PostgreSQL 서버로 접속하려는 경우에 발생합니다. Connection to 192.168.56.101:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. 위와같은 오류가 발생하는 원인 postgresql.conf 파일의 LISTEN_ADDRESSES 파라미터 postgresql.conf 파일의 'CONNECT..

PostgreSQL 의 프로세스 PostgreSQL 은 postmaster 의 메인프로세스와 postmaster 에서 fork 된 프로세스들로 구성됩니다. fork 된 프로세스는 PostgreSQL 을 위해 각자의 역할을 수행합니다. 1 2 3 4 5 6 7 8 9 postgres@VirtualBox:~$ ps -ef | grep postgres postgres 937 1 0 23:03 ? 00:00:00 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf postgres 1384 937 0 23:04 ? 00:00:00 postgres: ..

PostgreSQL 의 XID PostgreSQL 에서 하나의 튜플은 xmin, xmax 의 2개의 트랜잭션 ID 를 가집니다. Tuple 이 생성된 시점은 xmin / Tuple 이 삭제되는 (이전의 값) 인 경우는 xmax 로 TID 를 저장합니다. 이 때 다른 세션에서 해당 데이터를 조회 시 사용되는 트랜잭션ID 가 xmin 과 xmax 사이라면 보여지고, 그렇지 않다면 보여지지 않도록 합니다. PostgreSQL 의 XID 제약조건 TID 는 40억개를 사용할 수 있으며 20억개는 older , 20억개는 newer 로 사용됩니다. 40억개가 다 사용되면 다시 1부터 시작됩니다. 여기서 중요한 문제가 발생합니다. 트랜잭션 ID 가 40억에서 새로운 데이터가 적재되어 xmin 이 40억인 최신의 데..

Vacuum 사전적의미로 청소, 진공, 공백을 의미합니다. PostgreSQL 에서 Vacuum 은 MVCC 에 의해 현재 사용되지 않는 이전의 저장된 값들을 정리합니다. Vacuum 을 하는 이유 PostgreSQL 은 MVCC 에 의해 이전의 값들이 갱신되거나 삭제되더라도 새로운 데이터로 추가(갱신 경우)하고 기존의 데이터는 정리하지 않고 삭제표기만 남겨둡니다. 이렇게 사용하지 않는 값들로 인해 파일 사이즈가 커져 성능저하가 발생하게 되는걸 방지합니다. Transaction ID 가 겹침으로 인해 자료의 손실이 발생하는 것을 방지합니다. 이전의 데이터가 정리되면서 실행계획에서 사용할 통계치 정보를 최신으로 갱신해 줍니다. Visibility MAP ( VM = Dead Tuple 의 존재 여부를 표기..

PostgreSQL 백업 방법 PostgreSQL 은 장애시 복구를 위해 3가지 백업방법이 있습니다. 3가지 방법은 SQL 덤프 / 파일 시스템 기반 백업 / 아카이브 모드 백업입니다. 이 중 아카이브 모드 백업에 대해 다뤄보겠습니다. 아카이브 모드 백업 PostgreSQL 은 미리쓰기기로그 (WAL, Write ahead log) 을 pg_xlog 디렉터리에서 관리합니다. 이 로그는 데이터베이스에 대한 모든 조작 기록을 보관하고 있어, 서버가 갑자기 비정상적으로 종료된경우 데이터파일에 적용하지 못한 작업은 이 로그파일을 읽어서 복구할 수 있습니다. 이 로그를 다른 서버로 보내, 이 로그의 내용을 그대로 실행하여 원본 서버와 똑같이 만들수 있습니다. 또한 특정시점 까지만 실행하도록 하여, 특정시점으로 복..

PostgreSQL 백업 방법 PostgreSQL 은 장애시 복구를 위해 3가지 백업방법이 있습니다. 3가지 방법은 SQL 덤프 / 파일 시스템 기반 백업 / 아카이브 모드 백업입니다. 이 중 파일 시스템 기반 백업에 대해 다뤄보겠습니다. 파일시스템 백업 방법 말그대로 데이터파일을 압축해서 백업하는 방식입니다. 백업 전 PostgreSQL 을 중지해야 합니다. 1 tar -cvzf PostgreSQL.tar.gz /var/lib/postgresql/10/main cs 파일시스템 복원 방법 압축한 파일을 PostgreSQL 데이터경로에 풀어줍니다. 이 후 DB 를 재구동합니다. 버전과 아키텍처에 영향을 받습니다. 만약 복원경로가 변경되는 경우에는 pg_ctl 명령어를 사용하여 기본 디렉터리를 변경해 줍니다..

PostgreSQL 데이터베이스 경로 확인 방법 1. 데이터베이스의 기본 데이터파일 경로 확인 data_directory 를 확인합니다. 실제 데이터파일은 이 폴더경로의 base 폴더 하위에 있습니다. 1 2 3 4 postgres=# show data_directory; data_directory ----------------------------- /var/lib/postgresql/10/main cs 2. 각 데이터베이스의 oid 를 확인 이 oid 를 확인해 주는 이유는 위 data_directory 경로의 base 폴더에 database_id 값으로 폴더가 생성되기 때문입니다. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 postgres=# select oid as database..

PostgreSQL 백업 방법 PostgreSQL 은 장애시 복구를 위해 3가지 백업방법이 있습니다. 3가지 방법은 SQL 덤프 / 파일 시스템 기반 백업 / 아카이브 모드 백업입니다. 이 중 SQL 덤프에 대해 다뤄보겠습니다. SQL 덤프 백업방법 이 백업방식은 pg_dump 명령어로 진행합니다. pg_dump 를 실행하면 DB_NAME 데이터베이스에 생성된 객체와 데이터들을 SQL 구문 텍스트 형식으로 백업합니다. 기본적으로 표준출력이기 때문에 리다이렉션 기능을 사용하여 파일로 저장합니다. 1 $ pg_dump DB_NAME > postgres_DB_NAME_bak.sql cs pg_dump 에서 제공하는 연결 옵션 옵션을 보면 일반 클라이언트와 동일하게 원격으로 접속할 수 있는 옵션을 제공하고 있습..