作者
digoal
日期
2021-05-17
标签
PostgreSQL , SaaS , K8S , cloud native postgresql , 跨云 , SQL规整 , 执行计划解读和优化建议 , 参数优化 , AWR , 索引推荐 , 错误日志解读和应对策略
背景
数据库生命周期管理类产品, K8S架构, 支持跨云部署.
1、EDB (来自PG全球开发组major committee厂商)
https://www.enterprisedb.com/docs/kubernetes/cloud_native_postgresql/
- Direct integration with Kubernetes API server for High Availability, without requiring an external tool (与Kubernetes API服务器直接集成以实现高可用性，而无需外部工具)
- Self-Healing capability, through (通过以下方式实现自治):
- failover of the primary instance by promoting the most aligned replica (自动选取与主实例最接近的副本对主实例的故障进行故障转移)
- automated recreation of a replica (自动重建副本)
- Planned switchover of the primary instance by promoting a selected replica (支持选择指定的副本进行有计划的主动主从角色切换)
- Scale up/down capabilities (扩容、缩容功能)
- Definition of an arbitrary number of instances (minimum 1 - one primary server) ( 定义任意数量的实例（最少1个-一台主服务器）)
- Definition of the read-write service, to connect your applications to the only primary server of the cluster (定义读写服务，以将您的应用程序连接到数据库集群的主实例)
- Definition of the read-only service, to connect your applications to any of the instances for reading workloads (定义只读服务，以将您的应用程序连接到数据库集群的任何实例(包括主实例和只读实例))
- Support for Local Persistent Volumes with PVC templates (通过PVC模板支持本地永久卷)
- Reuse of Persistent Volumes storage in Pods (重用Pods中的持久卷存储)
- Rolling updates for PostgreSQL minor versions and operator upgrades (自动和手动PostgreSQL小版本升级)
- TLS connections and client certificate authentication (TLS连接和客户端证书认证)
- Continuous backup to an S3 compatible object store (连续备份到兼容S3的对象存储)
- Full recovery and Point-In-Time recovery from an S3 compatible object store backup (从S3兼容的对象存储备份中进行全量恢复和时间点恢复)
- Support for Synchronous Replicas (支持同步副本(quorum based sync replication))
- Support for node affinity via nodeSelector (通过nodeSelector支持节点亲和(例如数据库主从节点不能在同一台物理机上))
- Standard output logging of PostgreSQL error messages (PostgreSQL错误消息的标准输出日志记录)
2、crunchydata (来自PG全球开发组major committee厂商)
https://www.crunchydata.com/products/crunchy-postgresql-for-kubernetes/
https://github.com/CrunchyData/postgres-operator/
Production PostgreSQL for Kubernetes, from high availability Postgres clusters to full-scale database-as-a-service.
- Cluster Infrastructure
- Cluster Infrastructure provides fundamental functions for nodes to work together as a cluster, configuration-file management, membership management, lock management, and fencing.
- High Availability Service Management
- Provides failover of services from one cluster node to another in case a node becomes inoperative.
- Cluster Administration Tools
- Provides configuration and management tools for setting up, configuring, and managing the HA Add-On. The tools are for use with the Cluster infrastructure components, the HA and Service Management components, and storage.
- Heterogeneous Replication
- Replicates data across different platforms, with compatibility for many databases. Synchronize from any database to any database in a heterogeneous environment.
3、中国人开发的pigsty
http://pigsty.cc/zh/
https://github.com/Vonng/pigsty/
- 做最好的开源PostgreSQL监控系统
- 开箱即用的生产级数据库供给方案
- 让数据库的使用与管理不再有门槛
- Monitoring System based on prometheus & grafana & pg_exporter
- Provisioning Solution based on ansible. Kubernetes style, scale at ease.
- HA Deployment based on patroni. Self-healing and failover in seconds
- Service Discovery based on DCS (consul / etcd), maintenance made easy.
- Offline Installation without Internet access. Fast and reliable.
- Infrastructure as Code. Fully configurable and customizable.
- Based on PostgreSQL 13 and Patroni 2. Verified in proudction environment (CentOS 7, 200+nodes)
Remote DBA 工具或SaaS或插件
主要用于发现问题, 解决问题. 例如: SQL自动优化、SQL审查、远程DBA、数据库远程诊断、SQL 格式化、SQL autoexplain结果解读、报错信息解读等web服务.
SaaS工具在于降低数据库使用门槛, 不需要专业DBA知识也能用好数据库.
用户群体:
开发者
DBA
运维
已有产品:
1、pgdash
- https://pgdash.io/features
- 使用pgmetrics收集数据库运行过程统计信息, 可视化展示、问题分析等. 目标是暴露性能问题, 解决性能问题.
2、pganalyze
- https://pganalyze.com/
- Discover the root cause of critical issues, optimize slow queries, and find missing indices.
- 通过 pganalyze collector 采集信息, 同时需要 Enable pg_stat_statements
特性如下:
- Postgres Query Analysis
- Get per-query statistics and query drilldowns
- Catch slow queries before they become big problems
- Postgres index check
- Postgres explain plans
- Specific example queries for more insights
- Database Visualization
- Get insights via sophisticated dashboards
- Analyze meaningful trends and historic query performance
- Monitor your key metrics in real-time
- Collect insights with Postgres Autovacuum Monitoring
- EXPLAIN Plans
- Visualization of query plans
- EXPLAIN plan insights
- Direct integration with auto_explain
- Easy references to schema data
- Grouping of similar plans
- Detailed documentation
- Log Insights
- auto_explain Integration
- Postgres Vacuum Monitoring
- PII Filtering
- Performance Optimization
- Comparison Over Time & Analyze Trends
- Config Tuning Recommendations
- Smart Index Check
- Buffer Cache Hit Ratio
- Option to run On-Premise
- Encrypted Log Monitoring
- Store statistics in compliance with local policies
- Sensitive data
- SSO Integration
- Careful PII handling / query normalization
- Sophisticated User Management and Permissions
3、pgstats
- 口袋地图(pg动态视图鸟瞰图) :
- https://pgstats.dev/
4、auto tune postgresql.conf
- 自动参数优化:
- https://github.com/timescale/timescaledb-tune
5、SQL standard
- SQL 规整:
- https://www.depesz.com/
6、explain reading
- explain 解读和优化建议:
- https://explain.depesz.com/
7、AWR
- AWR 插件:
- https://github.com/percona/pg_stat_monitor
8、索引推荐
- 索引推荐插件:
- https://github.com/HypoPG/hypopg
9、错误代码速查和解决方案策略
哪些用户会用?
- 完全托管(数据库服务):
- 中小用户, 没有专业运维团队, 需要全托管的服务. 大型客户核心场景, 开源满足不了客户需求, 需要内核背锅侠.
- 半托管(使用数据库生命周期管理SaaS开源产品):
- 中等用户, 有运维devops团队, 已使用K8S管理编排IT资源.
- SaaS、工具、插件类产品: (数据库监控、优化、诊断、SQL自动优化、SQL审查、远程DBA、数据库远程诊断、SQL 格式化、SQL autoexplain结果解读、报错信息解读等web服务)
- 所有类型用户, 研发和DBA都可以使用, 提高工作效率.
- 门槛是采集可能有agent要部署.
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
