bedbmsguru

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

https://stackoverflow.com/questions/21738408/postgresql-list-and-order-tables-by-size/21738505

테이블 구조 확인 SQL

--V12.0 이상

SELECT
f.attnum AS number,
f.attname AS name,
f.attnum,
f.attnotnull AS notnull,
pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
CASE
WHEN p.contype = 'p' THEN 't'
ELSE 'f'
END AS primarykey,
CASE
WHEN p.contype = 'u' THEN 't'
ELSE 'f'
END AS uniquekey,
CASE
WHEN p.contype = 'f' THEN g.relname
END AS foreignkey,
CASE
WHEN p.contype = 'f' THEN p.confkey
END AS foreignkey_fieldnum,
CASE
WHEN p.contype = 'f' THEN g.relname
END AS foreignkey,
CASE
WHEN p.contype = 'f' THEN p.conkey
END AS foreignkey_connnum,
CASE
WHEN f.atthasdef = 't' THEN pg_get_expr(d.adbin, d.adrelid)
END AS default
FROM pg_attribute f
JOIN pg_class c ON c.oid = f.attrelid
JOIN pg_type t ON t.oid = f.atttypid
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
WHERE c.relkind = 'r'::char
AND n.nspname = 'schema' -- Replace with Schema name
AND c.relname = 'tablename' -- Replace with table name
AND f.attnum > 0 ORDER BY number;

--V 11이하

SELECT
f.attnum AS number,
f.attname AS name,
f.attnum,
f.attnotnull AS notnull,
pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
CASE
WHEN p.contype = 'p' THEN 't'
ELSE 'f'
END AS primarykey,
CASE
WHEN p.contype = 'u' THEN 't'
ELSE 'f'
END AS uniquekey,
CASE
WHEN p.contype = 'f' THEN g.relname
END AS foreignkey,
CASE
WHEN p.contype = 'f' THEN p.confkey
END AS foreignkey_fieldnum,
CASE
WHEN p.contype = 'f' THEN g.relname
END AS foreignkey,
CASE
WHEN p.contype = 'f' THEN p.conkey
END AS foreignkey_connnum,
CASE
WHEN f.atthasdef = 't' THEN d.adsrc
END AS default
FROM pg_attribute f
JOIN pg_class c ON c.oid = f.attrelid
JOIN pg_type t ON t.oid = f.atttypid
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
WHERE c.relkind = 'r'::char
AND n.nspname = 'schema' -- Replace with Schema name
AND c.relname = 'tablename' -- Replace with table name
AND f.attnum > 0 ORDER BY number;

https://stackoverflow.com/questions/109325/postgresql-describe-table

postgresql 튜닝대상 SQL 을 수집하기 위한 pg_stat_statements 

pg_stat_statements 설정법

https://rastalion.me/pg_stat_statements/

pg_stat_statements Documents

https://www.postgresql.org/docs/12/pgstatstatements.html

blocking session 모니터링

SELECT
          datname,
          usename,
          application_name,
          now()-backend_start AS "Session duration",
          pid,
          wait_event,
          wait_event_type,
          query
        FROM
          pg_stat_activity
        WHERE
          state='active'
        AND wait_event IS NOT NULL;

postgresql의 환경설정(postgresql.conf)을 저장하고 있는 시스템테이블

postgresql.conf내용을 Query를 이용해서 확인 할때 사용

내용은 아래 링크에 잘 정리되어 있음

http://kimchki.blogspot.com/2019/04/postgresql-pgsettings.html

설정 parameters

1. shared_buffers :

     PostgresSQL에서 DISK I/O를 줄이기 위해 사용하는 Buffer

     -->물리적 메모리의 1/2 ~ 1/4

2. work_mem:

    join, sorting, hashing 작업시 session 별로 할당되는 메모리

       -->복잡한 sort가 많이 사용되거나 큰 sort 가 필요하다면 이 값을 증가 시켜야함

       -->session 별로 할당되기 때문에 설정시 최대 접속자 수를 고려하여야 함

3. maintenance_work_mem

  유지관리  작업(CREATE INDEX, Vacuum, Alter Table 등)에 사용되는 메모리

   -->  Share Buffers 의 25%

   -->  autovacuum_work_mem 파라미터가 -1 이면 Autovacuum Worker Process들도  이 메모리를 사용

Visual Studio 2013용 Visual C++ 재배포 가능 패키지를 설치해주시면 됩니다.
구글에 VC++ redistributable 2013 혹은 Visual Studio 2013용 Visual C++ 재배포 가능 패키지 

설치 하면됨

출처

https://lsy0318.blogspot.com/2018/07/postgresql-libpqdll-featheidisql.html

1. psql 의 prompt 설정 하기

--사용자의 홈디렉토리에서 설정할 것

vi ~/.psqlrc

\set PROMPT1 '%M:%> %n@%/%R%#%x '

• %M 서버 hostname
• %> DB port
• %n session username
• %/  current database
• %R  whether you’re in single-line mode (^) or disconnected (!) but is normally =
• %# refers to whether you’re a superuser (#) or a regular user (>)
• %x  refers to the transaction status – usually blank unless in a transaction block (*)

2. color 설정

\set PROMPT1 '%M:%[%033[1;31m%]%>%[%033[0m%] %n@%/%R%#%x '

• 32 for green
• 33 for yellow
• 34 for blue
• 35 for magenta
• 36 for cyan
• 37 for white

https://www.digitalocean.com/community/tutorials/how-to-customize-the-postgresql-prompt-with-psqlrc-on-ubuntu-14-04