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

초간단 postgresql.conf 설정

postgresql.conf


 - PostgreSQL의 환경변수를 지정해주는 설정파일. 오라클의 파라미터 파일과 비슷한 역할을 합니다.


postgres.conf 파일 안에는 다양하고 많은 설정 값들이 있는데, 아래의 설정 값 정도만 설정하면 싱글 DB를 운영하는데 있어 크게 문제될 사항은 없습니다.



listen_addresses = '*'                       # 로컬 호스트 밖에서의 접속 허용

shared_buffers = 3GB                         # 물리 메모리  2/3 ~ 1/4

checkpoint_segments = 128                    # 2GB redo 로그, 9.4 이하에서

max_wal_size = 2GB                           # 2GB redo 로그, 9.5 이상에서

min_wal_size = 2GB                           # 2GB redo 로그, 9.5 이상에서

wal_level = logical                          # 일단 최대 자세하게

archive_mode = on                            # 아카이빙 기능은 켜두고,

archive_command = 'true'                     # 아카이빙을 임시로 사용 안함

log_destination = 'stderr'                   # pg_log 에 로그 남김

logging_collector = on                       # 자체 로그 프로세스 사용

log_line_prefix = '%t %u@%r/%d(%c 또는 %p)'   # 좀 더 자세히

stats_temp_directory = '/run/shm'            # 실시간 통계 정보는 공유 메모리로

effective_cache_size = 4GB                   # 물리 메모리 1/2 , 9.4 이하



- 더 자세한 설정 값들에 대해서는 나중에 따로 정리 하도록 하겠습니다.

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

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
PostgreSQL의 기본 개념  (0) 2018.12.12

pg_hba.conf

pg_hba.conf



- PostgreSQL의 인증관련 설정 파일 ( HBA : host-based authentication 호스트 기반의 인증 약어 )


1) $PGDATA 에 존재. (클러스터홈)

2) PostgreSQL의 pg_hba.conf 파일을 통해 외부접근에 대한 처리는 되도록 배재하는것이 좋습니다.

   pg_hba.conf 설정을 통하여 외부접근을 하게 되면, PostgreSQL 인증체크처리 부하가 발생하여 전체적인 성능에 부하가 걸릴 수 있기 때문에, OS단의 iptables에서 통제 하거나, 그 앞단에서 방화벽, 보안장비에서 통제하는 편이 좋습니다.

3) 설정은 간단하며, 접근 Host나 Host의 데이터 전송방식과 암호화 전송방식에 대한 설정을 가지고 있습니다.

4) 실제적인 계정에 대한 정보는 PostgreSQL의 카탈로그 테이블인 pg_user에서 관리하기 때문에 계정 권한, 패스워드 변경 등의 작업은 실시간 적용이 가능하나, 클라이언트의 접근 방식이나, 암호 전달 방식은 pg_ctl reload 또는, pg_ctl restart 명령을 통해 pg_hba.conf를 다시 로드해야 합니다. restart는 불편함이 있지만, 불법 접근에 대한 처리에 있어 빠른 응답으로 PostgreSQL 서버 부담을 줄이고, 최대한 성능을 끌어내기 위해 채택한 방법입니다.



- pg_hba.conf 내용



# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

# IPv6 local connections:

host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

#host    replication     enterprisedb        127.0.0.1/32            md5

#host    replication     enterprisedb        ::1/128                 md5




- 환경설정



1. Host Type


Host Type은 접근자의 접근위치와 통신의 암호화 관련 설정입니다.

local, host, hostssl, hostnossl을 지원합니다.

 

local은 자칫 localhost로 생각할 수 있겠지만, local은 Unix Domain Socket을 통한 접속에 해당되는 것으로 다소 햇갈릴수 있으니 주의 하시기 바랍니다.

 

hostssl은 ssl인증서를 통한 암호화 통신만 지원하며 localhost, 127.0.0.1식의 즉 TCP/IP접속에 해당된다.

hostnossl은 ssl접속은 불가능하며, TCP/IP통신을 지원합니다.

 

host나 hostssl로 설정한 상태에서 SSL기능을 사용하시려면 Postgres컴파일시 --with-openssl옵션을 주어야 하며, postgresql.conf에 ssl=true로 설정을 해주셔야만 합니다.

 

 

2. Database Name


특정한 디비에 대한 접속을 제한할 수 있으며 ,(콤마)로 여러 개의 DB로 접근 제어가 가능합니다.  모든 DB에 대한 접근을 풀려면 all로 설정하시면 됩니다. 만약에 설정할 DB가 수십개라면 기재하기 불편하실경우 @dblist.txt 식으로 설정하고 dblist.txt을 PGDATA로 설정한 폴더의 안에 넣어 두시면됩니다.

 


3. User Name


계정설정으로 ,(콤마)구분으로 할 수 있으며, Database의 @파일명 식으로 따로 파일을 만들어서 처리하실수도 있습니다. PostgreSQL의 계정 그룹 카탈로그 테이블인 pg_group 또는 create_group 명령으로 그룹을 만들어 계정들은 하위(SYSID)에 묶어두었을때는 +(플러스) 키를 붙인 그룹명으로 설정하면 해당 그룹에 대한 모든 접근이 가능해집니다.

 


4. CIDR-ADDRESS or IP-Mask


IPv4 CIDR구분으로 해당 C Class에 대해 모두 접근처리를 할 경우는 : xxx.xxx.xxx.0/24

해당 IP에 대한 접근처리를 할 경우는 : xxx.xxx.xxx.xxx/32

 


5. Authentication Method


이 부분은 실제적인 계정의 패스워드에 대한 서버로의 전송을 어떻게 할 것인가를 정하는 것입니다.

PostgreSQL Server와 Client와의 접속에는 처음 Client가 접속을 하게 되면 pg_hba.conf에 대해 검색해서 해당 접속에 대한 접근허용을 확인하고 확인이 되면 이 Auth.Method에 설정된 암호화 방식으로 패스워드를 전송하라고 응답메시지를 보내고 다시 Client가 Server로 로그인을 하게 되는 방식입니다.


trust : 패스워드 없이 접근 가능

reject : 거부

md5 : 패스워드를 md5로 암호화해서 전송

crypt : crypt로 암호화 해서 전송 Postgres 7.2이후부터는 사용 안함. (이전버전설정 호환용)

password : text로 패스워드를 전송하는 것.

krb4, krb5 : KerberOS V4, 5를 지원한다.

ident : 접속 ClientOS User이름을 확인하는 방법?

pam : PAM(Pluggable Authentication Modules)서비스를 사용한 인증




- 설정 예제



# 로컬 시스템상의 모든 유저가 임의의 데이터베이스에 

# 임의의 데이터베이스 유저명으로 Unix 도메인 소켓을 사용해 접속하는 것을 허가 

# (로컬 접속에서는 디폴트). 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  local   all         all                               trust 



# 로컬 loopback의 TCP/IP 접속을 사용하는 것은 위와 같다. 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  host    all         all         127.0.0.1/32          trust    



# 분리된 netmask 열을 사용하고 있는 것을 제외하고 위와 같다. 

# TYPE    DATABASE    USER        IP-ADDRESS    IP-MASK            METHOD 

  host    all         all         127.0.0.1     255.255.255.255    trust 



# IP주소 192.168. 93. x를 가지는 모든 호스트의 모든 유저가, 

# ident가 그 접속에 대해 보고하는 것과 같은 유저명(전형적으로는 Unix 유저명)으로 

# 데이터베이스 "postgres"에 접속하는 것을 허가. 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  host    postgres    all         192.168.93.0/24       ident sameuser 



# 유저의 패스워드가 올바르게 입력되었을 경우, 

# 호스트 192.168. 12.10부터의 유저가 데이터베이스 "postgres"에 접속하는 것을 허가 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  host    postgres    all         192.168.12.10/32      md5 



# 선행하는 "host"행이 없으면, 이 2행에 의해 192.168. 54.1으로 접속 시도는 

# 모두 거부(이 항목이 최초로 일치되기 때문에). 

# 다만, 인터넷상의 다른 모든 장소로부터의 Kerberos 5 접속은 허가. 

# 제로 마스크는, 호스트 IP주소의 비트를 고려하지 않고 

# 어느 호스트라도 조합할 수 있는 것을 의미합니다. 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  host    all         all         192.168.54.1/32       reject 

  host    all         all         0.0.0.0/0             krb5 

 


# 192.168. x.x 호스트로부터의 유저가, ident 검사를 통과하는 경우, 

# 어느 데이터베이스라도 접속을 허가. 만약, 예를 들면, ident가 "bryanh"라고 인정해 

# "bryanh"가 PostgreSQL의 유저 "guest1"로서 

# 접속 요구를 내는 경우, "bryanh"는 "guest1"로 접속이 허가된다고 합니다. 

# 맵 "omicron"에 대한 기재사항이 pg_ident.conf에 있으면 접속을 허가. 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  host    all         all         192.168.0.0/16        ident omicron 



# 로컬 접속에 대해서, 이하의 단 3행 밖에 기재가 없는 경우, 로컬 유저는 

# 자신의 데이터베이스(데이터베이스 유저명과 같은 이름의 데이터베이스)에게만 접속 허가. 

# 다만 관리자와 롤 "support"의 멤버는 모든 데이터베이스에 접속 가능. 

# $PGDATA/admins 파일은 관리자의 리스트를 포함한다. 

# 모든 경우에 패스워드가 필요. 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  local  sameuser      all                              md5 

  local  all           @admins                          md5 

  local  all           +support                         md5 



# 위의 마지막 2행은 1개의 행으로 정리하는 것이 가능. 

  local  all           @admins,+support                 md5 


# 데이터베이스의 열에는 리스트나 파일명도 사용할 수 있지만, 그룹은 사용할 수 없다. 

  local  db1,db2,@demodbs  all                          md5 



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

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
PostgreSQL의 기본 개념  (0) 2018.12.12
pgadmin4 설치  (0) 2018.12.03

RAC용 점검 스크립트

RAC 1노드에서 양쪽 노드 모두 확인하는 점검 스크립트입니다.



엔지니어 분들이 고객사 점검에 사용하면 좋게 만들었습니다.

운영하시는 분들도 일일 상태 체크나 DB관련 정보 확인을 위해 사용 할 수 있습니다.

11g 기준으로 작성 되었습니다.


check_RAC.sql


'ORACLE > Operation & Admin' 카테고리의 다른 글

RAC용 점검 스크립트  (0) 2018.12.25
무료 오라클 성능진단 툴 Mumbai 3.1.1  (0) 2018.12.25
Table 또는 인덱스의 사이즈를 확인 하는 쿼리  (0) 2018.11.01
CURSOR_SHARING  (0) 2018.10.31
ASM 용량 확인  (0) 2018.10.28
Datafile Resize 계산하기  (0) 2018.10.18

무료 오라클 성능진단 툴 Mumbai 3.1.1

오라클 성능 진단 툴인 Mumbai 3.1.1





일단 AWR, Statspack, ASH 등을 이툴로 DB에 접속해 직접 추출 할 수 있으며, 무엇보다 좋은것은 시각화를 가능하게 해주는 점입니다.


실시간 모니터링 보다는 성능진단시 더 유용합니다. 시각화를 통한 보고서 작성이나 성능에 관련한 다양한 내용을 확인 할 수 있기 때문에 좋습니다.


아래 출처에 가시면 다운받을수 있습니다.


링크 : https://marcusmonnig.wordpress.com/2016/09/21/mumbai-version-3-1-1-available/



'ORACLE > Operation & Admin' 카테고리의 다른 글

RAC용 점검 스크립트  (0) 2018.12.25
무료 오라클 성능진단 툴 Mumbai 3.1.1  (0) 2018.12.25
Table 또는 인덱스의 사이즈를 확인 하는 쿼리  (0) 2018.11.01
CURSOR_SHARING  (0) 2018.10.31
ASM 용량 확인  (0) 2018.10.28
Datafile Resize 계산하기  (0) 2018.10.18

Linux에 Postgres 9.6 버전 설치

PostgreSQL 9.6 설치


현재 가장 많이 사용하는 버전이 9.6버전이기 때문이기도 하고, Postgres가 성능적인 부분에서 많은 발전을 이뤄낸 버전이기도 하기 때문에 9.6버전의 설치하는 법을 안내 해보고자 합니다.


기존의 run 파일을 올려서 설치하는 방식이 아닌, PostgreSQL 홈페이지에서 yum repo를 제공하고 있으며, 누구나 쉽게 설치를 할 수 있으나, 처음부터 yum으로 설치 하게 되면, 설치 기본 경로라던가 엔진의 경로, datafile의 경로등 자신이 원하는 대로 설치가 용이 하지않고 나중에 수정해주는 작업은 매우 번거롭기도 하기 때문에 설치 단계에서부터 원하는 입맛대로 설치를 위한 방법을 알아보도록 하겠습니다.



CentOS 7 64bit 설치


PostgreSQL DB는 일반적으로 같은 오픈소스 OS인 CentOS를 많이 이용합니다.

psql을 사용하는 이유는 명백히 단가 절감을 위함인데, 라이센스 비용이 들어가는 OS, 즉, 윈도우나 유닉스에 설치하는 것은 그 의미가 퇴색된다고 봅니다. 그래서 CentOS 7 버전에 PostgreSQL을 설치합니다.



CentOS의 설치는 간단합니다. 오라클 설치 때와는 확연히 다르게, 미니멀 설치를 합니다.

설치를 하면서 중요한 부분입니다. 


Compatibility Library, Development Tools


두 패키지는 추가로 설치하도록 합니다. gcc나 openssh 등 기본적으로 개발자들이나 운영하는데 있어 추가적인 무언가 하기위해 필요한 기본적인 것들이 많이 들어있습니다.


그리고 설치가 완료 되면 yum으로 추가적인 것들을 설치 해줍니다.


# yum -y install net-tools elinks wget bind-utils vim sysstat


서버 네트워크를 Bonding 한다거나, 그 밖의 다른 설정이 필요하다면 다른 패키지들을 충분히 설치합니다.



그룹 및 유저 추가


yum으로 postgresql를 설치하면 기본적으로 postgres 그룹과 postgres 유저를 설치해줍니다.

유저 홈은 /var/lib/pgsql 으로 설정이 되고, 데이터의 기본 경로는 /var/lib/pgsql/9.6/data 밑으로 가게됩니다.

이게 참 애매한 부분이 있기도 하고 운영하는데 불편하기도 하고 해서 그룹과 유저를 기본적으로 세팅을 해놓고 시작을 해보겠습니다.


# groupadd -g 26 postgres


# useradd -u 26 -g postgres -d /home/postgres postgres


# passwd postgres


# su - postgres



프로파일 설정


$ vi .bash_profile


# .bash_profile


# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi


# User specific environment and startup programs


PATH=$PATH:$HOME/.local/bin:$HOME/bin


export PATH


[ -f /etc/profile ] && source /etc/profile

PGDATA=/usr/local/pgsql/data              <----- 데이터가 설치될 경로입니다.

export PGDATA

# If you want to customize your settings,

# Use the file below. This is not overridden

# by the RPMS.

[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile


export PS1="\[\e[36;1m\]\u@\[\e[32;1m\]\h:\[\e[31;1m\]\w]$ \[\e[0m\]"

export TERM=linux


PATH=$PATH:$HOME/bin:/usr/pgsql-9.6/bin

export PATH


alias vi='vim $*'


:wq!



유저가 보기도 좋고 관리도 편하게 세팅이 됩니다.



PostgreSQL 설치


데이터를 설치할 경로, 로그를 쌓을 경로를 만들어주고 권한을 부여합니다. 데이터 경로나, 로그의 경로는 다르게 지정을 해도 됩니다. 스스로가 관리하기 편한 경로에 지정 해주는것이 좋습니다. /usr/local/pgsql/data 이 경로는 이전 버전에서 사용하던 경로 입니다. 예를 들면 /app/db/pgsql/data 이렇게 설치를 하고, /app/db/pgsql/diag/log 라는 경로를 만들어 로그를 쌓고 싶다면, 만들어주면 됩니다. DB 관리의 용이함을 위해 data 파티션을 따로 지정해준다거나 하는 것도 좋습니다. 


저는 usr과 var의 의미를 살리기 위해 저는 아래와 같이 설정하고 설치를 진행합니다.

 


# mkdir -p /usr/local/pgsql/data

# chown -R postgres:postgres /usr/local/pgsql


# mkdir -p /var/postgresql/log

# chown -R postgres:postgres /var/postgresql


yum을 이용해 설치


# yum -y install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

# yum -y install postgresql96 postgresql96-server


설치가 끝났습니다.

너무 간단하죠? 오라클에 비하면 할게 없습니다.



클러스터 초기화


PostgreSQL에서 클러스터란 database 집합체를 뜻한다고 설명한 적이 있습니다. 클러스터를 초기화 해야만 Database를 사용 할 수 있습니다. initdb 명령은 클러스터를 생성하거나 초기화 하는데 사용됩니다.


$ initdb -D /usr/local/pgsql/data



서버 실행


클러스터가 초기화 되면 Database를 시작 할 수 있습니다. PostgreSQL의 기본이 되는 postgres, template0, template1을 시작하는 것 입니다. 


$ pg_ctl start -D /usr/local/pgsql/data -l /var/postgresql/log/postgresql.log


서버를 실행하는 명령은 pg_ctl 뿐만 아니라 다른 명령도 있습니다.


$ postgres -D /usr/local/pgsql/data


이렇게만 실행해도 서버가 시작됩니다. postgresql의 공식 메뉴얼에는 백그라운드로 실행시키는 것은 권장하고 있습니다.


$ postgres -D /usr/local/pgsql/data >/var/postgresql/log/postgresql.log 2>&1 &


pg_ctl 명령으로는 백그라운드로 실행해도 포그라운드로 나오는데, 이건 좀 더 체크를 해볼 필요가 있을것 같습니다.


OS에서 Jobs를 확인해보면 백그라운드에서 실행중임을 알수 있습니다.


$ jobs

[1]+  Running                 postgres -D /usr/local/pgsql/data > /var/postgresql/log/postgresql.log 2>&1 &




설치 확인


$ psql -d postgres -U postgres


접속을 하면 sql 프롬프트가 나옵니다.


postgres=# 

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          +

(3 rows)


클러스터를 구성하는 3개의 DB가 시작된 것을 볼 수 있습니다.


postgres=# \q


하면 OS로 나갈 수 있습니다.

이 것으로 설치 및 확인은 완료 되었습니다.





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

초간단 postgresql.conf 설정  (0) 2018.12.27
pg_hba.conf  (0) 2018.12.27
Linux에 Postgres 9.6 버전 설치  (0) 2018.12.25
PostgreSQL의 기본 개념  (0) 2018.12.12
pgadmin4 설치  (0) 2018.12.03
PostgreSQL 11 설치  (0) 2018.12.03

PostgreSQL의 기본 개념

PostgreSQL의 기본개념


 오라클과 psql의 차이점 때문에 용어 선택에서 상당히 헷갈리는 부분이 존재합니다.



1) 용어 차이

 

- 오라클은 DB 엔진이 올라간 후, 인스턴스를 생성하면서 데이터베이스가 생성됩니다. 즉, 오라클에서는 하나의 인스턴스가 하나의 DB를 나타냅니다. 하나의 데이터베이스 엔진으로 여러개의 인스턴스를 생성하면 하나의 서버에 여러개의 DB를 생성 할 수 있습니다. 인스턴스 = DB의 개념이지만, RAC 환경에서는 다수의 인스턴스가 하나의 DB를 이루는 경우도 있습니다. 


- PostgreSQL에서 인스턴스란, 서버 단위의 개념입니다. 서버 단일 인스턴스라고 부르는 PostgreSQL의 인스턴스는 서버 = 인스턴스 개념입니다. 


- 오라클에서 클러스터는 RAC 환경의 공유 노드 개념입니다. 하나의 스토리지를 바라보는 여러대의 노드, 오라클 클러스터웨어인 grid instructure 를 통한 active-active의 환경을 조성하고 클러스터웨어를 통해 이루어진 공유 노드를 클러스터라고 합니다. 


- Psql에서의 클러스터는 Psql DB 집합체를 이야기 합니다. PostgreSQL은 처음 DB를 설치하면 기본적으로 설치되는 DB가 있는데, postgres database, template0, template1 생성하는데, 이 3가지의 DB 집합체를 클러스터라고 합니다.



2) 데이터베이스 구조


- PostgreSQL의 물리적 구조는 매우 단순합니다.

- Shared Memory, 적은 수의 백그라운드 프로세스, 데이터 파일로 구성되어 있습니다.



2-1) Shared Memory


2-1-1) Shared Buffer


 - Shared Buffer의 목적은 DISK I/O를 최소화 하는 것입니다.

 - 그러기 위해서는 아래 항목을 만족해야 합니다.


  ● 매우 큰 버퍼를 빠르게 엑세스해야 한다. (수백GB 단위)

  ● 많은 사용자가 동시에 접근할 때 경합을 최소화해야 한다.

  ● 자주 사용되는 블록은 최대한 오랫동안 버퍼 내에 있어야 한다.


2-1-2) WAL 버퍼


 - WAL 버퍼는 데이터베이스의 변경 사항은 잠시 저장하는 버퍼입니다.

 - WAL 버퍼 내에 저장된 내용은 정해진 시점에서 WAL 파일로 기록됩니다.

 - 백업 및 복구 관점에서 WAL 파일은 매우 중요하고, 오라클에서 Redo의 개념과 비슷한 부분이 있습니다.



2-2) 프로세스 유형


 - Postmaster (Daemon) 프로세스

 - Background 프로세스

 - Backend 프로세스

 - Client 프로세스


2-2-1) Postmaster 프로세스


 - PostgreSQL를 구동 할 때 가장 먼저 시작되는 프로세스 입니다.

 - 초기 기동시 복구 작업, Shared Memory 초기화 작업, 백그라운드 프로세스 구동작업을 수행합니다

 - 클라이언트 프로세스의 접속 요청이 있을 때 Backend 프로세스를 생성합니다.


2-2-2) Background 프로세스


 - Autovacuum launcher를 제외하면, 오라클과 비슷한 백그라운드 프로세스들이 존재합니다.


 ● logger : 에러메세지를 로그 파일에 기록한다.

 ● checkpointer : 체크포인트 발생시, dirty 버퍼를 파일에 기록한다.

 ● writer : 주기적으로 dirty 버퍼를 파일에 기록한다.

 ● wal writer : WAL버퍼 내용을 WAL 파일에 기록한다.

 ● autovacuum launcher : Vacuum이 필요한 시점에 autovacuum worker를 fork 한다.

 ● archiver : Archive Log 모드일 때, WAL 파일을 지정된 디렉토리에 복사한다.

 ● stats collector : 세션 수행 정보 (pg_stat_activity)와 테이블 사용 통계 정보 (pg_stat_all_tables)와 같은 DBMS 사용 통계 정보를 수집한다.


2-2-3) Backend 프로세스


 - Backend 프로세스의 최대 개수는 max_connections 파라미터로 설정 가능하며, 기본값은 100입니다.

 - Backend 프로세스는 사용자 프로세스의 쿼리 요청을 수행한 후, 결과를 전송하는 역할을 수행합니다.

 - 쿼리 수행에 몇가지 메모리 구조가 필요한데, 이 것을 통칭해서 로컬 메모리라고 합니다.

 

 - 로컬 메모리 관련 주요 파라미터


 ◈ work_mem 파라미터

  - 정렬 작업, Bitmap 작업, 해시 조인과 Merge조인 작업시에 사용되는 공간. 기본값은 4MB

 

 ◈ maintenance_work_mem 파라미터

  - Vacuum 및 create index 작업 시에 사용되는 공간. 기본값은 64MB


 ◈ temp_buffers 파라미터

  - Temporary 테이블을 저장하기 위한 공간. 기본값은 8MB



 



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

pg_hba.conf  (0) 2018.12.27
Linux에 Postgres 9.6 버전 설치  (0) 2018.12.25
PostgreSQL의 기본 개념  (0) 2018.12.12
pgadmin4 설치  (0) 2018.12.03
PostgreSQL 11 설치  (0) 2018.12.03
PostgreSQL 백업 및 복구  (0) 2018.11.24

pgadmin4 설치

pgadmin 은 웹에서 postgresql을 관리 해줄수 있게 해주는 관리 툴입니다.

오라클 EM 같은거라고 보시면 됩니다.



SELINUX 해제


setenforce 0

sed -i 's/^SELINUX=.*/SELINUX=permissive/g' /etc/selinux/config



Yum으로 pgadmin 설치


# yum install  https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm -y

# yum install epel-release


# yum install pgadmin4



pgadmin4 환경설정


# vi /usr/lib/python2.7/site-packages/pgadmin4-web/config_distro.py

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'

SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'

SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'

STORAGE_DIR = '/var/lib/pgadmin4/storage'



# python /usr/lib/python2.7/site-packages/pgadmin4-web/setup.py

NOTE: Configuring authentication for SERVER mode.


Enter the email address and password to use for the initial pgAdmin user account:


Email address: <이메일 주소>

Password: <패스워드>

Retype password: <패스워드 확인>

pgAdmin 4 - Application Initialisation

======================================



# mkdir -p /var/lib/pgadmin4/

# mkdir -p /var/log/pgadmin4/


# chown -R apache:apache /var/lib/pgadmin4

# chown -R apache:apache /var/log/pgadmin4


# chcon -R -t httpd_sys_content_rw_t "/var/log/pgadmin4/"

# chcon -R -t httpd_sys_content_rw_t "/var/lib/pgadmin4/"




virtual host 만들기


# vi /etc/httpd/conf.d/pgadmin4.conf


<VirtualHost *>

    ServerName 192.168.0.9/pgadmin


    WSGIDaemonProcess pgadmin processes=1 threads=25

    WSGIScriptAlias / /usr/lib/python2.7/site-packages/pgadmin4-web/pgAdmin4.wsgi

 

    <Directory "/usr/lib/python2.7/site-packages/pgadmin4-web/">

        WSGIProcessGroup pgadmin

        WSGIApplicationGroup %{GLOBAL}

        Require all granted

    </Directory>

</VirtualHost>



# apachectl configtest

Syntax OK



# systemctl restart httpd


# setsebool -P httpd_can_network_connect 1


# firewall-cmd --permanent --add-service=http

# firewall-cmd --reload

success



웹 브라우저에서 192.168.0.9/pgadmin 으로 접속해보면 아래와 같은 화면이 나오고 위에서 입력한 메일 주소로 로그인이 가능합니다.




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

Linux에 Postgres 9.6 버전 설치  (0) 2018.12.25
PostgreSQL의 기본 개념  (0) 2018.12.12
pgadmin4 설치  (0) 2018.12.03
PostgreSQL 11 설치  (0) 2018.12.03
PostgreSQL 백업 및 복구  (0) 2018.11.24
PostgreSQL 소스 코드로 설치  (0) 2018.04.03

PostgreSQL 11 설치

Linux Cent OS 7.5 x64


PostgreSQL 11



우선 yum repository를 설치합니다. 


# yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm



그 후에 yum으로 postgresql을 설치 합니다.


# yum -y install postgresql11-server postgresql11



설치하고 나면 postgres 라는 계정이 생성됩니다.


# su - postgres

-bash-4.2$


프로토콜이 보기 불편하니 변경해줍니다.


$ vi .bash_profile


export PS1="[\u@\h:\w]\\$ "   

추가


root에서 DB를 서비스에 init 합니다.


[root@psql-db ~]# /usr/pgsql-11/bin/postgresql-11-setup initdb

Initializing database ... OK


systemctl 로 DB를 구동하고, 서버 실행시 자동 실행되게 등록해줍니다.


[root@psql-db ~]# systemctl start postgresql-11

[root@psql-db ~]# systemctl enable postgresql-11

Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service.

[root@psql-db ~]#


방화벽에 PostgreSQL을 차단하지 않게 정책을 추가해줍니다.


[root@psql-db ~]# firewall-cmd --add-service=postgresql --permanent

success

[root@psql-db ~]# firewall-cmd --reload

success

[root@psql-db ~]#



PostgreSQL의 conf 파일은 /var/lib/pgsql/11/data/postgresql.conf 있습니다.


vi로 열어서 listener  설정을 해줍니다.



listen_addresses = '192.168.0.9'



remote 접속 허용 설정을 해줍니다.


[root@psql-db ~]# vi /var/lib/pgsql/11/data/pg_hba.conf


# Accept from anywhere  (아무나 다)

host all all 0.0.0.0/0 md5


or


# Accept from trusted subnet (특정 대역대만)

host all all 192.168.18.0/24 md5



예>


# IPv4 local connections:

host    all             all             0.0.0.0/0               md5

#host    all             all             127.0.0.1/32            ident


Postgres 재구동


# systemctl restart postgresql-11



TEST 유저와 TEST DB를 만들고 접속 해봅니다.


[postgres@psql-db:~]$ createuser test_user

[postgres@psql-db:~]$ createdb test_db -O test_user

[postgres@psql-db:~]$ psql -c "alter user test_user with password 'test'"


권한을 주기위해 admin 유저로 접속을 합니다. postgres/postgres


[postgres@psql-db:~]$ psql -U postgres -d postgres

psql (9.2.24, server 11.1)

WARNING: psql version 9.2, server version 11.0.

         Some psql features might not work.

Type "help" for help.


postgres=# 

postgres=# 

postgres=# grant all privileges on database test_db to test_user;

GRANT

postgres=# \q


터미널에서 TEST DB에 접속


[postgres@psql-db:~]$ psql -U test_user -h 192.168.0.9 -d test_db

psql (9.2.24, server 11.1)

WARNING: psql version 9.2, server version 11.0.

         Some psql features might not work.

Type "help" for help.


test_db=>


설치가 완료 되었습니다.

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

PostgreSQL의 기본 개념  (0) 2018.12.12
pgadmin4 설치  (0) 2018.12.03
PostgreSQL 11 설치  (0) 2018.12.03
PostgreSQL 백업 및 복구  (0) 2018.11.24
PostgreSQL 소스 코드로 설치  (0) 2018.04.03
PostgreSQL 버그 리포팅 가이드 라인  (0) 2018.04.03