史上最全：PostgreSQL DBA常用SQL查詢語句（建議收藏學習）

文章作者：廖學強

來自公衆号：資料和雲

連結：

http://blog.itpub.net/30126024/viewspace-2655205/

PostgreSQL連續兩年被評為年度資料庫，備受很多DBA的青睐，本文我們一起來了解學習PostgreSQL常用的查詢語句有哪些？

檢視幫助指令

DB=# help --總的幫助
DB=# \h --SQL commands級的幫助
DB=# \? --psql commands級的幫助           

按列顯示，類似MySQL的G

DB=# \x
Expanded display is on.           

檢視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           

檢視所有資料庫

1. 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();           

檢視資料庫檔案目錄

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           

檢視表空間

select * from pg_tablespace;           

檢視語言

select * from pg_language;           

查詢所有schema，必須到指定的資料庫下執行

select * from information_schema.schemata;

SELECT nspname FROM pg_namespace;

\dnS           

檢視表名

DB=# \dt --隻能檢視到目前資料庫下public的表名

DB=# SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;

DB=# SELECT * FROM information_schema.tables WHERE table_name='ff_v3_ff_basic_af';           

檢視表結構

DB=# \d tablename

DB=# select * from information_schema.columns where table_schema='public' and table_name='XX';           

檢視索引

DB=# \di

DB=# select * from pg_index;           

檢視視圖

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 indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;           

檢視參數檔案

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;           

檢視運作日志的相關配置，運作日志包括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;           

檢視所有使用者

DB=# select * from pg_user;
DB=# select * from pg_shadow;           

檢視所有角色

DB=# \du
DB=# select * from pg_roles;           

查詢使用者XX的權限，必須到指定的資料庫下執行

select * from information_schema.table_privileges where grantee='XX';           

建立使用者XX，并授予超級管理者權限

create user XXX SUPERUSER PASSWORD '123456'
建立角色，賦予了login權限，則相當于建立了使用者，在pg_user可以看到這個角色

create role "user1" superuser;--pg_roles有user1，pg_user和pg_shadow沒有user1

alter role "user1" login;--pg_user和pg_shadow也有user1了           

授權

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：對于程式語言來說，允許使用指定的程式語言建立函數;對于Schema來說，允許查找該Schema下的對象;對于序列來說，允許使用currval和nextval函數;對于外部封裝器來說，允許使用外部封裝器來建立外部伺服器;對于外部伺服器來說，允許建立外部表。           

檢視表上存在哪些索引以及大小

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           

檢視表所對應的資料檔案路徑與大小

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');           

4、查詢目前lsn在日志檔案中的偏移量

SELECT * FROM pg_walfile_name_offset(pg_current_wal_lsn());           

切換pg_wal日志

select pg_switch_wal();           

清理pg_wal日志

pg_archivecleanup /postgresql/pgsql/data/pg_wal 000000010000000000000005

表示删除000000010000000000000005之前的所有日志

--pg_wal日志沒有設定保留周期的參數，即沒有類似mysql的參數expire_logs_days，pg_wal日志永久保留，除非shell腳步删除幾天前或pg-rman備份時候設定保留政策           

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

select * from pg_replication_slots;           

啟動時間

select statement_timestamp()-pg_postmaster_start_time() AS up_time;           

檢視有幾個從庫

select * from pg_stat_replication;           

主從延遲，主庫上執行

select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_flush_lsn(),write_lsn)) delay_wal_size,* from pg_stat_replication ;           

-- 手動激活從庫為主庫

-- 激活位點最新的庫為主庫

pg_ctl promote -D $PGDATA           

檢視活躍連接配接數

select count(*) from pg_stat_activity where query <>'IDLE';           

類似 mysql source xx.sql

\i xx.sql           

殺掉某連接配接

select pg_cancel_backend(pid); — session 還在，transaction 復原, pid 來自 pg_stat_activity
select pg_terminate_backend(pid); — session 消失，transaction 復原, pid 來自 pg_stat_activity