본문 바로가기
반응형

DBMS/PostgreSQL24

[PostgreSQL] dead tuple 모니터링 시 주의(vacuum full) DBMS: PostgreSQL 14.9- vacuum full 수행 시 pg_stat_all_tables 값(n_live_tup, n_dead_tup) 변경안됨(dead tuple 모니터링 시 주의)- vacuum 수행 시 pg_stat_all_tables 값(n_live_tup, n_dead_tup) 변경됨[목차여기]VACUUM FULL 수행 시 n_dead_tup 값 변화delete 작업으로 데이터 삭제하여 dead tuple 발생 후vacuum full 수행 시 실제 사이즈는 줄어 dead tuple 삭제가 되었지만pg_stat_all_tables의 n_live_tup, n_dead_tup 값은 변경되지 않고ANALYZE 명령으로 통계 갱신 후 업데이트가 되는 것을 확인 할 수 있.. 2024. 4. 21.
[PostgreSQL] postgres 계정 로그인 불가 single 모드 복구 (FATAL: role "postgres" is not permitted to log in) DBMS: PostgreSQL 14.9 postgres계정처럼 superuser의 login 권한을 제거하였을 경우 접속이 불가한 경우 해결하는 방법입니다 싱글모드로 접속하여 postgres 계정에 grant 문을 통한 권한 부여로 복구합니다 [목차여기] postgres 계정 권한 제거 superuser인 postgres 계정의 login 권한을 제거한 후 테스트 시 접속 실패합니다 -- postgres(super user)로그인 권한 제거 postgres=# alter user postgres nologin ; ALTER ROLE postgres=# \du List of roles Role name | Attributes | Member of -------------+-------------------.. 2024. 4. 19.
[PostgreSQL] ERROR: current transaction is aborted 에러 해결 법 DBMS: PostgreSQL 14.9 - BEGIN 명령을 통한 트랜잭션 진행 중 ERROR 발생 시 이후 모든 명령은 에러 발생함 ERROR: current transaction is aborted, commands ignored until end of transaction block - commit 또는 rollback 후 정상 작동 [목차여기] 테스트 테이블 생성 postgres=# create table t1 as select * from pg_tables ; SELECT 66 postgres=# postgres=# select count(*) from t1 ; count ------- 66 (1 row) 테스트 postgresql은 기본 autocommit 으로 작동합니다. 수동으로 begin.. 2024. 4. 7.
[PostgreSQL] pg_dump 파일에서 테이블명 변경하여 복구 DBMS: PostgreSQL 14.9 pg_dump로 백업받은 파일에서 특정 테이블 이름을 변경하여 복구하고자 하는 경우 오라클의 datapump에서의 remap 기능을 현재 PostgreSQL은 지원하지 않습니다 - text format으로 받은 경우 스크립트 수정하여 실행 - custom format으로 받은 경우 스크립트 추출하여 내용 변경 후 실행 - pg_restore -t 옵션 사용 시 해당 오브젝트만 추출(관련 인덱스 등 안나옴)되므로 주의 명령어 예시 -- sk1.dmp 파일에서 tab1 테이블 tab2_all.sql 파일로 추출 pg_restore -f tab2_all.sql -n public -t tab1 sk1.dmp 이번 포스팅은 커스텀 포멧의 백업파일에서 스크립트 추출 후 테이블.. 2024. 3. 12.
[PostgreSQL] WAL 경로 변경하는법 DBMS: PostgreSQL 14.9 [목차여기] 초기생성 시 wal 경로 지정 initdb -D /pg_data -E'UTF8' --locale=en_US.utf8 --waldir=/pg_wal 또는 이미 생성된 기존 경로 변경 시 아래와 같은 순서로 작업 진행 1. pg_ctl stop 2. 신규 경로 디렉토리 생성(/pg_wal) 및 권한 부여 3. 기존 경로 파일 이동($PGDATA/pg_wal -> /pg_wal) 4. 심볼릭 링크 생성 4.1 기존 디렉토리 삭제 (rm -fr $PGDATA/pg_wal) 4.2 심볼릭 링크 생성 (ln -s /pg_wal $PGDATA/pg_wal) 5. pg_ctl start 초기 생성 시 WAL 경로 지정 initdb 명령으로 초기 생성 시 --waldi.. 2024. 3. 7.
[PostgreSQL] auto_explain 사용/미사용 성능 테스트 DBMS: PostgreSQL 14.9 [목차여기] - auto_explain 사용 시 모든 SQL 실행통계 수집(성능저하 발생) - auto_explain.log_min_duration 값은 postgres.log 파일에 로깅 여부만 관여(지정 값보다 빨리 끝나서 미사용시와 동일한 성능나오는거 아님) - auto_explain.log_timing 값이 성능에 가장 많은 영향 - 꼭 사용하고 싶다면 auto_explain.log_timing 파라미터 off 고려해보자(구간별 수행시간 안나옴) 테스트 시나리오 auto_explain의 설정의 주요 파라미터인 아래 3개 파라미터의 값이 다른 환경에서 동일 SQL반복 수행 auto_explain.log_analyze auto_explain.log_buffers.. 2024. 2. 29.
[PostgreSQL] 기본 권한 설정 default privilege(권한 자동 부여) oracle에서는 신규 오브젝트 생성 후 권한을 부여해줘야 합니다. postgresql은 기본권한을 설정하여 신규생성되는 오브젝트에 자동으로 권한을 부여하는 편리한 기능이 있습니다 - 신규 생성되는 오브젝트에 권한을 자동으로 부여하는 방법 - ALTER DEFAULT PRIVILEGES ... 명령으로 수행 - 기존 오브젝트는 적용안되고 기본권한 설정 후 신규 오브젝트에만 적용됨(기본 권한 제거시에도 동일) [목차여기] 명령어 ALTER DEFAULT PRIVILEGES [ FOR USER target_user [, ...] ] [ IN SCHEMA schema_name [, ...] ] grant_or_revoke_clause where grant_or_revoke_clause is one of: GR.. 2024. 2. 15.
[PostgreSQL] pg_hba.conf 우선순위 pg_hba.conf 파일은 PostgreSQL 접속 시 허용 및 차단 등의 정책을 정의하는 파일 입니다. 중복되는 정책이 있을 경우 상위에 정의한 정책이 우선순위가 높음 테스트 시나리오 local에서 postgres 계정으로 접속 시 비밀번호 없어 바로 접속 허용하도록 설정 기존 정책 아래에 추가 pg_hba.conf 파일에 local all postgres trust 정책을 기존 정책 아래에 추가 하였습니다 # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all scram-sha-256 local all postgrestrust # IPv4 local connections:.. 2024. 2. 1.
[PostgreSQL] 데이터 이관 시 ERROR: permission denied for schema COPY 명령등을 사용하여 데이터 이관 작업 진행 시 ERROR: permission denied for schema 에러 발생하는 경우가 있습니다 이런 경우 외래키(foreign key)가 설정되어 있는지 확인이 필요합니다 INSERT 하려는 테이블이 다른 테이블의 데이터를 참조하는 FK(외래키)가 생성되어 있을 때 발생합니다 : 참조되는 테이블의 owner가 작업을 실행하는 계정과 동일하다면 발생하지 않고 다른 경우 발생 PostgreSQL에서는 트리거를 사용하여 외래키(참조 무결성 제약)가 구현되므로 trigger를 비활성화 해주면 됩니다 방법 1 세션 파라미터 변경 session_replication_role 파라미터를 replica로 설정하면 replica 상태의 트리거만 활성화되고 나머지는 비.. 2024. 1. 29.
[PostgreSQL] 열을 행으로 변환하기 UNNEST 함수 배열(array)을 row로 변경 SELECT unnest(ARRAY['V1','V2','V3','V4','V5']) AS c1 ; c1 ---- V1 V2 V3 V4 V5 (5 rows) 배열 개수가 다른경우 NULL 로 표시함 select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b); a | b ---+----- 1 | foo 2 | bar | baz 구분자가 있는 컬럼을 행으로 변경 테스트 테이블 select * from tab1; id | text_data ----+--------------------- 1 | apple,orange,banana 2 | dog,cat,bird,fish 3 | red,green,bl.. 2024. 1. 4.
[PostgreSQL] ANY 연산자 ANY 연산자 PostgreSQL에서 ANY 연산자는 조건에 들어오는 값 중 하나라도 조건을 충족하는지 확인하는 데 사용됩니다. 이미 익숙한 IN 연산자와 유사하게 사용됩니다. 차이점은 ANY는 = 비교외에 부등호(>=, , = ANY(ARRAY[1, 3]); id | number ----+-------- 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 (5 rows) -- 비교조건 중 가장 큰 값보다 작은 데이터 조회 test=# SELECT * FROM test11 WHERE number 2024. 1. 1.
[PostgreSQL] 자동 증가(Auto Increment) 컬럼 사용법 PostgreSQL에서 값이 자동으로 증가하는 컬럼을 만들어 사용하는 방법은 아래 3가지 - 시퀀스 사용 - SERIAL 컬럼 사용 - GENERATED AS IDENTITY 컬럼 사용 여러 특징을 고려했을 때 generated always as identity가 자동증가 컬럼을 사용하는 이유에 가장 적합해 보입니다. 여기서 시퀀스 사용은 오래전부터 많이 사용해 오는 방식이므로 설명하지 않습니다. 1. SERIAL 컬럼 자동으로 시퀀스를 생성하여 채번에 사용함 serial 컬럼은 integer 컬럼 타입으로 생성됨 해당 컬럼에 직접 데이터 입력 가능(시퀀스 번호가 같이 증가하지 않음 주의) create table seri_t1 (c1 serial, c2 varchar(30)) ; 2. IDENTITY 컬.. 2023. 12. 15.
[PostgreSQL] unlogged/logged table 성능비교 PostgreSQL은 테이블에 INSERT/UPDATE/DELETE 등이 발생할 때 변경사항을 WAL에 기록합니다. 테이블 변경 사항을 logging 하지 않으려고 할 때 unlogged 옵션을 사용 할 수 있습니다. - WAL에 변경사항을 로깅하지 않아 일반 테이블보다 좋은 성능 - 테이블 문제 발생 시 복구 불가능 - unlogged → logged 변경 시 로그 발생 및 테이블 lock(SELECT 불가) - replication 구성 환경에서 read 노드로 복제 안됨 - 중요하지 않은 임시 데이터 같은 경우 쓸만할 듯 1. unlogged ↔ logged 변경 명령어 -- unlogged로 변경 alter table {테이블명} set unlogged ; -- logged로 변경 alter ta.. 2023. 12. 14.
[PostgreSQL] 테이블 데이터 text 파일로 저장하기(COPY 명령) PostgreSQL에서 테이블에 있는 데이터를 파일로 저장하고자 할 때 사용하는 COPY 유틸리티 테이블 text 파일 모두 가능해서 유용하게 사용하는 경우가 종종 있습니다. Oracle에서 text파일 데이터를 테이블에 적재할 때 사용하는 SQL*Loaderd와 비슷한 방식이라고 생각하면 됩니다. COPY 명령 PSQL 커맨드라인에서 COPY 명령 실행 참고 사이트 https://www.postgresql.org/docs/current/sql-copy.html -- 테이블 -> text파일 COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | PROGRAM 'command' | STDOUT } [ [ WITH ] ( .. 2023. 12. 12.
[PostgreSQL] SQL 실행계획 관리(pg_store_plans) OS: linux 7.5 DBMS: PostgreSQL 14.9 [목차여기] PostgreSQL은 기본적으로 SQL 실행계획을 저장하지 않습니다. 운영 시 SQL 실행 계획이 변경되었는지 확인하는 것은 중요한 부분 중 하나인데 그게 불가능한 것이죠. pg_store_plans이라는 extension을 사용하면 이 부분이 해결가능한데 pg_store_plans을 설치하고 간단하게 사용해 보는 테스트를 진행해 보겠습니다. rpm 파일 다운 pg_store_plans rpm 파일 다운 사이트 https://github.com/ossc-db/pg_store_planshttps://github.com/ossc-db/pg_store_plans/releases 플랫폼에 해당하는 rpm 다운로드 rpm 설치 [root.. 2023. 12. 5.
반응형

티스토리툴바