请选择 进入手机版 | 继续访问电脑版

postgresql数据库 DBA 运维 经常使用的命令

[复制链接]
科达工艺 发表于 2020-12-31 18:59:01 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
**
postgresql数据库 DBA 运维 经常使用的命令

**
(连续更新)
最近一直在维护postgresql数据库 ,整理一下经常会用到的命令,sql语句。
postgresql11 常用命令

**
1.死锁

**
因为postgresql的自己,经常会导致一部分表被锁
1.查询锁
  1. select w1.pid as 等待进程,w1.mode as 等待锁模式,w2.usename as 等待用户,w2.query as 等待会话,b1.pid as 锁的进程,b1.mode 锁的锁模式,b2.usename as 锁的用户,b2.query as 锁的会话,b2.application_name 锁的应用,b2.client_addr 锁的IP地点,b2.query_start 锁的语句执行时间from pg_locks w1join pg_stat_activity w2 on w1.pid=w2.pidjoin pg_locks b1 on w1.transactionid=b1.transactionid and w1.pid!=b1.pidjoin pg_stat_activity b2 on b1.pid=b2.pidwhere not w1.granted;
复制代码
2.杀死死锁进程
  1. SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid='62560'
复制代码
如果仍然不能杀死会话,可以在操纵系统层面,直接kill 掉
**
二.检察正在运行的进程

**
用于服务器监控,可查询进程,时间消耗与锁相关
  1. SELECT C.relname 对象名称,l.locktype 可锁对象的范例,l.pid 进程id,l.MODE 持有的锁模式,l.GRANTED 是否已经对锁举行授权,l.fastpath,psa.datname 数据库名称,psa.usesysid 用户id,psa.usename 用户名称,psa.application_name 应用程序名称,psa.client_addr 毗连的IP地点,psa.client_port 毗连使用的TCP端标语,psa.backend_start 进程开始时间,psa.xact_start 事务开始时间,psa.query_start 事务执行此语句时间,psa.state_change 事务状态改变时间,psa.wait_event_type 等待事件范例,psa.wait_event 等待事件,psa.STATE 查询状态,backend_xid 事务是否有写入操纵,backend_xmin 是否执事务快照,psa.query 执行语句,now( ) - query_start 连续时间FROMpg_locks lINNER JOIN pg_stat_activity psa ON ( psa.pid = l.pid )LEFT OUTER JOIN pg_class C ON ( l.relation = C.oid )-- where l.relation = 'tb_base_apparatus'::regclasswhere relkind ='r'ORDER BY query_start asc
复制代码
三 PostgreSql 查询数据库,索引,表,表空间大小

– 查询表空间大小
  1. select pg_size_pretty(pg_tablespace_size('pg_default'));
复制代码
– 查询所有数据库大小
  1. select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_databas;
复制代码
– 查询指定索引大小
  1. select pg_size_pretty(pg_relation_size('dotime'));
复制代码
– 查询指定表所有索引大小
  1. select pg_size_pretty(pg_indexes_size('tb_sys_loginfo'));
复制代码
– 查询指定schema 里所有的索引大小,按从大到小的顺序分列。
  1. select relname,indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
复制代码
– 查询指定表大小(仅仅指定表数据)
  1. select pg_size_pretty(pg_relation_size('tb_sys_loginfo'));
复制代码
– 查询指定表总大小(包括表数据与索引)
  1. select pg_size_pretty(pg_total_relation_size('tb_sys_loginfo'));
复制代码
– 查询指定schema内里所有表大小(仅仅指定表数据,从大到小)
  1. select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
复制代码
–查询指定schema内里所有表数据大小,总索引大小,总大小,行数
  1. SELECT    table_size.relname 表名,    pg_size_pretty ( pg_relation_size ( relid ) ) 表数据大小,    pg_size_pretty ( pg_indexes_size ( relid ) ) 表总索引大小,    pg_size_pretty ( pg_total_relation_size ( relid ) ) 表总大小,    表行数FROMpg_stat_user_tables table_size    LEFT JOIN (        SELECT            relname,            reltuples :: DECIMAL ( 19, 0 ) 表行数        FROM        pg_class r        JOIN pg_namespace n ON ( relnamespace = n.oid )        WHERE            relkind = 'r'            AND n.nspname = 'public'        ) table_num ON table_num.relname = table_size.relnameWHERE    schemaname = 'public'ORDER BY    pg_relation_size ( relid ) DESC;
复制代码
**
四 查询外部server

**
  1. select * from pg_foreign_server;
复制代码
**
五 杀掉所有数据库毗连

**
  1. SELECT    pg_terminate_backend(pid)FROM    pg_stat_activityWHERE    -- don't kill my own connection    pid  pg_backend_pid()    -- don't kill the connections to other databases    AND datname = '库名'    ;
复制代码
**
六 重新启动服务

**
  1. --启动服务:systemctl start postgresql-11--停止服务:systemctl stop postgresql-11--重启服务:systemctl restart postgresql-11--检察pgagent_11服务systemctl enable pgagent_11systemctl status pgagent_11systemctl start pgagent_11
复制代码
**
七 备份与还原

**

  • 备份数据库
  1. pg_dump --file "/home/back/pgsql-all-"$(date +%F+%T)".backup" --host "0.0.0.11" --port "5432" --username "postgres" --dbname "postgres" --verbose --role "postgres" --format=c --blobs --encoding "UTF8"
复制代码
2.还原数据库
  1. pg_restore --username "postgres" --no-password --role "postgres" --dbname "hrmwv2" --verbose /home/back/pgsql-all-postgres-2020-07-22+11:03:28.backup
复制代码
来源:https://blog.csdn.net/yang_z_1/article/details/111885234
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )