Postgres调试

        级联删除表     truncate table tablename_notification_receiver CASCADE;
        pg配置文件/opt/pg/pgdata/postgresql.conf 
        查看活跃sql select * from pg_stat_activity where pid=xx;
        取消正在执行的任务： select pg_cancel_backend(pid) from pg_stat_activity where state='active';
        强制停止并取消正在执行的任务： select pg_terminate_backend(pid) from pg_stat_activity where state='active';
        
        查看正在执行的sql
            SELECT pid,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.pid AS pid,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 pgsa.STATE !='idle' AND pgsa.STATE !='idle in transaction' AND pgsa.STATE !='idle in transaction (aborted)') idleconnections ORDER BY query_stay DESC;

        查看进程jc情况  jstat -gcutil pid 

        删除重复数据
            delete from tablename a where a.id <> (select min(t.id) from tablename t where a.role_type_id=t.role_type_id and a.web_resource_id=t.web_resource_id and a.method=t.method);
        更改pg序列起始值 alter sequence name_id_seq restart with 200410;
            /usr/pgsql-10/bin/pg_ctl stop -D /data10/pgdata/
            /usr/pgsql-10/bin/pg_ctl start -D /data10/pgdata/
        导出数据库表
            psql -d dbname -U postgres -W COPY log_operation TO '/tmp/log.csv' WITH csv;

        SELECT pid,STATE,backend_start,xact_start,xact_stay,query_start,query_stay,
        REPLACE ( query, chr( 10 ), ' ' ) AS query 
        FROM (SELECT
        pgsa.pid AS pid,
        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 pgsa.STATE != 'idle' 
        AND pgsa.STATE != 'idle in transaction' 
        AND pgsa.STATE != 'idle in transaction (aborted)' ) idleconnections ORDER BY query_stay DESC;

        查询元祖和那些表需要清理 查询当前数据库表已经达到自动清理条件的表及相关信息
            SELECT c.relname 表名,        (current_setting('autovacuum_analyze_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_analyze_scale_factor')::NUMERIC(12,4))*reltuples AS 自动分析阈值,    (current_setting('autovacuum_vacuum_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_vacuum_scale_factor')::NUMERIC(12,4))*reltuples AS 自动清理阈值,
            reltuples::DECIMAL(19,0) 活元组数,
            n_dead_tup::DECIMAL(19,0) 死元组数
            FROM pg_class c 
            LEFT JOIN pg_stat_all_tables d
            ON C.relname = d.relname
            WHERE c.relname LIKE 'tablename%'  AND reltuples>0
            AND n_dead_tup> (current_setting('autovacuum_analyze_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_analyze_scale_factor')::NUMERIC(12,4))*reltuples;    
            
        查询当前正在进行自动清理的表及相关信息
            SELECT c.relname 对象名称,l.pid 进程id,psa.STATE 查询状态,psa.query 执行语句,now()-query_start 持续时间
            FROM pg_locks l INNER JOIN pg_stat_activity psa ON ( psa.pid = l.pid )
            LEFT OUTER JOIN pg_class C ON ( l.relation = C.oid ) 
            WHERE psa.query like 'autovacuum%' and l.fastpath='f' ORDER BY query_start asc;

        数据表收缩
            VACUUM VERBOSE 表名;
            VACUUM VERBOSE ANALYZE 表名;
            select * from pg_stat_progress_vacuum;

        告警分区表添加索引
            insert into public.system_partition_table_info("table_name","idx_key","idx_cols","trigger_func","info_type") values ('alarm_event','ueid','ueid','',1);
            
        复制表结构 CREATE TABLE emp3  (LIKE employee);
        复制表内容 insert into emp4 select * from employee;
        删除索引：drop index if exists indexname;
        查看当前扩展
            psql#\dx
            psql#select * from pg_available_extensions;

        pg内存相关配置项
            max_connections = 1000
            shared_buffers=2GB #--1/4物理内存
            temp_buffers=8MB #--默认值
            work_mem=4MB #--默认值
            wal_buffers=16MB #--with-wal-segsize的默认值
            autovacuum_max_workers = 3 #--默认值
            maintenance_work_mem=2GB  #--vacuum，create index等维护工作内存
            autovacuum_work_mem = 1GB #–autovacuum清理进程，总耗内存：autovacuum_max_workers*autovacuum_work_mem        

      pg配置参数说明参考文档：https://postgresqlco.nf/doc/zh/param/

        pg内存计算公式：max_connections * work_mem + max_connections * temp_buffers +shared_buffers * 2 + autovacuum_max_workers * autovacuum_work_mem + maintenance_work_mem + wal_buffers  + （linux运行最小要求的内存(GB)+ 其它应用内存(GB)) <  服务器物理内存
        监控查看缓存， 可以看到将数据加载至shared_buffers
            #create extension pg_buffercache;
            # SELECT c.relname,pg_size_pretty(count(*) * 8192) as buffered, round(100.0 * count(*) /(SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1)AS buffers_percent, round(100.0 * count(*) * 8192 /pg_relation_size(c.oid),1)AS percent_of_relation FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) GROUP BY c.oid,c.relname ORDER BY 3 DESC LIMIT 10;
            
        使用5433 端口启动数据库时清理共享内存问题
        sudo -u postgres PGPORT=5433 /usr/pgsql-9.4/bin/pg_ctl -D "/data10/pgdata" start -w
        注意：上面这个命令的 -D 参数后面的 "/data10/pgdata" 。这个值在 3061，3062中是 “/data10/pgdata”，但是在 3063中是 "/data/pgdata"。
        如果在使用5433 端口的时候报错了，那么大概率是信号量和共享内存出了问题，需要清理一下共享内存。清理完了共享内存，再执行上面启动数据库的命令。
        semids=$(ipcs -s | grep -E 'postgres' | head -n 15 | awk '{print $1}')
        for semid in ${semids} ; do 
            echo "delete semaphore : [${semid}]"
            ipcrm -S "${semid}"
        done
        shmkeys=$(ipcs -m | grep -E 'postgres' | awk '{print $1}')
        for shmkey in ${shmkeys} ; do 
            echo "delete shared memory : [${shmkey}]"
            ipcrm -M ${shmkey}
        done