数据库操作

数据库操作

基础数据库函数

select uuid_generate_v4();
select current_time(6);
select now();

数据库进程操作

数据库复制-流模式

su - postgres

psql -c"create database hspipmptest"
pg_dump hspipmp | psql hspipmptest

权限管理

密码修改

ALTER USER {user name} WITH PASSWORD '123456';

修改/赋予权限

alter user {user name} superuser createrole createdb replication login;

修改数据库所有者

alter database {database name} OWNER TO {new user name};

进程管理

查询当前正在运行的访问到上述表的慢查询

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

查看连接数变化

select count( * ) from pg_stat_activity where state not like '%idle';

查询读取Buffer次数最多的SQL

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

查询并杀掉某个表的正在执行的sql进程

select
    pg_terminate_backend(pid),
    query
from
    pg_stat_activity
where
    query ~* 'ipmp_home_page_status_image'
    and pid <> pg_backend_pid();

查询最耗时的SQL

select * from pg_stat_statements order by total_time desc limit 5;

查出使用表扫描最多的表

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

su - postgres
psql
-- 查看所有postgres用户
\du

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='hspipmptest' AND pid<>pg_backend_pid();