## 什么是 PostgreSQL
PostgreSQL 是一款强调数据可靠性、SQL 能力和可扩展性的开源关系型数据库，尤其适合对数据一致性和复杂业务处理要求较高的系统。
## PostgreSQL 的主要优势
1. 数据可靠性非常强
```sql
BEGIN;
UPDATE account
SET balance = balance - 100
WHERE id = 1;
UPDATE account
SET balance = balance + 100
WHERE id = 2;
COMMIT;
```
2. 复杂 SQL 能力非常强
```sql
WITH ...
SELECT ...
JOIN ...
GROUP BY ...
HAVING ...
WINDOW FUNCTION ...
```
3. JSONB 非常好用
```sql
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name TEXT,
profile JSONB -- { "age": 30, "city": "Shanghai", "tags": ["vip", "developer"] }
);
SELECT * FROM users WHERE profile->>'city' = 'Shanghai';
```
4. 数据类型非常丰富
5. 扩展生态很强
6. 主从复制和高可用能力成熟
7. MVCC 并发控制成熟
## 主从复制
**Streaming Replication（流复制）** 是 PostgreSQL 原生提供的主从复制机制，也是 PostgreSQL 做高可用、热备最常见的基础方案。
```
服务器 A 服务器 B
PostgreSQL Primary PostgreSQL Standby
│ │
│ INSERT / UPDATE / DELETE │
▼ │
产生 WAL │
│ │
└──── Streaming WAL ────────────►│
▼
重放 WAL
│
▼
数据保持一致
```
**docker-compose 文件**
```yaml
services:
postgres:
image: postgres:16.13-bookworm
container_name: postgres
restart: always
networks:
- common_network
ports:
- 5432:5432
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
POSTGRES_DB: postgres
volumes:
- postgres:/var/lib/postgresql/data
command:
- postgres
- -c
- wal_level=replica
- -c
- max_wal_senders=10
- -c
- max_replication_slots=10
- -c
- wal_keep_size=1024MB
volumes:
postgres:
```
**服务器 A，Primary（主库）**
```sh
# 启动主库
docker compose up -d
# 进入 postgres
docker exec -it postgres psql -U postgres
# 创建复制用户
CREATE ROLE replicator
WITH
REPLICATION
LOGIN
PASSWORD '密码';
# 退出
exit
# 查找数据目录，通常是 /var/lib/postgresql/data/pg_hba.conf
docker exec postgres psql -U postgres -c "SHOW hba_file;"
# 进入容器
docker exec -it postgres bash
# 修改主库
echo "host replication replicator 192.168.33.0/24 scram-sha-256" \
>> /var/lib/postgresql/data/pg_hba.conf
# 退出
exit
# 重新加载 pg_hba
docker exec postgres psql -U postgres -c "SELECT pg_reload_conf();"
# 检查主库监听，通常返回 *
docker exec postgres psql -U postgres -c "SHOW listen_addresses;"
```
**服务器 B，Standby（从库）**
```sh
# 创建 volume，这里的 database 一般是当前 docker-compose 文件所在的目录名
docker volume create database_postgres
# 初始化从库，全量备份
docker run --rm \
-e PGPASSWORD='密码' \
-v database_postgres:/var/lib/postgresql/data \
postgres:16.13-bookworm \
pg_basebackup \
-h 服务器地址 \
-U replicator \
-D /var/lib/postgresql/data \
-Fp \
-Xs \
-P \
-R
# 启动从库
docker compose up -d
# 查看日志，检查从库状态
docker logs -f postgres
# 正常应该出现类似：
# started streaming WAL from primary
```
**检查主从状态**
```sh
# 检查主备状态，分别在 服务器A、服务器B 执行
docker exec postgres \ psql -U postgres \ -c "SELECT pg_is_in_recovery();"
# 返回值 f (false)：表示数据库实例处于正常运行状态（通常是主库）可以进行读写操作
# 返回值 t (true)：表示数据库实例正在进行 WAL 日志重放，处于恢复阶段或只读备库状态。
# 主库检查复制状态
docker exec postgres \
psql -U postgres \
-c "
SELECT
application_name,
client_addr,
state,
sync_state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn
FROM pg_stat_replication;
"
# client_addr | state | sync_state
# ---------------+-----------+-----------
# 192.168.33.133 | streaming | async
# 看到 state 为 streaming，说明配置成功了
```