PSQL基本使用(定製維護指令碼)

wanghao2979發表於2021-01-21

環境描述:

作業系統:            CentOS Linux release 7.6.1810 (Core) 

postgresql版本       postgres (PostgreSQL) 12.4

host1               192.168.112.200

host2               192.168.112.201



首先翻看 postgresql(12.4) 官方文件 (Part VI.Reference => PostgreSQL Client Applications =>psql) 的定義部分

也可以參考 postgresql(12.4) 的原始碼 src/bin/psql/help.c  檔案中 usage 的函式部分

或者執行 psql --help



[postgres@pg1 ~]$ psql --help

psql is the PostgreSQL interactive terminal.


Usage:

  psql [OPTION]... [DBNAME [USERNAME]]


General options:

  -c, --command=COMMAND    run only single command (SQL or internal) and exit

  -d, --dbname=DBNAME      database name to connect to (default: "postgres")

  -f, --file=FILENAME      execute commands from file, then exit

  -l, --list               list available databases, then exit

  -v, --set=, --variable=NAME=VALUE

                           set psql variable NAME to VALUE

                           (e.g., -v >

  -V, --version            output version information, then exit

  -X, --no-psqlrc          do not read startup file (~/.psqlrc)

  -1 ("one"), --single-transaction

                           execute as a single transaction (if non-interactive)

  -?, --help[=options]     show this help, then exit

      --help=commands      list backslash commands, then exit

      --help=variables     list special variables, then exit


Input and output options:

  -a, --echo-all           echo all input from script

  -b, --echo-errors        echo failed commands

  -e, --echo-queries       echo commands sent to server

  -E, --echo-hidden        display queries that internal commands generate

  -L, --log-file=FILENAME  send session log to file

  -n, --no-readline        disable enhanced command line editing (readline)

  -o, --output=FILENAME    send query results to file (or |pipe)

  -q, --quiet              run quietly (no messages, only query output)

  -s, --single-step        single-step mode (confirm each query)

  -S, --single-line        single-line mode (end of line terminates SQL command)


Output format options:

  -A, --no-align           unaligned table output mode

      --csv                CSV (Comma-Separated Values) table output mode

  -F, --field-separator=STRING

                           field separator for unaligned output (default: "|")

  -H, --html               HTML table output mode

  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)

  -R, --record-separator=STRING

                           record separator for unaligned output (default: newline)

  -t, --tuples-only        print rows only

  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)

  -x, --expanded           turn on expanded table output

  -z, --field-separator-zero

                           set field separator for unaligned output to zero byte

  -0, --record-separator-zero

                           set record separator for unaligned output to zero byte


Connection options:

  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")

  -p, --port=PORT          database server port (default: "5432")

  -U, --username=USERNAME  database user name (default: "postgres")

  -w, --no-password        never prompt for password

  -W, --password           force password prompt (should happen automatically)


For more information, type "\?" (for internal commands) or "\help" (for SQL

commands) from within psql, or consult the psql section in the PostgreSQL

documentation.



sql命令與元命令的區別在官方文件 (Part VI.Reference => PostgreSQL Client Applications =>psql) 中的 Entering SQL Commands 與 Meta-Commands 進行描述


1.\x 設定查詢結果輸出

設定前:

     postgres=# select * from test;

     id  

     -----

     123

    (1 row)

設定後:

     postgres=# \x

     Expanded display is on.

     postgres=# select * from test;

     -[ RECORD 1 ]

     id | 123


2.\timing 顯示執行時間

設定前: 

     postgres=# select * from test;

     id  

     -----

     123

    (1 row)

設定後:

     postgres=# \timing on

     Timing is on.

     postgres=# select * from test;

     id  

     -----

     123

    (1 row)


    Time: 0.221 ms


3.\watch反覆執行當前SQL



     postgres=# select * from test;

     id  

     -----

     123

    (1 row)


     postgres=# \watch

     Thu 21 Jan 2021 05:01:44 AM EST (every 2s)


      id  

     -----

      123

     (1 row)


     Thu 21 Jan 2021 05:01:46 AM EST (every 2s)


      id  

     -----

      123

     (1 row) 


4.-E 獲取元命令對應的SQL語句

使用前:

     [postgres@pg1 ~]$ psql 

     psql (12.4)

     Type "help" for help.


     postgres=# \d

           List of relations

     Schema | Name | Type  | Owner  

    --------+------+-------+--------

     public | test | table | SYSTEM

    (1 row)


使用後:

     [postgres@pg1 ~]$ psql -E

     psql (12.4)

     Type "help" for help.


     postgres=# \d

     ********* QUERY **********

     SELECT n.nspname as "Schema",

       c.relname as "Name",

       CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN          'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",

       pg_catalog.pg_get_userbyid(c.relowner) as "Owner"

     FROM pg_catalog.pg_class c

          LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

     WHERE c.relkind IN ('r','p','v','m','S','f','')

           AND n.nspname <> 'pg_catalog'

           AND n.nspname <> 'information_schema'

           AND n.nspname !~ '^pg_toast'

       AND pg_catalog.pg_table_is_visible(c.oid)

     ORDER BY 1,2;

     **************************


            List of relations

      Schema | Name | Type  | Owner  

     --------+------+-------+--------

      public | test | table | SYSTEM

     (1 row)


5. -c psql執行SQL指令碼

效果:

      [postgres@pg1 ~]$ psql  -c "select * from test"

      id  

     -----

      123

     (1 row)


6.-A 設定非對其模式輸出


設定前:

      [postgres@pg1 ~]$ psql  -c "select * from test"

      id  

     -----

      123

     (1 row)


設定後:

      [postgres@pg1 ~]$ psql -A  -c "select * from test"

      id

      123

      (1 row)


7.-t 只顯示記錄資料


設定前:

     [postgres@pg1 ~]$ psql -c "select id from test";

      id  

     -----

     123

     (1 row)


設定後:

     [postgres@pg1 ~]$ psql -t -c  "select id from test";

       123


備註: psql -tA -c "select id from test";  -tA的寫法對後期編寫shell指令碼進行維護幫助極大,以及對監控工具(比如zabbix,請關注後期更新)極其友好.


8. \set 元命令傳遞變數


     postgres=# \set v_id 123

     postgres=# select * from test where id = :v_id;

      id  

     -----

      123

     (1 row)



使用psql 定製日常維護指令碼


編輯 .psqlrc 檔案或者 pg_config --sysconfdir 目錄下的 psqlrc 檔案 (postgresql原始碼檔案 src/bin/psql/psqlrc.sample 檔案內容)


\set active_session 'select pid,usename,datname,query,client_addr from pg_stat_activity where pid <> pg_backend_pid() and state = \'active\' order by query';

\set wait_event 'select pid,usename,datname,query_start,client_addr,wait_event_type,wait_event from pg_stat_activity where pid <> pg_backend_pid() and wait_event is not null order by wait_event_type';

\set connections 'select datname,usename,client_addr,count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1,2,3 order by 1,2,4 desc';


1.查詢活動會話


postgres=# :active_session

 pid | usename | datname | query | client_addr 

-----+---------+---------+-------+-------------

(0 rows)


2.查詢等待事件


postgres=# :wait_event

 pid  | usename | datname | query_start | client_addr | wait_event_type |     wait_event      

------+---------+---------+-------------+-------------+-----------------+---------------------

 9727 | SYSTEM  |         |             |             | Activity        | LogicalLauncherMain

 9724 |         |         |             |             | Activity        | AutoVacuumMain

 9722 |         |         |             |             | Activity        | BgWriterHibernate

 9721 |         |         |             |             | Activity        | CheckpointerMain

 9723 |         |         |             |             | Activity        | WalWriterMain

(5 rows)


3.檢視資料庫連線數


postgres=# :connections

 datname | usename | client_addr | count 

---------+---------+-------------+-------

         | SYSTEM  |             |     1

         |         |             |     4

(2 rows)





來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7569309/viewspace-2751622/,如需轉載,請註明出處,否則將追究法律責任。

相關文章