pg_archivecleanup -d $PGDATA/pg_xlog 000000140000431A0000001A会删除所有早于000000140000431A0000001A的归档日志

PG维护

select pid ,(now() - xact_start) as time , state,query::char(120) from pg_stat_activity where state != 'idle' order by time desc ;


--SET statement_timeout =xxx;
select * from pg_stat_activity where pid=41435;  
select pg_terminate_backend(48536) --杀掉对应执行语句
select pg_terminate_backend(pid) from pg_stat_activity where  query like '%SELECT%' and pid != pg_backend_pid(); --批量kill

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname='sxsdb'
  AND state = '%idle in transaction%'--杀死所有空闲事务

SELECT * FROM pg_stat_activity WHERE datname='sxsdb' and waiting='t'; --查看锁表

SELECT * FROM pg_stat_activity where wait_event_type = 'Lock'; --查看锁表
select oid from pg_class where relname='intern_stream_good' --查看可能锁了的表
select pid from pg_locks where relation='49509707' --上面查出的oid


SELECT oversea ID FROM   user_base_info  WHERE oversea IS NULL

UPDATE user_base_info SET oversea=2 WHERE oversea IS NULL
SELECT datname, age(datfrozenxid) FROM pg_database; --查询库xid


SELECT c.oid::regclass as table_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') order by age desc;--查询表xid


SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relkind = 'r' and pg_table_size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 20;


select pg_size_pretty(pg_relation_size('resume_deliver'));

--连接数
show max_connections; 
select count(1) from pg_stat_activity;

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;

select client_addr,sync_state from pg_stat_replication;--查询从库同步
select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
select pg_xlog_location_diff(sent_location, replay_location) from pg_stat_replication; 

 select * from pg_stat_replication ;--查看从库延迟
 
 SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS replication_lag;--延迟时间
 
 select client_addr,application_name, pg_xlog_location_diff(pg_current_xlog_location(), 
replay_location) as diff from pg_stat_replication;



SELECT
  pg_last_xlog_receive_location() receive,
  pg_last_xlog_replay_location() replay,
  (
   extract(epoch FROM now()) -
   extract(epoch FROM pg_last_xact_replay_timestamp())
  )::int lag;  ---查看延迟
	
	
select pg_current_xlog_insert_location();  --查看xlog位置


--查看锁的sql

SELECT locker.pid,  
        pc.relname,  
        locker.mode,  
        locker_act.application_name,  
        least(query_start,xact_start) start_time,  
        locker_act.state,  
        CASE  
    WHEN granted='f' THEN  
    'wait_lock'  
    WHEN granted='t' THEN  
    'get_lock'  
    END lock_satus,current_timestamp - least(query_start,xact_start) AS runtime,  
    locker_act.query  
FROM pg_locks locker,pg_stat_activity locker_act, pg_class pc  
WHERE locker.pid=locker_act.pid  
        AND NOT locker.pid=pg_backend_pid()  
        AND application_name<>'pg_statsinfod'  
        AND locker.relation = pc.oid  
        AND pc.reltype<>0 --and pc.relname='t' 
ORDER BY  runtime desc;




--------
SELECT locktype,
        pg_locks.pid,
         virtualtransaction,
         transactionid,
         nspname,
         relname,
         mode,
         granted,
    CASE
    WHEN granted='f' THEN
    'get_lock'
    WHEN granted='t' THEN
    'wait_lock'
    END lock_satus,
    CASE
    WHEN waiting='f' THEN
    'already get lock,sql executing'
    WHEN waiting='t' THEN
    'waiting get lock,sql waiting execute'
    END lock_satus,
 current_timestamp - least(query_start,xact_start) AS runtime,
 cast(date_trunc('second',query_start) AS timestamp) AS query_start, substr(query,1,25) AS query
FROM pg_locks LEFT OUTER
JOIN pg_class
    ON (pg_locks.relation = pg_class.oid) LEFT OUTER
JOIN pg_namespace
    ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity
WHERE NOT pg_locks.pid=pg_backend_pid()
        AND pg_locks.pid=pg_stat_activity.pid
        AND pg_class.relname='t' --此处进行替换
ORDER BY  query_start;


select substring(, 1, 2)||'******'||substring(username, length(username), 1) AS username , substring(tel, 1, 3)||'****'||substring(tel, length(tel) -3, 4) as tel,* from users  LIMIT 10

SELECT CONCAT(LEFT(username, 2), '****', RIGHT(username, 1)) FROM users  LIMIT 10

SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes


--vacuum full intern_stream_good

select count(*) from user_base_info;  --表数据
select pg_size_pretty(pg_relation_size('resume_deliver')); --表容量
select * from pg_stat_user_tables where relname = 'resume';--查看表的状态n_live_tup的数量是当前表的数据量。n_dead_tup的数据量是未回收的空间。
--vacuum resume;
--数据库大小
SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,  
   CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')  
       THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))  
       ELSE 'No Access'  
   END AS SIZE  
FROM pg_catalog.pg_database d  
   ORDER BY  
   CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')  
       THEN pg_catalog.pg_database_size(d.datname)  
       ELSE NULL  
   END DESC 
   LIMIT 20
	 
	 
	 SELECT c.oid:犀利士
:regclass as table_name,
     greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm'); -- 查询当前所有表的年龄