$ cd .src/contrib/dblink
$ make
$ make install

test=# create extension dblink;
CREATE extension

test=# select dblink_connect('db링크이름','hostaddr=호스트IP port=호스트PORT dbname=DB명 user=유저명 password=비밀번호');
 dblink_connect 
----------------
 OK
(1 row)
-- 조회(as 뒤에 select결과에 대한 type을 column별로 지정)
test=# select * from dblink('db2', 'select * from test') as t1(c1 char);
 c1 
----
 d
(1 row)
-- 접속해제
test=# select dblink_disconnect('db2');
 dblink_disconnect 
-------------------
 OK
(1 row)

-- new table bloat query
-- still needs work; is often off by +/- 20%
WITH constants AS (
    -- define some constants for sizes of things
    -- for reference down the query and easy maintenance
    SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 8 AS ma
),
no_stats AS (
    -- screen out table who have attributes
    -- which dont have stats, such as JSON
    SELECT table_schema, table_name
    FROM information_schema.columns
        LEFT OUTER JOIN pg_stats
        ON table_schema = schemaname
            AND table_name = tablename
            AND column_name = attname
    WHERE attname IS NULL
        AND table_schema NOT IN ('pg_catalog', 'information_schema')
    GROUP BY table_schema, table_name
),
null_headers AS (
    -- calculate null header sizes
    -- omitting tables which dont have complete stats
    -- and attributes which aren't visible
    SELECT
        hdr+1+(sum(case when null_frac <> 0 THEN 1 else 0 END)/8) as nullhdr,
        SUM((1-null_frac)*avg_width) as datawidth,
        MAX(null_frac) as maxfracsum,
        schemaname,
        tablename,
        hdr, ma, bs
    FROM pg_stats CROSS JOIN constants
        LEFT OUTER JOIN no_stats
            ON schemaname = no_stats.table_schema
            AND tablename = no_stats.table_name
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
        AND no_stats.table_name IS NULL
        AND EXISTS ( SELECT 1
            FROM information_schema.columns
                WHERE schemaname = columns.table_schema
                    AND tablename = columns.table_name )
    GROUP BY schemaname, tablename, hdr, ma, bs
),
data_headers AS (
    -- estimate header and row size
    SELECT
        ma, bs, hdr, schemaname, tablename,
        (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
        (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM null_headers
),
table_estimates AS (
    -- make estimates of how large the table should be
    -- based on row and page size
    SELECT schemaname, tablename, bs,
        reltuples, relpages * bs as table_bytes,
    CEIL((reltuples*
            (datahdr + nullhdr2 + 4 + ma -
                (CASE WHEN datahdr%ma=0
                    THEN ma ELSE datahdr%ma END)
                )/(bs-20))) * bs AS expected_bytes
    FROM data_headers
        JOIN pg_class ON tablename = relname
        JOIN pg_namespace ON relnamespace = pg_namespace.oid
            AND schemaname = nspname
    WHERE pg_class.relkind = 'r'
),
table_estimates_plus AS (
-- add some extra metadata to the table data
-- and calculations to be reused
-- including whether we cant estimate it
-- or whether we think it might be compressed
    SELECT current_database() as databasename,
            schemaname, tablename, reltuples as est_rows,
            CASE WHEN expected_bytes > 0 AND table_bytes > 0 THEN
                TRUE ELSE FALSE END as can_estimate,
            CASE WHEN expected_bytes > table_bytes THEN
                TRUE ELSE FALSE END as is_compressed,
            CASE WHEN table_bytes > 0
                THEN table_bytes::NUMERIC
                ELSE NULL::NUMERIC END
                AS table_bytes,
            CASE WHEN expected_bytes > 0 
                THEN expected_bytes::NUMERIC
                ELSE NULL::NUMERIC END
                    AS expected_bytes,
            CASE WHEN expected_bytes > 0 AND table_bytes > 0
                AND expected_bytes <= table_bytes
                THEN (table_bytes - expected_bytes)::NUMERIC
                ELSE 0::NUMERIC END AS bloat_bytes
    FROM table_estimates
),
bloat_data AS (
    -- do final math calculations and formatting
    select current_database() as databasename,
        schemaname, tablename, can_estimate, is_compressed,
        table_bytes, round(table_bytes/(1024^2)::NUMERIC,3) as table_mb,
        expected_bytes, round(expected_bytes/(1024^2)::NUMERIC,3) as expected_mb,
        round(bloat_bytes*100/table_bytes) as pct_bloat,
        round(bloat_bytes/(1024::NUMERIC^2),2) as mb_bloat,
        table_bytes, expected_bytes
    FROM table_estimates_plus
)
-- filter output for bloated tables
SELECT databasename, schemaname, tablename,
    --can_estimate, is_compressed,
    pct_bloat, mb_bloat,
    table_mb
FROM bloat_data
-- this where clause defines which tables actually appear
-- in the bloat chart
-- example below filters for tables which are either 50%
-- bloated and more than 20mb in size, or more than 25%
-- bloated and more than 4GB in size
WHERE ( pct_bloat >= 50 AND mb_bloat >= 10 )
    OR ( pct_bloat >= 25 AND mb_bloat >= 1000 )
ORDER BY pct_bloat DESC;

create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by -' || l_val || ' minvalue 0';

    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/

testdb=# explain analyze select * from test_table where test_column like '%TEXT%';
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Seq Scan on test_table  (cost=0.00..288431.08 rows=264 width=298) (actual time=952.905..1119.831 rows=6 loops=1)
   Filter: ((test_column)::text ~~ '%TEXT%'::text)
 Total runtime: 1119.854 ms
(3 rows)

$ make
$ make install

testdb=# create extension pg_trgm;
CREATE EXTENSION
CREATE INDEX trgm_idx ON test_table USING gin (test_column gin_trgm_ops);
CREATE INDEX

testdb=# explain analyze select * from test_table where test_column like '%TEXT%';
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_table  (cost=22.42..1245.43 rows=313 width=298) (actual time=0.021..0.029 rows=6 loops=1)
   Recheck Cond: ((test_column)::text ~~ '%TEXT%'::text)
   ->  Bitmap Index Scan on trgm_idx  (cost=0.00..22.35 rows=313 width=0) (actual time=0.014..0.014 rows=6 loops=1)
         Index Cond: ((test_column)::text ~~ '%TEXT%'::text)
 Total runtime: 0.051 ms
(5 rows)

# umount /directory
umount: /directory: device is busy.
        (In some cases useful info about processes that use
         the device is found by lsof(8) or fuser(1))

# lsof +D /directory
COMMAND    PID     USER   FD   TYPE DEVICE SIZE/OFF     NODE NAME
bash          10479 postgres  cwd    DIR    8,4     8192 16056321 /directory/postDATA/DATA/pg_log
psql              20824 postgres  cwd    DIR    8,4     4096 15974401 /directory/postDATA/DATA
postgres        21635 postgres  cwd    DIR    8,4     4096 15974401 /directory/postDATA/DATA
postgres        21636 postgres  cwd    DIR    8,4     4096 15974401 /directory/postDATA/DATA
postgres        21636 postgres    8w   REG    8,4     1072 16056322 /directory/postDATA/DATA/pg_log/postgresql-2013-02-25_142559.log