PostgreSQL常用操作命令

需要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 );