PostgreSQLDBA常用SQL查詢語句有哪些

本篇內(nèi)容介紹了“PostgreSQL DBA常用SQL查詢語句有哪些”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

成都創(chuàng)新互聯(lián)公司專注于莎車企業(yè)網(wǎng)站建設(shè),響應(yīng)式網(wǎng)站建設(shè),商城網(wǎng)站開發(fā)。莎車網(wǎng)站建設(shè)公司,為莎車等地區(qū)提供建站服務(wù)。全流程定制設(shè)計(jì)，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，成都創(chuàng)新互聯(lián)公司專業(yè)和態(tài)度為您提供的服務(wù)

查看幫助命令

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

按列顯示，類似MySQL的\G

DB=# \x
Expanded display is on.

查看DB安裝目錄(最好root用戶執(zhí)行)

find / -name initdb

查看有多少DB實(shí)例在運(yùn)行(最好root用戶執(zhí)行)

find / -name postgresql.conf

查看DB版本

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

查看DB實(shí)例運(yùn)行狀態(tài)

pg_ctl status

查看所有數(shù)據(jù)庫

psql –l --查看5432端口下面有多少個(gè)DB
psql –p XX –l --查看XX端口下面有多少個(gè)DB
DB=# \l
DB=# select * from pg_database;

創(chuàng)建數(shù)據(jù)庫

createdb database_name
DB=# \h create database --創(chuàng)建數(shù)據(jù)庫的幫助命令
DB=# create database database_name

進(jìn)入某個(gè)數(shù)據(jù)庫

psql –d dbname
DB=# \c dbname

查看當(dāng)前數(shù)據(jù)庫

DB=# \c
DB=# select current_database();

查看數(shù)據(jù)庫文件目錄

DB=# show data_directory;
cat $PGDATA/postgresql.conf |grep data_directory
cat /etc/init.d/postgresql|grep PGDATA=
lsof |grep 5432得出第二列的PID號(hào)再ps –ef|grep PID

查看表空間

select * from pg_tablespace;

查看語言

select * from pg_language;

查詢所有schema，必須到指定的數(shù)據(jù)庫下執(zhí)行

select * from information_schema.schemata;
SELECT nspname FROM pg_namespace;
\DNS

查看表名

DB=# \dt --只能查看到當(dāng)前數(shù)據(jù)庫下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';

查看表結(jié)構(gòu)

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

查看觸發(fā)器

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數(shù)據(jù)庫的大小

SELECT pg_size_pretty(pg_database_size('XX')) As fulldbsize;

查看所有數(shù)據(jù)庫的大小

select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;

查看各數(shù)據(jù)庫數(shù)據(jù)創(chuàng)建時(shí)間：

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;

查看參數(shù)文件

DB=# show config_file;
DB=# show hba_file;
DB=# show ident_file;

查看當(dāng)前會(huì)話的參數(shù)值

DB=# show all;

查看參數(shù)值

select * from pg_file_settings

查看某個(gè)參數(shù)值,比如參數(shù)work_mem

DB=# show work_mem

修改某個(gè)參數(shù)值,比如參數(shù)work_mem

DB=# alter system set work_mem='8MB'

--使用alter system命令將修改postgresql.auto.conf文件，而不是postgresql.conf，這樣可以很好的保護(hù)postgresql.conf文件，加入你使用很多alter system命令后搞的一團(tuán)糟，那么你只需要?jiǎng)h除postgresql.auto.conf，再執(zhí)行pg_ctl reload加載postgresql.conf文件即可實(shí)現(xiàn)參數(shù)的重新加載。

查看是否歸檔

DB=# show archive_mode;

查看運(yùn)行日志的相關(guān)配置，運(yùn)行日志包括Error信息，定位慢查詢SQL，數(shù)據(jù)庫的啟動(dòng)關(guān)閉信息，checkpoint過于頻繁等的告警信息。

show logging_collector;--啟動(dòng)日志收集
show log_directory;--日志輸出路徑
show log_filename;--日志文件名
show log_truncate_on_rotation;--當(dāng)生成新的文件時(shí)如果文件名已存在，是否覆蓋同名舊文件名
show log_statement;--設(shè)置日志記錄內(nèi)容
show log_min_duration_statement;--運(yùn)行XX毫秒的語句會(huì)被記錄到日志中，-1表示禁用這個(gè)功能，0表示記錄所有語句，類似mysql的慢查詢配置
查看wal日志的配置，wal日志就是redo重做日志

存放在data_directory/pg_wal目錄

查看當(dāng)前用戶

DB=# \c
DB=# select current_user;

查看所有用戶

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

查看所有角色

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

查詢用戶XX的權(quán)限，必須到指定的數(shù)據(jù)庫下執(zhí)行

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

創(chuàng)建用戶XX，并授予超級(jí)管理員權(quán)限

create user XXX SUPERUSER PASSWORD '123456'

創(chuàng)建角色，賦予了login權(quán)限，則相當(dāng)于創(chuàng)建了用戶，在pg_user可以看到這個(gè)角色

create role "user1" superuser;--pg_roles有user1，pg_user和pg_shadow沒有user1
alter role "user1" login;--pg_user和pg_shadow也有user1了

授權(quán)

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這個(gè)schema下的所有表
GRANT create ON schema schemaname TO dbuser;--給用戶授予在schema上的create權(quán)限，比如create table、create view等
GRANT USAGE ON schema schemaname TO dbuser;
grant select on schema public to dbuser;--報(bào)錯(cuò)ERROR: invalid privilege type SELECT for schema
--USAGE：對(duì)于程序語言來說，允許使用指定的程序語言創(chuàng)建函數(shù);對(duì)于Schema來說，允許查找該Schema下的對(duì)象;對(duì)于序列來說，允許使用currval和nextval函數(shù);對(duì)于外部封裝器來說，允許使用外部封裝器來創(chuàng)建外部服務(wù)器;對(duì)于外部服務(wù)器來說，允許創(chuàng)建外部表。

查看表上存在哪些索引以及大小

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

查看過程函數(shù)定義

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

查看表所對(duì)應(yīng)的數(shù)據(jù)文件路徑與大小

SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'empsalary';

posegresql查詢當(dāng)前l(fā)sn

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、查詢當(dāng)前的lsn值：

apple=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------------
0/45000098

3、查詢當(dāng)前l(fā)sn對(duì)應(yīng)的日志文件

select pg_walfile_name('0/1732DE8');

4、查詢當(dāng)前l(fā)sn在日志文件中的偏移量

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日志沒有設(shè)置保留周期的參數(shù)，即沒有類似mysql的參數(shù)expire_logs_days，pg_wal日志永久保留，除非shell腳步刪除幾天前或pg-rman備份時(shí)候設(shè)置保留策略

查詢有哪些slot，任意一個(gè)數(shù)據(jù)庫下都可以查，查詢的結(jié)果都一樣

select * from pg_replication_slots;

“PostgreSQL DBA常用SQL查詢語句有哪些”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！

新聞名稱：PostgreSQLDBA常用SQL查詢語句有哪些
URL網(wǎng)址：http://bm7419.com/article38/jjsppp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供用戶體驗(yàn)、網(wǎng)站營銷、域名注冊(cè)、網(wǎng)站導(dǎo)航、網(wǎng)站改版、網(wǎng)站維護(hù)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容