PostgreSQL 관리 20101225

[기본 정보]

현재 PostgreSQL 사용 Port : SELECT inet_server_port();

현재 Database : SELECT current_database();

현재 접속 User : SELECT current_user;

현재 Server IP : SELECT inet_server_addr();

현재 PostgreSQL version : SELECT version();

현재 PostgreSQL 시간 : SELECT current_time;

현재 PostgreSQL 의 UPTIME : SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;

현재 PostgreSQL 의 시작시간 : SELECT pg_postmaster_start_time();

현재 PostgreSQL 의 UPTIME 상세 : SELECT current_timestamp - pg_postmaster_start_time();

현재 PostgreSQL 에 존재하는 Database : SELECT datname FROM pg_database;

[Lock Monitor]

DBMS 운영시 특정 Session의 lock을 감지하여 해당 Process를 강제종료 해야하는 경우가 종종 발생한다.

* 감지방법

SELECT * from pg_stat_activity;

  혹은

SELECT

w.current_query as waiting_query

, w.procpid as w_pid

, w.usename as w_user

, l.current_query as locking_query

, l.procpid as l_pid

, l.usename as l_user

, t.schemaname || '.' || t.relname as tablename

FROM pg_stat_activity w

join pg_locks l1 on w.procpid = 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.procpid

join pg_stat_user_tables t on l1.relation = t.relid;

* 종료방법

select pg_cancel_backend(11836);

[Table / Column 보기]

SELECT pg_attribute.attname, pg_type.typname

 FROM pg_class, pg_attribute, pg_type

WHERE pg_class.relname = 'table_name' 

 AND pg_attribute.attnum > 0 

 AND pg_attribute.attrelid = pg_class.oid 

 AND pg_attribute.atttypid = pg_type.oid;

[Column Default Value 추가]

ALTER TABLE <table name> ALTER COLUMN <column name> SET DEFAULT <value>;

예)

ALTER TABLE tb_jp_2 ALTER COLUMN last_modified SET DEFAULT current_timestamp;

[성능유지]

아래 사항에 대해 Data 변경이 발생시 혹은 주기적으로 작업을 해야 한다고 한다.

(확인된 사항은 아니지만, 알아둬야 할 것 같다.)

---------------------------------------------------

update, delete 가 발생한 것에 대한 garbage 정리를 해야 한다.

#-- postgres 로 login

vacuumdb -a -f -z -v

index 정리

#-- postgres 로 login

clusterdb [databasename]

---------------------------------------------------

[Tablespace]

PostgreSQL 도 8.xx version 부터는 Tablespace를 지원한다.

Tablespace의 일반적인 개념은 Oracle을 참고하면 비슷할 거라 예상되며, MSSQL은 File Group 을 참고하면 된다.

아래는 실제 작업했던 script 이다.

. Tablesapce 대상 선정

. directory 설정.

---------------------------------------------------

su -

mkdir /mng/solrdata/pg_data

chown postgres /mng/solrdata/pg_data

chgrp postgres /mng/solrdata/pg_data

su - postgres

psql -d dbtest

CREATE TABLESPACE ts_xxxx location '/mnt/solrdata/pg_data';

---------------------------------------------------

위에 보면 알 수 있듯이 directory를 생성 후 그 directory를 통채로 Tablesapce로 지정하는 형태이다.

Tablespace 변경은 아래처럼 한다.

---------------------------------------------------

ALTER TABLE usasg SET TABLESPACE ts_forxtest;

ALTER TABLE kr_cwn SET TABLESPACE ts_forxtest;

ALTER TABLE kr_kpeg_factordb SET TABLESPACE ts_forxtest;

.

.

.

---------------------------------------------------