1.查看表列表

SELECT
    c.oid,
    obj_description (c.oid),
    c.relhasoids AS hasoids,
    n.nspname AS schemaname,
    c.relname AS tablename,
    c.relkind,
    pg_get_userbyid (c.relowner) AS tableowner,
    t.spcname AS "tablespace",
    c.relhasindex AS hasindexes,
    c.relhasrules AS hasrules,
    c.relhastriggers AS hastriggers,
    ft.ftoptions,
    fs.srvname,
    c.relacl,
    c.reltuples,
    (
        (
            SELECT
                count (*)
            FROM
                pg_inherits
            WHERE
                inhparent = c.oid
        ) > 0
    ) AS inhtable,
    i2.nspname AS inhschemaname,
    i2.relname AS inhtablename,
    c.reloptions AS param,
    c.relpersistence AS unlogged
FROM
    pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN (
    pg_inherits i
    INNER JOIN pg_class c2 ON i.inhparent = c2.oid
    LEFT JOIN pg_namespace n2 ON n2.oid = c2.relnamespace
) i2 ON i2.inhrelid = c.oid
LEFT JOIN pg_foreign_table ft ON ft.ftrelid = c.oid
LEFT JOIN pg_foreign_server fs ON ft.ftserver = fs.oid
WHERE
    (
        (c.relkind = 'r' :: "char")
        OR (c.relkind = 'f' :: "char")
    )
AND n.nspname = 'public';

2.查看表列属性定义

SELECT 
  A.attnum, 
  A.attname AS field, 
  T.typname AS TYPE, 
  A.attlen AS LENGTH, 
  A.atttypmod AS lengthvar, 
  A.attnotnull AS NOTNULL, 
  b.description AS COMMENT 
FROM 
  pg_class C, 
  pg_attribute A 
  LEFT OUTER JOIN pg_description b ON A.attrelid = b.objoid 
  AND A.attnum = b.objsubid, 
  pg_type T 
WHERE 
  C.relname = 't_sku' 
  AND A.attnum > 0 
  AND A.attrelid = C.oid 
  AND A.atttypid = T.oid 
ORDER BY 
  A.attnum;

3.pg 正在执行的sql

SELECT
    pid,
    datname,
    usename,
    client_addr,
    application_name,
    STATE,
    backend_start,
    xact_start,
    xact_stay,
    query_start,
    query_stay,
    REPLACE ( query, chr( 10 ), ' ' ) AS query 
FROM
    (
    SELECT
        pgsa.pid AS pid,
        pgsa.datname AS datname,
        pgsa.usename AS usename,
        pgsa.client_addr client_addr,
        pgsa.application_name AS application_name,
        pgsa.STATE AS STATE,
        pgsa.backend_start AS backend_start,
        pgsa.xact_start AS xact_start,
        EXTRACT ( epoch FROM ( now( ) - pgsa.xact_start ) ) AS xact_stay,
        pgsa.query_start AS query_start,
        EXTRACT ( epoch FROM ( now( ) - pgsa.query_start ) ) AS query_stay,
        pgsa.query AS query 
    FROM
        pg_stat_activity AS pgsa 
    WHERE
        pgsa.STATE != 'idle' 
        AND pgsa.STATE != 'idle in transaction' 
        AND pgsa.STATE != 'idle in transaction (aborted)' 
    ) idleconnections 
ORDER BY
    query_stay DESC

4.pg中事务信息

SELECT
  procpid,
  START,
  now() - START AS lap,
  current_query
FROM
  (
    SELECT
      backendid,
      pg_stat_get_backend_pid (S.backendid) AS procpid,
      pg_stat_get_backend_activity_start (S.backendid) AS START,
      pg_stat_get_backend_activity (S.backendid) AS current_query
    FROM
      (
        SELECT
          pg_stat_get_backend_idset () AS backendid
      ) AS S
  ) AS S
WHERE
  current_query <> '<IDLE>'
ORDER BY
  lap DESC;