test=# create table test_table(col1 numeric);
CREATE TABLE
test=# insert into test_table values(1);
INSERT 0 1
test=# explain select col1 from test_table where col1 = '1';
                                  QUERY PLAN                                   
-------------------------------------------------------------------------
 Seq Scan on test_table  (cost=10000000000.00..10000000026.38 rows=7 width=32)
   Filter: (col1 = 1::numeric)
(2 rows)

test=# prepare foo(varchar) as
test-# select col1 from test_table where col1 = $1;
ERROR:  operator does not exist: numeric = character varying
LINE 2: select col1 from test_table where col1 = $1;
                                               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
test=#

test=# prepare foo(varchar) as
select col1 from test_table where col1 = $1::numeric;
PREPARE
test=# execute foo('1');
 col1 
------
    1
(1 row)

estdb=# create extension pgcrypto;
CREATE EXTENSION
testdb=# create table user_info(user_id varchar(30), password varchar(100));
CREATE TABLE
testdb=# insert into user_info values('id1', crypt('id1_password', gen_salt('md5')));
INSERT 0 1
testdb=# insert into user_info values('id2', crypt('id2_password', gen_salt('md5')));  
INSERT 0 1

testdb=# select user_id from user_info where user_id='id1' 
            and password = crypt('id1_password', password);
 user_id 
---------
 id1
(1 row)

testdb=# 

pg_test=# select context,name,setting from pg_settings
where name in ('enable_seqscan','authentication_timeout','max_connections');
  context   |          name          | setting 
------------+------------------------+---------
 sighup     | authentication_timeout | 60
 user       | enable_seqscan         | on
 postmaster | max_connections        | 100
(3 rows)

pg_test=# ALTER SYSTEM set authentication_timeout=30;
ALTER SYSTEM
pg_test=# ALTER SYSTEM set enable_seqscan = off;
ALTER SYSTEM
pg_test=# ALTER SYSTEM set max_connections = 1000;
ALTER SYSTEM
pg_test=# select context,name,setting from pg_settings
where name in ('enable_seqscan','authentication_timeout','max_connections');
  context   |          name          | setting 
------------+------------------------+---------
 sighup     | authentication_timeout | 60
 user       | enable_seqscan         | on
 postmaster | max_connections        | 100
(3 rows)

pg_test=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

pg_test=# select context,name,setting from pg_settings
where name in ('enable_seqscan','authentication_timeout','max_connections');
  context   |          name          | setting 
------------+------------------------+---------
 sighup     | authentication_timeout | 30
 user       | enable_seqscan         | off
 postmaster | max_connections        | 100
(3 rows)

pg_test=# ALTER SYSTEM reset authentication_timeout;
ALTER SYSTEM
pg_test=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
pg_test=# select context,name,setting from pg_settings
where name in ('enable_seqscan','authentication_timeout','max_connections');
  context   |          name          | setting 
------------+------------------------+---------
 sighup     | authentication_timeout | 60
 user       | enable_seqscan         | off
 postmaster | max_connections        | 100
 

pg_test=# ALTER SYSTEM reset all;
ALTER SYSTEM

pg_test=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

pg_test=# select context,name,setting from pg_settings
where name in ('enable_seqscan','authentication_timeout','max_connections');
  context   |          name          | setting 
------------+------------------------+---------
 sighup     | authentication_timeout | 60
 user       | enable_seqscan         | on
 postmaster | max_connections        | 100
(3 rows)

pg_test=# alter system set data_directory='/data';
ERROR:  parameter "data_directory" cannot be changed

postgres=# CREATE TABLE cities (
postgres(#     name            text,
postgres(#     population      float,
postgres(#     altitude        int     -- in feet
postgres(# );
CREATE TABLE
postgres=# \d+ cities
                       Table "public.cities"
   Column   |       Type       | Modifiers | Storage  | Description 
------------+------------------+-----------+----------+-------------
 name       | text             |           | extended | 
 population | double precision |           | plain    | 
 altitude   | integer          |           | plain    | 
Has OIDs: no

postgres=# CREATE TABLE capitals (
postgres(#     state           char(2)
postgres(# ) INHERITS (cities);
CREATE TABLE
postgres=# \d+ capitals;
                      Table "public.capitals"
   Column   |       Type       | Modifiers | Storage  | Description 
------------+------------------+-----------+----------+-------------
 name       | text             |           | extended | 
 population | double precision |           | plain    | 
 altitude   | integer          |           | plain    | 
 state      | character(2)     |           | extended | 
Inherits: cities
Has OIDs: no

postgres=# insert into cities values('kms',10.23,1000);
INSERT 0 1
postgres=# insert into capitals values('kms',10.23,1000,'01');
INSERT 0 1
postgres=# select * from cities;
 name | population | altitude 
------+------------+----------
 kms  |      10.23 |     1000
 kms  |      10.23 |     1000
(2 rows)
                           ^
postgres=# select * from only cities;
 name | population | altitude 
------+------------+----------
 kms  |      10.23 |     1000
(1 row)

postgres=# create table streets(col1 char(1)) inherits(capitals);
insert into streets values ('kms',20.24,2100,02,'1');
INSERT 0 1

postgres=# select * from only cities;
 name | population | altitude 
------+------------+----------
 kms  |      10.23 |     1000
(1 row)

postgres=# select * from only capitals;
 name | population | altitude | state 
------+------------+----------+-------
 kms  |      10.23 |     1000 | 01
(1 row)

postgres=# select * from streets;
 name | population | altitude | state | col1 
------+------------+----------+-------+------
 kms  |      20.24 |     2100 | 2     | 1
(1 row)

postgres=# select * from cities;
 name | population | altitude 
------+------------+----------
 kms  |      10.23 |     1000
 kms  |      10.23 |     1000
 kms  |      20.24 |     2100
(3 rows)

FATAL:  could not create semaphores: No space left on device
[2012-10-24 16:49:55.313 KST][][][][][20731]DETAIL:  Failed system call was semget(5433033, 17, 03600).
[2012-10-24 16:49:55.313 KST][][][][][20731]HINT:  This error does *not* mean that you have run out of disk space.
 It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number
 of semaphores (SEMMNS), would be exceeded.  You need to raise the respective kernel parameterively, reduce PostgreSQL's 
consumption of semaphores by reducing its max_connections parameter (currently 510).
 The PostgreSQL documentation contains more information about configuring your system for PostgreSQL.

$ ipcs -l

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1

------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767

------ Messages: Limits --------
max queues system wide = 16
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536

$ vi /etc/sysctl.conf
kerlel.sem=250 256000 32 128    ## 네번째 값이 SEMMNI이고 이 값을 증가시켜야 합니다.(세마포어 배열의 최대갯수).
$ sysctl -p /etc/sysctl.conf    ## 수정한 값으로 리로드 합니다.

$ 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;

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)