插件

-- uuid_generate_v4不存在,怎么解决
CREATE EXTENSION "uuid-ossp";



复制

槽 复制槽

(postgresql)postgres=# select * from pg_replication_slots;
        slot_name        |    plugin     | slot_type | datoid |     database     | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size 
-------------------------+---------------+-----------+--------+------------------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------
 ex38b4_dec42125ced8ec8c | test_decoding | logical   |  16384 | beacon2_capsm_db | f         | f      |            |      |    105607169 | C/82F46928  | C/82F46960          | extended   |              
 ex4f13_4dc611d57469d205 | test_decoding | logical   |  16384 | beacon2_capsm_db | f         | f      |            |      |    223976279 | 24/7F88E180 | 24/7FA18478         | extended   |              
(2 rows)

(postgresql)postgres=# SELECT * FROM pg_drop_replication_slot('ex38b4_dec42125ced8ec8c');
 pg_drop_replication_slot 
--------------------------
 
(1 row)


(postgresql)postgres=# SELECT * FROM pg_drop_replication_slot('ex4f13_4dc611d57469d205');
 pg_drop_replication_slot 
--------------------------
 
(1 row)

(postgresql)postgres=# select * from pg_replication_slots;
 slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size 
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------
(0 rows)


复制

pg 锁阻塞

\x
with
t_wait as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted
),
t_run as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted
),
t_overlap as
(
select r.* from t_wait w join t_run r on
(
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and
r.virtualxid is not distinct from w.virtualxid and
r.transactionid is not distinct from w.transactionid and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid and
r.pid <> w.pid
)
),
t_unionall as
(
select r.* from t_overlap r
union all
select w.* from t_wait w
)
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,
string_agg(
Pid:||case when pid is null then NULL else pid::text end||chr(10)||
Lock_Granted: ||case when granted is null then NULL else granted::text end|| , Mode: ||case when mode is null then NULL else mode::text end|| , FastPath: ||case when fastpath is null then NULL else fastpath::text end|| , VirtualTransaction: ||case when virtualtransaction is null then NULL else virtualtransaction::text end|| , Session_State: ||case when state is null then NULL else state::text end||chr(10)||
Username: ||case when usename is null then NULL else usename::text end|| , Database: ||case when datname is null then NULL else datname::text end|| , Client_Addr: ||case when client_addr is null then NULL else client_addr::text end|| , Client_Port: ||case when client_port is null then NULL else client_port::text end|| , Application_Name: ||case when application_name is null then NULL else application_name::text end||chr(10)||
Xact_Start: ||case when xact_start is null then NULL else xact_start::text end|| , Query_Start: ||case when query_start is null then NULL else query_start::text end|| , Xact_Elapse: ||case when (now()-xact_start) is null then NULL else (now()-xact_start)::text end|| , Query_Elapse: ||case when (now()-query_start) is null then NULL else (now()-query_start)::text end||chr(10)||
SQL (Current SQL in Transaction): ||chr(10)||
case when query is null then NULL else query::text end,
chr(10)||--------||chr(10)
order by
( case mode
when INVALID then 0
when AccessShareLock then 1
when RowShareLock then 2
when RowExclusiveLock then 3
when ShareUpdateExclusiveLock then 4
when ShareLock then 5
when ShareRowExclusiveLock then 6
when ExclusiveLock then 7
when AccessExclusiveLock then 8
else 0
end  ) desc,
(case when granted then 0 else 1 end)
) as lock_conflict
from t_unionall
group by
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;


---
-[ RECORD 1 ]-+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
locktype      | relation
datname       | common2_glory_db
relation      | 16573
page          |
tuple         |
virtualxid    |
transactionid |
classid       |
objid         |
objsubid      |
lock_conflict | Pid: 338528                                                                                                                                                              +
              | Lock_Granted: true , Mode: AccessExclusiveLock , FastPath: false , VirtualTransaction: 6/51 , Session_State: idle in transaction                                         +
              | Username: common2glorydev , Database: common2_glory_db , Client_Addr: 10.62.101.144/32 , Client_Port: 10562 , Application_Name: DBeaver 22.1.0 - SQLEditor <Script-6.sql>+
              | Xact_Start: 2023-07-24 16:28:20.090283+08 , Query_Start: 2023-07-24 16:28:45.679311+08 , Xact_Elapse: 00:07:59.119982 , Query_Elapse: 00:07:33.530954                    +
              | SQL (Current SQL in Transaction):                                                                                                                                        +
              | SHOW search_path                                                                                                                                                         +
              | --------                                                                                                                                                                 +
              | Pid: 339423                                                                                                                                                              +
              | Lock_Granted: false , Mode: AccessShareLock , FastPath: false , VirtualTransaction: 11/15 , Session_State: active                                                        +
              | Username: common2glorydev , Database: common2_glory_db , Client_Addr: 10.62.96.83/32 , Client_Port: 9191 , Application_Name: DBeaver 21.3.1 - Metadata <common2_glory_db>+
              | Xact_Start: 2023-07-24 16:29:23.998955+08 , Query_Start: 2023-07-24 16:29:23.999152+08 , Xact_Elapse: 00:06:55.21131 , Query_Elapse: 00:06:55.211113                     +
              | SQL (Current SQL in Transaction):                                                                                                                                        +
              | select c.oid,pg_catalog.pg_total_relation_size(c.oid) as total_rel_size,pg_catalog.pg_relation_size(c.oid) as rel_size                                                   +
              | FROM pg_class c                                                                                                                                                          +
              | WHERE c.relnamespace=$1



SELECT 
    a.pid,
    locktype,
    database,
    relation,
    mode,
    granted,
    a.pid AS blocked_pid,
    now() - query_start AS wait_duration
FROM 
    pg_locks l
JOIN 
    pg_stat_activity a ON l.pid = a.pid
WHERE 
    NOT granted;

SELECT 
    a.datname,
    a.pid AS blocked_pid,
    a.query AS blocked_query,
    b.pid AS blocking_pid,
    b.query AS blocking_query
FROM 
    pg_stat_activity a
JOIN 
    pg_locks bl ON a.pid = bl.pid
JOIN 
    pg_locks tl ON bl.transactionid = tl.transactionid AND bl.pid != tl.pid
JOIN 
    pg_stat_activity b ON tl.pid = b.pid
WHERE 
    NOT bl.granted AND bl.mode = 'ExclusiveLock' AND tl.granted;




复制

pg 等待类型 锁

2023-09-21 16:35:16.916 CST,"eliteepicqa","elite_epic_db",3624880,"26.8.0.193:53996",650c0043.374fb0,4,
"DROP TABLE waiting",2023-09-21 16:35:15 CST,182/684177,31283929,LOG,00000,
"process 3624880 still waiting for AccessExclusiveLock on relation 948021 of database 16393 after 1000.048 ms",
"Process holding the lock: 3082360. Wait queue: 3624880.",,,,,"DROP TABLE IF EXISTS TB_EPIC_SOURCE_OPTY_INFO;",,,"psql","client backend"


(postgresql)elite_epic_db=# select * from pg_stat_activity where wait_event_type='Lock';
-[ RECORD 1 ]----+-----------------------------------------------
datid            | 16393
datname          | elite_epic_db
pid              | 3624880
leader_pid       |
usesysid         | 16392
usename          | eliteepicqa
application_name | psql
client_addr      | 26.8.0.193
client_hostname  |
client_port      | 53996
backend_start    | 2023-09-21 16:35:15.909706+08
xact_start       | 2023-09-21 16:35:15.914991+08
query_start      | 2023-09-21 16:35:15.914991+08
state_change     | 2023-09-21 16:35:15.914992+08
wait_event_type  | Lock
wait_event       | relation
state            | active
backend_xid      | 31283929
backend_xmin     | 31283929
query            | DROP TABLE IF EXISTS TB_EPIC_SOURCE_OPTY_INFO;
backend_type     | client backend

(postgresql)elite_epic_db=# select * from pg_stat_activity where pid=3082360;
-[ RECORD 1 ]----+-------------------------------------------
datid            | 16393
datname          | elite_epic_db
pid              | 3082360
leader_pid       |
usesysid         | 16392
usename          | eliteepicqa
application_name | DBeaver 22.1.0 - SQLEditor <Script-85.sql>
client_addr      | 10.62.67.163
client_hostname  |
client_port      | 7578
backend_start    | 2023-09-20 15:30:35.106045+08
xact_start       | 2023-09-21 13:42:55.561658+08
query_start      | 2023-09-21 15:25:18.151026+08
state_change     | 2023-09-21 15:25:18.151033+08
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      |
backend_xmin     |
query            | SHOW search_path
backend_type     | client backend



-- 查询表锁
select a.locktype,a.pid,a.relation,a.mode,a.granted,b.relname from pg_locks a,pg_class b where a.relation=b.oid and a.mode='AccessExclusiveLock';    --库里查询
#Kill process
select pg_cancel_backend('进程 ID');   --取消执行
select pg_terminate_backend('进程 ID')  --清理idle进程
#batch kill
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_ and query ~ ' autovacuum ' AND pid <> pg_backend_pid();

select pid,query,pg_blocking_pids(pid) as blocked from pg_stat_activity where pid in ('3887401','2119016');


select pg_cancel_backend('3887401'); 
select pg_terminate_backend('3154777');
select pg_terminate_backend('2640142');





复制

schema 授权情况

(postgresql)dove_pridelabel_db=# SELECT nspname,nspacl FROM pg_namespace where nspname not like pg_% and nspname not in (recyclebin,information_schema);
 nspname |                        nspacl
---------+------------------------------------------------------
 public  | {postgres=UC/postgres,=UC/postgres}
 backup  | {postgres=UC/postgres,dovepridelabeldev=UC/postgres}
(2 rows)


复制

序列 select * from pg_class where relkind=S;

select * from pg_class where relkind=S;

(postgresql)sisofsdb=# select count(0) from seq_t_jygs_bfs_cust_pv;
 count
-------
     1
(1 row)

(postgresql)sisofsdb=# select count(0) from seq_t_jygs_bfs_cust_pv;
 count
-------
     1
(1 row)

(postgresql)sisofsdb=# select * from seq_t_jygs_bfs_cust_pv;
 last_value | log_cnt | is_called
------------+---------+-----------
         41 |       0 | f
(1 row)

(postgresql)sisofsdb=# select * from  addr_id;
 last_value | log_cnt | is_called
------------+---------+-----------
         61 |       0 | f
(1 row)

(postgresql)sisofsdb=# select * from seq_t_jygs_bfs_cust_pv;
 last_value | log_cnt | is_called
------------+---------+-----------
         41 |       0 | f
(1 row)

(postgresql)sisofsdb=# select * from  addr_id;
 last_value | log_cnt | is_called
------------+---------+-----------
         61 |       0 | f
(1 row)

(postgresql)sisofsdb=# select nextval(addr_id);
 nextval
---------
      61
(1 row)

(postgresql)sisofsdb=# select nextval(seq_t_jygs_bfs_cust_pv);
 nextval
---------
      41
(1 row)

(postgresql)sisofsdb=#



复制

权限查询

select grantee,grantor,table_schema,table_name,string_agg(privilege_type,',') from information_schema.table_privileges
where table_schema!=current_schema group by grantee,grantor,table_schema,table_name order by grantee,grantor,table_schema,table_name;


select grantee,grantor,table_schema,table_name from information_schema.table_privileges;


select grantee,grantor,table_schema,table_name,string_agg(privilege_type,',') from information_schema.table_privileges
group by grantee,grantor,table_schema,table_name order by grantee,grantor,table_schema,table_name;


select grantor,grantee,table_schema,table_name,string_agg(privilege_type,',') 
from information_schema.table_privileges
where table_schema = 'public'
group by grantee,grantor,table_schema,table_name order by grantee,grantor,table_schema,table_name;


-- 查询表的权限
SELECT table_catalog, table_schema, table_name,privilege_type, grantee, is_grantable FROM information_schema.table_privileges;



复制

PostgreSQL怎么查看数据库用户系统权限、对象权限

https://blog.csdn.net/pg_hgdb/article/details/111364929

select * from pg_roles where rolname=aromasoarqa_opr;
select * from information_schema.table_privileges where grantee=aromasoarqa and table_schema=xedm_crms;
select * from information_schema.table_privileges where grantee=aromasoarqa_opr and table_schema=xedm_crms;

select * from information_schema.usage_privileges where grantee=aromasoarqa_opr;
select * from information_schema.routine_privileges where grantee=aromasoarqa_opr; 
select * from information_schema.column_privileges where grantee=aromasoarqa_opr; 

1、查看某用户的系统权限
SELECT * FROM  pg_roles WHERE rolname=aromasoarqa_opr;

2、查看某用户的表权限
select * from information_schema.table_privileges where grantee=aromasoarqa_opr;

3、查看某用户的usage权限
select * from information_schema.usage_privileges where grantee=aromasoarqa_opr;

4、查看某用户在存储过程函数的执行权限
select * from information_schema.routine_privileges where grantee=aromasoarqa_opr; 

5、查看某用户在某表的列上的权限
select * from information_schema.column_privileges where grantee=aromasoarqa_opr; 

6、查看当前用户能够访问的数据类型
select * from information_schema.data_type_privileges ; 

7、查看用户自定义类型上授予的USAGE权限
select * from information_schema.udt_privileges where grantee=aromasoarqa_opr;


复制

权限问题 SELECT * FROM pg_user;

刘老师，请教一个问题，信创数据库我创建了一个序列表且赋权限了，但我使用NEXTVAL函数一直报没有权限，是为啥呀？
grant select on SEQ_T_INVEST_PATH_MEDAL_DETAIL to SISCTOPR;
 SELECT NEXTVAL(SEQ_T_INVEST_PATH_MEDAL_DETAIL);


这个数据库SIS-STOCK-CONTENT

UAT：sisctdb
VIP 10.25.196.148:5431
DBNAME sisctdb


jdbc:postgresql://10.25.252.39:35432/rs_test
rs_test
密码  4gu7TYcu4k

grant select on SEQ_T_INVEST_PATH_MEDAL_DETAIL to SISCTOPR;
 SELECT NEXTVAL(SEQ_T_INVEST_PATH_MEDAL_DETAIL);




(postgresql)sisctdb=> SELECT * FROM pg_user;
    usename     | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
----------------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres       |       10 | t           | t        | t       | t            | ******** |          |
 pcsmon         |    16384 | f           | f        | f       | f            | ******** |          |
 dbamon         |    16390 | f           | f        | f       | f            | ******** |          |
 ludbgatesync   |    16396 | f           | f        | t       | f            | ******** |          |
 ludbgateswitch |    16399 | f           | t        | f       | f            | ******** |          |
 sisctopr       |    16387 | f           | f        | f       | f            | ******** |          |
 sisctdata      |    16385 | f           | f        | f       | f            | ******** |          |
 devsup01       |    16389 | f           | f        | f       | f            | ******** |          |
(8 rows)


grant all on all sequences in schema public to sisctopr;
alter default privileges for role sisctdata in schema public grant all on sequences to sisctopr;



复制

查看状态

\x
select * from pg_stat_replication;
SELECT * FROM pg_stat_wal_receiver;

SELECT * FROM pg_stat_activity;

select * from pg_tables where tableowner =jrzdcyl;

select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_catalog=postcyl_pazq and table_schema=backup;
select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_catalog=postcyl_pazq and table_schema=common;
select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_catalog=postcyl_pazq and table_schema=jrzdcyl;
select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_catalog=postcyl_pazq and table_schema=pingan;
select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_catalog=postcyl_pazq and table_schema=public;


ps -ef|grep walreceiver
ps -ef|grep walsender

Flzxsqc2019



复制

数据库字符集

select pg_encoding_to_char(encoding) from pg_database where datname='grace_miracle_db';


复制

按schema查表的大小

-- https://www.cnblogs.com/ldsweely/p/15262832.html

(postgresql)aroma_air_db=# select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname=public order by pg_relation_size(relid) desc;
                relname                | pg_size_pretty
---------------------------------------+----------------
 ashareinsideholder                    | 168 GB
 air_rp_industry_rating                | 12 GB
 air_rp_bond_spread                    | 10205 MB
 air_bond_invest_manage_log            | 6759 MB
 finance_index_result                  | 5774 MB
 finance_derived_index_result          | 5152 MB
 air_cbondanalysiscnbd                 | 4619 MB
 report_record                         | 4533 MB
 air_rp_classify_spread                | 4215 MB
 air_compintroduction                  | 3852 MB
 cruise_finance_index_result           | 3047 MB
 air_bondanninf                        | 2770 MB
 csfinance_derived_index_result        | 2733 MB
 finance_index_result_quarter          | 2335 MB
 cbondvaluation                        | 2325 MB
 cbondanalysiscnbd                     | 2310 MB
 comp_rating_result                    | 2225 MB
 air_windcustomcode                    | 2059 MB
 air_cbondcurvecnbd                    | 1957 MB
 compintroduction                      | 1927 MB
 air_cbondcashflow                     | 1476 MB
 air_ashareincome                      | 1374 MB
 air_cbondincome                       | 1207 MB
 care_ent_info                         | 1160 MB
 air_ent_info                          | 1143 MB
 air_customer_info                     | 1135 MB
 report_template_instance_his          | 1123 MB
 air_big_rating_info_part              | 1097 MB
 cbondcurvememberscnbd                 | 1074 MB


(postgresql)aroma_air_db=# select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname=backup order by pg_relation_size(relid) desc;
            relname             | pg_size_pretty
--------------------------------+----------------
 air_ent_info0609               | 1140 MB



(postgresql)aroma_air_db=# select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname=syncforprd order by pg_relation_size(relid) desc;
            relname             | pg_size_pretty
--------------------------------+----------------
 air_rp_industry_rating         | 12 GB
 air_rp_bond_spread             | 10011 MB
 report_record                  | 4719 MB
 air_rp_classify_spread         | 4119 MB
 air_genealogy_control          | 1070 MB



复制

提示pg_hba.conf 限制了访问，烦请帮忙设置下白名单，谢谢 – 26.8.0.193/26.8.0.194部署的jenkins主机

host all all all md5

pg_stat_statements,auto_explain 加执行计划

vi postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'

#pg_stat_statements
compute_query_id = on
pg_stat_statements.max = 10000
pg_stat_statements.track = all

#auto_explain
auto_explain.log_min_duration = '1s'
auto_explain.log_nested_statements = on



[postgres@xxoo180095 ~]$ /postgres/base/2.0.1/bin/pg_ctl -D /pgdata5410/doveboatqa start
waiting for server to start....2023-11-01 13:31:35 CST::@:[3041750]:LOG:  redirecting log output to logging collector process
2023-11-01 13:31:35 CST::@:[3041750]:HINT:  Future log output will appear in directory "log".
 done
server started




SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;


export LD_LIBRARY_PATH=/postgres/base/2.0.1/lib:/postgres/base/2.0.1/lib/postgresql:/lib:/usr/lib

(postgresql)postgres=# CREATE EXTENSION auto_explain;
ERROR:  extension "auto_explain" is not available
DETAIL:  Could not open extension control file "/postgres/base/2.0.1/share/postgresql/extension/auto_explain.control": No such file or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is running.
(postgresql)postgres=#


-- 创建扩展auto_explain失败：No such file or directory https://blog.51cto.com/u_10930585/5779284
cd /postgres/base/2.0.1/share/postgresql/extension/
vi auto_explain.control
# auto_explain extension

comment = 'auto_explain sql exceted functions'
default_version = '1.0'
module_pathname = '$libdir/auto_explain'
relocatable = true
trusted = true

touch auto_explain--1.0.sql
(postgresql)postgres=# CREATE EXTENSION auto_explain;
CREATE EXTENSION
(postgresql)postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 auto_explain       | 1.0     | public     | auto_explain sql exceted functions
 pg_background      | 1.1     | pg_catalog | Run SQL queries in the background
 pg_stat_statements | 1.10    | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgresql_ddl_time   | 1.0     | pg_catalog | record ddl time
(5 rows)


--
postgres=# LOAD 'auto_explain';
postgres=# SET auto_explain.log_min_duration = 0;
postgres=# SELECT count(*)
	     FROM pg_class, pg_index
	     WHERE oid = indrelid AND indisunique;


Query Text: SELECT count(*)
     FROM pg_class, pg_index
     WHERE oid = indrelid AND indisunique;
Aggregate  (cost=28.39..28.40 rows=1 width=8)
  ->  Hash Join  (cost=21.91..28.01 rows=154 width=0)
        Hash Cond: (pg_index.indrelid = pg_class.oid)
        ->  Seq Scan on pg_index  (cost=0.00..5.70 rows=154 width=4)
              Filter: indisunique
        ->  Hash  (cost=16.62..16.62 rows=423 width=4)
              ->  Index Only Scan using pg_class_oid_index on pg_class  (cost=0.27..16.62 rows=423 width=4)",,,,,,,,,"psql","client backend",,3843738038909618734

--"
这个文档写的没有 CREATE EXTENSION的操作
https://blog.csdn.net/ctypyb2002/article/details/81296034


drop extension auto_explain;


vi /pgdata5410/doveboatqa/postgresql.conf



/postgres/base/2.0.1/bin/pg_ctl -D /pgdata5410/doveboatqa stop

/postgres/base/2.0.1/bin/pg_ctl -D /pgdata5410/doveboatqa start


-- 笛卡尔乘积 
(postgresql)postgres=# select count(0) from pg_class p1,pg_class p2,pg_class p3;
  count
----------
 76765625
(1 row)


-- 查看执行计划 花了6秒多
2023-11-01 15:15:46.466 CST,"postgres","postgres",266401,"[local]",6541fa9a.410a1,5,"SELECT",2023-11-01 15:13:30 CST,4/15,0,LOG,00000,"duration: 6774.621 ms  plan:
Query Text: select count(0) from pg_class p1,pg_class p2,pg_class p3;
Aggregate  (cost=1137577.71..1137577.72 rows=1 width=8)
  ->  Nested Loop  (cost=0.44..948360.29 rows=75686967 width=0)
        ->  Nested Loop  (cost=0.29..2260.66 rows=178929 width=0)
              ->  Index Only Scan using pg_class_tblspc_relfilenode_index on pg_class p1  (cost=0.15..11.49 rows=423 width=0)
              ->  Materialize  (cost=0.15..13.61 rows=423 width=0)
                    ->  Index Only Scan using pg_class_tblspc_relfilenode_index on pg_class p2  (cost=0.15..11.49 rows=423 width=0)
        ->  Materialize  (cost=0.15..13.61 rows=423 width=0)
              ->  Index Only Scan using pg_class_tblspc_relfilenode_index on pg_class p3  (cost=0.15..11.49 rows=423 width=0)",,,,,,,,,"psql","client backend",,5909535198636122001
2023-11-01 15:15:46.466 CST,"postgres","postgres",266401,"[local]",6541fa9a.410a1,6,"SELECT",2023-11-01 15:13:30 CST,4/0,0,LOG,00000,"duration: 6775.320 ms  statement: select count(0) from pg_class p1,pg_class p2,pg_class p3;",,,,,,,,,"psql","client backend",,5909535198636122001




复制

创建定时任务 删除多余的log文件

0 6 * * *  /postgres/shell/pg_del_log.sh >> /tmp/pg_del_log.out 2>&1

[postgres@xxoo080225 ~]$ cat /postgres/shell/pg_del_log.sh 
#!/bin/bash
expired=30

for pg_data in `ps -ef|grep pgdata|grep "\-D"|grep -v "grep "|awk -F'-D' '{print $2}'|awk '{print $1}'`
do
  echo $pg_data
  pg_inst=`echo $pg_data |sed -r 's/.*\/.*\/(.*)/\1/'`
  echo $pg_inst
  find /pgdata*/$pg_inst/log -type f -name 'postgres*.log' -mtime +$expired  -exec rm -f {} \;
  find /pgdata*/$pg_inst/log -type f -name 'postgres*.csv' -mtime +$expired  -exec rm -f {} \;
done


-------------------------------------------------------------------------------------------------------
#!/bin/bash
expired=30

for pg_data in `ps -ef|grep pg_data|grep "\-D"|grep -v "grep "|awk -F'-D' '{print $2}'|awk '{print $1}'`
do
  echo $pg_data
  pg_inst=`echo $pg_data |sed -r 's/.*\/.*\/(.*)/\1/'`
  echo $pg_inst
  find /pg_data/$pg_inst/log -type f -name 'postgres*.log' -mtime +$expired  -exec rm -f {} \;
  find /pg_data/$pg_inst/log -type f -name 'postgres*.csv' -mtime +$expired  -exec rm -f {} \;
done



复制

删除数据类型type

-- 查询是否有这个数据类型
SELECT n.nspname AS schema_name, 
       t.typname AS data_type_name
FROM   pg_catalog.pg_type t
JOIN   pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE  n.nspname = 'kdetl'
and t.typname ='etl_wd_index_pettm'; 


-- 查询数据类型信息
SELECT a.attname AS field_name,
       format_type(a.atttypid, a.atttypmod) AS field_type,
       a.attnotnull AS is_not_null
FROM   pg_attribute a
JOIN (
    SELECT typrelid
    FROM pg_catalog.pg_type
    JOIN pg_catalog.pg_namespace ON typnamespace = pg_namespace.oid
    WHERE nspname = 'kdetl' AND typname = 'etl_wd_index_pettm'
) AS t ON a.attrelid = t.typrelid
WHERE  a.attnum > 0; 


-- 查询依赖的对象有哪些
SELECT DISTINCT c.relname AS dependent_object
FROM   pg_depend d
JOIN   pg_class c ON d.objid = c.oid
JOIN   pg_namespace ns ON c.relnamespace = ns.oid
JOIN   pg_type t ON d.refobjid = t.oid
JOIN   pg_namespace n ON t.typnamespace = n.oid
WHERE  n.nspname = 'kdetl' AND t.typname = 'etl_wd_index_pettm';

--
SELECT n.nspname AS schema_name, 
       t.typname AS data_type_name
FROM   pg_catalog.pg_type t
JOIN   pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE   t.typname ='etl_wd_index_pettm'; 


DROP TYPE IF EXISTS kdetl.etl_wd_index_pettm CASCADE;


-- 检查是否有依赖于类型kdetl.etl_wd_index_pettm的对象
SELECT * FROM pg_depend WHERE refobjid = 'kdetl.etl_wd_index_pettm'::regtype;


SELECT nmsp.nspname AS schema_name, c.relname AS object_name FROM pg_class c
JOIN pg_namespace nmsp ON nmsp.oid = c.relnamespace WHERE c.oid = 1247;


SELECT d.refclassid::regclass, d.refobjid::regclass, d.classid::regclass, a.attname
FROM pg_depend d
LEFT JOIN pg_attribute a ON d.objid = a.attrelid AND d.objsubid = a.attnum
WHERE d.refobjid = 'kdetl.etl_wd_index_pettm'::regtype;

SELECT typname, typtype, typnamespace::regnamespace FROM pg_type WHERE oid = 994128;



复制

参考如下：oracle兼容模式 初始化之后 登陆数据库 执行show all 查看参数

initdb -D $PGDATA --wal-segsize=128 -E UTF8 --local=C --rase_compatibility_db_mode=oracle