巡检人:小麦苗　QQ:646634621　微信公众号：DB宝　提供OCP、OCM、高可用（rac+dg）、PostgreSQL和MySQL培训　BLOG地址: https://www.dbaup.com 若需要脚本可私聊我
PG数据库巡检报告

Copyright (c) 2015-2100 (https://www.dbaup.com) dbaup. All rights reserved.

巡 检 人：小麦苗([QQ：646634621] [微信公众号：DB宝] [提供OCP、OCM、高可用、MySQL和PostgreSQL最实用的培训])
版 本 号：v7.0.0
修改日期：2021-07-14 18:28:00

[转到页底]

目录
总体概况 实例信息数据库实例信息 数据库基本信息数据库的总体概况、版本、主机情况、数据库负载情况、数据库属性等 所有表空间所有表空间 当前客户端连接信息当前客户端连接信息 前几张大表前几张大表
当前库的所有数据库对象当前库的所有数据库对象
锁情况 所有进程
SQL部分 总执行时间最长的SQL 最耗IO的SQL 最耗共享内存
索引部分
高可用情况 主从流复制情况主从流复制情况 逻辑复制PG逻辑复制情况
数据库性能 参数配置 pg_hba参数文件 后台写进程统计信息
其它 用户和角色 所有插件 获取已安装的PostgreSQL的信息获取已安装的PostgreSQL的信息，表pg_config


数据库总体概况

● 实例信息
pg_start_time server_ip server_port client_ip client_port server_version primary_or_standby now_date
2021-07-13 17:26:55.155501+08 172.17.0.13 5433 172.17.0.1 22842 PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit primary 2021-07-21 17:13:06.900674+08

● 数据库基本信息
Name Owner Encoding Collate Ctype Access privileges Size Tablespace Description
bench postgres UTF8 en_US.utf8 en_US.utf8   23 MB pg_default  
db1 postgres UTF8 en_US.utf8 en_US.utf8   7581 kB pg_default  
db10 postgres UTF8 en_US.utf8 en_US.utf8   7581 kB pg_default  
db2 postgres UTF8 en_US.utf8 en_US.utf8   7581 kB pg_default  
db3 postgres SQL_ASCII C C   7581 kB pg_default  
db4 postgres SQL_ASCII en_US.utf8 en_US.utf8   7581 kB pg_default  
db5 postgres SQL_ASCII C C   7581 kB pg_default  
db6 postgres SQL_ASCII C C   7581 kB pg_default  
db7 postgres UTF8 en_US.utf8 en_US.utf8   7581 kB pg_default  
korean postgres SQL_ASCII C C   7581 kB pg_default  
lhrdb postgres UTF8 en_US.utf8 en_US.utf8   105 MB pg_default  
postgres postgres UTF8 en_US.utf8 en_US.utf8   622 MB pg_default default administrative connection database
template0 postgres UTF8 en_US.utf8 en_US.utf8 =c/postgres
postgres=CTc/postgres		 7581 kB pg_default unmodifiable empty database
template1 postgres UTF8 en_US.utf8 en_US.utf8 =c/postgres
postgres=CTc/postgres		 7581 kB pg_default default template for new databases
● 查看各数据库数据创建时间
datname size access modification change creation isdir
postgres 3 2021-07-06 18:23:44+08 2021-07-06 17:35:00+08 2021-07-06 18:21:45+08   f
bench 3 2021-07-20 18:28:07+08 2021-07-12 18:25:03+08 2021-07-12 18:25:03+08   f
template1 3 2021-07-06 18:23:44+08 2021-07-06 17:34:59+08 2021-07-06 18:21:44+08   f
template0 3 2021-07-06 18:23:44+08 2021-07-06 17:34:59+08 2021-07-06 18:21:45+08   f
lhrdb 3 2021-07-21 14:13:17+08 2021-07-15 14:09:43+08 2021-07-15 14:09:43+08   f
db1 3 2021-07-20 20:52:58+08 2021-07-16 20:52:07+08 2021-07-16 20:52:07+08   f
db2 3 2021-07-19 09:42:09+08 2021-07-19 09:42:09+08 2021-07-19 09:42:09+08   f
korean 3 2021-07-19 09:49:10+08 2021-07-19 09:49:10+08 2021-07-19 09:49:10+08   f
db3 3 2021-07-19 09:49:40+08 2021-07-19 09:49:40+08 2021-07-19 09:49:40+08   f
db4 3 2021-07-19 09:49:54+08 2021-07-19 09:49:54+08 2021-07-19 09:49:54+08   f
db5 3 2021-07-19 09:52:46+08 2021-07-19 09:52:46+08 2021-07-19 09:52:46+08   f
db6 3 2021-07-19 09:53:08+08 2021-07-19 09:53:08+08 2021-07-19 09:53:08+08   f
db7 3 2021-07-19 09:57:01+08 2021-07-19 09:57:01+08 2021-07-19 09:57:01+08   f
db10 3 2021-07-21 15:28:17+08 2021-07-21 15:28:13+08 2021-07-21 15:28:13+08   f

● 所有表空间
oid Name Owner Location Access privileges Options Size Description
1663 pg_default postgres       831 MB  
1664 pg_global postgres       559 kB  

● 当前客户端连接信息
current_user current_database pg_backend_pid
postgres lhrdb 15558

● 前几张大表
db schemaname relname rowcount table_size indexes_size total_size
lhrdb public deltest_bk 510000 22 MB 0 bytes 22 MB
lhrdb public deltest3 500000 22 MB 0 bytes 22 MB
lhrdb public deltest2 500000 22 MB 0 bytes 22 MB
lhrdb public deltest 500000 22 MB 11 MB 32 MB
lhrdb public tbp_2 0 8192 bytes 0 bytes 8192 bytes
lhrdb public tbp_3 0 8192 bytes 0 bytes 8192 bytes
lhrdb public tbp_4 0 8192 bytes 0 bytes 8192 bytes
lhrdb public toast_t1 0 8192 bytes 0 bytes 8192 bytes
lhrdb public tbp 0 0 bytes 0 bytes 0 bytes
lhrdb public tbp_1 0 8192 bytes 0 bytes 8192 bytes

● 当前库的所有数据库对象
schemaname objecttype cnt
public composite type 1
public VIEW 2
public INDEX 1
public partitioned_table 1
public TABLE 9
public SEQUENCE 1
public proc 1
public func 5

[回到目录]

锁情况

● 所有进程

● 总计
最大连接数 当前连接数 剩余连接数
100 9 91

● 详情
datid datname pid leader_pid usesysid usename application_name client_addr client_hostname client_port backend_start xact_start query_start state_change wait_event_type wait_event state backend_xid backend_xmin query backend_type
    15159   10 postgres         2021-07-13 17:26:55.163569+08       Activity LogicalLauncherMain         logical replication launcher
    15157               2021-07-13 17:26:55.164116+08       Activity AutoVacuumMain         autovacuum launcher
16484 lhrdb 15558   10 postgres psql 172.17.0.1   22842 2021-07-21 17:13:06.140042+08 2021-07-21 17:13:07.723435+08 2021-07-21 17:13:07.723435+08 2021-07-21 17:13:07.723439+08     active   3804 select a.* from pg_stat_activity a ; client backend
16484 lhrdb 26482   10 postgres psql     -1 2021-07-18 20:58:29.563362+08   2021-07-18 21:02:10.342108+08 2021-07-19 05:16:05.404244+08 Client ClientRead idle     delete from deltest a where a.ctid <> (select min(t.ctid) from deltest t where a.id=t.id); client backend
16484 lhrdb 9466   10 postgres psql     -1 2021-07-21 15:35:58.367116+08   2021-07-21 15:50:17.723352+08 2021-07-21 15:50:17.728013+08 Client ClientRead idle     SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 5;		 client backend
16484 lhrdb 13124   10 postgres psql     -1 2021-07-21 16:33:54.721974+08   2021-07-21 16:56:33.88919+08 2021-07-21 16:56:33.894343+08 Client ClientRead idle     SELECT query,
calls,
round(total_exec_time::numeric, 2) AS total_time,
round(mean_exec_time::numeric, 2) AS mean_time,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;		 client backend
    15155               2021-07-13 17:26:55.162688+08       Activity BgWriterHibernate         background writer
    15154               2021-07-13 17:26:55.162483+08       Activity CheckpointerMain         checkpointer
    15156               2021-07-13 17:26:55.162911+08       Activity WalWriterMain         walwriter

[回到目录]

SQL情况

● 总执行时间最长的SQL
query calls total_time mean_time percentage
delete from deltest a where a.ctid <> (select min(t.ctid) from deltest t where a.id=t.id) 1 29635059.79 29635059.79 99.84
copy test_copy from stdin delimiter ',' csv header 1 21578.79 21578.79 0.07
copy test_copy from stdin 1 9759.27 9759.27 0.03
SELECT n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
CASE p.prokind
WHEN $1 THEN $2
WHEN $3 THEN $4
WHEN $5 THEN $6
ELSE $7
END as "Type"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> $8
AND n.nspname <> $9
ORDER BY 1, 2, 4		 9 1878.70 208.74 0.01
checkpoint 1 1836.38 1836.38 0.01
delete from deltest2 a where a.ctid not in (select min(ctid) from deltest2 group by id) 1 1759.07 1759.07 0.01
select
nsp.nspname as SchemaName
,case cls.relkind
when $1 then $2
when $3 then $4
when $5 then $6
when $7 then $8
when $9 then $10
when $11 then $12
when $13 then $14
when $15 then $16
when $17 then $18
when $19 then $20
else cls.relkind::text
end as ObjectType,
COUNT(*) cnt
from pg_class cls
join pg_namespace nsp
on nsp.oid = cls.relnamespace
where nsp.nspname not in ($21, $22)
and nsp.nspname not like $23
GROUP BY nsp.nspname,cls.relkind
UNION all
SELECT n.nspname as "Schema",
CASE p.prokind
WHEN $24 THEN $25
WHEN $26 THEN $27
WHEN $28 THEN $29
ELSE $30
END as "Type",
COUNT(*) cnt
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname not in ($31, $32)
GROUP BY n.nspname ,p.prokind		 7 1391.23 198.75 0.00
insert into deltest select generate_series($1, $2), $3 2 1282.00 641.00 0.00
insert into deltest2 select * from deltest_bk 1 1158.62 1158.62 0.00
insert into deltest3 select * from deltest_bk 1 1151.01 1151.01 0.00

● 最耗IO的SQL
query calls total_time io_read_time io_write_time percentage
CREATE DATABASE DB4 WITH ENCODING 'sql_ascii' TEMPLATE=template0 1 202.37 0.00 0.00 0.00
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES ($1, $2, $3, $4, $5 )		 7 0.71 0.00 0.00 0.00
SELECT c.oid::pg_catalog.regclass
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid = i.inhparent AND i.inhrelid = $1
AND c.relkind != $2 AND c.relkind != $3
ORDER BY inhseqno		 3 0.22 0.00 0.00 0.00
CREATE TABLE COMPANY1(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
)		 1 9.00 0.00 0.00 0.00
select
nsp.nspname as SchemaName
,case cls.relkind
when $1 then $2
when $3 then $4
when $5 then $6
when $7 then $8
when $9 then $10
when $11 then $12
when $13 then $14
when $15 then $16
when $17 then $18
when $19 then $20
else cls.relkind::text
end as ObjectType,
COUNT(*) cnt
from pg_class cls
join pg_namespace nsp
on nsp.oid = cls.relnamespace
where nsp.nspname not in ($21, $22)
GROUP BY nsp.nspname,cls.relkind
order by nsp.nspname		 1 0.30 0.00 0.00 0.00
create table deltest(id int, name varchar(255)) 1 5.19 0.00 0.00 0.00
SELECT n.nspname as "Schema",
CASE p.prokind
WHEN $1 THEN $2
WHEN $3 THEN $4
WHEN $5 THEN $6
ELSE $7
END as "Type",
COUNT(*) cnt
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname not in ($8, $9)
GROUP BY n.nspname ,p.prokind		 1 246.29 0.00 0.00 0.00
CREATE TYPE dup_result AS (f1 int, f2 text) 1 4.13 0.00 0.00 0.00
select * from pg_class limit $1 1 0.22 0.00 0.00 0.00
delete from deltest3 a where a.ctid = any(array (select ctid from (
select row_number() over (partition by id), ctid from deltest3) t where t.row_number > $1))		 1 836.70 0.00 0.00 0.00

● 最耗共享内存 SQL
userid dbid queryid query plans total_plan_time min_plan_time max_plan_time mean_plan_time stddev_plan_time calls total_exec_time min_exec_time max_exec_time mean_exec_time stddev_exec_time rows shared_blks_hit shared_blks_read shared_blks_dirtied shared_blks_written local_blks_hit local_blks_read local_blks_dirtied local_blks_written temp_blks_read temp_blks_written blk_read_time blk_write_time wal_records wal_fpi wal_bytes
10 16484 -4083206497394824830 delete from deltest a where a.ctid <> (select min(t.ctid) from deltest t where a.id=t.id) 0 0 0 0 0 0 1 29635059.787814 29635059.787814 29635059.787814 29635059.787814 0 10000 1406082812 0 60 0 0 0 0 0 0 0 0 0 10000 57 1003653
10 16484 -3259684640249375243 insert into deltest3 select * from deltest_bk 0 0 0 0 0 0 1 1151.0147 1151.0147 1151.0147 1151.0147 0 510000 518266 2 2757 2757 0 0 0 0 0 0 0 0 510000 0 34680000
10 16484 5123484616814316744 insert into deltest2 select * from deltest_bk 0 0 0 0 0 0 1 1158.6191880000001 1158.6191880000001 1158.6191880000001 1158.6191880000001 0 510000 518266 2 2757 2757 0 0 0 0 0 0 0 0 510000 0 34680000
10 16484 -3588817466290473895 insert into deltest_bk select * from deltest 0 0 0 0 0 0 1 1141.977469 1141.977469 1141.977469 1141.977469 0 510000 518266 2 2757 2760 0 0 0 0 0 0 0 0 510000 0 34680000
10 16484 -4947015774599039384 insert into deltest select generate_series($1, $2), $3 0 0 0 0 0 0 2 1281.997408 21.851168 1260.14624 640.998704 619.1475360000001 510000 515509 2 2757 2757 0 0 0 0 0 0 0 0 510000 0 34680000

[回到目录]

高可用情况

● 主从流复制情况

● 主库查看wal日志发送状态
pid usesysid usename application_name client_addr client_hostname client_port backend_start backend_xmin state sent_lsn write_lsn flush_lsn replay_lsn write_lag flush_lag replay_lag sync_priority sync_state reply_time

● 从库查看wal日志接收状态
pid status receive_start_lsn receive_start_tli written_lsn flushed_lsn received_tli last_msg_send_time last_msg_receipt_time latest_end_lsn latest_end_time slot_name sender_host sender_port conninfo

● 主从库延迟数据（单位MB）
slave_latency_mb

● 主从流复制冲突统计
datid datname confl_tablespace confl_lock confl_snapshot confl_bufferpin confl_deadlock
12661 postgres 0 0 0 0 0
16430 bench 0 0 0 0 0
1 template1 0 0 0 0 0
12660 template0 0 0 0 0 0
16484 lhrdb 0 0 0 0 0
16635 db1 0 0 0 0 0
16655 db2 0 0 0 0 0
16656 korean 0 0 0 0 0
16657 db3 0 0 0 0 0
16658 db4 0 0 0 0 0
16659 db5 0 0 0 0 0
16660 db6 0 0 0 0 0
16661 db7 0 0 0 0 0
16711 db10 0 0 0 0 0

[回到目录]

● 逻辑复制

● 查看发布
oid pubname pubowner puballtables pubinsert pubupdate pubdelete pubtruncate pubviaroot

● 查看发布的表
pubname schemaname tablename

● 查看所有的订阅者
pid usesysid usename application_name client_addr client_hostname client_port backend_start backend_xmin state sent_lsn write_lsn flush_lsn replay_lsn write_lag flush_lag replay_lag sync_priority sync_state reply_time

● 复制进度
subid subname pid relid received_lsn last_msg_send_time last_msg_receipt_time latest_end_lsn latest_end_time

● 查看订阅
oid subdbid subname subowner subenabled subconninfo subslotname subsynccommit subpublications

● 查看订阅的表
srsubid srrelid srsubstate srsublsn srrelid

● 查看复制槽
slot_name plugin slot_type datoid database temporary active active_pid xmin catalog_xmin restart_lsn confirmed_flush_lsn wal_status safe_wal_size

[回到目录]

数据库性能

● 相关参数

● 参数文件记录的参数
sourcefile sourceline seqno name setting applied error
/pg13/pgdata/postgresql.conf 64 1 max_connections 100 t  
/pg13/pgdata/postgresql.conf 121 2 shared_buffers 128MB t  
/pg13/pgdata/postgresql.conf 142 3 dynamic_shared_memory_type posix t  
/pg13/pgdata/postgresql.conf 228 4 max_wal_size 1GB t  
/pg13/pgdata/postgresql.conf 229 5 min_wal_size 80MB t  
/pg13/pgdata/postgresql.conf 563 6 log_timezone PRC t  
/pg13/pgdata/postgresql.conf 678 7 datestyle iso, mdy t  
/pg13/pgdata/postgresql.conf 680 8 timezone PRC t  
/pg13/pgdata/postgresql.conf 694 9 lc_messages en_US.utf8 t  
/pg13/pgdata/postgresql.conf 696 10 lc_monetary en_US.utf8 t  
/pg13/pgdata/postgresql.conf 697 11 lc_numeric en_US.utf8 t  
/pg13/pgdata/postgresql.conf 698 12 lc_time en_US.utf8 t  
/pg13/pgdata/postgresql.conf 701 13 default_text_search_config pg_catalog.english t  
/pg13/pgdata/postgresql.conf 780 14 listen_addresses * t  
/pg13/pgdata/postgresql.conf 781 15 port 5433 t  
/pg13/pgdata/postgresql.conf 782 16 unix_socket_directories /pg13/pgdata t  
/pg13/pgdata/postgresql.conf 783 17 logging_collector on t  
/pg13/pgdata/postgresql.conf 784 18 log_directory pg_log t  
/pg13/pgdata/postgresql.conf 785 19 log_filename postgresql-%a.log t  
/pg13/pgdata/postgresql.conf 786 20 log_truncate_on_rotation on t  
/pg13/pgdata/postgresql.auto.conf 3 21 shared_preload_libraries pg_stat_statements t  
● 重要参数
name setting unit category short_desc extra_desc context vartype source min_val max_val enumvals boot_val reset_val sourcefile sourceline pending_restart
archive_mode off   Write-Ahead Log / Archiving Allows archiving of WAL files using archive_command.   postmaster enum default     {always,on,off} off off     f
client_encoding UTF8   Client Connection Defaults / Locale and Formatting Sets the client's character set encoding.   user string default       SQL_ASCII UTF8     f
config_file /pg13/pgdata/postgresql.conf   File Locations Sets the server's main configuration file.   postmaster string override         /pg13/pgdata/postgresql.conf     f
data_directory /pg13/pgdata   File Locations Sets the server's data directory.   postmaster string override         /pg13/pgdata     f
hba_file /pg13/pgdata/pg_hba.conf   File Locations Sets the server's "hba" configuration file.   postmaster string override         /pg13/pgdata/pg_hba.conf     f
ident_file /pg13/pgdata/pg_ident.conf   File Locations Sets the server's "ident" configuration file.   postmaster string override         /pg13/pgdata/pg_ident.conf     f
log_directory pg_log   Reporting and Logging / Where to Log Sets the destination directory for log files. Can be specified as relative to the data directory or as absolute path. sighup string configuration file       log pg_log /pg13/pgdata/postgresql.conf 784 f
log_filename postgresql-%a.log   Reporting and Logging / Where to Log Sets the file name pattern for log files.   sighup string configuration file       postgresql-%Y-%m-%d_%H%M%S.log postgresql-%a.log /pg13/pgdata/postgresql.conf 785 f
logging_collector on   Reporting and Logging / Where to Log Start a subprocess to capture stderr output and/or csvlogs into log files.   postmaster bool configuration file       off on /pg13/pgdata/postgresql.conf 783 f
log_min_duration_statement -1 ms Reporting and Logging / When to Log Sets the minimum execution time above which all statements will be logged. Zero prints all queries. -1 turns this feature off. superuser integer default -1 2147483647   -1 -1     f
log_statement none   Reporting and Logging / What to Log Sets the type of statements logged.   superuser enum default     {none,ddl,mod,all} none none     f
log_truncate_on_rotation on   Reporting and Logging / Where to Log Truncate existing log files of same name during log rotation.   sighup bool configuration file       off on /pg13/pgdata/postgresql.conf 786 f
port 5433   Connections and Authentication / Connection Settings Sets the TCP port the server listens on.   postmaster integer command line 1 65535   5432 5433     f

● pg_hba.conf配置文件信息（PG10新增）
line_number type database user_name address netmask auth_method options error
2 local {all} {all}     trust    
3 host {all} {all} 127.0.0.1 255.255.255.255 trust    
4 host {all} {all} 0.0.0.0 0.0.0.0 md5    
5 host {replication} {all} 0.0.0.0 0.0.0.0 md5    

● 后台写进程统计信息
checkpoints_timed checkpoints_req checkpoint_write_time checkpoint_sync_time buffers_checkpoint buffers_clean maxwritten_clean buffers_backend buffers_backend_fsync buffers_alloc stats_reset
2579 34 1623053 11073 140770 241880 1330 930287 0 678407 2021-07-06 17:35:37.084751+08

[回到目录]

其它

● 当前库的所有schema
catalog_name schema_name schema_owner default_character_set_catalog default_character_set_schema default_character_set_name sql_path
lhrdb pg_toast_temp_4 postgres        
lhrdb pg_temp_4 postgres        
lhrdb information_schema postgres        
lhrdb public postgres        
lhrdb pg_catalog postgres        
lhrdb pg_toast postgres        

● 当前库的所有角色(用户)
rolname rolsuper rolinherit rolcreaterole rolcreatedb rolcanlogin rolreplication rolconnlimit rolpassword rolvaliduntil rolbypassrls rolconfig oid
pg_signal_backend f t f f f f -1 ********   f   4200
pg_read_server_files f t f f f f -1 ********   f   4569
postgres t t t t t t -1 ********   t   10
pg_write_server_files f t f f f f -1 ********   f   4570
pg_execute_server_program f t f f f f -1 ********   f   4571
pg_read_all_stats f t f f f f -1 ********   f   3375
pg_monitor f t f f f f -1 ********   f   3373
pg_read_all_settings f t f f f f -1 ********   f   3374
pg_stat_scan_tables f t f f f f -1 ********   f   3377
lhr f t f t t f -1 ********   f   16662

usename usesysid usecreatedb usesuper userepl usebypassrls passwd valuntil useconfig
postgres 10 t t t t ********    
lhr 16662 t f f f ********    

usename usesysid usecreatedb usesuper userepl usebypassrls passwd valuntil useconfig
postgres 10 t t t t md5da3edeb741de62d06ab73785ed222494    
lhr 16662 t f f f md53e5c401ee2b9f28db1bb075b1b99e0ad    

● 所有语言
oid lanname lanowner lanispl lanpltrusted lanplcallfoid laninline lanvalidator lanacl
12 internal 10 f f 0 0 2246  
13 c 10 f f 0 0 2247  
14 sql 10 f t 0 0 2248  
12651 plpgsql 10 t t 12648 12649 12650  

● 所有可用插件

● 所有插件（已编译）
name default_version installed_version comment
file_fdw 1.0   foreign-data wrapper for flat file access
pgcrypto 1.3   cryptographic functions
tcn 1.0   Triggered change notifications
earthdistance 1.1   calculate great-circle distances on the surface of the Earth
pageinspect 1.8   inspect the contents of database pages at a low level
pg_prewarm 1.2   prewarm relation data
lo 1.1   Large Object maintenance
amcheck 1.2   functions for verifying relation integrity
unaccent 1.1   text search dictionary that removes accents
tsm_system_time 1.0   TABLESAMPLE method which accepts time in milliseconds as a limit
moddatetime 1.0   functions for tracking last modification time
pg_trgm 1.5   text similarity measurement and index searching based on trigrams
insert_username 1.0   functions for tracking who changed a table
dblink 1.2   connect to other PostgreSQL databases from within a database
fuzzystrmatch 1.1   determine similarities and distance between strings
intarray 1.3   functions, operators, and index support for 1-D arrays of integers
isn 1.2   data types for international product numbering standards
pg_buffercache 1.3   examine the shared buffer cache
pg_stat_statements 1.8 1.8 track planning and execution statistics of all SQL statements executed
dict_xsyn 1.0   text search dictionary template for extended synonym processing
postgres_fdw 1.0   foreign-data wrapper for remote PostgreSQL servers
ltree 1.2   data type for hierarchical tree-like structures
intagg 1.1   integer aggregator and enumerator (obsolete)
pgrowlocks 1.2   show row-level locking information
pgstattuple 1.5   show tuple-level statistics
tablefunc 1.0   functions that manipulate whole tables, including crosstab
cube 1.4   data type for multidimensional cubes
autoinc 1.0   functions for autoincrementing fields
hstore 1.7   data type for storing sets of (key, value) pairs
pg_freespacemap 1.2   examine the free space map (FSM)
plpgsql 1.0 1.0 PL/pgSQL procedural language
dict_int 1.0   text search dictionary template for integers
repmgr 5.2   Replication manager for PostgreSQL
btree_gin 1.3   support for indexing common datatypes in GIN
bloom 1.0   bloom access method - signature file based index
tsm_system_rows 1.0   TABLESAMPLE method which accepts number of rows as a limit
refint 1.0   functions for implementing referential integrity (obsolete)
pg_visibility 1.2   examine the visibility map (VM) and page-level visibility info
citext 1.6   data type for case-insensitive character strings
btree_gist 1.5   support for indexing common datatypes in GiST
adminpack 2.1   administrative functions for PostgreSQL
seg 1.3   data type for representing line segments or floating-point intervals

● 已安装的插件
Name Version Schema Description
pg_stat_statements 1.8 public track planning and execution statistics of all SQL statements executed
plpgsql 1.0 pg_catalog PL/pgSQL procedural language

● 获取已安装的PostgreSQL的信息
name setting
BINDIR /pg13/pg13/bin
DOCDIR /pg13/pg13/share/doc/postgresql
HTMLDIR /pg13/pg13/share/doc/postgresql
INCLUDEDIR /pg13/pg13/include
PKGINCLUDEDIR /pg13/pg13/include/postgresql
INCLUDEDIR-SERVER /pg13/pg13/include/postgresql/server
LIBDIR /pg13/pg13/lib
PKGLIBDIR /pg13/pg13/lib/postgresql
LOCALEDIR /pg13/pg13/share/locale
MANDIR /pg13/pg13/share/man
SHAREDIR /pg13/pg13/share/postgresql
SYSCONFDIR /pg13/pg13/etc/postgresql
PGXS /pg13/pg13/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE  '--prefix=/pg13/pg13'
CC gcc -std=gnu99
CPPFLAGS -D_GNU_SOURCE
CFLAGS -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
CFLAGS_SL -fPIC
LDFLAGS -Wl,--as-needed -Wl,-rpath,'/pg13/pg13/lib',--enable-new-dtags
LDFLAGS_EX  
LDFLAGS_SL  
LIBS -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -ldl -lm
VERSION PostgreSQL 13.3

[回到目录]