首页 > 数据库 >PostgreSQL常用运维SQL

PostgreSQL常用运维SQL

时间:2023-06-10 15:59:03浏览次数:51  
标签:PostgreSQL 运维 pgsa start pg SQL query select size

一、数据库连接

1、获取数据库实例连接数

select count(*) from pg_stat_activity;

2、获取数据库最大连接数

show max_connections

3、查询当前连接数详细信息

select * from pg_stat_activity;

4、查询数据库中各个用户名对应的数据库连接数

select usename, count(*) from pg_stat_activity group by usename; 

二、赋权操作

1、为指定用户赋予指定表的select权限

GRANT SELECT ON table_name TO username;

2、修改数据库表所属的ownner

alter table table_name owner to username;

3、授予指定用户指定表的所有权限

grant all privileges on table product to username;

4、授予指定用户所有表的所有权限

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;

三、数据库表或者索引

1、获取数据库表中的索引

select * from pg_indexes where tablename = 'product'; 

2、获取当前db中所有表信息

select * from pg_tables;

3、查询数据库安装了哪些扩展

select * from pg_extension; 

4、查询数据库中的所有表及其描述

select relname as TABLE_NAME ,col_description(c.oid, 0) as COMMENTS from pg_class c where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%'

四、获取数据大小

1、查询执行数据库大小

select pg_size_pretty (pg_database_size('db_product'));

2、查询数据库实例当中各个数据库大小

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

3、查询单表数据大小

select pg_size_pretty(pg_relation_size('product')) as size;

4、查询数据库表包括索引的大小

select pg_size_pretty(pg_total_relation_size('table_name')) as size;

5、查看表中索引大小

select pg_size_pretty(pg_indexes_size('product'));

6、获取各个表中的数据记录数

select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' order by rowCounts desc

7、查看数据库表对应的数据文件

select pg_relation_filepath('product');

五、数据库分析

1、查看数据库实例的版本

select version();

2、查看最新加载配置的时间

select pg_conf_load_time();

3、查看当前wal的buffer中有多少字节未写入磁盘

select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());

4、查询最耗时的5个sql

select * from pg_stat_statements order by total_time desc limit 5;
备注:需要开启pg_stat_statements

5、获取执行时间最慢的3条SQL,并给出CPU占用比例

SELECT substring(query, 1, 1000) AS short_query,
round(total_time::numeric, 2) AS total_time,
calls,
round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 3;

6、分析评估SQL执行情况

EXPLAIN ANALYZE SELECT * FROM product

7、查看当前长时间执行却不结束的SQL

select datname, usename, client_addr, application_name, state, backend_start, xact_start, xact_stay, query_start, query_stay, replace(query, chr(10), ' ') as query from (select pgsa.datname as datname, pgsa.usename as usename, pgsa.client_addr client_addr, pgsa.application_name as application_name, pgsa.state as state, pgsa.backend_start as backend_start, pgsa.xact_start as xact_start, extract(epoch from (now() - pgsa.xact_start)) as xact_stay, pgsa.query_start as query_start, extract(epoch from (now() - pgsa.query_start)) as query_stay , pgsa.query as query from pg_stat_activity as pgsa where pgsa.state != 'idle' and pgsa.state != 'idle in transaction' and pgsa.state != 'idle in transaction (aborted)') idleconnections order by query_stay desc limit 5;

8、查出使用表扫描最多的表

select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;

9、查询读取buffer最多的5个SQL

select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;

10、获取数据库当前的回滚事务数以及死锁数

select datname,xact_rollback,deadlocks from pg_stat_database

11、查询访问指定表的慢查询

select * from pg_stat_activity where query ilike '%<table_name>%' and query_start - now() > interval '10 seconds';

六、数据库备份(非SQL)

1、备份postgres库并tar打包

pg_dump -h 127.0.0.1 -p 5432 -U postgres -f postgres.sql.tar -Ft

2、备份postgres库,转储数据为带列名的INSERT命令

pg_dumpall -d postgres -U postgres -f postgres.sql --column-inserts






标签:PostgreSQL,运维,pgsa,start,pg,SQL,query,select,size
From: https://www.cnblogs.com/tianqing/p/17471385.html

相关文章

  • MySQL数据库安全评估工具
    MySQL数据库安全评估工具MySQLDatabaseSecurityAssessmentTool   MySAT执行多项测试以分析数据库配置和安全策略。MySAT可以帮助评估并因此提高MySQL数据库的安全性。MySAT是一个简单的SQL脚本,它易于理解,易于维护。MySAT的结果是一份HTML格式的报告。MySQL数据库安全评估......
  • redis集群部署(运维笔记)
    Redis是一个高性能的NoSQL数据库,由于其高性能和可靠性,越来越多的企业开始使用Redis。在生产环境中,Redis集群是必不可少的,因为它可以提供高可用性和可扩展性。本文将介绍如何在Linux环境下部署Redis集群。1.安装Redis首先,需要在每个节点上安装Redis。可以使用以下命令从Redis官方......
  • SQL注入(Low)
    首先判断是否可以注入输入:’发现网站报错了,表示可以注入(后台有进行执行)在判断是为字符型还是数字型输入:1and1=1再输入:1and1=2因为1=1为真,1=2为假,但是1=2还是有回显输入:1‘and1=1#  (#号和--一样注释掉后面部分)--后有空格再输入:1’and1=2#没有回显所以表示这个是字......
  • SQL注入
    一、寻找注入点这一部分其实对于我这种刚学基础的小白是最难的,很长一段时间就在盲目的换网站,测试,扫描,不行就换下一个,我推荐大家使用googlehacking,其实就是高级搜索语言,我总结几个最简单最常用的用法intext:寻找正文中含有关键字的网页例如:intext:后台登录将只返回......
  • Failed to bind properties under 'spring.datasource.primary' to javax.sql.DataSou
     2023-06-1011:04:13.778WARN22452---[main]ConfigServletWebServerApplicationContext:Exceptionencounteredduringcontextinitialization-cancellingrefreshattempt:org.springframework.beans.factory.UnsatisfiedDependencyException:Error......
  • Failed to bind properties under 'spring.datasource.primary' to javax.sql.DataSou
     2023-06-1011:04:13.778WARN22452---[main]ConfigServletWebServerApplicationContext:Exceptionencounteredduringcontextinitialization-cancellingrefreshattempt:org.springframework.beans.factory.UnsatisfiedDependencyException:Error......
  • Failed to bind properties under 'spring.datasource.primary' to javax.sql.DataSou
    ​  2023-06-1011:04:13.778WARN22452---[main]ConfigServletWebServerApplicationContext:Exceptionencounteredduringcontextinitialization-cancellingrefreshattempt:org.springframework.beans.factory.UnsatisfiedDependencyException:......
  • Failed to bind properties under 'spring.datasource.primary' to javax.sql.DataSou
    ​  2023-06-1011:04:13.778WARN22452---[main]ConfigServletWebServerApplicationContext:Exceptionencounteredduringcontextinitialization-cancellingrefreshattempt:org.springframework.beans.factory.UnsatisfiedDependencyException:......
  • Sql2012安装
    在MSDN,我告诉你-做一个安静的工具站(itellyou.cn)中选择合适的版本;也可以直接ed2k://|file|cn_sql_server_2012_developer_edition_with_sp1_x64_dvd_1234492.iso|4231520256|C3653494E5E01CA5ADFAF910CBC32D75|/下载这个链接下载好后会进入这个界面我们选择左上角的安装......
  • SQL SERVER 2008 R2安装教程
    1、下载sqlserver2008r2的安装包(如果需要安装包可以私我,密钥自动输入)2、打开安装包,点击setup.exe3、点击“安装”,下一步选择“全新安装或向现有的安装添加功能”4、检测完成后点击确定5、选择输入产品密钥,这里会自动输入,下一步6、选择“我接受许可条款”,下一步7、选......