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

PostgreSQL 권한 부여 및 해제

Grant & Revoke



Grant - user,group 혹은 모든 user들에게 해당 객체에 대한 사용권한을 승인합니다.


Synopsis :

 

GRANT privilege [,...] ON object [,...]

    TO { PUBLIC | GROUP group | username}


privilege

SELECT : 특정 TABLE/VIEW 의 column에 대한 access 을 승인

INSERT : 특정 TABLE의 모든 column 에 데이타의 삽입에 대한 권한 승인

UPDTAE : 특정 TABLE의 모든 column 의 갱신에 대한 권한 승인

DELETE : 특정 TABLE 의 row 의 삭제에 대한 권한 승인

RULE : 특정 TABLE/VIEW에 대한 rule 을 정의하는 권한에 대한 승인

ALL : 모든 권한을 승인한다.


object

 access 를 승인하는 객체의 이름으로서 다음과 같은 객체들이 있다.


Table

Sequence

View

Index


PUBLIC

 모든 유저를 승인


GROUP group

 사용 권한을 획득할 group을 지정, group 을 명시적으로 생성되어져 있어야 함.


username

 사용권한을 획득할 사용자명. PUBLIC 은 모든 유저에 대해서 적용된다.



Notes

psql 에서 "\z" 를 사용하여 존재하는 객체에 대한 permission 등을 참조할 수 있다.


permission 정보의 형식


username=arwR : 유저에게 승인된 사용권한

group gname=arwR : GROUP 에게 승인된 사용권한 

=arwR : 모든 유저에게 승인된 사용권한


a : INSERT privilege

r : SELECT privilege

w : UPDATE/DELETE privilege

R : RULE privilege

arwR : ALL privilege



예)

postgres=# GRANT INSERT ON imsi_table TO PUBLIC;

postgres=# GRANT ALL ON imsi_table TO test_user;




Revoke - user,group 혹은 모든 user로부터 객체에 대한 사용권한을 무효화합니다.


Synopsis :

 

REVOKE privilege [,...]

    ON object [,...]

    FROM { PUBLIC | GROUP gname | username }


privilege

 SELECT ,INSERT ,UPDATE, DELETE, RULE, ALL


object

 적용될 수 있는 객체 : table, view, sequence, index


group

 privilege 를 취소할 그룹명


username


PUBLIC



예)

postgres=# REVOKE INSERT ON imsi_table FROM PUBLIC;

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

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
PostgreSQL Tablespace 생성  (0) 2018.12.27

PostgreSQL 유저 생성

유저 생성


 - DATABASE에서 USER는 DATABASE를 사용하는 주체로서 OS를 운영하는 USER와는 분리되어 있습니다. USER는 소유하고 있는 DATABASE안에 있는 OBJECT의 권한을 변경하고 제어할 수 있습니다.


 - 유저를 생성하기 위해서는 먼저 DATABASE에서 SUPERUSER권한을 가지고 있어야 합니다. PostgreSQL에 SUPERUSER의 default 계정은 postgres입니다.



유저 조회


postgres=# SELECT * FROM PG_SHADOW;


or


postgres=# \du



 - \du를 입력하면 USER들이 가지고 있는 ROLE들을 확인 할 수 있습니다. SUPERUSER인 postgres는 SUPERUSER, CREATE ROLE, CREATE DB, REPLICATION기능을 가지고 있습니다.


SUPERUSER : USER들을 생성하고 권한을 부여해 주는 USER

CREATE ROLE : USER가 새로운 ROLE을 정의하는 기능을 생성

CREATE DB : USER가 DB를 생성하는 권한을 부여하는 기능

REPLICATION : USER가 DB를 실시간으로 복사하는 기능



Synopsis :


CREATE USER username [[ WITH ] option [ ... ]]

where option can be:



SUPERUSER  | NOSUPERUSER   - 해당 USER를 SUPERUSER권한을 주는 것입니다. 

                             따로 지정하지 않을 경우 DEFAULT값으로 NOSUPERUSER가 됩니다.

CREATEDB   | NOCREATEDB    - DATABASE를 생성하는 권한을 정의합니다. 

                             CREATEDB를 선택할 경우 USER는 DATABASE를 생성할 권한이 부여됩니다. 

                             NOCREATEDB를 선택할 경우 USER는 DATABASE를 생성할 권한이 거부됩니다. 

       따로 정의 되어있지 않을 경우 NOCREATEDB값이 default 설정되어 있습니다.

CREATEUSER | NOCREATEUSER  - 스스로 새로운 유저를 생성하는 권한을 부여하는 것을 정의합니다.

                             CREATEUSER를 선택할 경우 USER를 생성할 수 있는 권한이 부여됩니다.

                             NOCREATEUSER를 선택할 경우 USER를 생성할 권한이 거부됩니다.

INHERIT    | NOINHERIT    - DATABASE의 권한을 다른 구성원들에게 상속하는 역할을 합니다.

                             따로 정의 되어있지 않을 경우 INHERIT 값이 default값으로 설정 되어 있습니다.

LOGIN      | NOLIGIN    - USER가 LOGIN을 하는 역할을 부여합니다.

CONNECTION LIMIT connlimit - 로그인 할 때 동시연결을 지원 하는 기능으로 default값으로 -1(제한없음)로 

                             설정 되어 있습니다.

[ENCRYPTED | UNCRYPTED ] PASSWORD 'password' - ‘password’를 입력하고 인증이 필요 없는 경우 옵션을 생                                                 략이 가능합니다.



생성 예제


postgres=# create user TEST_USER with password 'test01';


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

PostgreSQL 점검  (0) 2018.12.31
PostgreSQL 권한 부여 및 해제  (0) 2018.12.31
PostgreSQL 유저 생성  (0) 2018.12.28
PostgreSQL 스키마 생성  (0) 2018.12.28
PostgreSQL Tablespace 생성  (0) 2018.12.27
PostgreSQL DB 생성 및 삭제  (0) 2018.12.27

PostgreSQL 스키마 생성

SCHEMA 생성


 - SCHEMA는 Object들의 논리적 집합체 입니다. 

 - TABLE, VIEW, SEQUENCE, SYNONYM, DOMIAN, FUNCTION 등으로 구성되어 있습니다.

 - SCHEMA를 사용하는 이유는 논리적 집합체를 만들어서 관리의 편의성을 높이고, 여러 USER들 간의 간섭 없이 접속 할 수 있게 합니다.



Synopsis :


1. CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ]

2. CREATE SCHEMA AUTHORIZATION user_name [3. schema_element [ ... ] ]

4. CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ]

5. CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name


1. CREATE SCHEMA ‘schemaname’을 입력하여 SCHEMA를 생성합니다. 이름을 입력하지 않을 경우 USER의 이름이 SCHEMA이름으로 사용됩니다. (단 Pg_로 시작하는 이름은 스키마로 불가능 합니다.)


2. AUTHORIZATION ‘username’ 스키마를 소유한 USER의 이름을 입력합니다. 이를 생략할 경우 접속되어 있던 USER가 

default값으로 저장되고 SUPERUSER만이 다른 USER가 소유한 SCHEMA를 만들 수 있습니다.


3. schema_element [...]을 입력하여 SCHEMA 내에서 객체를 정의하는 SQL문을 작성합니다. CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER, GRANT등이 포함될 수 있습니다.


4. CREATE SCHEMA IF NOT EXISTS ‘schema_name’ 을 입력하면 특정 이름이 SCHEMA에 없을 경우 그 SCHEMA를 생성합니다.


5. CREATE SCHEMA IF NOT EXISTS AUTHORIZATION ‘user_name’ 은 USER가 소유한 SCHEMA가 존재하지 않을 때 SCHEMA를 생성합니다.




SCHEMA 생성 예제


postgres=# CREATE SCHEMA test01 AUTHORIZATION test_user;


postgres=# GRANT ALL ON SCHEMA test01 TO test_user;



- 오라클에서 SCHEMA = USER의 개념으로 하나의 User가 하나의 Schema를 소유합니다. 히지만 Postgres나 Mysql 같은 DB에서는 User와 Schema 는 분리된 개념이며,하나의 유저가 여러개의 스키마를 소유 할 수도 있습니다.



PostgreSQL Tablespace 생성

Tablespace


- 데이터베이스에서 Tablespace는 오라클과 PostgreSQL에서만 존재하는 개념입니다.

- 테이블스페이스가 존재 함으로 각 schema의 오브젝트 관리가 용이해지며, 데이터파일 관리 및 용량 관리에 있어서, 또는 성능 관리에 있어서 효과적인 관리가 가능해 집니다.


- 테이블 스페이스 확인


postgres=# select * from pg_tablespace;

  spcname   | spcowner | spcacl | spcoptions 

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

 pg_default |       10 |        | 

 pg_global  |       10 |        | 

(2 rows)


postgres=# \db

       List of tablespaces

    Name    |  Owner   | Location 

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

 pg_default | postgres | 

 pg_global  | postgres | 

(2 rows)


postgres=# \db+

                                  List of tablespaces

    Name    |  Owner   | Location | Access privileges | Options |  Size  | Description 

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

 pg_default | postgres |          |                   |         | 29 MB  | 

 pg_global  | postgres |          |                   |         | 497 kB | 

(2 rows)


postgres=# 


- 특별히 유저나 스키마에 테이블 스페이스를 지정하지 않고 pg_default 테이블스페이스를 이용 할 수 있습니다.

- 해당 디렉토리는 postgres의 권한을 가지고 있어야 합니다.



테이블스페이스 생성


Synopsis :

CREATE TABLESPACE tablespace_name

    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]

    LOCATION ’directory’

    [ WITH ( tablespace_option = value [, ... ] ) ]



생성 예제


postgres@pgsqldb:~]$ psql -d postgres -U postgres

psql (9.6.11)

Type "help" for help.

postgres=# CREATE TABLESPACE mydb01 LOCATION '/postgresql/tbs';

CREATE TABLESPACE

postgres=#



Tablespace 조회


postgres=# \db

           List of tablespaces

    Name    |  Owner   |    Location     

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

 mydb01     | postgres | /postgresql/tbs

 pg_default | postgres | 

 pg_global  | postgres | 

(3 rows)


postgres=# select * from pg_tablespace;

  spcname   | spcowner | spcacl | spcoptions 

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

 pg_default |       10 |        | 

 pg_global  |       10 |        | 

 mydb01     |       10 |        | 

(3 rows)


postgres=# \q

postgres@pgsqldb:~]$ ls -l /postgresql/tbs/

total 0

drwx------ 2 postgres postgres 6 Dec 27 14:03 PG_9.6_201608131

postgres@pgsqldb:~]$ ls -l $PGDATA/pg_tblspc

total 0

lrwxrwxrwx 1 postgres postgres 15 Dec 27 14:03 16392 -> /postgresql/tbs

postgres@pgsqldb:~]$ 


- tablespace owner를 지정해서 생성 할 수도 있습니다.




- 경로를 지정하여 테이블스페이스를 생성하면, $PGDATA/pg_tblspc 밑에 OID로 심볼릭 링크가 생성됩니다.

- 실제 파일은 경로에 있고, 클러스터 홈 밑에 pg_tblspc에 링크가 생성되어 DB에 정보를 전달하는것 입니다.



Tablesapce 이름 변경


postgres=# ALTER TABLESPACE mydb RENAME TO mydb01;



Tablespace Owner 변경


postgres=# ALTER TABLESPACE mydb01 OWNER TO POSTGRES;

PostgreSQL DB 생성 및 삭제

PostgreSQL을 관리하거나 운영하는 방법은 크게 두가지로 분류 할 수 있습니다.


- 터미널을 이용한 커맨드라인 사용

- pgadmin을 이용한 GUI 사용



DB생성


Synopsis :

CREATE DATABASE name

[ [ WITH ] [ OWNER [=] user_name ]

        [ TEMPLATE [=] template ]

        [ ENCODING [=] encoding ]

        [ LC_COLLATE [=] lc_collate ]

        [ LC_CTYPE [=] lc_ctype ]

        [ TABLESPACE [=] tablespace_name ]

        [ ALLOW_CONNECTIONS [=] allowconn ]

        [ CONNECTION LIMIT [=] connlimit ] ]

        [ IS_TEMPLATE [=] istemplate ]


터미널에서 su - postgres 계정 접속 후에


$ createdb mydb


간단하게 만들수 있습니다.

그리고 psql 명령으로 접속 할 수 있습니다.


$ psql mydb

psql (9.6.11)

Type "help" for help.


mydb=#


이렇게 나오면 DB에 접속이 된것입니다.



DB에서 빠져나가려면 \q 하면 OS로 돌아갑니다.



이 방법 말고도 postgres db에 접속해서 생성하는 방법이 있습니다.


$ psql -d postgres -U postgres

psql (9.6.11)

Type "help" for help.


postgres=# create database mydb2;

CREATE DATABASE

postgres=#


오라클에 비하면 너무나 간단한 DB 생성 방법입니다.



반대로 DB를 삭제하려면


$ dropdb mydb


또는


postgres=# drop database mydb2;


이렇게 하시면 됩니다.



Database 목록 확인


\l


명령으로 데이터베이스 리스트를 확인 할 수 있습니다.


postgres-# \l

                                  List of databases

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

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

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

 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

(4 rows)



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

PostgreSQL 스키마 생성  (0) 2018.12.28
PostgreSQL Tablespace 생성  (0) 2018.12.27
PostgreSQL DB 생성 및 삭제  (0) 2018.12.27
CentOS 7 에서 방화벽에 PostgreSQL 리스너 포트 등록하기  (0) 2018.12.27
초간단 postgresql.conf 설정  (0) 2018.12.27
pg_hba.conf  (0) 2018.12.27

CentOS 7 에서 방화벽에 PostgreSQL 리스너 포트 등록하기

접근제어


- PostgreSQL은 pg_hba.conf를 통해 접근제어를 할 수 있으나, 성능상의 이슈로 인해 OS단이나 방화벽 장비, 보안장비에서 제어하는 것을 추천 한다고 했습니다.


- CentOS 7 에서는 새로나온 Firewalld 와 기존의 iptables 모두 사용 가능 합니다.



firewalld 의 설정



# firewall-cmd --permanent --zone=trusted --add-source=<Client IP address>/32


# firewall-cmd --permanent --zone=trusted --add-port=5432/tcp


# firewall-cmd --reload



iptables 설정


# iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d <Server IP address> --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT


# iptables -A OUTPUT -p tcp -s <Server IP address> --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT




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

PostgreSQL Tablespace 생성  (0) 2018.12.27
PostgreSQL DB 생성 및 삭제  (0) 2018.12.27
CentOS 7 에서 방화벽에 PostgreSQL 리스너 포트 등록하기  (0) 2018.12.27
초간단 postgresql.conf 설정  (0) 2018.12.27
pg_hba.conf  (0) 2018.12.27
Linux에 Postgres 9.6 버전 설치  (0) 2018.12.25