一、数据库连接
1、获取数据库实例连接数
select count(*) from pg_stat_activity;
2、查询数据库中各个用户名对应的数据库连接数
select usename, count(*) from pg_stat_activity group by usename;
3、查询数据库中对应的数据库连接数
select datname, count(*) from pg_stat_activity group by datname;
4、查询当前连接数详细信息
select * from pg_stat_activity;
5、获取数据库最大连接数
show max_connections
6、查询当前连接数详细信息
select * from pg_stat_activity;
二、赋权操作
1、为指定用户赋予指定表的select权限
GRANT SELECT ON table_name in schema public TO username;
2、为某个schema下所有表赋予select权限
GRANT SELECT ON all tables in schema public TO username;
3、修改数据库表所属的ownner
alter table table_name owner to username;
4、授予指定用户指定表的所有权限
grant all privileges on table product to username;
5、授予指定用户所有表的所有权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;
6、撤销指定用户所有表的所有权限
revoke insert,update,delete ON ALL TABLES in schema public from usename;
revoke all on schema public from usename;
revoke all on table from usename;
7
、
查看用户的系统权限
select * from pg_roles where rolname =’usename’;
8
、查询用户的表权限
select * from information_schema.tale_privileges where grantee=’usename’
9
、
查看当前有多少用户
select rolname ,rolpassword from pg_authid;
10
、
给一个用户一张表的权限
alter table tabname owner to usename;
11
、
修改原用户密码
alter user usename with password ‘passwd’;
12
、
查询
schema
的列表
select distinct schemaname from pg_tables;
13
、
删除一个用户
注:需要先删除和这个用户的关联的库
drop owned by dbname cascade;
drop user dbname;
14
、
copy
命令
导出数据:
\copy (select * from tabname where …) to ‘/home/postgres/xxx.csv’;
导入数据:
\copy tabname from ‘/home/postgres/xxx.csv’;
注:
csv
的文档不能有空格,而且不能有菜单栏,第一行是数据即可
处理空格的办法:
sed -i ‘s/ //g’ /home/postgres/xxx.csv --
去掉空格
sed -i ‘s/.{1\}$//’ /home/postgres/xxx.csv --
去掉行末的第几个字符
三、数据库表或者索引
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_%'
5、查看参数文件
方法1
[postgres@gs12 data]$ ls $PGDATA/postgresql.conf
/pgdb/data/postgresql.conf
方法2
postgres=# show config_file;
config_file
----------------------------
/pgdb/data/postgresql.conf
(1 row)
show config_file;
四、获取数据大小
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
select query calls,total_time,(total_time/calls) as average,rows,100.0*shared_blk_hit / nullif(shared_blks_hit + shared_blks_read,0) as hit_percent from pg_stat_statements order by average desc limit 3;
7
、分析评估
SQL
执行情况
EXPLAIN ANALYZE SELECT * FROM product
9
、查看当前长时间执行却不结束的
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;
9、查出使用表扫描最多的表
select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;
10、查询读取buffer最多的5个SQL
select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;
11、获取数据库当前的回滚事务数以及死锁数
select datname,xact_rollback,deadlocks from pg_stat_database
12、查询访问指定表的慢查询
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