共計 5847 個字符，預計需要花費 15 分鐘才能閱讀完成。

本篇內容介紹了“PostgreSQL DBA 常用 SQL 查詢語句有哪些”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

查看幫助命令

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

查看所有數據庫

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;

“PostgreSQL DBA 常用 SQL 查詢語句有哪些”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！