postgresql dba常用sql查詢語句

檢視幫助命令
DB=# help  --總的幫助
DB=# \h    --SQL commands級的幫助
DB=# \?    --psql commands級的幫助
\dn[S+] [PATTERN]，其中[PATTERN]表示可以使用正規表示式，比如\dns f*表示列出所有f開頭的schema

按列顯示，類似mysql的\G
DB=# \x
Expanded display is on.

顯示斜槓\後面的命令對應的語句，ECHO_HIDDEN必須大寫
\set ECHO_HIDDEN on
或
psql -E


檢視DB安裝目錄(最好root使用者執行)
find / -name initdb

檢視有多少DB例項在執行(最好root使用者執行)
find / -name postgresql.conf

檢視DB版本
cat  $PGDATA/PG_VERSION
psql --version
DB=# show server_version;
DB=# select version();

檢視DB例項執行狀態
pg_ctl status

檢視所有資料庫
psql -l              --檢視5432埠下面有多少個DB
psql -p XX -l        --檢視XX埠下面有多少個DB
DB=# \l
DB=# select * from pg_database;

建立資料庫
createdb database_name
DB=# \h create database  --建立資料庫的幫助命令
DB=#  create database database_name

進入或切換到某個資料庫
psql –d dbname
DB=# \c dbname

檢視當前資料庫
DB=# \c
DB=# select current_database();

postgresql資料庫啟動時間
select pg_postmaster_start_time();

查詢當前客戶端的埠號
select inet_client_port();

檢視與當前會話相關聯的伺服器程式ID
select pg_backend_pid();

檢視配置檔案最後一次載入時間
select pg_conf_load_time();


檢視資料庫檔案目錄
DB=# show data_directory;
cat $PGDATA/postgresql.conf |grep data_directory
cat /etc/init.d/postgresql|grep PGDATA=
lsof |grep 5432得出第二列的PID號再ps –ef|grep PID

檢視每個資料庫對應的目錄
DB=# select oid, datname from pg_database;--每個database會在base目錄下有一個子目錄，base目錄裡的每一個數字目錄對於一個database的 oid
再到檔案目錄比如ll /pgdata/data/base/，就可以看到和上面oid對應的目錄名稱
--drop database後，base目錄下面這個資料庫的子目錄也隨即刪除了


檢視錶空間(表空間是例項級別的，任意一個資料庫下看到的結果都是一樣，一個表空間可以讓多個資料庫使用，而一個資料庫可以使用多個表空間。\db還能看到表空間的預設存放目錄)
\db
select * from pg_tablespace;

檢視語言
select * from pg_language;


查詢所有schema，必須到指定的資料庫下執行
select * from information_schema.schemata;--普通使用者只能看到使用者自己有許可權登入的schema的資訊
SELECT nspname FROM pg_namespace;
\dnS


檢視錶名
DB=# \dt  --使用預設的search_path引數，只看到public的表名，如果有幾個schema，每個schema的表名相同，也只能看到一個schema下的表名，看誰在引數值的前面。如果每個schema下的表名都不一樣，則所有表名都可以看到
DB=# select * from  pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;--普通使用者也能查詢到整個資料庫下的所有表,標準語句
DB=# select table_catalog,table_schema,table_name,table_type,is_insertable_into,is_typed from information_schema.tables where table_schema<>'information_schema' and table_schema<>'pg_catalog';--superuser可以查詢到所有表，普通使用者只能查詢到自己owner的表，但是查詢結果居然有檢視在裡面，所以不是標準語句
DB=# SELECT n.nspname as "Schema",c.relname as "Name",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 ='r' AND n.nspname in('public',current_user) ORDER BY 2,1;


檢視錶結構
DB=# \d tablename  --表名前面加上schema名稱就可以不同schema下表的表結構
DB=# select * from information_schema.columns where table_schema='public' and table_name='XX';

增加多個欄位
DB=# alter table tablename add column_name1 data_type,add column_name2 data_type,add column_name3 data_type

檢視錶對應的目錄
DB=# select pg_relation_filepath('schema_name.table_name')  --需要到指定的資料庫下執行
這個檔案的時間表示的是表的checkpoint時間


檢視索引(表名前面沒有schema字首)
DB=# \di  --只能看到public這個schema下的索引
DB=# select * from pg_indexes where tablename='tbname'; --要到當前資料庫下執行  
DB=# select * from pg_statio_all_indexes where relname='tbname';--要到當前資料庫下執行  

檢視檢視
DB=# \dv
DB=# select * from pg_views where schemaname = 'public';
DB=# select * from information_schema.views where table_schema = 'public';


檢視觸發器
DB=# select * from information_schema.triggers;

檢視序列
DB=# select * from information_schema.sequences where sequence_schema = 'public';

檢視約束
DB=# select * from pg_constraint where contype = 'p'
DB=# select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 'cc';











檢視XX資料庫的大小
SELECT pg_size_pretty(pg_database_size('XX')) As fulldbsize;


檢視所有資料庫的大小
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;

檢視各資料庫資料建立時間：
select datname,(pg_stat_file(format('%s/%s/PG_VERSION',case when spcname='pg_default' then 'base' else 'pg_tblspc/'||t2.oid||'/PG_11_201804061/' end, t1.oid))).* from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid;

按佔空間大小，順序檢視所有表的大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='public' group by 1;

按佔空間大小，順序檢視索引大小
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||indexname))/1024/1024) "Size_MB" from pg_indexes where schemaname='public' group by 1;

按佔空間大小，順序檢視錶包含索引的大小
select indexrelname, pg_size_pretty(pg_total_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;

檢視各個表空間的大小
select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace;




檢視引數檔案
DB=# show config_file;
DB=# show hba_file;
DB=# show ident_file;

檢視當前會話的引數值
DB=# show all;

檢視引數值
select * from pg_file_settings

檢視某個引數值,比如引數work_mem
DB=# show work_mem

修改某個引數值,比如引數work_mem
DB=# alter system set work_mem='8MB'
--使用alter system命令將修改postgresql.auto.conf檔案，而不是postgresql.conf，這樣可以很好的保護postgresql.conf檔案，加入你使用很多alter system命令後搞的一團糟，那麼你只需要刪除postgresql.auto.conf，再執行pg_ctl reload載入postgresql.conf檔案即可實現引數的重新載入。


檢視是否歸檔
DB=# show archive_mode;

檢視WAL歸檔資訊,pg_stat_archiver來源於pg_stat_get_archiver()函式,執行\dS+ pg_stat_archiver可看到詳細資訊
select * from pg_stat_archiver;


檢視執行日誌的相關配置，執行日誌包括Error資訊，定位慢查詢SQL，資料庫的啟動關閉資訊，checkpoint過於頻繁等的告警資訊。
show logging_collector;--啟動日誌收集
show log_directory;--日誌輸出路徑
show log_filename;--日誌檔名
show log_truncate_on_rotation;--當生成新的檔案時如果檔名已存在，是否覆蓋同名舊檔名
show log_statement;--設定日誌記錄內容
show log_min_duration_statement;--執行XX毫秒的語句會被記錄到日誌中，-1表示禁用這個功能，0表示記錄所有語句，類似mysql的慢查詢配置


檢視wal日誌的配置，wal日誌就是redo重做日誌
存放在data_directory/pg_wal目錄






檢視當前使用者
DB=# \c
DB=# select current_user;

切換使用者，當前使用者切換到user1使用者
DB=# \c - user1

檢視所有使用者，及使用者包含的許可權
DB=# \du
DB=# select * from pg_user;
DB=# select * from pg_shadow;

檢視所有角色擁有的許可權和包含的使用者
DB=# \du
DB=# select * from pg_roles;

psql 終端可以用\du 或\du+ 檢視，也可以檢視系統表
select * from pg_roles;
select * from pg_user;


查詢使用者XX的許可權，必須到指定的資料庫下執行
select * from information_schema.table_privileges where grantee='XX';
select * from INFORMATION_SCHEMA.role_table_grants where grantee='XX';

建立使用者XX，並授予超級管理員許可權
create user XXX SUPERUSER PASSWORD 'A_@#qa23'

備註：建立角色，賦予了login許可權，則相當於建立了使用者，在pg_user可以看到這個角色
create role "user1" superuser;--pg_roles有user1，pg_user和pg_shadow沒有user1
alter role "user1" login;--pg_user和pg_shadow也有user1了

把角色role1賦給user2
GRANT role1 to user2;


檢視某個使用者是否擁有某個schema的usage許可權
\dns+ schema1
--顯示結果中第三列access privileges如果有使用者1，表示使用者1有schema1的usage許可權

檢視某個使用者是否擁有查詢某個schema的所有表的許可權
\ddp schema1


查詢group
select * from pg_group


授權
DB=# \h grant
GRANT ALL PRIVILEGES ON schema schemaname TO dbuser;
grant ALL PRIVILEGES on all tables in schema fds to dbuser;
GRANT ALL ON tablename TO user;
GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;
grant select on all tables in schema public to dbuser;--給使用者讀取public這個schema下的所有表
GRANT create  ON schema schemaname TO dbuser;--給使用者授予在schema上的create許可權，比如create table、create view等
GRANT USAGE  ON schema schemaname TO dbuser;
grant select on schema public to dbuser;--報錯ERROR:  invalid privilege type SELECT for schema
--USAGE：
對於LANGUAGE過程語言而言，表示允許使用指定的過程語句(pl/sql，pl/python等)建立相應的函式。目前過程語句上只有這一種許可權控制
對於SCHEMA模式而言，表示允許查詢模式中物件，當然，如果要查詢一個模式中的表，實際上還需要有表的select許可權。當然如果沒有usage許可權仍然有辦法可以看見模式下的物件的名字，比如透過查詢系統檢視pg_tables
對於SEQUENCE序列而言，表示允許使用currval和nextval函式
對於FOREIGN DATA WRAPPER外部資料封裝器來說，表示允許被授權者使用外部資料封裝器建立新的外部伺服器(FOREIGN SERVERS)
對於FOREIGN SERVER外部伺服器來說，允許建立外部表(FOREIGN TABLE)
--CREATE：對於資料庫，允許在資料庫上建立Schema；對於Schema，允許對Schema上建立資料庫物件；對於表空間，允許把表或是索引指定到對應的表空間上。
--ALL PRIVILEGES，其中關鍵字PRIVILEGES可以忽略


在psql中的檢視許可權的快捷指令
(options: S = show system objects, + = additional detail)
\du或\dg   列出role名稱和在它所屬的哪個許可權組
--du或dg等價，因為postgresql資料庫中角色和使用者不分
\dns+  列出所有schema和它的schema owner和哪些使用者對它有usage許可權
\ddp   列出預設許可權，列出schema的名稱並列出哪些角色對他什麼型別的物件有許可權
\dts+  列出表屬於哪個schema，這些表的owner是誰，表多大，但是隻能顯示public這個schema的資訊
\dp    列出表，檢視和序列的訪問許可權，同\z，但是隻能顯示public這個schema的資訊

\drds [模式1 [模式2]] 列出每個資料庫的角色設定
\dp - lists table/view permissions
\dn+ - lists schema permissions
\l+ does not list all users that can access the database




\du *owner*
列出包含owner名稱的角色名稱和在它所屬的哪個許可權組
\dns+ s*
列出包含s名稱資訊的schema和它的schema owner和哪些使用者對它有usage許可權
\ddp schema1
列出schema1這個schema的名稱，列出哪些角色對他什麼型別的物件有許可權
\dt s*
列出包含s名稱的表的資訊，這些表屬於哪個schema，這些表的owner是誰


\set ECHO_HIDDEN on|off或psql -E來開啟\命令後面的語句











database、schema、table_seq_view_etc、table_column 分4個級別來授權。

schema：一個database下可以有多個schema。可以給schema指定一個owner，如果沒有指定，那麼當前使用者就是schema的預設owner。

每個schema有自己的owner，並且db owner可以操作所有schema，db owner可以訪問所有schema下的表。

常見問題分析
postgresql ERROR: permission denied for schema
不僅需要授予對schema中表的訪問許可權，還需要授予對schema本身的訪問許可權。
手冊中有這樣一段：預設情況下，使用者不能訪問他們不擁有的schema中的任何物件。要允許這樣做，schema的所有者必須授予此使用者對該schema的使用許可權，即使該使用者有對該schema下所有物件的操作許可權(public schema除外)。因此，要麼讓所建立的使用者成為該schema的所有者，要麼將schema的使用權授予這個使用者。

# GRANT USAGE ON SCHEMA the_schema TO some_user;
接下來再對schema下的TABLES,SEQUENCES,FUNCTIONS等物件進行授權。




alter schema s2 owner to owner_1;
修改schema s2的owner為owner_1，但是s2下的表的許可權還是保持原來的owner，並沒有也一併成了owner1

-- 變更指定表owner
alter table s2.t1 owner to owner_1;

-- 或者在不變更表的owner的情況下，批次賦權s2下的所有表許可權給owner_1
grant all on all tables in schema s2 to owner_1;


對一個使用者授予只讀某個schema的許可權
grant usage on schema s9 to owner_2;
grant select on all tables in schema s9 to owner_2;
--授權owner_2可以訪問s9下面的所有表
-- 重要提示：這種方式僅對已經存在的表有效。以後建立的表不會自動有隻讀許可權
如果新建的表也能訪問，則操作如下
alter default privileges for user user1 in schema s9 grant select on tables to owner_2;
-- 對使用者user1以後在schema s9下新建的表,owner_2都可以訪問

alter default privileges in schema s9 grant select on tables to owner_2;
--當前使用者執行如上語句後，此使用者在s9下新建的任何表，owner_2都可以訪問（其他使用者使用者建立的表，owner_2不能訪問）
--上述語句不是這個意思：對於任何使用者在s9下新建的表，owner_2都可以訪問
alter default privileges for user user1,user2 in schema s9 grant select on tables to owner_2;
--以後user1,user2在schema s9下新建的表,使用者owner_2都可以訪問

備註：目前postgresql沒有一種方法，可以使以後任何使用者在s9下新建的表，owner_2都可以訪問。



















檢視錶上存在哪些索引以及大小
select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in
(select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc');

SELECT c.relname,c2.relname, c2.relpages*8 as size_kb FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname ='cc' AND c.oid =i.indrelid AND c2.oid =i.indexrelid ORDER BY c2.relname;

檢視索引定義
select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc';
select pg_get_indexdef(b.indexrelid);

檢視過程函式定義
select oid,* from pg_proc where proname = 'insert_platform_action_exist'; --oid = 24610
select * from pg_get_functiondef(24610);

檢視錶大小(不含索引等資訊)
select pg_relation_size('cc'); --368640 byte
select pg_size_pretty(pg_relation_size('cc')) --360 kB
t1=# \dts+ cc






檢視錶所對應的資料檔案路徑與大小
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'empsalary';


posegresql查詢當前lsn
1、用到哪些方法：
apple=# select proname from pg_proc where proname like 'pg_%_lsn';
proname
---------------------------------
 pg_current_wal_flush_lsn
 pg_current_wal_insert_lsn
 pg_current_wal_lsn
 pg_last_wal_receive_lsn
 pg_last_wal_replay_lsn

2、查詢當前的lsn值：
apple=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------------
0/45000098

3、查詢當前lsn對應的日誌檔案
select pg_walfile_name('0/1732DE8');
select pg_walfile_name(pg_current_wal_lsn());

4、查詢當前lsn在日誌檔案中的偏移量
SELECT * FROM pg_walfile_name_offset(pg_current_wal_lsn());



切換pg_wal日誌
select pg_switch_wal();

查詢當前lsn在pg_wal日誌中的詳細資訊,/pgdata1/data是預設的資料檔案目錄，/pgdata1/data/pg_wal是它的子目錄
pg_controldata /pgdata1/data

清理pg_wal日誌
pg_archivecleanup /postgresql/pgsql/data/pg_wal 000000010000000000000005
表示刪除000000010000000000000005之前的所有日誌
--pg_wal日誌沒有設定保留週期的引數，即沒有類似mysql的引數expire_logs_days，但是pg_wal日誌保留期限受引數wal_keep_segments、max_wal_size的影響



查詢邏輯複製中延遲資料量
select  pid, client_addr,application_name,state, sync_state, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) as sent_delay,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) as write_delay,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) as flush_delay,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_delay from pg_stat_replication;


查詢有哪些slot，任意一個資料庫下都可以查，查詢的結果都一樣
select * from pg_replication_slots;

刪除複製槽
/usr/pgsql-10/bin/pg_recvlogical -d postgres --drop-slot --slot=slotname
select pg_drop_replication_slot('slotname');



查詢某個使用者XX擁有的所有物件許可權，要到對應的資料庫下執行
SELECT relname,relacl FROM pg_class WHERE relacl::TEXT LIKE '%user1%'
備註：這裡只有表、檢視、序列、索引、物化檢視、複合型別、TOAST表、外部表
      函式、型別、語言、資料庫、表空間需要找對應的系統表pg_proc.proacl , pg_type.typacl , pg_language.lanacl , pg_database.datacl , pg_tablespace.spcacl
--系統許可權，postgresql沒有存放系統許可權的系統表或系統檢視，也是說postgresql不像oracle一樣有系統許可權的概念，poostgresql查不到select on all tables這樣的系統許可權

檢視某使用者的擁有對哪些表、檢視的許可權，要到對應的資料庫下執行
select * from INFORMATION_SCHEMA.role_table_grants where grantee='XX';
select * from information_schema.table_privileges where grantee='XX';

檢視usage許可權表
select * from information_schema.usage_privileges where grantee='XX';

檢視儲存過程函式相關許可權表
select * from information_schema.routine_privileges where grantee='XX';


查詢某個使用者擁有的角色

查詢某個角色擁有的許可權

檢視哪些使用者對XX表有哪些許可權
\z XX
\dp XX
select relname,relacl from pg_class where relname='XX';



建立一個使用者，再刪除使用者，報錯
postgres=# create role "lukes.liao";
CREATE ROLE
postgres=# alter role "lukes.liao" login;
ALTER ROLE
postgres=# grant all privileges on schema fds to "lukes.liao";
ERROR:  schema "fds" does not exist
postgres=# \c fds
fds=# grant all privileges on schema fds to "lukes.liao";
GRANT
fds=# grant ALL PRIVILEGES on all tables in schema fds to "lukes.liao";
GRANT
fds=# \c postgres
postgres=# drop user "lukes.liao";
ERROR:  role "lukes.liao" cannot be dropped because some objects depend on it
DETAIL:  248 objects in database fds


解決方法
postgres=# \c fds
fds=# select * from INFORMATION_SCHEMA.role_table_grants where grantee='lukes.liao';
fds=# revoke all privileges on schema fds from "lukes.liao";
REVOKE
fds=# drop user "lukes.liao";
and 147 other objects (see server log for list)
fds=# revoke ALL PRIVILEGES on all tables in schema fds from "lukes.liao";
REVOKE
fds=# drop user "lukes.liao";
DROP ROLE


檢視消耗cpu的sql
select * from pg_stat_activity where pid=XX;
或
SELECT procpid, START, now() - START AS lap, current_query  FROM ( SELECT backendid, pg_stat_get_backend_pid (S.backendid) AS procpid, pg_stat_get_backend_activity_start (S.backendid) AS START,pg_stat_get_backend_activity (S.backendid) AS current_query  FROM (SELECT pg_stat_get_backend_idset () AS backendid) AS S) AS S WHERE current_query <> '<IDLE>' and procpid=XX  ORDER BY lap DESC;

檢視鎖，需要到對應的db裡面執行
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname
from pg_locks a
join pg_class b on a.relation = b.oid

SELECT locker.pid,  
            pc.relname,  
            locker.mode,  
            locker_act.application_name,  
            least(query_start,xact_start) start_time,  
            locker_act.state,  
            CASE  
        WHEN granted='f' THEN  
        'wait_lock'  
        WHEN granted='t' THEN  
        'get_lock'  
        END lock_satus,current_timestamp - least(query_start,xact_start) AS runtime,  
        locker_act.query  
    FROM pg_locks locker,pg_stat_activity locker_act, pg_class pc  
    WHERE locker.pid=locker_act.pid  
            AND NOT locker.pid=pg_backend_pid()  
            AND application_name<>'pg_statsinfod'  
            AND locker.relation = pc.oid  
            AND pc.reltype<>0 --and pc.relname='t'
    ORDER BY  runtime desc;


檢視sql語句的執行計劃
explain analyze select XX



ANALYZE schema_name.table_name會在表上加上ShareUpdateExclusiveLock鎖，這個鎖不堵塞讀


不用重啟，直接載入配置檔案的兩種方法
1、使用linux命令pg_ctl reload
2、使用psql命令select pg_reload_conf();

客戶端連線數統計
select client_addr,count(1) from pg_stat_activity  group by client_addr;

檢視活動會話
select pid,usename,datname,query,client_addr from pg_stat_activity where pid<>pg_backend_pid() and state='active' order by query;

檢視等待事件
select pid,usename,datname,query,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;

檢視哪些欄位使用了timestamptz型別
SELECT relname, attname FROM pg_class c,pg_attribute attr WHERE c.oid = attr.attrelid and attisdropped = 'f' and atttypid=1184 and relname !~ '^pg_';



製作快捷方式
編輯家目錄下.psqlrc檔案，格式如下：
\set short_command 'SQL;'
--short_commad為自定義的快捷命令名稱
--SQL裡的單引號需要轉義
--需要帶分號

使用方法
DB=> :short_command


檢視配置檔案資訊
select name,setting from pg_settings where category='File Locations';


檢視是否自動提交
postgres=# \echo :AUTOCOMMIT



FDW的一些參考語句
select * from pg_extension;
select * from pg_foreign_data_wrapper;
select * from pg_foreign_server;
select * from pg_foreign_table;
select * from pg_user_mappings;



查詢表的碎片率
select n_dead_tup,(case when n_live_tup > 0 then n_dead_tup::float8/n_live_tup::float8 else 0 end) as "dead_tup/live_tup_pert" from pg_stat_all_tables

檢查autovacuum是否已對膨脹的表進行過處理
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_all_tables
ORDER BY n_dead_tup / (n_live_tup* current_setting('autovacuum_vacuum_scale_factor')::float8 + current_setting('autovacuum_vacuum_threshold')::float8)
DESC LIMIT 10;



查詢哪些SQL語句消耗cpu
ps aux | grep postgres | sort -n -r -k 3 | head -10
select procpid, start, now()-start as last_time, current_query
from
(select backendid, pg_stat_get_backend_pid(s.backendid) as procpid, pg_stat_get_backend_activity_start(s.backendid) as start, pg_stat_get_backend_activity(s.backendid) as current_query
from
(select pg_stat_get_backend_idset() as backendid) as s
) as s
where procpid IN (spid);










檢查堵塞
select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' as txn_duration
from svv_transactions a
left join (select pid,relation,granted from pg_locks group by 1,2,3) b
on a.relation=b.relation and a.granted='f' and b.granted='t'
left join (select * from stv_tbl_perm where slice=0) c
on a.relation=c.id
left join pg_class d on a.relation=d.oid
where  a.relation is not null;

Analyze表
select 'analyze '||schemaname||'.'||tablename||';' from pg_tables where schemaname not in ('pg_catalog','information_schema','dms_wam','public','admin');

Vacuum表
select 'vacuum '||schemaname||'.'||tablename||';' from pg_tables where schemaname not in ('pg_catalog','information_schema','dms_wam','public','admin');




資料庫尺寸函式

名字    返回型別    描述
pg_column_size(any)    int    儲存一個指定的數值需要的位元組數(可能壓縮過)
pg_database_size(oid)    bigint    指定 OID 代表的資料庫使用的磁碟空間
pg_database_size(name)    bigint    指定名稱的資料庫使用的磁碟空間
pg_relation_size(oid)    bigint    指定 OID 代表的表或者索引所使用的磁碟空間
pg_relation_size(text)    bigint    指定名稱的表或者索引使用的磁碟空間。表名字可以用模式名修飾。
pg_size_pretty(bigint)    text    把位元組計算的尺寸轉換成一個人類易讀的尺寸。
pg_tablespace_size(oid)    bigint    指定 OID 代表的表空間使用的磁碟空間
pg_tablespace_size(name)    bigint    指定名字的表空間使用的磁碟空間
pg_total_relation_size(oid)    bigint    指定 OID 代表的表使用的磁碟空間，包括索引和壓縮資料。
pg_total_relation_size(text)    bigint    指定名字的表所使用的全部磁碟空間，包括索引和壓縮資料。表名字可以用模式名修飾。


鎖函式

名字    返回型別    描述
pg_advisory_lock(keybigint)    void    獲取排它諮詢鎖
pg_advisory_lock(key1int, key2 int)    void    獲取排它諮詢鎖
pg_advisory_lock_shared(keybigint)    void    獲取共享諮詢鎖
pg_advisory_lock_shared(key1int, key2 int)    void    獲取共享諮詢鎖
pg_try_advisory_lock(keybigint)    boolean    嘗試獲取排它諮詢鎖
pg_try_advisory_lock(key1int, key2 int)    boolean    嘗試獲取排它諮詢鎖
pg_try_advisory_lock_shared(keybigint)    boolean    嘗試獲取共享諮詢鎖
pg_try_advisory_lock_shared(key1int, key2 int)    boolean    嘗試獲取共享諮詢鎖
pg_advisory_unlock(keybigint)    boolean    釋放排它諮詢鎖
pg_advisory_unlock(key1int, key2 int)    boolean    釋放排它諮詢鎖
pg_advisory_unlock_shared(keybigint)    boolean    釋放共享諮詢鎖
pg_advisory_unlock_shared(key1int, key2 int)    boolean    釋放共享諮詢鎖
pg_advisory_unlock_all()    void    釋放當前會話持有的所有諮詢鎖