【数据库】PostgreSQL（持续更新）

K8S 部署

# pg.yaml
---
apiVersion: v1
kind: PersistentVolume
metadata:name: tv-postgres-pvnamespace: locallabels:storage: tv-postgres-pv
spec:accessModes:- ReadWriteOncecapacity:storage: 50Gi  # 按需修改，需要保持与下方一致persistentVolumeReclaimPolicy: RetainhostPath:path: /mnt/local/tv-postgres-pvtype: DirectoryOrCreate---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:name: tv-postgres-pvcnamespace: local
spec:accessModes:- ReadWriteOnceresources:requests:storage: 50GistorageClassName: ""volumeMode: FilesystemvolumeName: tv-postgres-pv---
apiVersion: v1
kind: ConfigMap
metadata:name: tv-postgres-configmapnamespace: local
data:init.sql: | CREATE EXTENSION IF NOT EXISTS ltree WITH SCHEMA public;CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;---
apiVersion: apps/v1
kind: StatefulSet
metadata:name: tv-postgresnamespace: local
spec:serviceName: "tv-postgres-service"replicas: 1selector:matchLabels:app: tv-postgrestemplate:metadata:labels:app: tv-postgresspec:containers:- name: tv-postgresimage:   # 你可以选择你需要的 PostgreSQL 版本imagePullPolicy: IfNotPresentports:- containerPort: 5432name: tv-postgresenv:- name: POSTGRES_USERvalue: postgres- name: POSTGRES_PASSWORDvalue: mypassword- name: PGDATAvalue: /var/lib/tv-postgresql/data/pgdatavolumeMounts:- name: tv-postgres-datamountPath: /var/lib/tv-postgresql/data- name: init-scriptsmountPath: /docker-entrypoint-initdb.dreadinessProbe:exec:command:- pg_isready- -U- tv-postgresinitialDelaySeconds: 5periodSeconds: 5livenessProbe:exec:command:- pg_isready- -U- tv-postgresinitialDelaySeconds: 60periodSeconds: 10volumes:- name: init-scriptsconfigMap:name: tv-postgres-configmap- name: tv-postgres-datapersistentVolumeClaim:claimName: tv-postgres-pvc---
apiVersion: v1
kind: Service
metadata:name: tv-postgres-servicenamespace: local
spec:ports:- port: 5432targetPort: 5432name: tv-postgresclusterIP: None  # Headless serviceselector:app: tv-postgres---
apiVersion: v1
kind: Service
metadata:name: tv-postgres-service-externalnamespace: local
spec:ports:- port: 5432targetPort: 5432protocol: TCPnodePort: 30042selector:app: tv-postgrestype: NodePort

基本使用


-- 系统
SELECT version();
-- 查看当前活动的会话，包括每个连接的状态、正在执行的查询等信息。
-- 这对于诊断性能问题和锁定问题非常有用。
select * from pg_stat_activity;-- 提供每个数据库的统计信息，如磁盘读取次数、块命中次数、事务数量等。
-- 该表可以帮助管理员了解各个数据库的工作负载情况。
select * from pg_stat_database;-- 显示用户定义的表（非系统表）的统计信息，例如扫描次数、插入/更新/删除操作的数量。
-- 此信息对于评估表的使用频率和性能调优很有帮助。
select * from pg_stat_user_tables;-- 类似于 pg_stat_user_tables，但是它提供了更详细的存储统计信息，
-- 包括表的数据文件读写次数，有助于了解I/O模式。
select * from pg_statio_user_tables;-- 显示用户定义的索引的统计信息，比如索引扫描次数、索引块读取次数等。
-- 这对于分析索引的有效性和利用率非常重要。
select * from pg_stat_user_indexes;-- 类似于 pg_stat_user_indexes，但它提供了关于索引存储的详细统计信息，
-- 包括索引数据文件的读写次数，这有助于评估索引的I/O效率。
select * from pg_statio_user_indexes;-- 数据库
DROP DATABASE IF EXISTS "mydb";
CREATE USER "admin" WITH PASSWORD 'admin';
CREATE DATABASE "mydb" OWNER "mydb_admin";
CREATE EXTENSION IF NOT EXISTS ltree WITH SCHEMA public;-- 表操作
CREATE TABLE weather (city            varchar(80),temp_lo         int,           -- 最低温度temp_hi         int,           -- 最高温度prcp            real,          -- 湿度date            date
);
DROP TABLE tablename;-- 增
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO weather (date, city, temp_hi, temp_lo)VALUES ('1994-11-29', 'Hayward', 54, 37);
-- 查
SELECT * FROM weather;
SELECT * FROM weather JOIN cities ON city = name;
SELECT max(temp_lo) FROM weather;
-- 改
UPDATE weatherSET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2WHERE date > '1994-11-28';
-- 删
DELETE FROM weather WHERE city = 'Hayward';

高级特性


-- 视图
CREATE VIEW myview ASSELECT name, temp_lo, temp_hi, prcp, date, locationFROM weather, citiesWHERE city = name;SELECT * FROM myview;-- 外键
CREATE TABLE cities (name     varchar(80) primary key,location point
);CREATE TABLE weather (city      varchar(80) references cities(name),temp_lo   int,temp_hi   int,prcp      real,date      date
);-- 事务
BEGIN;
UPDATE accounts SET balance = balance - 100.00WHERE name = 'Alice';
-- etc etc
COMMIT;-- 窗口函数
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;-- 继承
CREATE TABLE cities (name       text,population real,elevation  int     -- (in ft)
);CREATE TABLE capitals (state      char(2) UNIQUE NOT NULL
) INHERITS (cities);

内置工具：

pg_stat_activity：查看当前活动的会话。
pg_stat_database：提供每个数据库的统计信息。
pg_stat_user_tables 和 pg_statio_user_tables：用于表级别的统计信息。
pg_stat_user_indexes 和 pg_statio_user_indexes：用于索引级别的统计信息。
pg_locks：查看锁定状态。

扩展模块：
pg_stat_statements：这是一个非常有用的扩展，可以记录所有SQL语句的执行统计，包括调用次数、总时间等。
auto_explain：可以用来自动解释（分析）慢查询。
pgBadger：虽然不是一个官方扩展，但它是一个流行的日志分析工具，可以从PostgreSQL日志中生成详细的报告。
外部监控工具：
Prometheus + Grafana：结合使用Prometheus进行数据采集，Grafana用于可视化展示，是目前比较流行的一种组合。
pgMonitor：基于Prometheus和Grafana构建，专门为PostgreSQL设计的监控解决方案。
PgHero：一个开源的应用程序，它提供了对PostgreSQL数据库的洞察力，包括查询性能、索引使用情况、锁等待等。
Datadog, New Relic, AWS CloudWatch 等云服务提供商提供的监控工具也支持PostgreSQL。
日志文件：
配置适当的日志级别和参数来记录错误、警告和其他重要事件。通过分析日志文件，可以获得关于系统行为的重要线索。
自定义脚本：
根据特定需求编写shell脚本或使用编程语言（如Python）编写的脚本来定期检查某些指标或者执行诊断命令。
报警机制：
设置阈值并配置报警机制，当关键性能指标超出正常范围时通知管理员。这可以通过上述提到的工具实现，也可以通过独立的报警服务完成。