熱衷于分享各種干貨知識，大家有想看或者想學的可以評論區留言，秉承著“開源知識來源于互聯網，回歸于互聯網”的理念，分享一些日常工作中能用到或者頻率比較的內容，希望大家能夠喜歡，并提出寶貴地意見，我們一起提升，守住自己的飯碗。

1、查看版本

postgres=# show server_version;

postgres=# select version();

postgres=#SELECT * FROM pg_catalog.pg_settings WHERE name = 'server_version'; #信息更全

2、查看有多少個數據庫及數據庫所屬編碼

postgres=# \l

3、查看字符集

postgres=# \encoding

4、查看數據庫的連接數量

postgres=# select datid,datname,pid,usename,state,client_addr,query from pg_stat_activity; #顯示更詳細

postgres=# SELECT COUNT(*) FROM pg_stat_activity; #匯總出數量

postgres=#show max_connections; #查看最大連接數

5、查詢當前的用戶

postgres=#select * from current_user;

postgres=# SELECT SESSION_USER;

postgres=# select * from pg_user; #查詢所有的用戶信息

6、查看數據庫運行的時間

postgres=# select pg_postmaster_start_time();

postgres=#SELECT age(now(), pg_postmaster_start_time()) AS uptime;#匯總出時間

7、查看當前用戶有多少個表

postgres=#SELECT tablename

FROM pg_catalog.pg_tables

WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

postgres=#SELECT tablename

FROM pg_catalog.pg_tables

WHERE schemaname = 'public'; #查看特定模式的表

8、表空間管理

postgres=# \db 

postgres=# SELECT pg_tablespace.spcname,

       pg_size_pretty(pg_tablespace_size(pg_tablespace.oid)) AS size

FROM pg_tablespace;

postgres=# select pg_size_pretty(pg_tablespace_size('pg_global'));#查詢表空間大小

postgres=# create tablespace test owner test location '/pgdb/data/test'; #創建表空間，/pgdb/data/test路徑得提前建好

postgres=# drop tablespace test; #刪除表空間

9、查看所有的schema

postgres=# select * from information_schema.schemata; #信息更全

postgres=# \dn

10、查看數據庫大小

postgres=# select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS total_size from pg_database;

postgres=#SELECT      table_schema || '.' || table_name AS table_full_name,      pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size  FROM information_schema.tables  ORDER BY      pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC #顯示用戶

11、查看是否開啟歸檔日志

postgres=# show archive_mode;

postgres=# show archive_command ;

12、查看鎖信息

postgres=# SELECT * FROM pg_locks;

13、查看表結構

postgres=# \d students

postgres=#SELECT 

    column_name,

    data_type,

    character_maximum_length,

    is_nullable,

    column_default 

FROM

    information_schema.COLUMNS 

WHERE

    TABLE_NAME = 'students';

14、用戶管理

postgres=# create user test1  with password '123456';#創建用戶

postgres=# create user superwith password '123456' superuser;#創建超級管理員

postgres=# create schema test1  authorization  test1;#創建和用戶同名的 schema

postgres=# grant all on schema test1  to test1;#將 schema 的所有權限給用戶

postgres=# grant usage on schema test1  to test;#將schema 的所有權限給另外一個用戶

postgres=# ALTER TABLE my_table SET SCHEMA test1;修改schema

postgres=# alter user superwith password '123123';#\password  super也可以進行修改postgres=# alter user super  with nologin; #禁止用戶登錄

postgres=# drop owned bysuper;#刪除用戶

postgres=#DROP USER test;#刪除用戶

15、角色管理

postgres=# \du

postgres=# create role role2;#創建角色

postgres=#drop role role1; #刪除角色

16、數據庫管理

postgres=#CREATE DATABASE test; #創建

postgres=#DROP DATABASE test; #刪除

postgres=# \c test #切換數據庫

postgres=# \ds #查看當前數據庫序列

17、 表管理

postgres=#ANALYZE test; #分析表

postgres=#alter table test add c3 int; #新增字段

postgres=#alter table test drop c3 ;  #刪除字段

postgres=#alter table test alter column c3 type varchar(10); #更改字段

postgres=#\x #列模式顯示每個字段

18、索引管理

postgres=#REINDEX TABLE tablename;#重建索引

postgres=# \di #列出所有索引

postgres=#SELECT t.relname AS table_name,         i.relname AS index_name,         string_agg(a.attname,         ',') AS column_nameFROM pg_class t, pg_class i, pg_index ix, pg_attribute aWHERE t.oid = ix.indrelid        AND i.oid = ix.indexrelid        AND a.attrelid = t.oid        AND a.attnum = ANY(ix.indkey)        AND t.relkind = 'r'        AND t.relname NOT LIKE 'pg_%'GROUP BY  t.relname, i.relnameORDER BY  t.relname, i.relname;

18、檢查表的大小

postgres=#SELECT pg_size_pretty(pg_total_relation_size('tablename'));

19、數據庫啟停管理

[postgres@localhost ~]$ pg_ctl stop #停止[postgres@localhost ~]$ pg_ctl start #啟動

20、數據庫登錄

[postgres@localhost ~]$psql #未開啟密碼登錄驗證可以用此登錄

[postgres@localhost ~]$ psql -U postgres -p 5785 -d postgres -h 192.168.59.138

21、查看所有的 function

postgres=# \df

postgres=#SELECT proname, proargtypes, prosrc

FROM pg_proc;

22、查看數據庫擴展

postgres=# \dx

23、切換工作路徑

postgres=# \cd /pgdb #執行腳本時必需

24、會話管理

postgres=#\conninfo #查看當前會話連接信息

postgres=#\x #因為表字段非常多，通過終端查詢會重疊在一塊，比較難看清，需要開啟擴展顯示，和 MySQL \G 類似

postgres=#select*from pg_stat_activity where state !='idle';

postgres=# SELECT datname, usename, pid, client_hostname, backend_start, query_start, wait_event_type, STATE, backend_type FROM pg_stat_activity;#查看會話的狀態

postgres=#select now(),pid,client_addr,now()-query_start exec_time,query from pg_stat_activity where state ='active'and pid <> pg_backend_pid()and query_start < now()-'60 s'::interval order by query_start;#查詢超過60S的會話

test=#select pg_backend_pid(); #查看當前會話ID

25、 顯示 SQL 執行時間

postgres=#\timing

26、將查詢結果輸出到操作系統（Oracle spool 功能）

postgres=# \o test.txt select * from demo1; \o

27、執行 SQL 腳本

postgres=#\i test.sql

28、序列管理

postgres=# CREATE SEQUENCE snc_seq INCREMENT BY 1 START WITH 1 NO MAXvalue NO CYCLE CACHE 10; #創建序列postgres=#  select * from snc_seq ; #查看序列postgres=# select currval(' snc_seq '); #查看序列當前值postgres=# select nextval('snc_seq'); #獲取序列下一個值

29、與序列有關的表

postgres=# select * from pg_sequence;postgres=# select * from pg_sequences;postgres=# select * from information_schema.sequences;

30、查看 blocker

postgres=# SELECT datname, pid, usename, client_hostname, wait_event, STATE, query FROM pg_stat_activity WHERE pid IN (SELECT pid FROM pg_locks WHERE GRANTED<>'f' AND transactionid = (SELECT transactionid FROM pg_locks WHERE GRANTED= 'f'));

31、 查看膨脹高的 top 10 表

postgres=#SELECT relname AS TABLE_NAME, pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size, n_dead_tup, n_live_tup,(n_dead_tup * 100 / (n_live_tup + n_dead_tup))AS dead_tup_ratio FROM pg_stat_user_tables WHERE n_dead_tup<>0 LIMIT 10;

32、查看長事務

postgres=# SELECT extract(epoch FROM (clock_timestamp() - xact_start)) AS longtrans,extract(epoch FROM (clock_timestamp() - query_start)) AS longquery FROM pg_stat_activity WHERE 1=1 AND STATE <> 'idle' ;

33、當前數據庫TOP10大小的表

postgres=#SELECT relname, pg_relation_size('public."' || relname || '"')/1024/1024 AS MB, relkind FROM pg_class WHERE relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname NOT IN ('pg_toast', 'pg_temp_1', 'pg_toast_temp_1', 'pg_catalog', 'information_schema')) AND relkind ='r' ORDER BY 2 DESC LIMIT 10 ;

34、查看當前數據庫top10對象

postgres=#SELECT relname, pg_relation_size('public."' || relname || '"')/1024/1024 AS MB, relkind FROM pg_class WHERE relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname NOT IN ('pg_toast', 'pg_temp_1', 'pg_toast_temp_1', 'pg_catalog', 'information_schema')) ORDER BY 2 DESC LIMIT 10;

35、查看統計信息

postgres=#analyze students; #收集統計信息（分析表）

postgres=#vacuum students; # vacuum 表

postgres=#vacuum;# vacuum 數據庫

postgres=#vacuum full; # vacuum 回收空間,會鎖表

postgres=#SELECT schemaname,relname, n_live_tup, n_dead_tup, n_mod_since_analyze, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname=’students’; # 查看統計信息

36、查看 SQL 執行計劃

postgres=#explain select * from students;postgres=#explain (analyze,verbose,costs,buffers,timing) select * from students;注意，analyze 會執行 SQL 。

37、查找PG堵塞

postgres=#SELECT blocked_locks.pid AS blocked_pid,         blocked_activity.usename AS blocked_user,         blocking_locks.pid AS blocking_pid,         blocking_activity.usename AS blocking_user,         blocked_activity.query AS blocked_statement,         blocking_activity.query AS current_statement_in_blocking_processFROM pg_catalog.pg_locks blocked_locksJOIN pg_catalog.pg_stat_activity blocked_activity    ON blocked_activity.pid = blocked_locks.pidJOIN pg_catalog.pg_locks blocking_locks    ON blocking_locks.locktype = blocked_locks.locktype        AND blocking_locks.database IS NOT DISTINCTFROM blocked_locks.database        AND blocking_locks.relation IS NOT DISTINCTFROM blocked_locks.relation        AND blocking_locks.page IS NOT DISTINCTFROM blocked_locks.page        AND blocking_locks.tuple IS NOT DISTINCTFROM blocked_locks.tuple        AND blocking_locks.virtualxid IS NOT DISTINCTFROM blocked_locks.virtualxid        AND blocking_locks.transactionid IS NOT DISTINCTFROM blocked_locks.transactionid        AND blocking_locks.classid IS NOT DISTINCTFROM blocked_locks.classid        AND blocking_locks.objid IS NOT DISTINCTFROM blocked_locks.objid        AND blocking_locks.objsubid IS NOT DISTINCTFROM blocked_locks.objsubid        AND blocking_locks.pid != blocked_locks.pidJOIN pg_catalog.pg_stat_activity blocking_activity    ON blocking_activity.pid = blocking_locks.pidWHERE NOT blocked_locks.granted;

38、查看當前執行的所有SQL語句

postgres=#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;

39、殺掉某個數據庫的所有連接

postgres=#SELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_stat_activity WHERE datname='數據庫名稱' AND pid<>pg_backend_pid();

40、查詢延遲

postgres=#select application_name,client_addr,client_port,write_lag,replay_lag,sync_state from pg_stat_replication ;

41、查看占用空間TOP10的表

postgres=#SELECT schemaname    ,tablename    ,pg_size_pretty(pg_total_relation_size((quote_ident(schemaname) || '.' || quote_ident(tablename))::regclass))FROM pg_tablesORDER BY pg_total_relation_size((quote_ident(schemaname) || '.' || quote_ident(tablename))::regclass) DESC limit 10;

42、查詢更新和刪除記錄條數top 10的表

postgres=#SELECT schemaname    ,relname    ,n_tup_upd    ,n_tup_del    ,round(n_tup_hot_upd / (            CASE                 WHEN n_tup_upd = 0                    THEN 1.0                ELSE n_tup_upd::NUMERIC                END            ), 4)FROM pg_stat_all_tablesORDER BY n_tup_upd + n_tup_del DESC limit 10;

43、查詢插入記錄條數top 10的表

postgres=#SELECT schemaname    ,relname    ,n_tup_insFROM pg_stat_all_tablesORDER BY n_tup_ins DESC limit 10;

44、查看歸檔任務狀態

postgres=#select * from pg_stat_get_archiver();

45、查看數據庫的啟動時間

postgres=# select pg_postmaster_start_time();

46、查詢數據庫是否為備庫

postgres=# select pg_is_in_recovery();

47、查看表所在的數據文件

test=# select pg_relation_filepath('students');

48、查詢當前的最老事務距離當前時間、距離當前事務數, 說明膨脹空間大小, 越大可能導致越多膨脹垃圾

test=#SELECT * FROM pg_prepared_xacts;

49、長事務、prepared statement

test=#WITH aAS (    SELECT min(TRANSACTION::TEXT::int8) m    FROM pg_prepared_xacts    )    ,bAS (    SELECT txid_snapshot_xmin(txid_current_snapshot())::TEXT::int8 AS m    )    ,cAS (    SELECT min(least(backend_xid::TEXT::int8, backend_xmin::TEXT::int8)) m    FROM pg_stat_activity    )    ,dAS (    SELECT datname        ,usename        ,pid        ,query_start        ,xact_start        ,now()        ,wait_event        ,query    FROM pg_stat_activity    WHERE backend_xid IS NOT NULL        OR backend_xmin IS NOT NULL    ORDER BY least(backend_xid::TEXT::int8, backend_xmin::TEXT::int8) limit 1    )    ,eAS (    SELECT *    FROM pg_prepared_xacts    ORDER BY TRANSACTION::TEXT::int8 limit 1    )SELECT b.m - least(a.m, c.m)    ,d.*    ,e.*FROM a    ,b    ,c    ,dLEFT JOIN e ON (1 = 1);

50、查看WAL的空間占用大小

postgres=# select pg_size_pretty(sum(size)) from pg_ls_waldir();

文中的概念來源于互聯網，如有侵權，請聯系我刪除。

歡迎關注公眾號：小周的數據庫進階之路，一起交流數據庫、中間件和云計算等技術。如果覺得讀完本文有收獲，可以轉發給其他朋友，大家一起學習進步！感興趣的朋友可以加我微信，拉您進群與業界的大佬們一起交流學習。

原文來源：https://mp.weixin.qq.com/s/uPF2o8w4p1_yRgZp6UAozg