需要vacuum的表：
select schemaname||'.'||relname, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
from pg_stat_user_tables
where n_dead_tup > n_live_tup/2;需要添加index的表：
select schemaname||'.'||relname, pg_size_pretty(pg_table_size(relid)) as table_size, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
from pg_stat_user_tables
where pg_table_size(relid) > 1024*1024*1024 and seq_scan>10000
order by seq_scan desc;重复创建的index：
SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
(array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
(array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM (
SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY
FROM pg_index) sub
GROUP BY KEY HAVING COUNT(*)>1
ORDER BY SUM(pg_relation_size(idx)) DESC;
or
select * from(
select tablespace,schemaname,tablename,indexname,pg_size_pretty(pg_table_size(schemaname||'."'||indexname||'"')) as index_size,indexdef,
count(1) over( partition by schemaname, tablename, regexp_replace(indexdef, E'(CREATE )(.*)(INDEX )(.+)( ON )(.+)',E'\\1\\3\\5\\6') )
from pg_indexes) as foo
where count > 1;表与索引的关系：
SELECT
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
CASE WHEN indisunique THEN 'Y' ELSE 'N' END AS UNIQUE,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid
JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;无效的index：
select pi.schemaname, pi.relname, pi.indexrelname, pg_size_pretty(pg_table_size(pi.indexrelid))
from pg_indexes pis
join pg_stat_user_indexes pi
on pis.schemaname = pi.schemaname
and pis.tablename = pi.relname
and pis.indexname = pi.indexrelname
left join pg_constraint pco
on pco.conname = pi.indexrelname
and pco.conrelid = pi.relid
where pi.schemaname='public'
and pco.contype is distinct from 'p' and pco.contype is distinct from 'u'
and (idx_scan,idx_tup_read,idx_tup_fetch) = (0,0,0)
and pis.indexdef !~ ' UNIQUE INDEX '
and pi.relname !~ 'backup$'
order by pg_table_size(indexrelid) desc;查看数据库中所有表的主键：
select table_schema||'.'||relname as tablename,attname as columname
from pg_constraint con,pg_class c,pg_attribute a,information_schema.columns col
where con.conrelid=c.oid
and a.attrelid=c.oid
and contype='p'
and a.attnum=con.conkey[1]
and col.table_name=c.relname
and a.attname=col.column_name;查看表及索引占用磁盘大小：
select schemaname||'.'||relname,pg_size_pretty(pg_total_relation_size(relid)) as all_size,pg_size_pretty(pg_table_size(relid)) as table_size,pg_size_pretty(pg_indexes_size(relid)) as all_indexes_size from pg_stat_user_tables order by pg_total_relation_size(relid) desc;
--增加tablespace
select ut.schemaname||'.'||ut.relname,pg_size_pretty(pg_total_relation_size(relid)) as all_size,pg_size_pretty(pg_table_size(relid)) as table_size,pg_size_pretty(pg_indexes_size(relid)) as all_indexes_size,ts.spcname from pg_class c left join pg_stat_user_tables ut on c.oid=ut.relid left join pg_tablespace ts on c.reltablespace=ts.oid left join pg_namespace ns on c.relnamespace=ns.oid where ns.nspname not in('pg_catalog', 'information_schema', 'pg_toast','pgq','pgq_node','pgq_ext','londiste') and c.relkind='r' order by pg_total_relation_size(relid) desc;查看索引大小及DDL：
select schemaname||'.'||relname,indexrelname,pg_size_pretty(pg_table_size(indexrelid)),pg_get_indexdef(indexrelid) from pg_stat_user_indexes order by pg_total_relation_size(indexrelid) desc;重置表或索引的使用率：
select pg_stat_reset_single_table_counters('object_name'::regclass);同时修改多个实例的hba文件：
ps aux | grep postgres | grep bin | grep data | egrep -v 'grep|pgbouncer'|awk -F 'D' '{print $2"/pg_hba.conf"}'|while read line; do echo $line;echo "host all all 127.0.0.1/32 md5" >> $line;pg_ctl -D `echo $line|awk -F 'pg_hba.conf' '{print $1}'` reload;cat $line;done随机数,时间,字符：
select generate_series(1,10),floor(random()*(25-10)+10),generate_series(now(),now() + '1 day', '1 hours'::interval) as timestamp,random(),random()::numeric(10,2) as numeric,round(random()*100::integer) as integer,lower(chr(int4(random()*26)+65))||chr(int4(random()*26)+65) as char,md5(random()::text) as text;执行超过3分钟的事物或者sql：
select pid,datname,usename,application_name,client_addr,backend_start,xact_start,query_start,(query_start - now()) as execute_time,state,query from pg_stat_activity where (((xact_start - now()) >= '00:03:00') or ((query_start - now()) >= '00:03:00')) and state != 'idle' order by (query_start - now()) desc;删除重复数据：
删除以col1,col2为分组的重复数据（逻辑取反方法）：
delete from tablename where id not in(select max(id) from tablename group by col1,col2);
按col1删除重复数据（适用于数据量较大，取反效果差的表）：
select * from tablename where id in(with tmp as (select id,col1 from (select id,col1,count(col1) over(partition by col1) as col1_count from tablename) as foo where col1_count>1)
select id from tmp where id not in(select max(id) from tmp group by msg_id));重建主键：
先创建唯一索引：
create unique index concurrently on tablename_backup(id);
删除原主键约束，在唯一索引上添加主键约束：
begin; alter table tablename_backup drop constraint tablename_backup_pkey1; alter table tablename_backup add PRIMARY KEY USING INDEX tablename_backup_id_idx;commit;为所有含有主键的表创建序列:
select case when array_length(conkey,1) !=1 then '--'||table_schema||'.'||relname||' 含有复合主键' else 'create sequence '||table_schema||'.'||relname||'_'||attname||'_seq owned by '||table_schema||'.'||relname||'.'||attname||';' end
from pg_constraint con,pg_class c,pg_attribute a,information_schema.columns col
where con.conrelid=c.oid
and a.attrelid=c.oid
and contype='p'
and a.attnum=con.conkey[1]
and col.table_name=c.relname
and a.attname=col.column_name
and col.column_default is null;为所有表的主键添加对应的序列:
select 'alter table '||table_schema||'.'||relname||' alter column '||attname||' set default nextval('''||table_schema||'.'||relname||'_'||attname||'_seq'');'
from pg_constraint con,pg_class c,pg_attribute a,information_schema.columns col
where con.conrelid=c.oid
and a.attrelid=c.oid
and contype='p'
and a.attnum=con.conkey[1]
and col.table_name=c.relname
and a.attname=col.column_name
and col.column_default is null;重置所有表的序列:
select case when array_length(conkey,1) !=1 then '--'||table_schema||'.'||relname||' 含有复合主键'
when table_schema||'.'||relname||'_'||attname||'_seq' in(select s.schemaname||'.'||s.relname from pg_statio_user_sequences s) then 'select setval('''||table_schema||'.'||relname||'_'||attname||'_seq'''||',coalesce(max('||attname||'),0)::bigint + 1) from '||table_schema||'.'||relname||';' else '--'||table_schema||'.'||relname||'_'||attname||'_seq 序列不存在，如想添加序列为主键的默认值，请执行: '||'create sequence '||table_schema||'.'||relname||'_'||attname||'_seq owned by '||table_schema||'.'||relname||'.'||attname||'; alter table '||table_schema||'.'||relname||' alter column '||attname||' set default nextval('''||table_schema||'.'||relname||'_'||attname||'_seq'');' end as col1
from pg_constraint con,pg_class c,pg_attribute a,information_schema.columns col
where con.conrelid=c.oid
and a.attrelid=c.oid
and contype='p'
and a.attnum=con.conkey[1]
and col.table_name=c.relname
and a.attname=col.column_name;生成按日期的sql：
按天生成:
select 'insert into tablename_backup select * from tablename where create_time>='''||a::timestamptz||'''and create_time<'''||a::timestamptz +'1 day'::interval||''';select pg_sleep(10);' from generate_series('2017-04-01'::date, '2017-06-30'::date, '1 day':: interval) as a ;
按月生成:
select 'insert into tablename_backup select * from tablename where create_time>='''||a::date||'''and create_time<'''||a::date + '1 month'::interval||''';select pg_sleep(10);' from generate_series('2017-04-01'::date, '2017-06-30'::date, '1 month':: interval) as a ;主从同步差异：
物理延时(主库执行):
select application_name,pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), replay_location)) as diff,state from pg_stat_replication;
时间延时(从库执行):
SELECT
now() AS now,
coalesce(pg_last_xact_replay_timestamp(), now()) replay,
extract(EPOCH FROM (now() - coalesce(pg_last_xact_replay_timestamp(), now()))) AS diff,
(SELECT regexp_replace(a,E'primary_conninfo.+(host.*=.*).*(port=\\d+).+',E'\\1 \\2', 'ig') AS master
FROM regexp_split_to_table(pg_read_file('recovery.conf'), E'\\n') t(a)
WHERE a ~ '^ *primary_conninfo');
--主从同步是否暂停
select pg_is_xlog_replay_paused();
--暂停主从同步
select pg_xlog_replay_pause();
--恢复主从同步
select pg_xlog_replay_resume();
--强制轮转一个WAL(xlog)文件,这在主从切换时可以用到
select pg_switch_xlog()关闭当前连接最多的sql：
系统级别,删除所有select
ps auxww | grep 'SELECT' | grep -v grep | awk '{print $2}' | xargs -I {} kill {}
数据库级别,先获取连接数最多的sql
select datname,usename,application_name,client_addr,query, count(query) from pg_stat_activity where state != 'idle' group by datname,usename,application_name,client_addr,query order by count(query) desc limit 5;
杀掉连接:
select pg_terminate_backend(pid) from pg_stat_activity where query in(select query from pg_stat_activity where state != 'idle' group by datname,usename,application_name,client_addr,query order by count(query) limit 1 );