PostgreSQL 테이블스페이스 및 오브젝트 사용량 확인



PostgreSQL 테이블스페이스 및 오브젝트 사용량 확인

 - 테이블스페이스 총량

  postgres=# select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace;


 - Table Size (Index 미포함)

 postgres=# select pg_relation_size('TableName');


 - Table Size (Index 포함) 

 postgres=# select pg_total_relation_size('TableName');


 - Index size

 postgres=# select pg_relation_size('IndexName');

 

 - Total Size ( 데이터 + 인덱스)

 postgres=# select pg_total_relation_size('TableName');


 ※ 단위적용 - pg_size_pretty()


 - DB Size

 postgres=# select pg_size_pretty(pg_database_size('DBName'));

PostgreSQL 백업 및 복원



PostgreSQL 백업 및 복원


 - PostgreSQL 데이터를 백업하는 3가지 방법

 

 1. SQL 덤프

 2. 파일 시스템 레벨 백업

 3. 연속 아카이빙


 

1. SQL 덤프


pg_dump 사용


 덤프 백업


 $ pg_dump dbname > outfile


 - pg_dump 는 최신 버전에서 다시 로드 할 수 있습니다.

 - 32bit -> 64bit 서버로 이동할 수 있는 유일한 방법입니다.

 - 한번에 하나의 데이터베이스만 백업 가능합니다.


 덤프 복원


 $ psql dbname < infile


 - 새로운 환경에서 복원하게 되면 dbname 부분의 들어가는 데이터베이스가 자동으로 생성 되지 않기 때문에, 미리 사용자가 같은 이름의 데이터베이스를 생성 해줘야 합니다.

 - SQL 덤프를 복원하기 전에 덤프된 데이터베이스의 개체를 소유하고 있거나 개체에 대한 권한이 부여된 모든 사용자가 이미 존재해야 합니다.

 - 덤프 복원은 에러가 나도 계속 진행하기 때문에 --set ON_ERROR_STOP=on 옵션을 주면 에러 발생시 psql을 종료하게 할 수 있습니다.

 - pg_dump로 생성된 덤프는 template0에 상대적입니다. 이것은 template1을 통해 추가된 언어, 프로시저 등도 pg_dump에 의해 덤프된다는 것을 의미합니다. 결과적으로 복원시, 커스터마이즈된 template1을 사용하는 경우 template0으로부터 비어있는 데이터베이스를 생성해야 합니다. 

 - 파이프에 쓰거나, 파이프에서 읽어 오는 pg_dump 및 psql의 기능은 데이터베이스를 특정 서버에서 다른 서버로 직접 덤프를 가능하게 합니다.


 $ pg_dump -h host1 dbname | psql -h host2 dbname



pg_dumpall 사용


 - pg_dump는 한 번에 하나의 데이터베이스만 덤프하며, role 또는 테이블스페이스에 대한 정보는 덤프하지 않습니다. 


 백업

 

 $ pg_dumpall > outfile


 복원


 $ psql -f infile postgres


 - 실제로 시작한 기존 데이터베이스 이름을 지정할 수 있지만 비어있는 클러스터로 로딩한 경우 postgres를 일반적으로 사용해야 합니다.

 - 클러스터 차원의 데이터는 pg_dumpall --globals-only 옵션을 사용하여 단독 덤프가 가능합니다.



거대 데이터베이스 처리


 - 일부 운영체제는 거대 pg_dump 출력 파일을 생성할 때 최대 파일 크기 제한이 있습니다. pg_dump는 표준 출력으로 사용할 수 있어, 유닉스 툴로 이런 문제의 가능성을 피할 수 있습니다.


 ◆ 압축 덤프 사용

  

  gzip을 이용한 덤프

  $ pg_dump dbnam | gzip > filename.gz


  리로드

  $ gunzip -c filename.gz | psql dbname


 ◆ split 사용

  

  1GB 단위로 분할 백업

  $ pg_dump dbname | split -b 1024m - filename


  리로드

  $ cat filename* | psql dbname


 ◆ pg_dump의 커스텀 덤프 형식 사용

  

  - 설치된 zlib 압축 라이르러리를 사용하여 PostgreSQL을 시스템에서 빌드한 경우 출력 파일에 쓸 때 커스텀 덤프 형식이 데이터를 압축합니다.

 - 덤프파일 크기는 gzip과 비슷하지만 테이블을 선택적으로 복원할 수 있다는 장점이 있습니다.


  백업

  $ pg_dump -Fc dbname > filename


  복원 : psql이 아닌 pg_restore를 이용해 복원합니다.

  $ pg_restore -d dbname filename


 ◆ pg_dump의 병렬 덤프 기능 사용


  - 거대 데이터베이스의 덤프 속도를 높이기 위하여 병렬 모드를 사용할 수 있습니다.

  - 병렬 덤프는 "디렉토리" 아카이브 형식에 대해서만 지원합니다.

 

 백업

 $ pg_dump -j <number> -F d -f out.dir dbname


 복원

 pg_resote -j 를 이용하여 복원 할 수 있습니다.




2. 파일 시스템 레벨 백업


 - 시스템 레벨에서 백업을 하는 것인데, 정상적인 백업을 위해서는 반드시 DB를 shutdown 해야 합니다. 복원도 마찬가지.


 tar -cf backup.tar /usr/local/pgsql/data


 - 파일 시스템 백업은 전체 데이터베이스 클러스터의 완전한 백업 및 복원에 대해서만 동작합니다.



 

3. 연속 아카이빙 및 PITR


 - PostgreSQL은 클러스터의 데이터 디렉토리의 pg_xlog/ 서브 디렉토리에 WAL를 항상 유지관리합니다. 이 로그에는 데이터 파일에서 일어난 모든 변경 내용이 기록 됩니다.

 - 복구가 필요한 경우, 파일 시스템 백업을 복원한 이후, 백업된 WAL 파일로부터 리플레이로 시스템을 현재 상태로 불러옵니다. (오라클 Hot backup 복원 후 archive 적용하는 것과 비슷한 개념입니다.)

 - 일반 파일 시스템 백업과 마찬가지로, 전체 데이터베이스 클러스터 복원만 지원합니다.


 베이스 백업


 - pg_base 를 이용한 백업

 

 1) WAL 아카이빙이 활성화 되어 있는지 확인한다.

 2) 슈퍼유저로 데이터베이스에 연결하고 다음 명령을 실행한다.

   SELECT pg_start_backup('label');

   여기서 label은 백업 작업의 식별을 위한 string이다.

   기본적으로 pg_start_backup은 완료 되는데 체크포인트를 수행하기 때문에 시간이 오래걸린다.

   백업을 가능한 빨리 시작하고 싶다면, 다음을 사용하는 것이 좋다.

   SELECT pg_start_backup('label',ture);

   체크포인트를 강제한다.

 3) tar 또는 cpio 같은 편리한 파일 시스템 백업툴을 사용하여 백업을 수행한다.

 4) 슈퍼유저로 데이터베이스에 접속후에 명령을 실행한다.

   SELECT pg_stop_backup();

 5) 백업을 아카이브 하는 중에 WAL 세그먼트가 활성화 되면 백업이 끝이 난다.

 

 ※ 오라클 Hot backup과 동일한 개념으로 보면 됩니다.


 연속 아카이브 백업을 사용한 복구


 1) 서버가 실행중인 경우 서버를 중지한다.

 2) 공간에 여유가 잇는 경우 필요할 때를 대비하여 전체 클러스터 데이터 디렉토리와 테이블스페이스를 임시 위치에 복사한다. 시스템 다운 정에 아카이브되지 않은 로그가 포함되었을 수 있는 클러스터의 pg_xlog 서브디렉토리의 내용을 최소한이라도 따로 저장해두어야 한다.

 3) 사용 중인 클러스터 데이터 디렉토리 아래 및 데이블스페이스의 root 디렉토리 아래의 모든 기본 파일 및 서브 디렉토리를 삭제한다.

 4) 데이터베이스 파일을 파일 시스템 백업으로 부터 복원한다. 올바른 소유권 확인 및 pg_tblspc의 심볼릭 링크가 바른지 확인.

 5) pg_xlog/ 밑에 오래된 파일 시스템 백업에서 온 로그를 삭제한다.

 6) pg_xlog/ 2단계에서 저장된 아카이브되지 않은 WAL 세그먼트 파일이 있을 경우 pg_xlog/에 복사한다.

 7) 클러스터 데이터 디렉토리에서 복구 명령 파일 recovery.conf를 생성한다. pg_hba.conf를 수정하여 복구하는 동안 접속을 막는다.

 8) 서버를 시작한다. 서버가 복구모드로 들어가고 아카이브된 WAL 파일을 통해 읽기가 진행 된다. 복구가 완료 되면 recovery.conf의 이름을 recovey.done 으로 변경하고 다시 복구 모드로 들어가지 않게 바꿔준다.

 9) 데이터베이스의 내용을 확인하고 pg_hba.conf 를 복원하여 사용자 연결을 확인한다.


※ recovery.conf


 예제 파일. /usr/pgsql-9.6/share/recovery.conf.sample을 이용하여 만들수 있다.


 아카이브 복구 설정


 restore_command (string)

  - WAL 파일 시리즈의 아카이브된 세그먼트의 검색을 실행하는 로컬 쉘 명령.

  - 아카이브 복구에 필요하지만 스트리밍 복제의 경우는 옵션.

  - string의 %f 는 아카이브에서 검색할 파일 이름으로 교체되고, %p 는 서버의 복사 대상 경로로 교체된다.

 

    restore_command = 'cp /data/pgsql/archvie/%f "%p"'


 archive_cleanup_command (string)

  -모든 restartingpoint에서 실행되는 쉘 명령을 지정한다.

  - 스탠바이 서버에서 더 이상 필요로 하지 않는 오래된 아카이브 WAL 파일을 클린업하는 메카니즘을 제공하는 것.

  - %r 은 마지막 유효 재시작 지점이 있는 파일의 이름으로 교체된다.

  

   archive_cleanup_command = 'pg_archivecleanup /data/pgsql/archive %r'


 recovery_end_command (string)

  - 복구 종료 시에 한 번만 실행되는 쉘 명령을 지정한다. 이 매개변수는 옵션이다.


복구 타깃 설정


recovery_target = 'immediate'

  - 온라인 백업에서 복원하는 경우 이것은 백업이 종료된 지점을 의미한다.

  - 현재 다른 매개변수는 없다.


recovery_target_name (string)

  - 이 매개변수는 지명된 복원 지점(pg_create_restore_point()를 사용하여 생성)을 복구가 진행되는 지점으로 지정.


recovery_target_time (timestamp)

  - 이 매개변수는 타임스탬프를 복구가 진행되는 지점으로 지정한다.

  - 정밀한 정지 지점은 recovery_target_inclusive의 영향도 받는다


recovery_target_xid (string)

  - 트랜잭션 ID를 복구가 진행되는 지점으로 지정한다.

  - 복구되는 트랜잭션은 지정된 것 이전에 커밋된 트랜잭션이다.


다음 옵션은 복구 타깃을 추가로 지정하며, 타깃에 도달 했을때, 발생하는 것에 영향을 끼친다.


recovery_target_inclusive (boolean)

  - 지정된 복구 타깃 직후에 중지 할 것인지, 직전에 중지 할 것인지 지정한다.

  - 기본값은 ture.


recovery_target_timeline (string)

  - 특정한 타임라인으로의 복구를 지정한다.

  - 기본값은 베이스 백업을 가져왔을 때 현재였던 동일한 타임라인을 따라 복구하는 것이다.

  - 이것을 lastest로 설정하면 아카이브의 최신 타임라인으로 복구 되며, 스탠바이 서버에 유용하다.

 







PostgreSQL 로컬라이제이션



PostgreSQL 로컬라이제이션


 - 로케일 지원은 initdb를 이용해 클러스터를 구성하면 자동으로 초기화 됩니다. 

 - 특별히 옵션을 넣지 않으면 en_US.UTF8로 설정이 됩니다.

 

 LC_COLLATE

 String 정렬 순서

 LC_CTYPE

 문자 분류 (어떤글자인지, 대문자도 동일한지)

 LC_MESSAGES

 메세지 언어

 LC_MONETARY

 통화 형식

 LC_NUMERIC

 숫자 형식

 LC_TIME

 날짜 및 시간 형식


 예)

 로케일을 한국으로 설정하되 통화 형식은 달러를 쓴다면,

 initdb --locale=ko_KR --lc-monetary=en_US 로 클러스터를 구성하면 됩니다.


 - 시스템에 로케일 지원이 안되는 것처럼 하고 싶으면 특수한 로케일 이름인 C 또는 동등하게 POSIX를 사용해야 합니다.

 - 일부 로케일 카테고리는 데이터베이스가 생성될 때 고정된 값이어야 합니다. 

 - 서로 다른 데이터베이스에 대해 서로 다른 설정을 사용할 수는 있지만, 데이터베이스가 생성된 다음에는 설정을 변경 할 수 없습니다.

 - 인덱스 정렬 순서에 영향을 미치므로 고정된 상태로 유지되어야 하며, 데이터베이스 운영 중 변경하면 인덱스 손상이 발생합니다.

 - initdb 에서 선택된 값은 postgresql.conf에 작성되어 서버 시작시 기본값으로 사용됩니다. 이 값을 postgresql.conf에서 제거하면 서버가 실행 환경에서 설정을 상속 받습니다.

 - 리눅스가 처음부터 ko_KR.UTF8로 설정 되어 있다먼, initdb 시 옵션을 넣지 않아도 ko_KR.UTF8로 설정됩니다.



※ 로케일 설정은 다음과 같은 SQL 기능에 영향을 줍니다.


 1. order by를 사용한 쿼리에서 정령 순서 또는 텍스트 데이터에서 표준 비교 연산자

 2. upper 및 lower, initcap 함수

 3. 패턴 일치 연산자 (LIKE, SIMILAR TO 및 POSIX 스타일 정규식). 대소문자 비 구분 일치 및 문자 클래스 정규식에 의한 문자 분류에 모두 영향을 미치는 로케일.

 4. TO_CHAR 계열 함수

 5. LIKE 절을 사용한 인덱스 사용 능력


 - PostgreSQL에서 C 또는 POSIX가 아닌 다른 로케일을 사용할 때의 단점은 성능입니다. 문자 처리가 느려지고 LIKE에서 사용 되는 일반 인덱스를 사용하지 못합니다. 이러한 이유로, 실제로 필요한 경우에만 로케일을 사용해야 합니다.


※ 이미 클러스터를 구성해서 운영중인데 다른 언어셋이 필요하다면, create database 명령에서 로케일 옵션을 변경해서 생성 할 수 있습니다.


postgres=# create database test_kr

with

template=template0

encoding='EUC_KR'

LC_COLLATE='POSIX'

LC_CTYPE='ko_KR.euckr'

tablespace=test_kr

connection limit=999;

CREATE DATABASE

postgres=# \l

                                  List of databases

   Name    |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   

-----------+-----------+----------+-------------+-------------+-----------------------

 postgres  | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |           |          |             |             | postgres=CTc/postgres

 template1 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |           |          |             |             | postgres=CTc/postgres

 test_kr   | postgres  | EUC_KR   | C           | ko_KR.euckr | 

 testdb    | test_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

(5 rows)



 - C가 아닌 로케일 하에서 LIKE 절을 사용한 인덱스를 PostgreSQL이 이용하려면 몇 가지 커스텀 연산자 클래스가 존재해야 합니다. 이것은 로케일 비교 규칙은 무시하면서 엄격한 문자별 비교를 수행하는 인덱스의 생성을 허용합니다. 다른 방법은 C 콜레이션을 사용하여 인덱스를 생성하는 것입니다.

PostgreSQL 데이터베이스 Role



PostgreSQL 데이터베이스 Role


 - PostgreSQL은 role 이라는 개념을 사용하여 데이터베이스 액세스 권한을 관리합니다.

 - role의 개념은 "사용자" 및 "그룹"의 개념을 포함합니다.

 - 데이터베이스 role은 운영체제 사용자와는 다른 개념입니다.

 - 특정 데이터베이스 연결에 사용할 role 이름은 애플리케이션 특정 방식의 연결 요청을 초기화하는 클라이언트에 의해 표시됩니다. 예를들면, psql 프로그램은 -U 옵션을 사용하여 연결한 role을 표시합니다. 다수의 애플리케이션이 기본적으로 현재 운영체제 시스템 사용자의 이름을 가정합니다. (createuser 및 psql 포함) 따라서 role 및 운영체제 사용자 간에 네이밍 연관성을 유지하는 것이 편리합니다.

 - 오라클 처럼 user와 role이 분리된 개념이 아니라, user=role 느낌으로 사용됩니다. 



role 생성


postgres=#CREATE ROLE [name];


role 삭제


postgres=# DROP ROLE [name];


role 확인


postgres=# \du

postgres=# select rolname from pg_roles;




※ role  속성


 - 로그인 권한 :

 postgres=# CREATE ROLE [name] LOGIN;

 postgres=# CREATE ROLE [name];


 Login 속성이 있는 role은 "데이터베이스 사용자"와 동일한 것으로 간주 될 수 있습니다.

 CREATE USER는 디폴트로 LOGIN 권한을 준다는 접에서 CREATE ROLE과 다릅니다.


 - 슈퍼유저:

 postgres=# CREATE ROLE [name] SUPERUSER;


 데이터베이스 슈퍼유저는 로그인 권한을 제외한 모든 권한 검사를 건너뜁니다. 

 아무에게나 슈퍼유저 권한을 주는 것은 위험합니다.


 - 데이터베이스 생성:

 postgres=# CREATE ROLE [name] CREATEDB;


 - role 생성:

 postgres=# CREATE ROLE [name] CREATEROLE;


 role을 추가적으로 생성하려면 권한이 명시적으로 role에 주어져야 합니다.

 CREATEROLE 권한이 있는 role은 다른 role을 변경, 삭제할 수 있으며, 멤버십을 부여 또는 취소할 수 있습니다.

 단 슈퍼유저의 role은 수정 불가.


 - 복제 초기화:

 postgres=# CREATE ROLE [name] REPLICATION LOGIN;


 streaming replication을 초기화하려면 필요한 권한입니다.

 해당 role은 항상 LOGIN 권한을 같이 가지고 있어야 합니다.


 - 패스워드:

 postgres=# CREATE ROLE [name] PASSWORD 'string';


 패스워드는 데이터베이스에 연결할 때 사용자가 패스워드를 입력해야 하는 클라이언트 인증 방법인 경우에만 중요합니다.

 password 및 md5 인증방법은 패스워드를 이용합니다.

 


 - role의 속성은 ALTER ROLE 명령으로 수정할 수 있습니다.



role 멤버십


 - 권한 관리의 편의상 사용자를 그룹으로 묶는 것이 편리할 수 있습니다. 이렇게 하면 권한을 그룹단위로 부여하거나 취소할 수 있습니다. PostgreSQL에서 이것은 그룹을 나타내는 role을 생성한 다음, 그룹 role의 멤버십을 개별 사용자 role에 부여하면 됩니다.

 - 그룹 role을 설정 하려면 먼저 role을 생성해야 합니다.

 - 그룹 role이 존재하는 경우 grant 및 revoke 명령을 사용하여 멤버를 추가 및 삭제할 수 있습니다.


 쉽게 설명하자면 아래와 같습니다.


 두개의 role을 만들어 봅니다.

 postgres=# CREATE ROLE user01 login;

 postgres=# CREATE ROLE admin;

 postgres=# GRANT admin to user01;

 postgres=# ALTER role admin WITH createdb createrole;


 user01을 이용해서 로그인은 가능하지만, admin으로는 로그인이 되지 않습니다.

 

 postgres@psql-db01:/usr/local/pgsql/data]$ psql -d postgres -U admin

 psql: FATAL:  role "admin" is not permitted to log in

 

 postgres@psql-db01:/usr/local/pgsql/data]$ psql -d postgres -U user01

 psql (9.6.11)

 Type "help" for help. 

 

 postgres=>


 user01은 로그인은 가능하지만 권한이 없기 때문에 create database 명령은 사용할 수 없습니다.

 

 postgres=> create database test02;

 ERROR:  permission denied to create database

 postgres=>

 

 

 user01은 슈퍼유저가 아니기 때문에 postgres에 롤로 전환할 수 없습니다.


 postgres=> set role postgres;

 ERROR:  permission denied to set role "postgres"



 하지만 admin에 속해 있기 때문에 admin으로 전환은 가능합니다.

 admin으로 전환해서 create database 명령을 사용 가합니다.


 postgres=> set role admin;

 SET

 postgres=> create database test03;

 CREATE DATABASE

 

 이렇게 여러가지 role을 만들어서 멤버십을 통해 다양하게 활용할 수 있습니다.

 

 role 간에 전환은 set role [name]; 명령으로 합니다.

 

 처음에 접속한 role로 돌아가려면


 set role [처음 접속한 role name];

 set role none;

 reset role;


 셋중 하나로 원상 복구가 됩니다.

 oracle로 치면 SQL> 안에서 conn 명령으로 유저가 이동하는것과 비슷한데, 허용된 멤버십 안에서 이동이 가능한 것 입니다.




 

'Database > PostgreSQL' 카테고리의 다른 글

PostgreSQL 백업 및 복원  (0) 2019.01.01
PostgreSQL 로컬라이제이션  (0) 2019.01.01
PostgreSQL 데이터베이스 Role  (0) 2019.01.01
PostgreSQL 아카이브 모드  (0) 2019.01.01
PostgreSQL 커널 리소스 관리  (0) 2019.01.01
PostgreSQL 모니터링  (0) 2018.12.31

PostgreSQL 아카이브 모드



PostgreSQL 아카이브 모드 (Archive Mode)



- PostgreSQL에서 아카이브 모드를 이해하기전에 WAL을 자세히 알고 넘어가야 할 필요가 있습니다. 

- WAL (Write-Ahead Logging)은 데이터 무결성을 보장하는 표준 방법입니다.

- WAL의 중심개념은 변경 내용을 설명하는 로그 레코드를 영구적 저장소에 먼저 기록한 후에 데이터 파일의 변경 내용을 작성한 다는 것입니다. (오라클의 redo-archive와 비슷한 역할)

- 충돌 발생시 로그를 사용하여 데이터베이스를 복구 할 수 있으므로 트랜잭션 커밋마다 데이터 페이지를 디스크에 쓸 필요가 없습니다. 데이터 페이지에 적용되지 않은 변경 내용은 로그 레코드에서 실행 취소가 가능합니다. (이것은 roll-forward 복구 이며, REDO라고도 합니다.)

- 로그 파일은 순차적으로 작성되며, 로그 파일 동기화 비용은 데이터 페이지 쓰기 비용보다 훨씩 적습니다. 

- 서버가 소규모 도시 트랜잭션을 다수 처리하는 경우 로그 파일의 fsync 하나로 여러가지 트랜잭션을 충분히 커밋할 수 있습니다.

- 온라인 백업 및 PIT(point-in-time) 복구를 지원 가능하게 합니다.




 ※ $PGDATA 밑에 있는 postgresql.conf 파일안의 파라미터 값 수정하여 설정 가능합니다.


 ◆ wal_level (enum)

 - wal_level은 WAL에 기록되는 정보의 양을 결정합니다. 기본값은 충돌 또는 즉시 셧다운으로부터 복구하기 위해 필요한 정보만 기록하는 minimal 입니다.

  

  minimal : 기본값

  archive : WAL 아카이브에 필요한 로깅만 추가. 

  hot_standby : 대기 서버에서 읽기전용 쿼리에 필요한 정보를 추가.


 ◆ archive_mode (boolean)

  -archive_mode를 사용하는것으로 설정하면 완료된 WAL 세그먼트가 archive_command 설정에 의하 아카이브 저장소로 전달 됩니다. archive_mode 및 archive_command는 별개의 변수이므로 아카이빙 모드를 해지하지 않고도 archive_command를 변경할 수 있습니다. 이 매개변수는 서버 시작 시 설정됩니다. wal_level이 minimal로 설정된 경우 archive_mode를 사용 할 수 없습니다.

 

 ◆ archive_command (string)

  - 완료된 WAL 파일 세그먼트를 아카이브하기 위해 실행하는 로컬 쉘 명령입니다.

  - String : %p 아카이브할 파일의 경로명으로 대체

             %f 파일명으로만 대체


 ◆ archive_timeout (integer)

  - archive_command는 완료된 WAL 세그먼트를 호출합니다. 그러므로 서버에는 WAL 트래픽이 발생되지 않아서 트랜잭션이 완료되는 시간과 아카이브 저장소에서 안전하게 기록 되는 사이에 긴 지연시간이 발생 할 수 있습니다. 데이터가 아카이브되지 않은 채로 방치되지 않게 하기 위해 서버가 새 WAL 세그먼트 파일로 주기적으로 전환되도록 archive_timeout을 설정할 수 있습니다. 

 - archive_timeout을 매우 짧게 설정하는 것은 저장소를 부풀게 함므로 현명하지 못하며, 1~2분정도로 설정하는 것이 좋습니다.




※ 실제로 설정 해보기


$ vi postgresql.conf


wal_level = archive

archive_mode = on

archive_command = 'cp %p /data/pgsql/archive/arch_%f.arc'

archvie_timeout = 120


:wq!


postgres@psql-db01:/usr/local/pgsql/data]$ pg_ctl stop

waiting for server to shut down.... done

server stopped

[1]+  Done                    postgres -D /usr/local/pgsql/data > /var/postgresql/log/postgresql.log 2>&1  (wd: /var/postgresql/log)

(wd now: /usr/local/pgsql/data)

postgres@psql-db01:/usr/local/pgsql/data]$ postgres -D /usr/local/pgsql/data >/var/postgresql/log/postgresql.log 2>&1 &

[1] 52044

$



※ 아카이브 모드 확인하기


postgres=# select * from pg_settings

           where name in ('archive_mode', 'archive_command', 'archive_timeout', 'wal_level');

postgres=# show wal_level;

postgres=# show archive_mode;

postgres=# show archive_command;



로그 스위치


pg_switch_xlog() / pg_xlogfile_name / pg_xlogfile_name_offset



pg_switch_xlog() - 현재 사용중인 로그 파일을 아카이빙하고 새로운 파일로 스위칭 함.


postgres=# select pg_switch_xlog();

 pg_switch_xlog 

----------------

 0/70000B0

(1 row)


pg_xlogfile_name / pg_xlogfile_name_offset - 아카이빙 된 파일명 출력


postgres=# select pg_xlogfile_name('0/3000078'), pg_xlogfile_name_offset('0/3000078');

     pg_xlogfile_name     |    pg_xlogfile_name_offset     

--------------------------+--------------------------------

 000000010000000000000003 | (000000010000000000000003,120)

(1 row)



현재 사용중인 로그 확인


pg_current_xlog_locatioin() : 현재 사용중인 로그 출력


postgres=# select pg_current_xlog_location();

 pg_current_xlog_location 

--------------------------

 0/41000060

(1 row)




'Database > PostgreSQL' 카테고리의 다른 글

PostgreSQL 로컬라이제이션  (0) 2019.01.01
PostgreSQL 데이터베이스 Role  (0) 2019.01.01
PostgreSQL 아카이브 모드  (0) 2019.01.01
PostgreSQL 커널 리소스 관리  (0) 2019.01.01
PostgreSQL 모니터링  (0) 2018.12.31
PostgreSQL 점검  (0) 2018.12.31

PostgreSQL 커널 리소스 관리



공유 메모리 및 세마포어


 - 공유 메모리 및 세마포어는 통칭 "System V IPC"라고 합니다. 윈도우 외에, PostgreSQL이 이러한 기능에 대한 자체적인 구현을 제공하는 경우 PostgreSQL을 실행하기 위해 이러한 기능이 요구됩니다.


 - PostgreSQL은 서버 사본별로 System V 공유 메모리 수 바이트가 필요합니다. (64비트 플랫폼의 경우 보통 48바이트)


 - 서버 사본은 다수 실행 중이거나 다른 애플리케이션도 System V 공유 메모리를 사용중인 경우 바이트 단위의 공유 메모리 최대 크기인 SHMMAX를 늘려야 하거나 시스템 차원(system-wide)의 System V 공유 메모리인 SHMALL을 늘려야 할 수 있습니다. SHMALL은 여러 시스템에서 바이트 단위가 아니라 페이지 단위로 처리된다는 점에 유의해야 합니다.


 - PostgreSQL은 16개 한 세트로, 허용된 연결당 (max_connections) 및 autovacuum worker 프로세스당(autovacuum_max_workers) 1개의 세마포어를 사용합니다. 시스템에서 세마포어 최대 수는 SEMMNS에 의해 설정되며, 따라서 최소한 max_connection + autovacuum_max_workers + 각각 허용된 16개의 연결에 1추가 + worker여야 합니다. 각각의 세트마다 다른 애플리케이션에서 사용되는 세마포어 세트와의 충돌을 감지하기 위한 "매직 넘버"가 17번째 세마포어에 포함되어 있습니다.


 ceil((max_connection + autovacuum_max_workers +4)/16)*17외 다른 애플리케이션의 여유분.




리눅스에서의 세마포어


 - 최대 세그먼트 크기 기본값은 32MB 이며, 최대 총 크기 기본값은 2097152 페이지 입니다. "huge page"를 이용한 특수 커널 구성일 때 외에는 페이지는 거의 항상 4096바이트 입니다. (확인하려면 getconf PAGE_SIZE 사용).


 - 공유 메모리 크기설정은 sysctl 인터페이스를 통해 변경이 가능합니다. 예를 들어, 16GB를 허용하려면 다음과 같이 합니다.


 # sysctl -w kernel.shmmax=17179869184

 # sysctl -w kernem.shmall=4194304


 또한, /etc/sysctl.conf 파일에서 리부팅 사이에서도 이 설정을 보존할 수 있습니다. 



'Database > PostgreSQL' 카테고리의 다른 글

PostgreSQL 데이터베이스 Role  (0) 2019.01.01
PostgreSQL 아카이브 모드  (0) 2019.01.01
PostgreSQL 커널 리소스 관리  (0) 2019.01.01
PostgreSQL 모니터링  (0) 2018.12.31
PostgreSQL 점검  (0) 2018.12.31
PostgreSQL 권한 부여 및 해제  (0) 2018.12.31

PostgreSQL 모니터링



PostgreSQL 모니터링


pg_catalog : tables, views, functions, indexes, fdw, trigger, constraints, rules, users, groups 등등 정보 저장

pg_attribute : 테이블 및 다른 pg_class object columns에 대한 정보 저장

pg_index : 인덱스에 대한 정보 저장

pg_depend : object dependent에 대한 정보

pg_rewrite : 테이블 및 view 에 대한 rules 정보 

pg_constraint : 테이블에 constraint 정보 

pg_stat_statements : db에서 수행된 쿼리에 대한 통계 정보

pg_buffercache : cache에 대한 deeper 한 정보

information_schema : 메타 정보를 담고 있는 view



1. 접속된 사용자 확인


SELECT datname, usename, client_addr, client_port, application_name FROM pg_stat_activity;


5초 간격으로 위에 SQL를 수행 => \watch 5



2. Active 세션 확인


SELECT datname, usename, state, query FROM pg_stat_activity WHERE state = 'active';


* 세션에 대한 detail한 분석은 pg_stat_statement 설치 (real-time performance for query)



3. long 실행 쿼리 확인


SELECT

    current_timestamp - query_start AS runtime,

    datname, usename, query

FROM pg_stat_activity 

WHERE state = 'active' ORDER BY 1 DESC;



1분 이상 실행되는 쿼리 확인


SELECT

    current_timestamp - query_start AS runtime,

    datname, usename, query

FROM pg_stat_activity

WHERE state = 'active'

    AND current_timestamp - query_start > '1 min'

ORDER BY 1 DESC;



4. query를 process title에 보이도록 설정 (postgresql.conf)


  update_process_title = on



5. wait 또는 blocking 되는 세션 확인


SELECT datname, usename, query FROM pg_stat_activity WHERE waiting = true;




6. query block user 찾기


SELECT

    w.query AS waiting_query,

    w.pid AS waiting_pid,

    w.usename AS waiting_user,

    l.query AS locking_query,

    l.pid AS locking_pid,

    l.usename AS locking_user,

    t.schemaname || '.' || t.relname AS tablename

 FROM pg_stat_activity w

       JOIN pg_locks l1 ON w.pid = l1.pid AND NOT l1.granted

       JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted

       JOIN pg_stat_activity l ON l2.pid = l.pid

       JOIN pg_stat_user_tables t ON l1.relation = t.relid

 WHERE w.waiting;



7. kill session


  postgresql에 세션 kill 아래 3가지 순서로 차례로 진행하면 됨 (1이 안되면 2, 2 안되면 3)


  1) pg_cancel_backend(pid)          -- current query kill and not disconnect

  2) pg_terminate_backend(pid)        -- connection disconnect. 

  3) kill -9 process



  10분동안 유휴 상태인 세션 kill


  SELECT pg_terminate_backend(pid) FROM pg_stat_activity

  WHERE state = 'idle in transaction' AND current_timestamp - query_start > '10 min';



  현재 세션을 제외한 모든 세션 kill.


  SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();



  작업을 위해 해당 데이터베이스 접속을 막음.


  UPDATE pg_database set datallowconn = 'false' WHERE datname = 'database to drop';



8. long 쿼리 수행시 timeout 설정


set statement_timeout to '10 s'


select ~~ from ~ : long query..



9.  2pc 사용시 lock 확인 


SELECT t.schemaname || '.' || t.relname AS tablename,

       l.pid, l.granted

       FROM pg_locks l JOIN pg_stat_user_tables t

       ON l.relation = t.relid;


아래 결과중 pid가 표시 되지 않는 부분


 tablename | pid | granted 

-----------+-----+---------

 test1     |     | t

 test1     | 111 | f


 

10. 테이블 사용량 확인


초기 사용량 저장


CREATE TEMPORARY TABLE tmp1_201512 AS  SELECT * FROM pg_stat_user_tables;




현시점 사용량이 비교하여 수치가 달라진 테이블 출력 


SELECT * FROM pg_stat_user_tables n

  JOIN tmp1_201512 t

    ON n.relid=t.relid

   AND (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del)

    <> (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);



기존 통계 데이터 초기화


SELECT pg_stat_reset();



테이블 정보를 snapshot 생성 (cron 또는 pg_agent, bgworker 활용)


INSERT INTO stat1_201512

SELECT current_timestamp AS snaptime, * FROM pg_stat_user_tables;




11. 테이블 마지막 사용 일자 확인


-- 아래 function 수행


do $$

         PERFORM table_file_access_info('test1', 'test');

$$  -- output이 없음



select table_file_access_info('test1', 'test');


-- 일자 확인 function 생성


CREATE OR REPLACE FUNCTION table_file_access_info(

   IN schemaname text, IN tablename text,

   OUT last_access timestamp with time zone,

   OUT last_change timestamp with time zone

   ) LANGUAGE plpgsql AS $func$

DECLARE

    tabledir text;

    filenode text;

BEGIN

    SELECT regexp_replace(

        current_setting('data_directory') || '/' || pg_relation_filepath(c.oid),

        pg_relation_filenode(c.oid) || '$', ''),

        pg_relation_filenode(c.oid)

      INTO tabledir, filenode

      FROM pg_class c

      JOIN pg_namespace ns

        ON c.relnamespace = ns.oid

       AND c.relname = tablename

       AND ns.nspname = schemaname;

    RAISE NOTICE 'tabledir: % - filenode: %', tabledir, filenode;

    -- find latest access and modification times over all segments

    SELECT max((pg_stat_file(tabledir || filename)).access),

           max((pg_stat_file(tabledir || filename)).modification)

      INTO last_access, last_change

      FROM pg_ls_dir(tabledir) AS filename

      -- only use files matching <basefilename>[.segmentnumber]

     WHERE filename ~ ('^' || filenode || '([.]?[0-9]+)?$');

END;

$func$;




12. TEMP 데이터 사용률 확인


1) 별도 temporary 테이블스페이스 사용 확인


 SELECT current_setting('temp_tablespaces');  -- 결과가 없으면 default tablespace 사용



2) 1의 결과가 나오면 다음 SQL를 사용하여 사용량 확인


WITH temporary_tablespaces AS (

  SELECT unnest(string_to_array(

    current_setting('temp_tablespaces'), ',')

  ) AS temp_tablespace

)   

SELECT tt.temp_tablespace,

  pg_tablespace_location(t.oid) AS location,

  -- t.spclocation AS location, -- for 9.0 and 9.1 users

  pg_tablespace_size(t.oid) AS size

FROM temporary_tablespaces tt

JOIN pg_tablespace t ON t.spcname = tt.temp_tablespace

  ORDER BY 1;


3) 1의 결과가 안나오면 다음과 같은 방법으로 사용량 확인


  SELECT current_setting('data_directory') || '$PG_HOME/base/pgsql_tmp'

  SELECT datname, temp_files, temp_bytes, stats_reset

  FROM pg_stat_database;



4) 기타 방법


- 실제 파일이 있는 디렉토리를 du -sk 로 확인

- pg_tablespace_location(oid), pg_tablespace_size(oid), pg_tablespace_size(name)함수로 확인



5) 템프 파일 사용량 로깅 


   conf파일에  log_temp_files = 0 or 특정값으로 설정하면 해당 값을 넘어갈때 postgresql log 파일에 로깅됨

   템프 파일 사용량이 증가하면 work_mem값을 크게 설정하여 메모리 사용하도록 유도..




13. Slow query 분석  


     postgresql에 pg_stat_statements 모듈 설치 (create extension pg_stat_statements)

     (해당 모듈 load를 위해 shared_preload_libraries = 'pg_stat_statements' 설정)


     1)  analyze    : db 전체 성능 향상을 위해 해당 명령어 수행 

         (모든 테이블에 통계 정보 Update 및 데이터 정리 작업 수행)


     2) 1번 수행후 별 다른 개선사항이 없으면

        \x

        select * from pg_stat_statements 수행후 결과중 shared_blks_hit 및 shared_blks_read 


        항목 확인


       SELECT pg_relation_size(relid) AS tablesize,schemaname,relname,n_live_tup

       FROM pg_stat_user_tables WHERE relname = 'album';


        n_live_tup 대비 tablesize가 너무 크면 가비지 데이터가 정리가 안된 상황 


        (vacuum 기준 확인 필요)


    3) slow query 자동 실행 계획 log print


        load 'auto_explain'


        (해당 모듈 load를 위해 shared_preload_libraries = 'auto_explain' 설정)


        SET auto_explain.log_min_duration = '5s';    (5초 이상 소요 되는 sql)

        SET auto_explain.log_analyze = true;

        SELECT count(*) FROM pg_class, pg_index

        WHERE oid = indrelid AND indisunique;        -- log 파일에 실행계획이 남음.


        (pg_stat_plans설치 : https://github.com/2ndQuadrant/pg_stat_plans)




14. 버그 분석


     반복적이고 재현 되는 문제에 대해서 pg_dump로 dump file 생성

     pageinspect 패키지로 해당 테이블 데이터 분석



     아래 사이트에서 해당 문제를 찾아보고 해당 문제에 대해서 질의함


     http://wiki.postgresql.org/wiki/Guide_to_reporting_problems


     http://wiki.postgresql.org/wiki/SlowQueryQuestions


     


15. postgresql 로그 관리


     rsyslog 및 logrotate 툴을 사용하여 postgresql log 주기적으로 정리 작업

     (postgresql에 log rotation은  postgresql.conf 파일에  log_rotation_age를 설정)

      => 분석은 pgBadger를 사용하여 분석 


     * postgresql.conf 파일에 로그 정보 출력과 관련된 설정 정보를 설정




16.  쿼리 성능 분석


     위에 pg_stat_statements 설치 되어 있는 상태에서


     1) 자주 호출 되는 SQL 순으로 확인


       SELECT query FROM pg_stat_statements ORDER BY calls DESC;


    2) 평균 수행 시간이 높은 순으로 확인


       SELECT query, total_time/calls AS avg, calls FROM pg_stat_statements 

       ORDER BY 2 DESC;

    


     * SQL에 대한 통계 정보를 초기화 하기 위해서 


     select pg_stat_statements_reset() 호출




17. 명령어 (/h, /?) 수행시 내부 쿼리 확인방법


    \set ECHO_HIDDEN

    \z test


    SELECT * FROM information_schema.views where table_schema IN ('pg_catlog', 'information_schema');   -- 내부 view 확인


    select 'account'::regclass::oid;  -- oid 조회


    select '17260'::regclass::text;



    -- stat 정보 table 및 view 확인


    SELECT relname, case relkind when 'r' then 'table'  WHEN 'v' THEN 'VIEW' END as type FROM pg_class WHERE relname like 'pg_sta%' AND relkind IN ('r','v');




18. 현재 파라미터 셋팅 내용 확인 및 변경


    select current_setting('work_mem');

    show work_mem;


    select set_config('work_mem', '8 MB', true);  --> 로컬 파타미터 값 설정 (false : global 설정)

    select set_config('shared_buffers', '1 GB', false);


    alter system set configuration_parameter { to | = } { value | 'value' | default }


    alter system reset configuration_parameter


    alter system reset all


    SELECT name, current_setting(name), source FROM pg_settings WHERE source IN ('configuration file');



19. 데이터 사용량 확인


    1) 데이터베이스 사용량 확인

 

   SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size       FROM pg_database;

 

   select oid from pg_database;


   du -h /data_dir/base/oid



    2) 테이블 사용량 확인


    SELECT tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) FROM        pg_tables WHERE schemaname = 'car_portal_app' LIMIT 2;


    


   3) 인덱스 사용량 확인


     SELECT indexrelid::regclass,  pg_size_pretty(pg_relation_size(indexrelid::regclass))  FROM       pg_index WHERE indexrelid::regclass::text like 'car_portal_app.%' limit 2;




20. Object 사용여부 확인


     사용하지 않은 테이블 2개 출력


     SELECT relname FROM pg_stat_user_tables WHERE n_live_tup= 0 limit 2;

  

     SELECT schemaname, tablename, attname FROM pg_stats WHERE null_frac= 1 

        and schemaname NOT IN ('pg_catalog', 'information_schema') limit 1;



     인덱스가 사용되는지 확인


     SELECT schemaname, relname, indexrelname FROM pg_stat_user_indexes s 

       JOIN pg_index i ON s.indexrelid = i.indexrelid WHERE idx_scan=0 AND NOT indisunique 

       AND NOT indisprimary;



    foreign key 확인


    SELECT * FROM pg_constraint WHERE contype = 'f';




    인덱스 중복 확인 SQL


    WITH index_info AS

      (SELECT pg_get_indexdef(indexrelid) AS index_def, 

       indexrelid::regclass index_name , 

       indrelid::regclass table_name, 

       array_agg(attname) AS index_att

       FROM pg_index i 

       JOIN pg_attribute a ON i.indexrelid = a.attrelid

       GROUP BY pg_get_indexdef(indexrelid), indrelid,  indexrelid

       )

     SELECT DISTINCT

     CASE WHEN a.index_name > b.index_name THEN a.index_def ELSE b.index_def END AS index_def,

     CASE WHEN a.index_name > b.index_name THEN a.index_name ELSE b.index_name END AS index_name,

       CASE WHEN a.index_name > b.index_name THEN b.index_def ELSE a.index_def END AS overlap_index_def,

     CASE WHEN a.index_name > b.index_name THEN b.index_def ELSE a.index_def END AS overlap_index_name, a.table_name

     FROM

     index_info a INNER JOIN index_info b ON (a.index_name != b.index_name 

     AND a.table_name = b.table_name AND a.index_att && b.index_att );



PK 및 Unique 인덱스를 가지고 있지 않은 테이블 확인


SELECT table_catalog, table_schema, table_name

FROM

  information_schema.tables

WHERE

  table_schema NOT IN ('information_schema', 'pg_catalog')

EXCEPT

SELECT

  table_catalog, table_schema, table_name

FROM

  information_schema.table_constraints

WHERE

  constraint_type IN ('PRIMARY KEY', 'UNIQUE') AND

  table_schema NOT IN ('information_schema', 'pg_catalog');



중복된 키를 테이블에서 삭제.


with should_not_delete as (

  SELECT min(ctid) FROM duplicate group by f1, f2

) DELETE FROM duplicate WHERE ctid NOT IN (SELECT min FROM should_not_delete);


CREATE TABLE <tmp> AS SELECT DISTINCT * FROM <orig_tbl>;


DROP TABLE <orig_tbl>;


ALTER TABLE <tmp> RENAME TO <orig_tbl>;


DELETE FROM dup_table a USING dup_table b

WHERE a.tt1 = b.tt1 AND ... AND b.attn= b.attn

AND a.pk < p.pk.




21. 데이터베이스 lock 관리


lock를 가지고 있는 쿼리 확인


\x


SELECT

  lock1.pid as locked_pid,

  stat1.usename as locked_user,

  stat1.query as locked_statement,

  stat1.state as state,

  stat2.query as locking_statement,

  stat2.state as state,

  now() - stat1.query_start as locking_duration,

  lock2.pid as locking_pid,

  stat2.usename as locking_user

FROM pg_catalog.pg_locks lock1

     JOIN pg_catalog.pg_stat_activity stat1 on lock1.pid = stat1.pid

     JOIN pg_catalog.pg_locks lock2 on

  (lock1.locktype,lock1.database,lock1.relation, lock1.page,lock1.tuple,lock1.virtualxid, lock1.transactionid,lock1.classid,lock1.objid, lock1.objsubid) IS NOT DISTINCT FROM

        (lock2.locktype,lock2.DATABASE, lock2.relation,lock2.page, lock2.tuple,lock2.virtualxid, lock2.transactionid,lock2.classid, lock2.objid,lock2.objsubid)

     JOIN pg_catalog.pg_stat_activity stat2 on lock2.pid = stat2.pid

WHERE NOT lock1.granted AND lock2.granted;



22. 메모리 설정


  1) shared buffers (shared_buffers, default 32MB) : 전체 메모리에 25% 설정

  2) working memory (work_mem, default 1MB) : work_mem * 전체 커넥션수

  3) checkpoint_segments : 값을 높이면 쓰기 성능이 좋아지고, 낮추면 복구 시간이 빨라짐.

  4) effective_cache_size : disk caching, 전체에 50 ~ 70%

  5) random_page_cost (default 4.0) : ssd, san 3, 1.5 ~ 2.5 


'Database > PostgreSQL' 카테고리의 다른 글

PostgreSQL 아카이브 모드  (0) 2019.01.01
PostgreSQL 커널 리소스 관리  (0) 2019.01.01
PostgreSQL 모니터링  (0) 2018.12.31
PostgreSQL 점검  (0) 2018.12.31
PostgreSQL 권한 부여 및 해제  (0) 2018.12.31
PostgreSQL 유저 생성  (0) 2018.12.28

PostgreSQL 점검

PostgreSQL 점검



1. 실시간 쿼리 점검


 postgres=# select * from pg_stat_activity;


 현재 DB에서 돌고 있는 전체적인 쿼리 및 접속정보등을 확인할 수 있습니다.


 xbackend_min 


 pg_stat_activity의 컬럼 중에 가장 중요한 정보는 xbackend_min 입니다. 

 이 값이 있는 쿼리는 현재 해당 쿼리 작업을 처리해야 진행된다는 의미입니다. 따라서 현재 xbackend_min이 있는 쿼리 중에서   현재 진행이 되지 않고 있는 쿼리가 있다면 앞의 쿼리 때문에 다음 쿼리가 진행되지 않는 경우를 의미하죠. 이런 경우는 앞에서   update 문 때문에 해당 row의 lock이 결린 경우가 대표적으로 나타납니다. 물론 update가 완료되어 lock이 금방 풀리면 해당   값이 금방 없어지기도 하죠.




2. 현재 테이블에 Lock을 확인하는 쿼리


SELECT t.relname,

       l.locktype,

       page,

       virtualtransaction,

       pid,

       mode,

       granted

  FROM pg_locks l,

       pg_stat_all_tables t

  WHERE l.relation = t.relid

  ORDER BY relation ASC;


 이 쿼리는 현재 테이블에 lock을 확인할 수 있는 쿼리 입니다. 일반적으로 어지간한 lock은 괜찮지만, RowExclusiveLock 같은 것이 검색된다면 해당 테이블에 접근하는 Row에 update등이 현재 지연되어 다른 쿼리에도 영향을 미친다고 판단하시면 됩니다.




3. 해당 작업을 Kill 하는 쿼리


PostgreSQL은 위에서 검색된 pid를 죽일 수 있습니다. 두가지 방법이 있죠.


select pg_cancel_backend(4678249);


SELECT pg_terminate_backend(32519) FROM pg_stat_activity ;


pg_cancel_backend는 해당 PID만 중지시키려고 하고, pg_terminate_backend는 해당 PID와 연계된 모든 상위 Query Process를 모두 중지시킵니다. 따라서 pg_cancel_backend로 해당 작업이 중지되는지 먼저 확인해 보고, 그래도 중지되지 않는다면 pg_terminate_backend를 수행해 보시면 됩니다.




4. 해당 작업들의 주의점


위의 모니터링 쿼리 및 작업을 Kill하는 쿼리도 사실 Transaction을 탈 수 있습니다. 따라서 Rollack, Commit이 되는 DB도구를 활용하시거나, 터미널에서 transaction으로 처리하시고, 이상없는지 확인하신 다음 Commit을 하시는 것이 DB를 안전하게 사용하시고, 데이터를 잘 관리할 수 있는 방법이라고 할 수 있습니다.

'Database > PostgreSQL' 카테고리의 다른 글

PostgreSQL 커널 리소스 관리  (0) 2019.01.01
PostgreSQL 모니터링  (0) 2018.12.31
PostgreSQL 점검  (0) 2018.12.31
PostgreSQL 권한 부여 및 해제  (0) 2018.12.31
PostgreSQL 유저 생성  (0) 2018.12.28
PostgreSQL 스키마 생성  (0) 2018.12.28