digoal / pgsql_admin_script Goto Github PK
View Code? Open in Web Editor NEW常用管理脚本
License: BSD 3-Clause "New" or "Revised" License
常用管理脚本
License: BSD 3-Clause "New" or "Revised" License
配置文件变更跟踪
pgsql_admin_script/generate_report.sh
Lines 781 to 799 in 5fbdcc8
这里用到的3个SQL在PG10中有改动
-- PG9
select pg_xlogfile_name(pg_current_xlog_location()) now_xlog, * from pg_stat_archiver;
-- PG10
select pg_walfile_name(pg_current_wal_lsn()) now_xlog, * from pg_stat_archiver;
--PG9
select pg_xlog_location_diff(pg_current_xlog_location(),flush_location), * from pg_stat_replication
--PG10
select pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn), * from pg_stat_replication;
--PG9
select pg_xlog_location_diff(pg_current_xlog_location(),restart_lsn), * from pg_replication_slots;
--PG10
select pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn), * from pg_replication_slots;
generate postgresql report
看看有没有危险参数,是否有优化空间等。
完善简单密码检查 .
cracklib 中的密码为$simple_pwd
数据库用户名为$username
检查pg_authid.rolpassword 是否和 'md5'||md5($simple_pwd||$username) 匹配,如果匹配,说明用户使用了简单密码。
如果是btrfs或者zfs,检查一下是否有通过快照来备份,
以及检查快照的有效性(pg_start_backup()后产生的快照)
快照的可恢复性(快照后产生的wal全部保留)
是否开启selinux
可以通过在psql 命令行中添加 -t 参数让查询结果中仅显示数据信息而不显示字段信息,方便在psql执行语句返回值传入shell等脚本进行判断和比较用。
查找pg_hba.conf和连接审计不一致的地方
MaxStartups 1000:30:3000
防止GP大量使用ssh连接时出现问题
等待事件、简单密码、配置文件变更、配置文件不一致、索引推荐、SQL rewrite优化
Transparent Huge Pages (THP)
https://docs.mongodb.com/manual/tutorial/transparent-huge-pages/
加入postgresql core 文件监控
pgsql_admin_script/generate_report.sh
Lines 704 to 709 in 5fbdcc8
德哥我在分析你给出的表膨胀SQL的时候,看到内部这个SQL中会输出:
bs | hdr | ma
------+-----+----
8192 | 23 | 8
(1 row)
SQL逻辑也比较简单,bs我能看出来是块大小,hdr是根据版本直接给出的,ma根据version 64bit给出的。这里的hdr和ma涉及到后面SQL的很多计算式,比如
...
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric
...
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END)))
...
想问一下这个hdr和ma是什么的缩写,代表什么意思?
ps. 我在分析表膨胀的长SQL,看到这个hdr和ma后面分析不下去了 :(
ps. 文章链接:https://github.com/mutex73/blog/blob/master/src/pgsql-sql-bloatsql.md
基于Postgresql 9.6 增加统计信息
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.