본문 바로가기
728x90
반응형

전체 글419

[PostgreSQL] Schema 관리(생성/추가/변경/삭제) 1. postgresql schema 구조 - schema는 object들의 논리적인 집합을 의미한다. - TABLE, VIEW, SEQUENCE, SYSNONYM, DOMAIN, FUNCTION 등의 OBJECT들로 구성된다. - SCHEMA를 사용하는 이유는 논리적집합체를 만들어 관리 편의성을 높이고 여러 사용자의 간섭없이 접속할 수 있게 한다. 2. 스키마 조회 - \dn 명령어로 조회 가능 3. 스키마 생성 (1) CREATE SCHEMA [schema_name] AUTHORIZATION [user_name] [ schema_element [ . . . ] ]; - 이름을 입력하지 않을 경우 USER의 이름을 schema 이름으로 사용한다. (pg_ 로 시작하는 이름은 스키마 이름으로 불가능) (.. 2021. 7. 16.
[PostgreSQL] SQL 파일 실행 postgres 계정에서 실행합니다. [root@localhost ~]$ su - postgres 비밀번호 입력 : [postgres@localhost ~]$ [postgres@localhost ~]$ psql -U postgres -d postgres -a -f /var/lib/pgsql/11/database.sql psql -U [database_user] -d [database_name] -a -f [sql file 절대경로] * postgres 계정으로 실행하면 관리자 권한이 없어 파일이 검색되지 않거나 찾지 못하는 경우가 많으니 postgres가 소유권을 가진 폴더로 sql 파일을 이동시켜 놓은 후 pwd로 정확한 현재 경로 확인해서 psql 명령어 실행하는게 간편하고 빠릅니다. 출처: http.. 2021. 7. 16.
[PostgreSQL] 문자열 함수 (자르기, 붙히기, 치환 등) 문자열 함수 btrim : 문자열의 시작과 끝 양쪽에 지정된 문자들을 제거 char_length : 문자열의 길이를 반환 character_length : 지정된 문자열의 길이를 반환 || : 2개의 문자열을 합하여 반환 initcap : 문자열 중 첫번째 문자는 대문자로 변환하고 나머지는 소문자로 변환 length : 문자열의 길이를 반환 lower : 지정된 문자열을 모두 소문자로 변환 lpad : 지정된 문자열을 원하는 길이로 맞추는데, 이때 부족한 문자를 왼쪽에 채움 ltrim : 문자열의 왼쪽에서 지정된 모든 문자를 제거 position : 문자열에서 지정된 문자열이 존재하는 인덱스값을 반환 repeat : 문자열을 원하는 만큼 반복해서 반환 replace : 기존의 문자열을 구성하는 부분 문자.. 2021. 7. 16.
[PostgreSQL] 사용하지 않는 인덱스 조회 및 성능 개선 인덱스(Index) 혹은 색인은 테이블 설계시 많이 고민하는 부분이고 성능 향상에 많은 도움을 주고 있지만 프로그램의 설계 변경 혹은 기능 개선등 여러 상황에 따라 사용하지 않는 인덱스가 발생하게 됩니다. 여러 상황 때문에 현재는 사용하지 않는 인덱스를 찾아보기 위한 간략한 정리 내용입니다. 인덱스 구조 이해를 위한 예시 인덱스는 지정한 컬럼에 대한 매핑 정보를 가지고 있습니다. 사용하지 않는 인덱스를 찾아봐야되는 이유 보통 쿼리 튜닝을 위해 운영중인 데이터베이스에서 EXPLAIN 명령어를 많이 사용하고 필요시 인덱스 추가는 생기지만 삭제는 거의 없을거라 생각됩니다. 만약 그렇다면 극단적으로 생각했을때 오래된 프로그램 일 수록 추가된 인덱스는 많지만 삭제된 인덱스가 없다는건 불필요한 인덱스도 많을수 있다.. 2021. 7. 16.
[PostgreSQL] 암호화 함수 사용 (pgcrypto) 1. 확장 모듈 설치 create extension pgcrypto; enterprisedb 계정으로 해당 모듈을 설치할 스키마 선택 후 실행. 암호화 관련 함수 생성 2. Raw encryption functions convert_to/convert_from : 문자열 변환/복원 encode/decode : 16진수 인코딩/디코딩 encrypt/decrypt : 암호화/복호화 예제) -> ENC_KEY는 실제 사용할 암호화KEY로 대체해야 함. * 암호화 : utf8로 변환한 후, 암호화키로 'aes'알고리즘을 사용하여 암호화한 후, 그 값을 16진수(hex)로 encoding함 select encode(encrypt(convert_to('홍길동','utf8'),'ENC_KEY','aes'),'hex'.. 2021. 7. 16.
[PostgreSQL] 배열형 컬럼 다루기 PostgreSQL 배열형 컬럼 선언 - 데이터 타입 선언 시 ' [] '만 붙이면 배열형 컬럼이 됨 ● 배열형 컬럼 생성 CREATE TABLE member( id serial PRIMARY KEY, name varchar(20), age integer, hobby varchar(100)[] -- 배열형 컬럼 ); ● 배열형 컬럼에 데이터 추가하기 -- 배열형 데이터를 {} 안에 ","로 구분해서 입력한다. INSERT INTO member (name, age, hobby) VALUES('kim', 10, '{book, music}'); -- 데이터를 하나만 넣을 때는 위와 같이 쓴다. INSERT INTO member (name, age, hobby) VALUES('lee', 12, '{game}').. 2021. 7. 16.
[PostgreSQL] 슬로우쿼리를 잡아내는 3가지 방법 쿼리 시간이 어느정도 느려지면, 쿼리 실행문을 로그에 남기기 위해 postgresql.conf에 설정값 추가한다. log_min_duration_statement = 5000 그리고 config를 reload 해주면 된다. SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) 특정 데이터베이스, 특정 유저 권한에서 발생한 슬로우쿼리만 모니터링 할 수도 있다. ALTER DATABASE test SET log_min_duration_statement = 5000; ALTER DATABASE 남긴 로그 확인 이렇게 하면, 부하를 유발하는 단일 쿼리를 파악하기 쉽다. 그러나 처리 시간은 빠르지만, 여러번 호출되서 부하를 발생시키는 쿼리 실행을 .. 2021. 7. 16.
[PostgreSQL] DB Lock을 줄이는 7가지 팁 1. Default 값이 있는 필드를 추가하면 안된다 PostgreSQL 10이하 버전에서, Default 값이 있는 필드를 추가하면, 테이블 락이 걸릴 수 있다. 그리고 엄청 느리다. 그래서 이런 쿼리를 날리면 안된다. ALTER TABLE items ADD COLUMN updated_at timestamptz DEFAULT now(); 기본값이 없는 필드를 추가한 후, UPDATE를 날리거나 ALTER TABLE items ADD COLUMN updated_at timestamptz; UPDATE items SET updated_at = now(); 이렇게 쪼개서, UPDATE를 해주는게 좋다. ( 한번에 전체 테이블을 Update를 하면, 오래걸리니까. ) do { numRowsUpdated = e.. 2021. 7. 16.
[PostgreSQL] DBLINK 설정 및 사용 * DB LINK? - 현재 데이터베이스 세션 내에서 물리적으로 다른 곳에 위치한 데이터베이스 연결을 지원하는 확장성 모듈. - EXTENSION MODULE - extension 설치 후, 사용자 설정에 맞춰 사용하고 삭제하지 않는 이상 재설치는 불가. - 스키마 지정이 없으면 default로 public에 설치가 되므로 스키마별로 사용이 필요한 경우, ALTER로 변경하거나 삭제 후 재설치하여 사용. * DB LINK EXTENSION 설치 CREATE EXTENSION [ IF NOT EXISTS ] extension_name [WITH] [SCHEMA schema_name] [VERSION version] [FROM old_version] * DB LINK 관리 CREATE EXTENSION IF.. 2021. 7. 16.
[PostgreSQL] 테이블 lock 조회 및 kill하기 현재 수행중인 SQL 전체 조회 select datname, pid, usename, application_name, client_addr, client_port, backend_start, query_start, wait_event_type, state, backend_xmin query from pg_stat_activity; 위 쿼리 실행시 현재 DB에서 돌고 있는 SQL 전체를 조회할 수 있다. datid oid 데이터베이스oid datname name 데이터베이스 이름 pid integer 프로세스id usesysid oid 사용자고유번호 usename name 사용자이름 application_name text 응용프로그램이름 client_addr inet 접속ip client_hostname .. 2021. 7. 16.
728x90
반응형

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."
loading

티스토리툴바