PostgreSQL【爬坑】常见问题

慢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 1=1
	and pgsa.state != 'idle' 
	and pgsa.state != 'idle in transaction' 
	and pgsa.state != 'idle in transaction (aborted)'
) idleconnections 
order by 
query_stay desc 
limit 
10;

主从状态

root权限下

select * from pg_stat_replication; 

读库下

select now() - pg_last_xact_replay_timestamp() as replication_delay;

复制流问题

从报错来看，执行更新和删除操作，需要用ALTER TABLE设置REPLICA IDENTITY

如果一张表缺少主键，并且涉及到复制，就需要执行这条语句

ALTER TABLE parking_record_away_202007 REPLICA IDENTITY FULL;