728x90

전체보기441

[PostgreSQL] 데이터 있으면 update 없으면 insert PostgreSQL 에서 사용하는 upsert구문에 대해 알아보자. (오라클에서는 merge into / mysql에서는 on duplicate on key update 를 사용한다.) 구문은 다음과 같다. INSERT INTO [TABLE] (COLUMN1, COLUMN2, ...) VALUES (VALUE1, VALUE2, ...) ON CONFLICT [column_name / ON CONSTRAINT constraint_name/ WHERE predicate] [DO NOTHING] [DO UPDATE SET column1 = value1, ...] 기본구문은 이런식으로 이루어져 있고 하나씩 설명하자면 ON CONFLICT column_name : 특정 컬럼명을 기준으로 체크를하며 컬럼을 여러개 .. 2021. 7. 17.

[PostgreSQL] 시퀀스 생성 및 사용 ▷생성 CREATE SEQUENCE [seq_name] ▷nextval nextval('seq_name') ▷currval currval('seq_name') ▷값 초기화 setval('seq_name', seq_val, [true/false]) ▷삭제 DROP SEQUENCE [seq_name] 사용 예제) CREATE SEQUENCE mine_seq; SELECT nextval('mine_seq'); SELECT currval('mine_seq'); DROP SEQUENCE mine_seq; 시퀀스를 생성해놓고 바로 확인을 하기 위해 currval()을 사용하여 확인하게 되면 다음과 같은 에러가 발생하는데 ERROR: 오류: "mine_seq" 시퀀스의 currval 값이 현재 세션에 지정되어 있지 .. 2021. 7. 17.

[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.

[PostgreSQL] 기본 조회 명령어 및 변수 선언, 설정 1) help information - psql의 명령어에 대한 정보를 얻기 위해서는 psql로 접속하여 \? 를 입력하면 명령어에 대한 정보를 얻을 수 있다. - sql에 대한 명령어 정보를 얻고 싶을 때는 \h 를 입력하면 sql명령어에 대한 정보를 얻을 수 있다. 2. database 목록 확인 - \l 명령어를 통해 서버에 접속된 database 목록을 확인할 수 있다. - database 내 relation 정보를 확인하려면 \d를 입력한다. ● \dt : database 내 table 조회 ● \d+ : relation 상세 조회 ● \dS : System table 조회 ● \dv : view 조회 ● \dl : Large object 조회 ● \di : index 조회 ● \df : fun.. 2021. 7. 16.

[PostgreSQL] 계정/유저관리 1. user 조회 select * from pg_shadow; postgres=# select * from pg_shadow; usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig ----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+----------- postgres | 10 | t | t | t | t | md53175bce1d3201d16594cebf9d7eb3f9d | | invako | 16384 | f |.. 2021. 7. 16.

[PostgreSQL] database 관리(생성/추가/삭제/변경) 1. DATABASE 소유 - Database를 생성하기 위해서는 SUPERUSER이거나 CREATEDB 권한을 가진 USER여야 한다. - SUPERUSER는 다른 USER가 만든 DATABASE를 소유할 수도 있지만 그 외 일반 USER들은 자신이 생성한 DATABASE만 소유할 수 있다. 2. DATABASE 조회 => psql -U postgres 접속후 영문 소문자 \l 입력하여 조회 postgres=# \l 데이터베이스 목록 이름 | 소유주 | 인코딩 | Collate | Ctype | 액세스 권한 -----------+----------+--------+-------------+-------------+----------------------- comdb | com | UTF8 | en_US.. 2021. 7. 16.

[PostgreSQL] Tablespace 생성 1. TABLESPACE - DB 내의 물리적인 부분으로 실제 데이터를 저장하는 공간이다. 2. TABLESPACE 생성 생성할 폴더를 만든다. mkdir postgre_db 소유권 변경 chown postgres postgre_db postgresql 접속 # psql -U postgres psql (13.3 (Debian 13.3-1.pgdg100+1)) 도움말을 보려면 "help"를 입력하십시오. postgres=# create tablespace invako_tbl owner invako location '/postgre_db'; CREATE TABLESPACE ■ CREATE TABLESPACE [tablespace_name]; ■ option (1) OWNER [user_name] - 입력하여.. 2021. 7. 16.

Postgresql 테이블스페이스 사용량 조회 -- tablespace 총량 select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace; -- table size (index 미포함) select pg_size_pretty(pg_relation_size('table')); -- index size select pg_size_pretty(pg_relation_size('idx1')); -- total size(data + index) select pg_size_pretty(pg_total_relation_size('table1')); -- DB size 단위적용 : pg_size_pretty() 2021. 7. 16.

[ 조은글 5188호 - 지금(now)과 여기(here) ] 합천 해인사 장경판전 주련(기둥에 연이어 걸어놓은 글판)에는 “원각도량하처(圓覺度量何處)”라는 글이 새겨져 있다. “깨달음의 도량 즉 행복한 세상은 어디인가?” 라는 뜻입니다. 그 질문에 대한 답은 맞은 편 기둥에 새겨져 있습니다. “현금생사즉시(現今生死卽時)” “당신의 생사가 있고 당신이 발 딛고 있는 지금 이곳이다.” 지금 살고있는 이 순간, 이곳에 충실하라는 뜻입니다. 삶의 모든 순간은 첫 순간이면서 마지막 순간이고, 유일한 순간입니다. 지금 이 순간은 영원할 수도 있지만 마지막이 될 수도 있는 순간입니다. 평생 일만 하고 사는 바보들이 놓치고 사는 것이 지금입니다. 매 순간을 생애의 마지막인 것처럼 사십시요. 과거에 연연하지 말고, 내일 일을 오늘 걱정하지 마십시요. 어제의 비로 오늘의 옷을 적시.. 2021. 7. 15.

[ 조은글 5187호 - 누군가를 믿는다는 것의 위대함 ] 미국에 사는 한국인 부부가 있다. 남자는 26살인데 운동선수다. 재능은 있지만, 아직 군대를 다녀오지 않은 상태이고, 팔꿈치 수술을 받는 등 안 좋은 일만 가득한 상황이다. 게다가 아이를 포함해 4식구이지만 월급이 100만 원 수준이라, 같은 팀의 세 선수가 함께 월세를 살 정도로 경제적으로도 힘들다. 결국 가족이 겪는 고통을 더는 볼 수 없었던 그는, 아내에게 이렇게 말한다. "한국에 돌아가자. 이젠 힘들 것 같아." 그러자 아내는 단호한 얼굴로 이렇게 응수한다. "나랑 애들 신경 쓰지 말고, 여기서 당신이 할 거 해. 당신이 처음 가졌던 꿈을 이루라고. 여기에 꿈을 이루려고 온 거잖아? 당신에게 방해된다면, 우리는 한국 가면 되니까 당신은 꿈을 포기하지 마!" 당시 아내는 건강도 안 좋은 상태였다. .. 2021. 7. 13.

[ 조은글 5186호 - 꼰대처럼 늙지 않는 ‘12345 법칙’ ] 어떤 마음으로 어떻게 행동해야 ‘어른’ 소리 들을까? “너의 젊음이 너의 노력으로 얻은 상이 아니듯, 내 늙음도 내 잘못으로 받은 벌이 아니다" 영화 은교에 등장해 유명해진 미국 시인 시어도어 루스케의 말처럼, 인간은 누구나 늙는다. 하지만 그렇다고 해서 모두 ‘성숙하게’ 늙는 것은 아니다. 마음 가는대로 거침없이 행동하고 자기 주장만 옳다고 큰소리치면 꼰대라고 손가락질 받기 십상이다. 따라서, 멋지게 늙는 법에도 요령이 필요하다. 창의경영연구소 조관일 대표는 자신의 채널 ‘조관일TV’에서 ‘매력있게 늙어가는 5가지 법칙’에 대해 이야기했다. 각 계명의 첫 음을 따 이름 붙인 ‘12345(일이삼사오) 법칙’은 다음과 같다. 1. 일부러 자주 웃어라! 저 같은 경우도 칩거하면서 하루 종일 연구만 하던 시절.. 2021. 7. 13.

이전 1 ··· 16 17 18 19 20 21 22 23 다음

728x90

C.B.W

전체보기441

티스토리툴바