史上最全:PostgreSQL DBA常用SQL查询语句(建议收藏学习)

简介: PostgreSQL连续两年被评为年度数据库,备受很多DBA的青睐,本文我们一起来了解学习Postgr…

简介: PostgreSQL连续两年被评为年度数据库,备受很多DBA的青睐,本文我们一起来了解学习PostgreSQL常用的查询语句有哪些?

文章作者:廖学强
来自公众号:数据和云

链接: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

本文来自网络,不代表陈小黑博客立场,转载请注明出处:https://www.chenxiaohei.net/archives/424

作者: admin

发表评论

电子邮件地址不会被公开。 必填项已用*标注

联系我们

联系我们

在线咨询: QQ交谈

邮箱: admin@chenxiaohei.net

如果您对网站有什么建议或者意见,欢迎您通过以上联系方式联系我们~
关注微信
微信扫一扫关注我们

微信扫一扫关注我们

返回顶部