PG

Postgres is good

—— Vonng

Posts / 文章

• [x] 计算机系为什么要学数据库原理和设计？
• [x] PG好处都有啥？
• [x] PostgreSQL开发规约
• [x] 并发异常那些事
• [x] 容器中的数据库是一个好主意吗？
• [x] Thou shalt not run a prod database inside a container (..but now I change my mind!)
• [x] 理解时间
• [x] 区块链与分布式数据库
• [x] 一致性：一个过载的术语
• [x] 架构演化：成熟度模型
• [x] PostgreSQL的KPI

Monitor / 监控

数据库没有监控系统，就像蒙着眼睛狂奔。

Run database without a monitoring system is like running while blindfloded

Monitor system / 监控系统

这里将介绍基于Prometheus，Grafana开发的Postgres监控系统 —— Pigsty (Postgres In Grafana Style)

• [x] 数据库集群管理概念与实体命名规范
• [ ] Pigsty监控系统架构
• [ ] Pigsty监控系统使用说明
• [ ] 服务发现
• [ ] Consul使用指南

Metrics / 监控指标

• [ ] [Node监控指标概览]
• [ ] [Postgres监控指标]
• [ ] [Pgbouncer中间件监控指标]
• [ ] [监控指标的聚合方式]
• [ ] [Prometheus指标预处理规则]
• [ ] [Prometheus机器报警规则]
• [ ] [Prometheus数据库报警规则]
• [ ] [黄金监控指标：PG Load]
• [ ] 9.4到13的监控指标变化梳理

Catalog Monitoring / 监控系统目录

• [x] 监控PG中表的大小
• [x] 监控WAL生成速率
• [x] 关系膨胀：监控与处理
• [x] PG中表占用磁盘空间
• [x] 使用pg_repack整理表与索引
• [ ] 监控表：空间，膨胀，年龄，IO
• [ ] 监控索引：空间，膨胀，重复，闲置
• [ ] 静态监控，配置项与角色
• [ ] 轻重缓急，快慢分离
• [ ] 操作系统监控
• [ ] 监控CPU使用
• [ ] 监控磁盘网络IO
• [ ] 监控数据库基本指标
• [ ] 监控死锁
• [ ] 监控连接
• [ ] 监控活动
• [ ] 监控复制延迟
• [ ] 系统级别监控
• [ ] 监控函数：调用量，时间
• [ ] 监控连接池：QPS，延迟，排队，连接
• [ ] 监控自动清理与检查点
• [ ] 系统视图详解
• [ ] 系统水位测量、经验值
• [ ] 确保表没有访问

Administration / 管理

当一个人能完成所有工作时，他是不需要管理的。

管理方案

• [x] PostgreSQL安装部署
• [x] PostgreSQL日志配置
• [x] PostgreSQL复制方案
• [x] PostgreSQL备份方案
• [x] PostgreSQL监控系统
• [x] PostgreSQL报警系统
• [x] PostgreSQL变更管理方案
• [x] PostgreSQL目录设计

备份与复制

• [ ] PostgreSQL备份与恢复概览
• [ ] PostgreSQL复制延迟问题
• [ ] 日志传输副本：WAL段复制
• [ ] 复制拓扑设计：同步、异步、法定人数
• [ ] 逻辑复制：发布与订阅
• [ ] 故障切换，权衡，比可用性更重要的是完整性

运维调优

• [ ] 维护表：VACUUM配置、问题、原理与实践。
• [ ] 重建索引：细节与注意事项
• [ ] 备份：机制、流程、问题、方法。
• [ ] 逻辑备份：pg_dump
• [ ] PITR生产实践
• [ ] PostgreSQL内存相关参数调谐
• [ ] PostgreSQL检查点相关参数调谐
• [ ] PostgreSQL自动清理相关参数调谐
• [ ] 操作系统内核参数调优
• [ ] ErrorTracking系统设计概览

配置

• [ ] PostgreSQL配置修改方式
• [ ] PostgreSQL客户端认证
• [ ] PostgreSQL角色权限

升级迁移

• [ ] 飞行中换引擎：PostgreSQL不停机数据迁移
• [ ] 跨大版本升级PostgreSQL，10与先前版本的不兼容性统计

扩展性

• [ ] 垂直拆分，分库分表
• [ ] 水平拆分与分片
• [ ] 如何管理几百个PostgreSQL实例

故障

• [x] 故障档案：移走负载导致的性能恶化故障
• [x] pg_dump导致的血案
• [x] PostgreSQL数据页损坏修复
• [x] 故障档案：事务ID回卷故障
• [x] 故障档案：pg_repack导致的故障
• [x] 故障档案：从删库到跑路
• [x] Template0的清理与修复
• [ ] 内存错误导致操作系统丢弃页面缓存
• [ ] 磁盘写满故障
• [ ] 救火：杀查询的正确姿势
• [ ] 存疑事务：提交日志损坏问题分析与修复
• [ ] 客户端大量无超时查询堆积导致故障
• [ ] 慢查询堆积导致的雪崩，定位与排查
• [ ] 硬件故障导致的机器重启
• [ ] Docker同一数据目录启动两个实例导致数据损坏
• [ ] 级联复制的配置问题

Development / 开发

案例

• [x] KNN问题极致优化：以找出最近餐馆为例
• [x] PostGIS高效解决行政区划归属查询问题
• [x] 使用PostgreSQL实现简易推荐系统
• [x] 使用PostgreSQL实现IP地理位置查询
• [x] 使用审计触发器自动记录数据变更
• [x] 实现基于通知触发器的逻辑复制
• [ ] 标签管理系统元数据库设计
• [ ] 实时用户画像系统数据库设计
• [ ] 博客数据库设计
• [ ] 使用Pg监控Pg：元数据库设计
• [ ] 连接池：连接数背后的问题
• [ ] 选择合适的全局唯一ID生成方式
• [ ] QPS/TPS：一个容易误解的指标
• [ ] 使用三维/四维点存储时空轨迹
• [ ] 自动化后端：PostGraphQL, PgRest, PostgRest横向对比
• [ ] PostGraphQL：解放前后端生产力
• [ ] postgres_fdw应用：管理远程数据库

SQL

• [x] PostgreSQL中的触发器
• [x] PostgreSQL中的锁
• [ ] PostgreSQL的LOCALE与本地化
• [ ] PostgreSQL 12 JSON
• [ ] PostgreSQL中的时间与时区
• [ ] Sequence的方方面面
• [ ] 常见索引类型及其应用场景
• [ ] PostgreSQL中的JOIN
• [ ] 子查询还是CTE？
• [ ] LATERAL JOIN
• [ ] DISTINCT ON子句与除重
• [ ] 递归查询
• [ ] Advanced SQL
• [ ] 找出并清除重复的记录
• [ ] Pl/PgSQL快速上手
• [ ] 函数的权限管理
• [x] PostgreSQL函数易变性分类

驱动

• [x] Golang的数据库标准接口教程：database/sql
• [ ] PostgreSQL驱动横向评测：Go语言
• [ ] PostgreSQL Golang驱动介绍：pgx
• [ ] PostgreSQL Golang驱动介绍：go-pg
• [ ] PostgreSQL Python驱动介绍：psycopg2
• [ ] psycopg2的进阶包装，让Python访问Pg更敏捷。
• [ ] PostgreSQL Node.JS驱动介绍：node-postgres

Kernel / 内核原理

• [x] PostgresSQL变更数据捕获
• [x] PostgreSQL前后端协议概述
• [x] PostgreSQL的逻辑结构与物理结构
• [x] 事务隔离等级
• [ ] 并发创建索引的实现方式（CREATE INDEX CONCURRENTLY）
• [ ] GIN索引的实现原理
• [ ] B树索引的原理与实现细节
• [ ] 查询处理原理
• [ ] JOIN类型及其内部实现
• [ ] VACUUM原理
• [ ] WAL：PostgreSQL WAL与检查点
• [ ] 流复制原理与实现细节
• [ ] 二阶段提交：原理与实践
• [ ] R树原理与实现细节
• [ ] PostgreSQL数据页结构
• [ ] FDW的结构与编写
• [ ] SSD Internal

Tools / 工具

命令行

• [x] psqlrc 使用基础
• [x] 批量配置SSH免密登录
• [x] 组合使用psql与bash

连接池

• [x] pgbouncer安装
• [x] pgbouncer配置文件
• [x] pgbouncer使用方法
• [ ] pgpool的应用方式

操作系统

• [x] 查看系统任务 —— top
• [x] 查看内存使用 —— free
• [x] 查看虚拟内存使用 —— vmstat
• [x] 查看IO —— iostat
• [ ] 查看硬盘信息——smartctl
• [ ] 查看网卡信息——ethtool

网络

• [ ] 使用Wireshark抓包分析PostgreSQL协议

性能测试

• [ ] pgbench
• [ ] sysbench

FDW

• [x] FileFDW妙用无穷——从数据库读取系统信息
• [x] RedisFDW Installation
• [x] MongoFDW Installation
• [ ] IMPORT FOREIGN SCHEMA与远程元数据管理
• [ ] MongoFDW设计与实现
• [ ] HBase FDW设计与实现
• [ ] 基于Multicorn编写FDW

PostGIS

• [x] PostGIS安装

• [x] Introduction to PostGIS

• [ ] DE9IM

• [ ] 地理坐标系相关知识

• [ ] PostGIS空间相交：DE9IM

• [ ] Geometry还是Geography？

• [ ] QGIS安装与简单使用

• [ ] TimescaleDB安装与使用

• [ ] PipelineDB安装

• [ ] PgAdmin Server 安装

• [ ] PgBackRest 中文文档

Reference

• PostgreSQL Documentation
  • Current 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4

• PostgreSQL 中文文档
• PostgreSQL Commit Fest
• PostGIS 3.0 Documentation

• Citus Documentation
• TimescaleDB Documentation
• PipelineDB Documentation
• Pgbouncer Documentation
• PG-INTERNAL