PostgreSQL開發與實戰（2）常用命令

作者：太陽

1、連庫相關

#連庫
$ psql -h <hostname or ip> -p <端口> [數據庫名稱] [用戶名稱]
#連庫并執行命令
$ psql -h <hostname or ip> -p <端口> -d [數據庫名稱]  -U <用戶名> -c "運行一個命令;"

備注：
可以將連接命令中的參數在環境變量中指定；
比如環境變量中配置如下，那么執行psql等同于執行psql -h 192.168.56.11 -p 5432 testdb postgres。

export PGDATABASE=testdb
export PGHOST=192.168.56.11
export PGPORT=5432
export PGUSER=postgres

2、一些查看命令

#查看命令語法的幫助命令
\h
#查看有哪些庫
\l
#進入指定數據庫
\c $db_name
#查看當前庫下的所有pattern(表、視圖、索引、序列)信息
\d
#查看當前庫下的pattern(表、視圖、索引、序列)信息，并輸出詳細內容
\d +
#查看當前庫下某張表的結構定義或某個表的索引信息
\d $table_name/$index_name
#只查看當前庫下表的信息
\dt
#只查看當前庫下的索引信息
\di
#只查看當前庫下的序列信息
\ds
#只查看當前庫下的視圖信息
\dv
#只查看當前庫下的函數信息
\df
#列出當前庫下所有shcema
\dn
#列出所有的表空間
\db
#列出所有的用戶/角色的高級權限
\du或\dg
#列出表/視圖/序列及訪問它們的相關權限
\dp或\z
#列出默認權限
\ddp

3、修改庫名

1.先關閉該庫下的連接會話：

# SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity WHERE datname='t1' AND pid<>pg_backend_pid();pg_terminate_backend 
----------------------t
(1 row)

說明：
pg_terminate_backend：用來終止與數據庫的連接的進程id的函數。
pg_stat_activity：是一個系統表，用于存儲服務進程的屬性和狀態。
pg_backend_pid()：是一個系統函數，獲取附加到當前會話的服務器進程的ID。

2.再用alter修改庫名：

# alter database t1 rename to t2;
ALTER DATABASE

4、復制數據庫到相同的實例

# 創建targetdb庫并將sourcedb庫中的數據復制到targetdb
CREATE DATABASE targetdb WITH TEMPLATE sourcedb;

5、schema相關

#查看庫下的schema：SELECT * FROM information_schema.schemata;或者\dn
#創建schema：create schema $schema_name;
#創建schema并指定owner用戶create schema $schame_name authorization $user_name;
#修改schema名稱或屬主alter schema $old_name rename to $new_name;alter schema $schema_name owner to $new_owner;
#查看當前所在的schema:show search_path;
#切換schema：set search_path to $schema_name;
#刪除一個空的schema(其中所有對象已被刪除)：drop schema $schema_name;
#刪除schema及其中包含的所有對象：drop schema $schema_name cascade;

6、查看活躍會話

#查看活躍會話
select * from pg_stat_activity where state<>'idle' ;#查看包含在事物內的會話
select * from pg_stat_activity where state like '%idle%transaction%';#查看耗時1s以上的活躍會話
select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '1 s' order by query_start ;

pg_stat_activity視圖各字段含義：

字段	描述
datid	數據庫OID。
datname	數據庫名稱。
procpid	后端進程的進程ID。（說明：只有4.3版本支持procpid字段。）
pid	后端進程的進程ID。（說明：只有6.0版本支持pid字段。）
sess_id	會話ID。
usesysid	用戶OID。
usename	用戶名。
current_query	當前正在執行的查詢。默認情況下，查詢文本最多顯示1024個字符，超出部分會被截斷，如需顯示更多字符，可以通過參數track_activity_query_size配置。（說明：只有4.3版本支持current_query字段。）
query	最近查詢的文本。如果state為active，顯示當前正在執行的查詢。在其他狀態下，顯示上一個執行的查詢。默認情況下，查詢文本最多顯示1024個字符，超出部分會被截斷，如需顯示更多字符，可以通過參數track_activity_query_size配置。（說明：只有6.0版本支持query字段。）
waiting	如果當前SQL在鎖等待，值為True，否則為False。
query_start	當前活動查詢開始執行的時間。如果state不是active，顯示上一個查詢的開始時間。
backend_start	當前后端進程的開始時間。
backend_xid	后端進程當前的事務ID。
backend_xmin	后端的xmin范圍。
client_addr	客戶端的IP地址。如果client_addr為空，表示客戶端通過服務器上的Unix套接字連接，或者表示進程是內部進程（例如AUTOVACUUM）。
client_port	客戶端和后端通信的TCP端口號。如果使用Unix套接字，值為-1。
client_hostname	客戶端主機名，通過client_addr的反向DNS查找報告。
application_name	客戶端應用名。
xact_start	當前事務的啟動時間。如果沒有活動事務，值為空。如果當前查詢是第一個事務，值與query_start的值相同。
waiting_reason	當前執行等待的原因，可能是等鎖或者等待節點間數據的復制。
state	后端的當前狀態，取值范圍：active，idle，idle in transaction，idle in transaction (aborted)，fastpath function call，disabled。（說明：只有6.0版本支持state字段。）
state_change	上次state狀態切換的時間。（說明：只有6.0版本支持state_change字段。）
rsgid	資源組OID。
rsgname	資源組名稱。
rsgqueueduration	對于排隊查詢，查詢排隊的總時間。

7、kill會話

##kill會話
select pg_terminate_backend($pid);##只取消當前某一個進程的查詢操作，但不能釋放數據庫連接
select pg_cancel_backend($pid);

8、查看庫表大小

##1.查看各庫大小：
select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database; ##2.查看當前庫下各schema表數量
select schemaname,count(*) from pg_stat_user_tables group by schemaname;##3.查看當前庫下top 20表或去掉limit 20查看所有表大小
select relname,schemaname, pg_size_pretty(pg_total_relation_size(relid)) from pg_stat_user_tables order by pg_total_relation_size(relid) desc limit 20;##4.查看某張表總大小(表數據+索引數據)：
select pg_size_pretty(pg_total_relation_size('xxx')); ##5.查看表數據大小，不包含索引：
select pg_size_pretty(pg_table_size('xxx')); ##6.查看表的索引大小：
select pg_size_pretty(pg_indexes_size('xxx'));

9、表字段變更

##1.增加字段：
alter table tbl_name add column col_name [col definer] ;
##2.刪除字段：
alter table tbl_name drop column col_name ;
##3.增加約束：
alter table tbl_name add [constraint];eg:alter table tbl_name alter column col_name set not null; （非空約束）
##4.刪除約束:
alter table tbl_name drop constraint_name; ##（約束名\d+ tbl_name查看）        
##5.修改字段數據類型：
alter table tbl_name alter column col_name [col definer];eg:alter table tai alter column name type varchar(500);
##6.重命名字段名稱：
alter table tbl_name rename column col_name to col_name_new;

10、pg_ctl

##1.初始化數據庫實例
pg_ctl init[db] [-s] [-D datadir] [-o options]##2.啟動、關閉數據庫實例等
pg_ctl start [-w] [-s] [-D datadir] [-l filename] [-o options] [-p path]
pg_ctl stop [-W] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ]
pg_ctl restart [-w] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ] [-o options]
pg_ctl status [-D datadir]##3.重新加載配置文件(pg_hba.conf、postgresql.conf等)
pg_ctl reload [-s] [-D datadir]

pg_ctl后綴參數含義，詳見官方文檔：http://postgres.cn/docs/13/app-pg-ctl.html

11、查看一些信息

##1.查看當前數據庫版本信息
select version();
##2.查看數據庫的啟動時間
select pg_postmaster_start_time();
##3.查看最后load配置文件的時間
select pg_conf_load_time();
備注：使用$pg_ctl reload會改變配置的裝載時間
##4.顯示當前數據庫時區
show timezone;
##5.查看當前用戶名
select user;或elect current_user;
##6.查看session用戶
select session_user;
##備注：session_user查看的是連接數據庫的原始用戶，如果中途用set role改變用戶角色，用session_user查看的還是原始用戶，用user查看的是改變后的用戶
##7.查看當前連接的數據庫名稱
select current_catalog;或select current_database();
##8.查看當前session所在客戶端的IP及端口
select inet_client_addr(),inet_client_port();
##9.查看當前數據庫服務器的IP地址及端口
select inet_server_addr(),inet_server_port();
##10.查看當前session的后臺服務進程的PID
select pg_backend_pid();
11.查看當前參數數值
show xxx;或select current_setting('xxx');
12.修改當前session的參數配置
set xxx to 'xxx';或select set_config('xxx','xxx',false);
13.查看當前正在寫的WAL文件
select pg_xlogfile_nale(pg_current_xlog_location());
14.查看當前WAL文件的buffer還有多少字節沒有寫入磁盤
select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());
15.查看數據庫實例是否正在做基礎備份
select pg_is_in_backup(),pg_backup_start_time();
16.查看當前數據庫實例處于Hot Standby狀態還是正常數據庫狀態
select pg_is_in_recovery();
備注：如果結果為真，則為Hot Standby狀態
17.查看表對應的數據文件
select pg_relation_filepath('xxx');