PostgreSQL里面的一些命令小結
近兩天總結了下PostgreSQL的基本操作命令，對PostgreSQL也有了一個基本的認識。
PostgreSQL的功能還是很豐富的，有序列，支持db link，基本Oracle里有的概念它這里也有，目前來看不支持package。
風格和Oracle也類似，沒有MySQL中快捷方便的show create table 這樣的語句。
從我的使用習慣來說，我基本關注以下的一些方面。
-
查看數據庫的配置
-
查看用戶信息
-
查看會話連接信息
-
show tables的類似方法
-
用戶的權限查看
-
建表語句
-
表空間信息
-
對象存儲信息
-
查看鎖的信息
-
查看數據庫參數
-
顯示數據庫的運行狀態
-
查看數據字典的信息
-
查看索引的信息
-
查看執行計劃
-
查看存儲過程
-
存儲過程的調度執行
-
事務隔離級別
1.查看數據庫的配置
可以直接使用\l 選項列出所有的數據庫來，字符集，基本的配置都一目了然，有點Oracle 12c中的show pdbs的感覺。
從進程情況來看，PG是多進程多線程的架構設計。
如果查看當前數據庫，可以使用current_database()。
- postgres=# select current_database();
- current_database
- ------------------
- postgres
2.查看用戶信息
可以使用\dn來得到schema的相關信息，在PG里面的schema和user還是有一些差別，在其他數據庫schema基本就是user了。
- postgres-# \dn
- List of schemas
- Name | Owner
- --------+----------
- public | postgres
我們創建一個schema,然后使用\dn來查看。
- postgres=# create schema jeanron100;
- CREATE SCHEMA
- postgres=# \dn
- List of schemas
- Name | Owner
- ------------+----------
- jeanron100 | postgres
- public | postgres
或者使用數據字典pg_authid來查看。
- postgres=# select *from pg_authid;
關于schema的概念，我們可以創建一個表test,
- postgres=# create table test(id int);
- CREATE TABLE
可以看到這個是一個public的schema
- postgres=# \d
- List of relations
- Schema | Name | Type | Owner
- --------+------+-------+----------
- public | test | table | postgres
如果使用\d來查看字段信息，結果如下：
- postgres=# \d test
- Table "public.test"
- Column | Type | Modifiers
- --------+---------+-----------
- id | integer |
還可以使用pg_users來查看，比如我創建了一個用戶replica，就會有相應的配置。
- select *from pg_user;
- usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
- ----------+----------+-------------+----------+---------+--------------+----------+----------
- postgres | 10 | t | t | t | t | ******** | |
- replica | 16384 | f | f | t | f | ******** | |
查看當前的schema信息，可以使用current_schema()
3.查看會話連接信息
如果查看PG中的會話信息，可以使用select * from pg_stat_activity;
如果新增了一個連接，開啟了一個會話，在服務端是會有一個影子進程存在的。可以根據pid找到對應的會話。包括執行的SQL都可以看到，如果有多個會話，就是多條記錄。
- postgres=# select * from pg_stat_activity;
- -[ RECORD 1 ]----+--------------------------------
- datid | 13241
- datname | postgres
- pid | 20644
- usesysid | 10
- usename | postgres
- application_name | psql
- client_addr |
- client_hostname |
- client_port | -1
- backend_start | 2018-03-25 05:38:16.988057+08
- xact_start | 2018-03-25 05:48:08.113649+08
- query_start | 2018-03-25 05:48:08.113649+08
- state_change | 2018-03-25 05:48:08.113653+08
- waiting | f
- state | active
- backend_xid |
- backend_xmin | 1753
- query | select * from pg_stat_activity;
4.show tables的類似方法
PG里面暫時沒有找到show tables這種的快捷方式，目前發現有兩類方式。
一種是通過數據字典pg_tables來查看，相當于Oracle里面的all_tables
或者是使用information_schema里面的tables來查看。
- postgres=# select *from information_schema.tables;
- postgres=# select *from pg_tables;
PG里面的information_schema比較特別，在數據庫中直接\l無法看到，但是確確實實存在，著數據字典風格和MySQL很相似。
5.用戶的權限查看
查看權限可以使用\dp來完成，或者等價的命令\z來實現。
- postgres=# \dp
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
- --------+------+-------+-------------------+-------------------+----------
- public | test | table | | |
6.建表語句
建表語句，目前還沒有發現show create table這種快捷的方式，不過可以通過pg_dump或者根據數據字典的信息來拼接了。
7.表空間信息
表空間的部分相對比較清晰，可以直接使用\db來完成。
- postgres=# \db
- List of tablespaces
- Name | Owner | Location
- ------------+----------+----------
- pg_default | postgres |
- pg_global | postgres |
或者使用pg_tablespace
- postgres=# select *from pg_tablespace;
- spcname | spcowner | spcacl | spcoptions
- ------------+----------+--------+------------
- pg_default | 10 | |
- pg_global | 10 | |
8.對象存儲信息
這部分信息可以參考pg_tables，還有一些更細節的
更多的細節還有待求證和發現。
9.查看鎖的信息
查看鎖的信息可以使用pg_locks來得到。
- postgres=# select *from pg_locks;
- -[ RECORD 1 ]------+----------------
- locktype | relation
- database | 13241
- relation | 11673
- page |
- tuple |
- virtualxid |
- transactionid |
- classid |
- objid |
- objsubid |
- virtualtransaction | 4/81
- pid | 20644
- mode | AccessShareLock
- granted | t
- fastpath | t
10.查看數據庫參數
這部分的功能不是很理解，因為沒有找到很便捷的方式。
比如查看緩存的設置
- postgres=# show shared_buffers;
- -[ RECORD 1 ]--+------
- shared_buffers | 128MB
或者根據參數文件postgresql.conf來查看。
11.顯示數據庫的運行狀態
這個信息毫無疑問，建議還是從pg_stats_activity來查看。
12.查看數據字典的信息
這應該是本小節的重點，通過查看視圖可以看到，有100多個視圖。
- postgres=# select count(*)from pg_views;
- -[ RECORD 1 ]
- count | 112
還可以使用information_schema中的信息來補充。
13.查看索引的信息
查看索引的信息，可以使用\di來完成，非常快捷。
14.查看執行計劃
查看執行計劃一般可以根據explain來得到，但是還有幾類方法，對結果做格式化處理，比如轉化為json或者xml的格式等。
- postgres=# explain select *from test;
- -[ RECORD 1 ]------------------------------------------------------
- QUERY PLAN | Seq Scan on test (cost=0.00..35.50 rows=2550 width=4)
- 得到json格式的執行計劃。
- postgres=# explain(format json) select *from test;
- -[ RECORD 1 ]------------------------------
- QUERY PLAN | [ +
- | { +
- | "Plan": { +
- | "Node Type": "Seq Scan",+
- | "Relation Name": "test",+
- | "Alias": "test", +
- | "Startup Cost": 0.00, +
- | "Total Cost": 35.50, +
- | "Plan Rows": 2550, +
- | "Plan Width": 4 +
- | } +
- | } +
- | ]
或者做一些分析，能夠得到更細節的執行信息。
- postgres=# explain analyze select *from test;
- QUERY PLAN
- --------------------------------------------------------------------------------------------------
- Seq Scan on test (cost=0.00..35.50 rows=2550 width=4) (actual time=0.001..0.001 rows=0 loops=1)
- Planning time: 0.018 ms
- Execution time: 0.009 ms
15.查看存儲過程
查看存儲過程就是比較單薄的。可以直接使用pg_proc來得到詳細的信息。
pg_proc
16.存儲過程的調度執行
目前沒有看到很直接的方式，這部分感覺還不夠強大。
17.事務隔離級別
根據公司現狀和業務規模的不斷擴大，其實技術上也是不斷地改進和積累，事務方面的處理也是如此，等規模達到了一定的量級，這部分的要求就會很明確。所以很多開發同學對于鎖機制都很感興趣。
查看事務隔離級別的兩種SQL語句。
- postgres=# show default_transaction_isolation;
- default_transaction_isolation
- -------------------------------
- read committed
查看當前的事務隔離級別設置。
- postgres=# show transaction_isolation;
- transaction_isolation
- -----------------------
- read committed
