Postgresql 기본 사용법

[psql]

\? : 기본 환경 설정 및 list 보기에 대한 help

\h : SQL command에 대한 help

\x        -- extend display on

\x off   -- extend display off

\e or \ef  -- edit query buffer.

\dx+ dblink     -- extension 설치 정보 확인

-- extension 설치

begin;

\i /usr/share/postgresql/contrib/dblink.sql

commit;

-- extension 설치 유형

contrib : 기본적으로 버전별 포함된 extension

pgFundry : open source site like source forge  => http://pgFoundry.org/

pgxn : central distribution system like mvn  => http://pgxn.org/

separate projects : 외부 큰 프로젝트 (ex PostGIS)   => http://www.postgis.org/

-- extension 설치 방법

installer : yum or apt-get

pgxn : pgxnclient

download package : download & rpm -ivh

source code : download & make install 

-- extension 호출 

load 호출extension

shared_preload_libraries 사용 ( postgresql.conf )

local_preload_libraries using alter role ( 특정 세션 )

[pg_config]

-- shared directory 확인

pg_config --sharedir

[instance 생성]

pg_ctl -D /var/pgsql/datadir2 start  (postgresql.conf 포트 번호 조정)

[pg data directory 설명]

Item	Description
PG_VERSION	A file containing the major version number of PostgreSQL
base	Subdirectory containing per-database subdirectories
global	Subdirectory containing cluster-wide tables, such as pg_database
pg_clog	Subdirectory containing transaction commit status data
pg_dynshmem	Subdirectory containing files used by the dynamic shared memory subsystem
pg_logical	Subdirectory containing status data for logical decoding
pg_multixact	Subdirectory containing multitransaction status data (used for shared row locks)
pg_notify	Subdirectory containing LISTEN/NOTIFY status data
pg_replslot	Subdirectory containing replication slot data
pg_serial	Subdirectory containing information about committed serializable transactions
pg_snapshots	Subdirectory containing exported snapshots
pg_stat	Subdirectory containing permanent files for the statistics subsystem
pg_stat_tmp	Subdirectory containing temporary files for the statistics subsystem
pg_subtrans	Subdirectory containing subtransaction status data
pg_tblspc	Subdirectory containing symbolic links to tablespaces
pg_twophase	Subdirectory containing state files for prepared transactions
pg_xlog	Subdirectory containing WAL (Write Ahead Log) files
postgresql.auto.conf	A file used for storing configuration parameters that are set by ALTER SYSTEM
postmaster.opts	A file recording the command-line options the server was last started with
postmaster.pid	A lock file recording the current postmaster process ID (PID), cluster data directory path, postmaster start timestamp, port number, Unix-domain socket directory path (empty on Windows), first valid listen_address (IP address or *, or empty if not listening on TCP), and shared memory segment ID (this file is not present after server shutdown)

[connect pool]

1. create pgbouncer.ini

postgres = port=5432 dbname=postgres

[pgbouncer]

listen_port = 6432

listen_addr = 127.0.0.1

admin_users = postgres

;stats_users = monitoring userid

auth_type = trust

; put these files somewhere sensible

auth_file = users.txt

logfile = pgbouncer.log

pidfile = pgbouncer.pid

; required for 9.0

ignore_startup_parameters = application_name

server_reset_query = DISCARD ALL;

; default values

pool_mode = session

default_pool_size = 20

log_pooler_errors = 0

2. password 파일 생성

\o users.txt

\t

select '"'||rolename||'" "'||rolepassword||'"' from pg_authid;

\q

3. lanuch pgbouncer

pgbouncer -d pgbouncer.ini

4. test connetion

psql -p 6432 -h 127.0.0.1 -U postgresql pgbouncer -c "reload"

psql -p 6432 pgbouncer -c "SHUTDOWN"  (reload/suspend... show stats, show pools ...)

\c pgbouncer

show databases;

[function]

pg_size_pretty

pg_relation_size

pg_total_relation_size

pg_database_size

[internal table]

pg_constraint

pg_class                           : meta 정보 

pg_attribute        

pg_database                        : database 정보 

pg_extension

pg_catalog

pg_available_extensions

pg_stat_activity                   

pg_settings                        : session 설정정보

pg_shadow                          : 사용자 패스워드 정보

pg_tablespace

pg_toast

pg_index

pg_stat_user_tables                : 사용자 테이블 사용량 통계 

pg_stat_user_indexes

pg_authid                          : 사용자 정보

SELECT * FROM pg_replication_slots;

[command]

initdb -- create a new PostgreSQL database cluster

pg_controldata -- display control information of a PostgreSQL database cluster

pg_ctl -- initialize, start, stop, or control a PostgreSQL server

  ex> pg_ctl reload -D ~  (config reload), pg_ctl restart 

pg_resetxlog -- reset the write-ahead log and other control information of a PostgreSQL database cluster

postgres -- PostgreSQL database server

postmaster -- PostgreSQL database server

clusterdb -- cluster a PostgreSQL database

createdb -- create a new PostgreSQL database

createlang -- install a PostgreSQL procedural language

createuser -- define a new PostgreSQL user account

dropdb -- remove a PostgreSQL database

droplang -- remove a PostgreSQL procedural language

dropuser -- remove a PostgreSQL user account

ecpg -- embedded SQL C preprocessor

pg_basebackup -- take a base backup of a PostgreSQL cluster

pg_config -- retrieve information about the installed version of PostgreSQL

pg_dump --  extract a PostgreSQL database into a script file or other archive file

pg_dumpall -- extract a PostgreSQL database cluster into a script file

pg_isready -- check the connection status of a PostgreSQL server

pg_receivexlog -- stream transaction logs from a PostgreSQL server

pg_recvlogical -- control PostgreSQL logical decoding streams

pg_restore --  restore a PostgreSQL database from an archive file created by pg_dump

psql --  PostgreSQL interactive terminal

reindexdb -- reindex a PostgreSQL database

vacuumdb -- garbage-collect and analyze a PostgreSQL database

information_schema.tables

-- table 정보 조회

SELECT reltablespace, relfilenode FROM pg_class WHERE oid = 'test1'::regclass;

-- database oid 조회 

SELECT oid as databaseid FROM pg_database WHERE datname = current_database()

-- 설치 extension 조회

SELECT * FROM pg_extension

-- table constraints 조회

SELECT * FROM pg_constraint WHERE confrelid = 'orders'::regclass;

[세션 정보 확인]

SELECT current_database();

SELECT current_user;

SELECT inet_server_addr(), inet_server_port();

SELECT version();

select current_user, session_user;

psql -c "SELECT current_time"

SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;

\conninfo

[세션 설정]

SHOW config 이름            -- config 내용 보기

ex) SHOW config_file        -- config file 위치 보기

    SHOW data_directiory;

ALTER SYSTEM SET shared_buffers = '1GB';  (postgresql.auto.conf 기록됨)

ALTER DATABASE saas SET configuration_parameter = v1;  -- for database

ALTER ROLE Simon SET configuration_parameter = v2; -- for user

ALTER ROLE Simon IN DATABASE saas SET configuration_parameter = v3;  -- for user and database

-- 세션 work 메모리 16mb 사용으로 설정

SET work_mem = '16MB';

-- 현재 트랜잭션에 대해서만 메모리 사용조정

SET LOCAL work_mem = '16MB';

-- 세션 메모리 사용 설정 reset

RESET work_mem

-- 전체 설정 reset

RESET ALL

-- config file 보기

show config_file

--  세션 설정 내용 확인

SELECT name, setting, reset_val, sourceFROM pg_settings WHERE source = 'session';

SELECT name, source, setting FROM pg_settings WHERE source != 'default'AND source != 'override'ORDER by 2, 1;

show all     -- 전체 설정 보기,    show 설정    -- 설정 내용보기

-- system 파라미터 변경

ALTER SYSTEM SET shared_buffers = '1GB';

[User & Security] => superuser role : superuser, createdb, createuser, createrole

-- current user 변경

set role to test1

CREATE USER username SUPERUSER;   -- make superuser

ALTER USER username NOSUPERUSER;  -- remove superuser role

REVOKE ALL ON atable FROM b;      -- rovoke all on the atable from b user

ex) revoke role1 from b           -- b user에서 role1 권한 제외 

\z    a               a table에 대한 권한 확인

\du   postgresql      postgresql 사용자에 대한 권한 확인

grant select on a to test1;

grant select, insert, update, delete to a to test1;

grant all on a to test1;

create group test2;

grant select on a to test2;

grant insert on b to test2;

grant test2 to u1, u2;

grant select on all tables in schema test1 to test2;

-- password 변경

alter user test password 'test' or 'md553175bce...' ( password 변경 )

createuser test3

alter user test1 nologin -- test1 로그 안됨

alter user test1 login   -- test1 로그 가능

alter user test1 connection limit 10      -- test1 커넥션 개수 제한 (-1 : unlimited)

alter role fred connection limit 1  -- user 접속 1로 제한

select rolconnlimit from pg_roles where rolename = 'test1'   -- user에 대한 접속 제한확인

select count(*) from pg_stat_activity where username = 'test1'  -- 현재 접속개수

-- login 권한이 제거된 사용자에 대해서 connection를 kill

SELECT pg_terminate_backend(pid)

  FROM pg_stat_activity a

   JOIN pg_roles r ON a.usename = r.rolname AND not rolcanlogin;

grant test1 test1_rep;                   -- test1권한을 test1_rep에게 부여

reassign owned by test1 to test2;        -- test1이 가진 ownership를 test2에 넘김

-- md5가 아닌 일반 패스워드 사용자 확인

select usename,passwd from pg_shadow where passwd not like 'md5%' or length(passwd) <> 35;

-- password 입력시 passward 패턴 검사 함수 설정

shared_preload_libraries = ''    (postgresql.conf, 암호 체크 함수 적용)

--함수에 권한 회수 및 부여

revoke all on function t1( text,  text) from public;

grant execute on function t1( text,  text) to test1;

--DDL 로깅  (log 파일에 ddl 변경 정보 write)

log_statement = 'ddl'  (postgresql.conf)

/etc/init-d/postgresql reload

-- create schema

create schema test1

create table test1.test (...)

-- 접속될 db 변경

alter role test1 set search_path = 'testdb1'

-- schema로 public 권한 제거 및 부여

revoke all on schema test1 from public

grant all on schema test1 to test2

grant usage on schema test1 to test2

grant create on schema test1 to test2

-- user 생성하고 database 설정

create user test1

create database test owner = test1

begin;

revoke connect to database test from public

grant connect to database test to test1

commit;

[DDL]  (각 row 내부 식별자는 ctid => 오라클에 rowid랑 유사)

-- table create

CREATE newtable(LIKE oldtable)  -- oracle ctas where 1=0

CREATE TABLE test1 (id integer PRIMARY KEY);

CREATE TABLE test2 (id integer,price smallint, PRIMARY KEY (id, price));

-- 중복 컬럼 리스트 테이블 생성 ( unlogged 옵션 사용으로 wal에 남기지 않음)

CREATE UNLOGGED TABLE dup_cust AS SELECT * FROM cust WHERE customerid IN (SELECT customerid FROM cust GROUP BY customerid HAVING count(*) > 1);

-- sequnce 컬럼 포함된 테이블

create table test3(id serial, name text);

-- ip address 포함 

CREATE TYPE inetrange AS RANGE (SUBTYPE = inet);

CREATE TABLE iprange3(iprange inetrange,owner text);

INSERT INTO iprange3 VALUES('[192.168.0.1,192.168.0.16]', 'Simon');

INSERT INTO iprange3 VALUES('[192.168.0.17,192.168.0.24]', 'Gianni');

INSERT INTO iprange3 VALUES('[192.168.0.32,192.168.0.64]', 'Hannu');

ALTER TABLE iprange3 ADD EXCLUDE USING GIST (iprange WITH &&);  --> 인덱스 생성

-- 컬럼이름이 동일한 테이블 컬럼 비교 하기

SELECT

 table_schema

,table_name

,column_name

,data_type

  ||coalesce(' ' || text(character_maximum_length), '')

  ||coalesce(' ' || text(numeric_precision), '')

  ||coalesce(',' || text(numeric_scale), '')

  as data_type

FROM information_schema.columns

WHERE column_name IN

(SELECT

 column_name

FROM

(SELECT

  column_name

 ,data_type

 ,character_maximum_length

 ,numeric_precision

 ,numeric_scale

 FROM information_schema.columns

 WHERE table_schema NOT IN ('information_schema', 'pg_catalog')

 GROUP BY

  column_name

 ,data_type

 ,character_maximum_length

 ,numeric_precision

 ,numeric_scale

) derived

GROUP BY column_name

HAVING count(*) > 1

)

AND table_schema NOT IN ('information_schema', 'pg_catalog')

ORDER BY column_name

;

-- 테이블 이름이 동일한 테이블 비교 하기

SELECT

 table_schema

,table_name

,column_name

,data_type

FROM information_schema.columns

WHERE table_name IN

(SELECT

  table_name

 FROM

 (SELECT DISTINCT 

   table_name 

  ,def 

   FROM 

   (SELECT 

     table_schema 

    ,table_name 

    ,string_agg(column_name||' '||data_type, ',' ORDER BY column_name) AS def 

    FROM information_schema.columns 

    WHERE table_schema NOT IN ('information_schema','pg_catalog') 

    GROUP BY 

     table_schema 

    ,table_name      

    ) t

 ) def

 GROUP BY

  table_name

 HAVING

  count(*) > 1

)

ORDER BY

 table_name

,table_schema

,column_name

-- alter table

ALTER TABLE test2 ADD FOREIGN KEY (id) REFERENCES test1 (id);

-- drop table

drop table test1

-- create trigger

Trigger variable	Data type	Description
NEW	RECORD	It holds the row that is inserted or updated. In the case of statement level trigger, it is NULL.
OLD	RECORD	It holds the old row that is updated or deleted. In the case of statement level trigger, it is NULL.
TG_NAME	NAME	The trigger name.
TG_OP	NAME	The trigger operation, which can have one of the following values: INSERT UPDATE DELETE TRUNCATE
TG_WHEN	NAME	The time when the trigger is fired. It can have one of the following values: AFTER BEFORE
TG_RELID	OID	The relation OID. One can get the relation name by casting it to text using regclass::text.
TG_TABLE_NAME	NAME	The trigger table name.
TG_TABLE_SCHEMA	NAME	The trigger table schema name.
TG_NARG	INTEGER	Number of arguments passed to the trigger.
TG_ARG[]	TEXT array	The trigger argument. The indexing starts from zero and a wrong index returns NULL.

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS

$$

BEGIN

  IF TG_OP = 'INSERT' THEN

    INSERT INTO car_log SELECT NEW.*, 'I', NOW();

  ELSIF TG_OP = 'UPDATE' THEN

    INSERT INTO car_log SELECT NEW.*, 'U', NOW();

  ELSIF TG_OP = 'DELETE' THEN

    INSERT INTO car_log SELECT OLD.*, 'D', NOW();

  END IF;

  RETURN NULL; --ignored since this is after trigger

END;

$$

LANGUAGE plpgsql;

CREATE TRIGGER emp_audit_truncate

AFTER TRUNCATE ON emp

    FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();

-- drop trigger

drop trigger emp_a on emp;

-- create index

CREATE INDEX ON booksdata USING GIN (pubinfo);

-- where 조건 기술로 unique한 인덱스 생성 가능

CREATE UNIQUE INDEX ON partial_unique (customerid) WHERE status = 'OPEN';

-- rename index

alter index test1_idx rename to table1_col_idx

-- create function

CREATE OR REPLACE FUNCTION diff_table_definition

(t1_schemaname text

,t1_tablename text

,t2_schemaname text

,t2_tablename text)

RETURNS TABLE

(t1_column_name text

,t1_data_type text

,t2_column_name text

,t2_data_type text

)

LANGUAGE SQL

as

$$

SELECT

 t1.column_name

,t1.data_type

,t2.column_name

,t2.data_type

FROM

 (SELECT column_name, data_type

  FROM information_schema.columns

  WHERE table_schema = $1

      AND table_name = $2

 ) t1

 FULL OUTER JOIN

 (SELECT column_name, data_type

  FROM information_schema.columns

  WHERE table_schema = $3

      AND table_name = $4

 ) t2

 ON t1.column_name = t2.column_name

 AND t1.data_type = t2.data_type

WHERE t1.column_name IS NULL OR t2.column_name IS NULL

;

$$;

-- drop function

DROP FUNCTION dblink_close(text);

CREATE OR REPLACE FUNCTION factorial(INTEGER ) RETURNS INTEGER AS $$

DECLARE

  fact ALIAS FOR $1;

BEGIN

  IF fact IS NULL OR fact < 0 THEN

    RAISE NOTICE 'Invalid Number';

    RETURN NULL;

  ELSIF fact = 1 THEN

    RETURN 1;

  END IF;

  DECLARE

    result INT;

  BEGIN

    result = factorial(fact - 1) * fact;

    RETURN result;

  END;

END;

$$ LANGUAGE 'plpgsql'

CREATE OR REPLACE FUNCTION cast_rank_to_text (rank int) RETURNS TEXT AS

$$

DECLARE

  rank ALIAS FOR $1;

  rank_result TEXT;

BEGIN

  IF rank = 5 THEN

    rank_result = 'Excellent';

  ELSIF rank = 4 THEN

    rank_result = 'Very Good';

  ELSIF rank = 3 THEN

    rank_result = 'Good';

  ELSIF rank = 2 THEN

    rank_result ='Fair';

  ELSIF rank = 1 THEN

    rank_result ='Poor';

  ELSE

    rank_result ='No such rank';

  END IF;

  RETURN rank_result;

END;

$$

Language plpgsql;

SELECT n,cast_rank_to_text(n) FROM generate_series(1,5) as foo(n);

-- anonymous function

CREATE ROLE

car_portal=# DO $$DECLARE r record;

BEGIN

    FOR r IN SELECT table_schema, table_name FROM information_schema.tables

             WHERE  table_schema = 'car_portal_app'

    LOOP

        EXECUTE 'GRANT SELECT ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO select_only';

    END LOOP;

END$$;

DO

CREATE TABLE test (

  id SERIAL PRIMARY KEY,

  name TEXT NOT NULL

);

DO $$

DECLARE

  auto_generated_id INT;

BEGIN

  INSERT INTO test(name) VALUES ('Hello World') RETURNING id INTO auto_generated_id;

  RAISE NOTICE 'The primary key is: %', auto_generated_id;

END

$$

-- looping and exit

CREATE OR REPLACE FUNCTION factorial (fact int) RETURNS BIGINT AS

$$

DECLARE

  result bigint = 1;

BEGIN

  IF fact = 1 THEN

    RETURN 1;

  ELSIF fact IS NULL or fact < 1 THEN

    RAISE EXCEPTION 'Provide a positive integer';

  ELSE

    LOOP

      result = result*fact;

      fact = fact-1;

      EXIT WHEN fact = 1;

    END Loop;

  END IF;

  RETURN result;

END;

$$

LANGUAGE plpgsql;

DO

$$

DECLARE

  first_day_in_month date := date_trunc('month', current_date)::date;

  last_day_in_month date := (date_trunc('month', current_date)+ INTERVAL '1 MONTH - 1 day')::date;

  counter date = first_day_in_month;

BEGIN

  WHILE (counter <= last_day_in_month) LOOP

    RAISE notice '%', counter;

    counter := counter + interval '1 day';

  END LOOP;

  

END;

$$

LANGUAGE plpgsql;

DO

$$

BEGIN

FOR j IN REVERSE -1 .. -10 BY 2 LOOP

    Raise notice '%', j;

END LOOP;

END;

$$

LANGUAGE plpgsql;

DO $$

DECLARE

  database RECORD;

BEGIN

  FOR database IN SELECT * FROM pg_database LOOP

    RAISE notice '%', database.datname;

  END LOOP;

END;

$$;

CREATE OR REPLACE FUNCTION t1.get_account_in_json (account_id INT) RETURNs JSON AS

$$

  SELECT row_to_json(account) FROM t1.account WHERE account_id = $1;

$$

LANGUAGE SQL;

GET DIAGNOSTICS i = ROW_COUNT;

-- error exception and handling..

CREATE OR REPLACE FUNCTION check_not_null (value anyelement ) RETURNS VOID AS

$$

BEGIN

  IF (value IS NULL) THEN

    RAISE EXCEPTION USING ERRCODE = 'check_violation';

  END IF;

END;

$$ LANGUAGE plpgsql;

SELECT check_not_null(null::text);

CREATE OR REPLACE FUNCTION factorial(INTEGER ) RETURNS BIGINT AS $$

DECLARE

  fact ALIAS FOR $1;

  

BEGIN

  PERFORM check_not_null(fact);

  IF fact > 1 THEN

    RETURN factorial(fact - 1) * fact;

  ELSIF fact IN (0,1) THEN

    RETURN 1;

  ELSE

    -- Negative values

    RETURN NULL;

  END IF;

  

EXCEPTION

  WHEN check_violation THEN

    RETURN NULL;

  WHEN OTHERS THEN

    RAISE NOTICE '% %', SQLERRM, SQLSTATE

END;

$$ LANGUAGE 'plpgsql';

SELECT * FROM factorial(null::int);

-- dynamic sql 

DO $$

DECLARE

table_name text;

BEGIN

  FOR table_name IN SELECT tablename FROM pg_tables WHERE schemaname = 'spprs' LOOP

    EXECUTE 'ANALYZE ' || table_name;

  END LOOP;

END;

$$;    

CREATE OR REPLACE FUNCTION get_advertisement_count (some_date timestamptz ) RETURNS BIGINT AS $$

DECLARE

  count BIGINT;

BEGIN

EXECUTE 'SELECT count (*) FROM car_portal_app.advertisement WHERE advertisement_date >= $1' USING some_date INTO count;

  RETURN count;

END;

$$

LANGUAGE plpgsql;

SELECT get_advertisement_count(now() -INTERVAL '1 year');

E'SELECT COALESCE (count(*)=1, false) FROM account WHERE email = \''|| $1 || E'\'  and password = \''||$2||E'\'';

CREATE OR REPLACE FUNCTION can_login (text, pass text) RETURNS BOOLEAN AS $$

DECLARE

stmt TEXT;

result bool;

BEGIN

    stmt = format('SELECT COALESCE (count(*)=1, false) FROM account WHERE email =  %L and password = %L', $1, $2);

    RAISE NOTICE '%' , stmt;

    EXECUTE stmt INTO result;

    RETURN result;

END;

$$

LANGUAGE plpgsql;

[DML]

-- insert data

insert into people (name, age) values('Mary',16), ('Jason',19) returning id, name;

INSERT INTO hats (owner, name, colour) VALUES(2, 'lacrosse','red') RETURNING *;

-- update data

UPDATE hats SET owner = 1 WHERE id = 1;

UPDATE car_portal_app.a SET a_int = b_int FROM car_portal_app.b WHERE a.a_text=b.b_text;   (table join)

UPDATE car_portal_app.a SET a_int =

  (SELECT b_int FROM car_portal_app.b

    WHERE a.a_text=b.b_text)

WHERE a_text IN (SELECT b_text FROM car_portal_app.b);

UPDATE car_portal_app.a SET a_int = b_int FROM car_portal_app.b;

-- delete data

delete from test1

using test2

where test1.id = test2.id and test1.name != test2.name;

DELETE FROM car_portal_app.a

USING car_portal_app.b WHERE a.a_int=b.b_int;

DELETE FROM car_portal_app.a RETURNING *;

-- 지운 데이터 정리

vaccum test1

-- object에 quote("") 필요한지 안한지 확인시

select quote_ident('MyCust')

SELECT 1 WHERE (date '2015-10-15', date '2015-10-21')

SELECT * FROM car_portal_app.car_model WHERE model ILIKE 's_ _ _';

SELECT * FROM car_portal_app.car_model WHERE model ~ '^\w+\W+\w+$';

SELECT * FROM car_portal_app.car_model OFFSET 5 LIMIT 5;  (5개 스킵하고 5개 get..)

SELECT (SELECT count(*) FROM car_portal_app.car_model)

FROM car_portal_app.car

LIMIT (SELECT MIN(car_id)+2 FROM car_portal_app.car);

ILIKE operator ==> ~~* 동일

LIKE            ==> ~~

execute 'create temp table ' || quote_ident(tablename) || '(col   test);'

(SELECT COUNT(*) FROM t1.account)::INT   -- type cast

SELECT select_expressions INTO [STRICT] targets FROM ...;    -- assign value to vari.

[LDAP 설정]

pg_hba.conf 파일 아래 라인 추가

host    all         all         10.10.0.1/16          ldap \

ldapserver=ldap.our.net ldapprefix="cn=" ldapsuffix=", dc=our,dc=net"

ldap://ldap.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=mydatabase)

[SSL 설정]

postgresql.conf 설정 (SSL과 일반접근 둘다 사용가능)

ssl = on

pg_hba.conf에 설정 (hostssl일 경우 SSL로만 접근 가능)

Host      all         all         192.168.1.0/24     md5

hostssl   all         all         0.0.0.0/0          md5

비공인 인증키 생성

openssl genrsa 2048 > server.key

openssl req -new -x509 -key server.key -out server.crt

[MV 생성]

CREATE TABLE dish

( dish_id SERIAL PRIMARY KEY

, dish_description text

);

CREATE TABLE eater

( eater_id SERIAL

, eating_date date

, dish_id int REFERENCES dish (dish_id)

);

INSERT INTO dish (dish_description)

VALUES ('Lentils'), ('Mango'), ('Plantain'), ('Rice'), ('Tea');

INSERT INTO eater(eating_date, dish_id)

SELECT floor(abs(sin(n)) * 365) :: int + date '2014-01-01'

, ceil(abs(sin(n :: float * n))*5) :: int

FROM generate_series(1,500000) AS rand(n);

-- View 생성

CREATE VIEW v_dish AS

SELECT dish_description, count(*)

FROM dish JOIN eater USING (dish_id)

GROUP BY dish_description

ORDER BY 1;

-- MView 생성

CREATE MATERIALIZED VIEW m_dish AS

SELECT dish_description, count(*)

FROM dish JOIN eater USING (dish_id)

GROUP BY dish_description

ORDER BY 1;

REFRESH MATERIALIZED VIEW m_dish;

CREATE MATERIALIZED VIEW  m_dish AS

SELECT dish_description, count(*)

FROM dish JOIN eater USING (dish_id)

GROUP BY dish_description

ORDER BY 1

WITH NO DATA;

REFRESH MATERIALIZED VIEW m_dish;

create unique index idx_m_disk_01 on m_dish(dish_description);

REFRESH MATERIALIZED VIEW CONCURRENTLY m_dish;

-- 수행시간 (mview : 0.393 ms, view : 274.095 ms)

 

CREATE MATERIALIZED VIEW account_search_history AS

SELECT

  Key,

  count(*)

FROM

 (SELECT

    regexp_split_to_table(search_key, '&') AS key

 FROM

    account_history

  WHERE

    search_date >= date_trunc('month', now()-INTERVAL '1 month') AND

    search_date < date_trunc('month', now())) AS FOO

GROUP BY key;

[with 절]

WITH RECURSIVE genealogy (bloodline, parent, level) AS

(

  SELECT child, child, 0

    FROM family WHERE parent IS NULL

  UNION ALL

  SELECT g.bloodline || ' -> ' || f.child, f.child,

      g.level + 1

    FROM family f, genealogy g

    WHERE f.parent = g.parent

)

SELECT bloodline, level FROM genealogy;

WITH car_model_insert AS

(

  INSERT INTO car_portal_app.car_model (marke, model)     

    VALUES ('Ford','Mustang') RETURNING car_model_id

)

INSERT INTO car_portal_app.car

  (number_of_owners, registration_number,

    manufacture_year, number_of_doors, car_model_id,

    mileage)

  SELECT 1, 'GTR1231', 2014, 4, car_model_id, 10423

    FROM car_model_insert;

WITH del_query AS

(DELETE FROM t RETURNING f)

INSERT INTO t SELECT 1

WHERE (SELECT count(*) FROM del_query) IS NOT NULL;

[window func]

select count() over wf, sum() over wf, avg() over (wf order by t rows between 1 preceding and 1 following) from test1 window wf as (partiton by t)

WITH monthly_data AS (

  SELECT date_trunc('month', advertisement_date) AS

      month, count(*) as cnt

    FROM car_portal_app.advertisement

    GROUP BY date_trunc('month', advertisement_date)

)

SELECT to_char(month,'YYYY-MM') as month, cnt,

    sum(cnt) OVER (w ORDER BY month) AS cnt_year,

    round(avg(cnt) OVER (ORDER BY month

      ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),1)

      AS mov_avg,

    round(cnt/sum(cnt) OVER w *100,1) AS ratio_year

  FROM monthly_data

  WINDOW w AS (PARTITION BY date_trunc('year',month));

[ 실행계획 확인 ]

EXPLAIN SELECT * from test;

                            QUERY PLAN

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

Seq Scan on test (cost=0.00..15406.00  rows=1000000 width=11)

SET enable_seqscan TO OFF;   -- sequence scan disable..

CREATE OR REPLACE FUNCTION configuration_test () RETURNS VOID AS

$$

BEGIN

RAISE NOTICE 'Current session enable_seqscan value: %', (SELECT setting FROM pg_settings WHERE name ='enable_seqscan')::text;

RAISE NOTICE 'Function work_mem: %', (SELECT setting FROM pg_settings WHERE name ='work_mem')::text;

---

---SQL statement here will use index scan when possible

---

SET LOCAL enable_seqscan TO TRUE;

RAISE NOTICE 'Override session enable_seqscan value: %', (SELECT setting FROM pg_settings WHERE name ='enable_seqscan')::text;

---

---SQL statement here will use index scan when possible

---

END;

$$

LANGUAGE PLPGSQL

SET enable_seqscan FROM current

SET work_mem = '10MB';    -- define memory size

[기타]

SELECT * FROM generate_series(5, 11, 3);

SELECT * FROM generate_series('2015-01-01'::date, '2015-01-31'::date, interval '7 days');

SELECT car_model_id,

    count(*) FILTER (WHERE number_of_doors = 2) doors2,

    count(*) FILTER (WHERE number_of_doors = 3) doors3,

    count(*) FILTER (WHERE number_of_doors = 4) doors4,

    count(*) FILTER (WHERE number_of_doors = 5) doors5

  FROM car_portal_app.car

  GROUP BY car_model_id;