PSQL基本使用(定製維護指令碼)
環境描述:
作業系統: 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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PSQL基本使用(定製PSQL提示符)SQL
- 使用shell定製awr指令碼指令碼
- 使用shell定製addm指令碼指令碼
- DBA日常維護SQL指令碼SQL指令碼
- PostgreSQL 定製psql提示符SQL
- 使用Linux系統的Shell指令碼維護Oracle(轉)Linux指令碼Oracle
- 用 WebSphere CloudBurst 實現定製: 使用指令碼包定製超級模式WebCloud指令碼模式
- MS SQL 日常維護管理常用指令碼(下)SQL指令碼
- MS SQL 日常維護管理常用指令碼(上)SQL指令碼
- rac維護基本命令
- 通過shell定製ash指令碼指令碼
- 透過shell定製ash指令碼指令碼
- 史上最全近百條Oracle DBA日常維護SQL指令碼指令OracleSQL指令碼
- DBA日常維護SQL指令碼_自己編寫的SQL指令碼
- Oracle RAC日常基本維護命令Oracle
- 使用jmeter錄製web指令碼JMeterWeb指令碼
- 【PG_PSQL】PSQL使用測試SQL
- 使用dbstart指令碼的設定指令碼
- 【轉】Oracle RAC日常基本維護命令Oracle
- QTP測試指令碼的維護 - 使用Update執行模式和Maintenance執行模式QT指令碼模式AINaN
- oracle維護管理指令(不斷更新)Oracle
- Oracle RAC系列之:ASM基本操作維護OracleASM
- crontab定時任務維護
- 使用Go語言製作二維碼Go
- oracle 複製中設定主體站點指令碼Oracle指令碼
- Oracle 12c CDB&PDB 基本維護Oracle
- shell指令碼——基本運算子指令碼
- bat指令碼的基本操作BAT指令碼
- Jmeter —— 錄製指令碼JMeter指令碼
- 一些常用的SQL(05版以上)資料庫維護指令碼SQL資料庫指令碼
- windows系統下postgresql的自動備份及維護的指令碼WindowsSQL指令碼
- psql 中使用 gexecSQL
- 陣列-在Shell指令碼中的基本使用介紹陣列指令碼
- Oracle密碼檔案的建立、使用和維護 (zt)Oracle密碼
- Oracle RAC系列之:ASM基本操作維護(經典)OracleASM
- shell-----構建基本指令碼指令碼
- Jmeter之錄製指令碼JMeter指令碼
- Jmeter代理錄製指令碼JMeter指令碼