PostgreSQL实战：从入门到高可用架构

作者：博睿谷Eva |  pgcm认证课程

---

一、快速部署与基础操作

1. 极简安装（5分钟搞定）

• Linux一键安装：

  sudo apt-get update && sudo apt-get install postgresql postgresql-contrib
  sudo systemctl start postgresql

  
  

• Docker部署（开发环境首选）：

  docker run --name mypostgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

  
  

2. 基本配置调优（入门必改项）

• 修改连接数限制：

  ALTER SYSTEM SET max_connections = 200;

  
  

• 调整内存分配：

  ALTER SYSTEM SET shared_buffers = '4GB';  -- 通常设为内存的25%
  ALTER SYSTEM SET work_mem = '64MB';       -- 复杂查询必备

  
  

• 重启生效：

  sudo systemctl restart postgresql

  
  

二、高阶功能实战

1. JSONB数据魔方（电商场景）

• 存储商品SKU信息：

  CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB
  );
  
  INSERT INTO products (details) VALUES (
    '{"name": "智能手表", "price": 899, "specs": {"color": "黑", "waterproof": true}}'
  );

  
  

• 闪电查询：

  SELECT * FROM products WHERE details @> '{"specs": {"waterproof": true}}';

  
  

2. 地理围栏（物流系统）

• 启用PostGIS扩展：

  CREATE EXTENSION postgis;

  
  

• 查询5公里内配送点：

  SELECT name FROM delivery_points 
  WHERE ST_DWithin(
    location::geography, 
    ST_MakePoint(116.4074, 39.9042)::geography, 
    5000
  );

  
  

三、性能压榨秘籍

1. 索引优化（10倍查询加速）

• GIN索引横扫JSONB：

  CREATE INDEX idx_products_details ON products USING GIN (details);

  
  

• BRIN索引碾压时间序列：

  CREATE INDEX idx_sales_time ON sales USING BRIN (sale_time);

  
  

2. 分库分表（亿级数据应对）

• 水平分片（pg_shard扩展）：

  CREATE EXTENSION pg_shard;
  SELECT master_create_distributed_table('orders', 'user_id', 'hash');
  SELECT master_create_worker_shards('orders', 4);

  
  

四、高可用架构（金融级方案）

1. 流复制双活

• 主库配置：

  ALTER SYSTEM SET wal_level = replica;
  ALTER SYSTEM SET max_wal_senders = 10;

  
  

• 从库同步：

  pg_basebackup -h 主库IP -D /var/lib/postgresql/12/main -U replicator -v -P

  
  

2. Patroni自动化灾备

• 三节点集群部署：

  # patroni.yml
  scope: pg-cluster
  name: node1
  restapi:
    listen: 0.0.0.0:8008
  etcd:
    host: 192.168.1.100:2379
  postgresql:
    data_dir: /var/lib/postgresql/12/main
    pgpass: /tmp/pgpass
    parameters:
      max_connections: 200

  
  

五、云原生部署（AWS实战）

1. RDS参数组优化

• 自定义参数组：

  rds.modify_db_parameter_group(
    DBParameterGroupName='my-custom-group',
    Parameters=[
        {'ParameterName': 'shared_preload_libraries', 'ParameterValue': 'pg_stat_statements'},
        {'ParameterName': 'work_mem', 'ParameterValue': '65536'}
    ]
  )

  
  

2. 读写分离配置

• 应用层路由（Python示例）：

  from sqlalchemy import create_engine
  writer = create_engine('postgresql://master:password@primary.rds.amazonaws.com/db')
  reader = create_engine('postgresql://replica:password@replica.rds.amazonaws.com/db')

  
  

立即扫码，免费获取
√《PostgreSQL性能调优红宝书》
√ 生产环境配置模板

（附：慢查询分析脚本+备份恢复应急预案）

总结：从单机到集群，从本地到云上，PostgreSQL用开源的身躯扛起企业级重任。掌握这些实战技巧，你也能成为老板眼中的数据库救世主！