PostgreSQL_note2025

SELECT pg_get_functiondef(p.oid) AS ddl_definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = ‘ods’ – 模式名
AND p.proname = ‘xxxxxxxx’; – 存储过程名

–表信息
select c.relname table_name,
nsp.nspname schema_name, cmt.description as description,
c.reltuples::bigint num_rows,
pg_total_relation_size(c.oid) / 1048576 storeCapacity,
coalesce(pg_stat_get_last_analyze_time(c.oid),
pg_stat_get_last_autoanalyze_time(c.oid)) createTime,
coalesce(pg_stat_get_last_vacuum_time(c.oid),
pg_stat_get_last_autovacuum_time(c.oid)) last_ddl_time,
current_setting(‘server_encoding’)::varchar as encoding,
CASE WHEN c.relkind = ‘f’ THEN ‘FOREIGN’ ELSE ‘NORMAL’ END as tableType
from pg_namespace nsp, pg_class c
left join pg_description cmt
on c.oid = cmt.objoid
and cmt.objsubid = 0
where c.relkind in (‘r’, ‘p’, ‘f’)
and c.relnamespace = nsp.oid
– and nsp.nspname = $1
–and c.relname in ($2)
order by c.relname;

–DB size
SELECT
datname AS database_name,
pg_size_pretty(pg_database_size(datname)) AS pretty_size,
pg_database_size(datname) AS raw_size
FROM pg_database
WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;

SELECT
schemaname AS schema_name,
tablename AS table_name,
n_tup_ins - n_tup_del AS estimated_row_count
FROM pg_stat_user_tables
ORDER BY n_tup_ins - n_tup_del DESC;

–活动会话
SELECT
pid,
usename AS username,
application_name,
client_addr AS client_ip,
client_hostname,
client_port,
backend_start,
state,
state_change,
query
FROM pg_stat_activity
ORDER BY backend_start;

SELECT pg_terminate_backend(1177582);

SELECT
COUNT() AS total_sessions, – 所有会话总数
COUNT() FILTER (WHERE state = ‘active’) AS active_sessions – 活动中的会话数（正在执行SQL）
FROM pg_stat_activity;

– 查询数据库级别的事务和查询统计信息
SELECT
datname AS database_name,
xact_commit + xact_rollback AS total_transactions,
tup_returned + tup_fetched + tup_inserted + tup_updated + tup_deleted AS total_queries,
stats_reset
FROM pg_stat_database
WHERE datname NOT IN (‘template0’, ‘template1’, ‘postgres’) – 可根据需要调整
ORDER BY total_queries DESC;

–两次计算差值

–当前所有正在执行的查询
SELECT
pid AS process_id,
usename AS username,
application_name,
client_addr AS client_ip,
client_hostname,
client_port,
backend_start,
xact_start AS transaction_start,
query_start,
state_change,
state,
round((EXTRACT(EPOCH FROM now()) - EXTRACT(EPOCH FROM query_start))::numeric, 3) AS duration_seconds,
query
FROM pg_stat_activity
WHERE state != ‘idle’ – 排除空闲连接
AND pid != pg_backend_pid() – 排除当前会话自身
AND query NOT ILIKE ‘%pg_stat_activity%’ – 避免自查询干扰
ORDER BY query_start;

–kill query
SELECT pg_cancel_backend(1803909);
SELECT pg_cancel_backend(1804307);

–kill session
SELECT pg_terminate_backend(12345);

–查看所有 SQL 的调用次数和平均响应时间
SELECT
query,
calls AS execution_count,
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

#############
SELECT * FROM pg_roles;

SELECT
d.classid::regclass AS object_class,
d.objid,
d.objsubid,
pg_describe_object(d.classid, d.objid, d.objsubid) AS object_description,
d.deptype
FROM pg_depend d
WHERE d.refobjid = (SELECT oid FROM pg_roles WHERE rolname = ‘svc_etl_inflow’)
AND d.deptype = ‘o’;

SELECT *
FROM pg_default_acl d
JOIN pg_roles r ON r.oid = d.defaclrole
WHERE r.rolname = ‘svc_etl_inflow’;

– 查看是否有 default privileges 引用该角色
SELECT
COALESCE(nspname, ‘global’) AS schema,
defaclobjtype,
pg_get_userbyid(defacluser) AS owner,
defaclacl
FROM pg_default_acl a
LEFT JOIN pg_namespace n ON n.oid = a.defaclnamespace
WHERE defacluser = (SELECT oid FROM pg_roles WHERE rolname = ‘svc_etl_inflow’);

– 兼容 PG 15+ 的写法：检查 pg_default_acl 是否引用 svc_etl_inflow
SELECT
COALESCE(n.nspname, ‘global’) AS schema_name,
d.defaclobjtype AS applies_to_object_type,
d.defaclacl AS acl_setting
FROM pg_default_acl d
LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace
WHERE d.defaclrole = (SELECT oid FROM pg_roles WHERE rolname = ‘svc_etl_inflow’);

– 清除该角色设置的所有 default privileges
ALTER DEFAULT PRIVILEGES FOR ROLE svc_etl_inflow REVOKE ALL ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE svc_etl_inflow REVOKE ALL ON SEQUENCES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE svc_etl_inflow REVOKE ALL ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE svc_etl_inflow REVOKE ALL ON TYPES FROM PUBLIC;

DROP OWNED BY svc_etl_inflow;
drop role svc_etl_inflow ;

#############

update “evolution_cli_client”
set cli_name=LEFT(cli_name, 1) || REPEAT(‘*’, LENGTH(cli_name) - 2) || RIGHT(cli_name, 1);

SELECT now()::timestamp + ‘1 year’; --当前时间加1年
SELECT now()::timestamp + ‘1 month’; --当前时间加一个月
SELECT now()::timestamp + ‘1 day’; --当前时间加一天
SELECT now()::timestamp + ‘1 hour’; --当前时间加一个小时

show shared_preload_libraries;

CREATE EXTENSION pg_cron;

GRANT USAGE ON SCHEMA cron TO other-user;

Grant all on xxx to Mark_dba with grant option;
grant UPDATE on cron.job to ;

grant all ON xxx to xxxadmin with grant option;

ALTER ROLE test_val VALID UNTIL ‘2024-09-01’;

select rolname,rolvaliduntil from pg_roles;

CREATE TABLE tablename (
colname SERIAL
);
等价于以下语句：
CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval(‘tablename_colname_seq’)
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

CREATE OR REPLACE PROCEDURE archive_db.name()
LANGUAGE sql
ASprocedureprocedureprocedure
BEGIN

END;

procedureprocedureprocedure

#################
create schema archive_db;

create table archive_db.proce_log(id serial,message varchar(120),err_date timestamp);

create table archive_db.huazhudb_t_fnol as (
select *,now() ach_date from huazhu_claim_db.t_fnol where 1=2);

create table archive_db.inscldb_t_fnol as (
select *,now() ach_date from insurer_claim_db.t_fnol where 1=2);

CREATE OR REPLACE PROCEDURE archive_db.archive_10ydata()
LANGUAGE plpgsql
as $$
declare
BEGIN

insert into archive_db.huazhudb_t_fnol(
select *,now() from huazhu_claim_db.t_fnol where last_modified_time <date_trunc(‘day’,now())::timestamp + ‘-10 year’);
delete from huazhu_claim_db.t_fnol where t_fnol_id in
(select t_fnol_id from archive_db.huazhudb_t_fnol
where ach_date >= date_trunc(‘day’,now()) ) ;

insert into archive_db.inscldb_t_fnol(
select *,now() from insurer_claim_db.t_fnol where last_modified_time <date_trunc(‘day’,now())::timestamp + ‘-10 year’);
delete from insurer_claim_db.t_fnol where t_fnol_id in
(select t_fnol_id from archive_db.inscldb_t_fnol
where ach_date >= date_trunc(‘day’,now()) ) ;

EXCEPTION
when others THEN
raise notice ‘Exception occurred’;
insert into archive_db.proce_log(message,err_date) values (substring(SQLERRM,1,100), now()) ;
END;
$$;

call archive_db.archive_10ydata();

CREATE INDEX idx_huazhudb_t_fnol_id ON archive_db.huazhudb_t_fnol (t_fnol_id,ach_date);
CREATE INDEX idx_inscldb_t_fnol_id ON archive_db.inscldb_t_fnol (t_fnol_id,ach_date);

CREATE EXTENSION pg_cron;

alter system set shared_preload_libraries=‘pg_cron’;
restart

GRANT USAGE ON SCHEMA cron TO mark_dba;
grant all ON schema public TO mark_dba ;
GRANT SELECT ON ALL TABLES IN SCHEMA cron TO mark_dba;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA cron TO mark_dba;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA cron TO mark_dba;

grant all ON xxx to xxxadmin with grant option;

grant all ON xxx to xxxADMIN with grant option;

select cron.schedule (
‘archive10years_personinfo’, – name of the cron job
'0 1 * * * ', – everyday 1:00am (GMT)
callarchivedb.archive10ydata(); call archive_db.archive_10ydata();callarchived​b.archive1​0ydata();
);

SELECT cron.schedule_in_database(
‘archive10years_personinfo’,
'30 * * * * ',
callarchivedb.archive10ydata(); call archive_db.archive_10ydata();callarchived​b.archive1​0ydata();,
‘cnteplatform’
);

SELECT cron.unschedule(7);

SELECT cron.schedule_in_database(
‘archive10years_personinfo’,
'10 * * * * ',
’ call lgyarchive_db.lgyarchive_10ydata(); ',
‘cnteplatformlgy’
);

select * from cron.job;

UPDATE cron.job SET database = ‘archive_db’ WHERE jobid = 106;

SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE ‘cron.%’ ORDER BY name;

#################
create schema archive_db;

create table archive_db.proce_log(id serial,message varchar(120),err_date timestamp);

create table archive_db.arc_tb_list (
id SERIAL,
table_name varchar(80),
date_column varchar(200),
info_column_1 varchar(80),
info_column_2 varchar(80),
info_column_3 varchar(80),
info_column_4 varchar(80),
info_column_5 varchar(80),
info_column_6 varchar(80),
info_column_7 varchar(80),
info_column_8 varchar(80),
info_column_9 varchar(80),
info_column_10 varchar(80),
info_column_11 varchar(80),
info_column_12 varchar(80),
info_column_13 varchar(80),
info_column_14 varchar(80),
info_column_15 varchar(80),
key_column varchar(80)
);

insert into archive_db.arc_tb_list(table_name,date_column,info_column_1,info_column_2,key_column)
values (‘jks_claim_db.t_case’,‘last_modified_time’,‘third_party_name’,‘third_party_id_no’,‘case_no’);

insert into archive_db.arc_tb_list(table_name,date_column,info_column_1,info_column_2,info_column_3,
info_column_4,info_column_5,info_column_6,info_column_7,info_column_8,key_column)
values (‘jks_claim_db.t_fnol’,‘last_modified_time’,‘property_owner_name’,‘property_owner_id’,‘fnol_name’
,‘fnol_phone’,‘fnol_email’,‘contact_name’,‘contact_phone’,‘property_address’,‘fnol_no’);

select * from archive_db.arc_tb_list;

insert into jks_claim_db.t_fnol (fnol_name,property_owner_id,property_owner_name,last_modified_time,fnol_phone,fnol_email,contact_name)
values(‘10年过期测试1’,‘22’,‘10年过期测试1’,‘2014-11-14’,‘1380001111108’,‘xxxx@xxx.com’,‘联系人xx’);

create table archive_db.jks_claim_db_t_case as (
select *,now() ach_date from jks_claim_db.t_case where 1=2);

create table archive_db.jks_claim_db_t_fnol as (
select *,now() ach_date from jks_claim_db.t_fnol where 1=2);

call archive_db.archive_10ydata();

CREATE EXTENSION pg_cron;

select cron.schedule (
‘archive10years_personinfo’, – name of the cron job
'0 1 * * * ', – everyday 1:00am (GMT)
callarchivedb.archive10ydata(); call archive_db.archive_10ydata();callarchived​b.archive1​0ydata();
);

SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE ‘cron.%’ ORDER BY name;

CREATE OR REPLACE PROCEDURE archive_db.archive_10ydata()
LANGUAGE plpgsql
ASprocedureprocedureprocedure
declare
arc_tb record;
arc_tb_name text;
dynamic_sql1 text;
dynamic_sql2 text;
dynamic_sql3 text;
BEGIN

for arc_tb in select * from archive_db.arc_tb_list loop
arc_tb_name := replace(arc_tb.table_name,‘.’,‘_’);
dynamic_sql1 := format('insert into archive_db.%L(%L,%L,%L,%L,%L,%L,%L,%L,%L,%L,%L,%L,%L,%L,%L,%L,ach_date)
(select %L,%L,%L,%L,%L,%L,%L,%L,%L,%L,%L,%L,%L,%L,%L,%L ,now() from %L where %L <> ‘‘Expired_dateled’’ and %L ',
arc_tb_name,arc_tb.info_column_1,
arc_tb.info_column_2,
arc_tb.info_column_3,
arc_tb.info_column_4,
arc_tb.info_column_5,
arc_tb.info_column_6,
arc_tb.info_column_7,
arc_tb.info_column_8,
arc_tb.info_column_9,
arc_tb.info_column_10,
arc_tb.info_column_11,
arc_tb.info_column_12,
arc_tb.info_column_13,
arc_tb.info_column_14,
arc_tb.info_column_15,arc_tb.key_column,
arc_tb.info_column_1,
arc_tb.info_column_2,
arc_tb.info_column_3,
arc_tb.info_column_4,
arc_tb.info_column_5,
arc_tb.info_column_6,
arc_tb.info_column_7,
arc_tb.info_column_8,
arc_tb.info_column_9,
arc_tb.info_column_10,
arc_tb.info_column_11,
arc_tb.info_column_12,
arc_tb.info_column_13,
arc_tb.info_column_14,
arc_tb.info_column_15,arc_tb.key_column,
arc_tb.table_name,arc_tb.info_column_1,arc_tb.date_column);

dynamic_sql2 := format(‘update %L set %L=’‘Expired_dateled’‘,%L=’‘Expired_dateled’‘,%L=’‘Expired_dateled’’
,%L=‘‘Expired_dateled’’,%L=‘‘Expired_dateled’’,%L=‘‘Expired_dateled’’,%L=‘‘Expired_dateled’’,%L=‘‘Expired_dateled’’,%L=‘‘Expired_dateled’’,%L=‘‘Expired_dateled’’,%L=‘‘Expired_dateled’’,%L=‘‘Expired_dateled’’,%L=‘‘Expired_dateled’’,%L=‘‘Expired_dateled’’,%L=‘‘Expired_dateled’’ where %L ’
,arc_tb.table_name,arc_tb.info_column_1
,arc_tb.info_column_2
,arc_tb.info_column_3
,arc_tb.info_column_4
,arc_tb.info_column_5
,arc_tb.info_column_6
,arc_tb.info_column_7
,arc_tb.info_column_8
,arc_tb.info_column_9
,arc_tb.info_column_10
,arc_tb.info_column_11
,arc_tb.info_column_12
,arc_tb.info_column_13
,arc_tb.info_column_14
,arc_tb.info_column_15,arc_tb.date_column);

dynamic_sql1 :=replace(dynamic_sql1,‘’‘’,‘’)||’ <date_trunc(‘‘day’’,now())::timestamp + ‘’-10 year’‘);’;
dynamic_sql1 :=replace(dynamic_sql1,‘,NULL’,‘’);
dynamic_sql1 :=replace(dynamic_sql1,‘Expired_dateled’,‘’‘Expired_dateled’‘’);

dynamic_sql2 :=replace(dynamic_sql2,‘’‘’,‘’)||’ <date_trunc(‘‘day’’,now())::timestamp + ‘’-10 year’‘;’;
dynamic_sql2 :=replace(dynamic_sql2,‘,NULL=Expired_dateled’,‘’);
dynamic_sql2 :=replace(dynamic_sql2,‘Expired_dateled’,‘’‘Expired_dateled’‘’);

RAISE NOTICE ‘SQL1:%’,dynamic_sql1;
RAISE NOTICE ‘SQL2:%’,dynamic_sql2;

execute dynamic_sql1;
execute dynamic_sql2;

end loop;

EXCEPTION
when others THEN
raise notice ‘Exception occurred’;
insert into archive_db.proce_log(message,err_date) values (substring(SQLERRM,1,100), now()) ;
END;
procedureprocedureprocedure
;

call archive_db.archive_10ydata();

insert into legacy_db.tbl_policy_employee_detail (id,employee_name,certificate_type,certificate_number,lastmodified)
values(1,‘10年过期测试1’,‘id_card’,‘1002222001000112222’,‘2014-11-15’);

insert into legacy_db.dgx_user (id,username,real_name,last_login_time)
values(159909,‘test111’,‘10年过期测试1’,‘2014-11-15’);

insert into legacy_db.tbl_customer(id,user_id,customer_name,post_code,lastmodified)
values(2,159909,‘10年过期测试1’,‘x12313123x’,‘2014-11-15’);

insert into jks_claim_db.t_case (creator,case_no,created_time,last_modified_time,third_party_name,third_party_id_no)
values(‘22’,‘11122’,‘2014-11-14’,‘2014-11-15’,‘10年过期测试1’,‘1002222001000112222’);
select * from jks_claim_db.t_case;

insert into jks_claim_db.t_fnol (property_address,created_time,fnol_no,creator,case_no,fnol_name,property_owner_id,property_owner_name,last_modified_time,fnol_phone,fnol_email,contact_name)
values(‘shanghai pudong xxxxx’,‘2014-11-14’,‘333’,‘22’,‘11122’,‘10年过期测试1’,‘22’,‘10年过期测试1’,‘2014-11-14’,‘1380001111108’,‘xxxx@xxx.com’,‘联系人xx’);

select * from jks_claim_db.t_fnol;
call archive_db.archive_10ydata();

select * from archive_db.jks_claim_db_t_case;
select * from archive_db.jks_claim_db_t_fnol;
#################

ALTER TABLE xxxxx.t_xxxx OWNER TO xxx_dba;

CREATE EXTENSION IF NOT EXISTS dblink;
SELECT dblink_connect(‘local_connect’,‘hostaddr=127.0.0.1 port=5432 dbname=xxxx user=xxxx password=xxxx’) as dev;

select dblink_get_connections();
–创建DBNLINK 连接
SELECT dblink_connect(‘lgy_connect’,‘hostaddr=127.0.0.1 port=5432 dbname=cnueplatformlgy user=appuser password=xxxxxx’) as uat;
–获取连接名
select dblink_get_connections();
–通过dblink跨库访问，以legacy_db.dgx_user表为例
select * from dblink(‘lgy_connect’,‘select id ,username,real_name from legacy_db.dgx_user’) as t(id numeric,username varchar(32),real_name varchar(256));

###############

UPDATE qcc2003_oridata
SET ori_text = TRIM(BOTH ‘"’ FROM ori_text) where id >9474;

UPDATE qcc2003_oridata set
ori_json=ori_text::json
where create_time>current_date-1 and id > 9467;

SELECT ori_json, (ori_json -> ‘Result’ -> ‘Data’ ->> ‘KeyNo’) AS keyno，
(ori_json -> ‘Result’ -> ‘Data’ ->> ‘Name’) AS name,
(ori_json -> ‘Result’ -> ‘Data’ -> ‘ChangeList’) AS ChangeList
FROM qcc2003_oridata where create_time>current_date-1 and id > 9467;

update qcc2003_oridata set ChangeList=(ori_json -> ‘Result’ -> ‘Data’ -> ‘ChangeList’)
where create_time>current_date-1 and id > 9467;

##################

create schema smartdata01;

CREATE ROLE “svc_etl01” CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD ‘xxxxxx’ CONNECTION LIMIT 100;

GRANT USAGE ON SCHEMA smartdata01 to svc_etl01;

grant select,update,delete,insert,truncate on inflow_increment_regno to svc_etl01;

grant USAGE on seq_qcc886_oridata to svc_etl01;
grant USAGE on seq_qcc2003_oridata to svc_etl01;

update qcc886_oridata set ori_json=ori_text::json;
UPDATE qcc886_oridata set ori_jsonB=ori_json;

select * from pg_roles;

alter table qcc2003_oridata add COLUMN creditcode varchar(50);

#####################
SELECT ori_jsonb ->> ‘keyno’ AS keyno, ori_jsonb ->> ‘name’ AS “name”,ori_jsonb ->> ‘orgno’ AS orgno FROM qcc2003_oridata;

SELECT ori_json ->> ‘Status’ AS Status, ori_jsonb ->> ‘Message’ AS Message,ori_jsonb ->> ‘OrderNumber’ AS OrderNumber FROM qcc886_oridata;

UPDATE qcc2003_oridata
SET ori_text = TRIM(BOTH ‘"’ FROM ori_text) where ;
UPDATE qcc2003_oridata set ori_json=ori_text::json where create_time>current_date-1 ;
update qcc2003_oridata set ChangeList=(ori_json -> ‘Result’ -> ‘Data’ -> ‘ChangeList’) where create_time>current_date-1 ;