共计 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 小编将为大家输出更多高质量的实用文章！
