postgresql

PostgreSQL

在开发和生产环境中管理、优化并保护 PostgreSQL 数据库。

适用场景

• 你需要可靠且符合 ACID 标准的关系型数据库。
• 你的应用需要 JSONB、全文搜索或 CTE 等高级功能。
• 你正在设置流复制或时间点恢复。
• 你需要调整现有 PostgreSQL 部署，以提高吞吐量。

前置条件

• Linux 服务器（Debian/Ubuntu 或基于 RHEL 的系统）或 Docker。
• 用于安装软件包的 root 或 sudo 访问权限。
• 熟悉 SQL 基础知识。

安装与设置

# Debian / Ubuntu
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# RHEL / Amazon Linux
sudo dnf install -y postgresql15-server postgresql15-contrib
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

# Verify
psql --version
sudo systemctl status postgresql

初始用户和数据库设置

# Switch to the postgres system user
sudo -u postgres psql

-- Create an application user
CREATE USER myapp WITH PASSWORD 'strong_password_here';

-- Create the database owned by that user
CREATE DATABASE mydb OWNER myapp;

-- Grant connection privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;

-- Connect to the database and set default privileges
\c mydb
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp;

psql 命令参考

\l              -- list databases
\dt             -- list tables in current database
\d+ tablename   -- describe table with storage info
\du             -- list roles
\x              -- toggle expanded output
\timing on      -- show query execution time
\i file.sql     -- execute SQL from file
\copy           -- fast client-side COPY

配置调优

编辑 /etc/postgresql/15/main/postgresql.conf（路径因操作系统和版本而异）。

# Connection settings
listen_addresses = '*'
max_connections = 200

# Memory — adjust to ~25% of total RAM for shared_buffers
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 512MB

# WAL / write performance
wal_buffers = 64MB
checkpoint_completion_target = 0.9
min_wal_size = 1GB
max_wal_size = 4GB

# Planner
random_page_cost = 1.1          # lower for SSD
effective_io_concurrency = 200  # for SSD

# Logging
log_min_duration_statement = 250   # log queries slower than 250 ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

# Reload configuration without restart
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# Some settings (shared_buffers, max_connections) require a full restart
sudo systemctl restart postgresql

pg_hba.conf — 客户端身份验证

# /etc/postgresql/15/main/pg_hba.conf
# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       postgres                  peer
host    mydb      myapp     10.0.0.0/8      scram-sha-256
host    all       all       0.0.0.0/0       reject

sudo systemctl reload postgresql

备份与恢复

使用 pg_dump 进行逻辑备份

# Plain SQL backup
pg_dump -U myapp -h localhost mydb > /backups/mydb_$(date +%F).sql

# Custom compressed format (recommended)
pg_dump -U myapp -h localhost -Fc mydb > /backups/mydb_$(date +%F).dump

# Backup a single table
pg_dump -U myapp -h localhost -t orders -Fc mydb > /backups/orders.dump

# Restore from custom format
pg_restore -U myapp -h localhost -d mydb --clean --if-exists /backups/mydb_2025-01-15.dump

# Restore plain SQL
psql -U myapp -h localhost -d mydb < /backups/mydb_2025-01-15.sql

使用 pg_basebackup 进行物理备份

# Full base backup (used for PITR and replica seeding)
pg_basebackup -h localhost -U replicator -D /backups/base_$(date +%F) \
  --wal-method=stream --checkpoint=fast --progress --verbose

# Verify the backup
pg_verifybackup /backups/base_2025-01-15

流复制

主服务器

-- Create replication user
CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'repl_secret';

# postgresql.conf on primary
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB

# pg_hba.conf on primary
host replication replicator 10.0.0.0/8 scram-sha-256

副本服务器

# Stop PostgreSQL on the replica
sudo systemctl stop postgresql

# Remove existing data directory
sudo rm -rf /var/lib/postgresql/15/main/*

# Base backup from primary
sudo -u postgres pg_basebackup \
  -h 10.0.0.1 -U replicator \
  -D /var/lib/postgresql/15/main \
  --wal-method=stream --checkpoint=fast --progress

# Create standby signal file
sudo -u postgres touch /var/lib/postgresql/15/main/standby.signal

# postgresql.conf on replica
primary_conninfo = 'host=10.0.0.1 port=5432 user=replicator password=repl_secret'
hot_standby = on

sudo systemctl start postgresql

验证复制

-- On primary
SELECT client_addr, state, sent_lsn, replay_lsn
FROM pg_stat_replication;

-- On replica
SELECT pg_is_in_recovery();           -- should return true
SELECT pg_last_wal_receive_lsn();
SELECT pg_last_wal_replay_lsn();

监控查询

-- Active connections by state
SELECT state, COUNT(*)
FROM pg_stat_activity
GROUP BY state;

-- Long-running queries (> 30 seconds)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '30 seconds'
ORDER BY duration DESC;

-- Table bloat and dead tuples
SELECT relname,
       n_live_tup,
       n_dead_tup,
       ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Index usage statistics
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 10;

-- Cache hit ratio (should be > 99%)
SELECT ROUND(
  100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2
) AS cache_hit_pct
FROM pg_stat_database;

-- Database size
SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

Docker Compose 设置

# docker-compose.yml
version: "3.9"

services:
  postgres:
    image: postgres:16-alpine
    restart: unless-stopped
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: myapp
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: mydb
    volumes:
      - pg_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    command: >
      postgres
        -c shared_buffers=256MB
        -c work_mem=8MB
        -c maintenance_work_mem=128MB
        -c effective_cache_size=768MB
        -c log_min_duration_statement=250
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U myapp -d mydb"]
      interval: 10s
      timeout: 5s
      retries: 5

  pgbouncer:
    image: edoburu/pgbouncer:latest
    restart: unless-stopped
    ports:
      - "6432:6432"
    environment:
      DATABASE_URL: postgres://myapp:secret@postgres:5432/mydb
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 500
      DEFAULT_POOL_SIZE: 40
    depends_on:
      postgres:
        condition: service_healthy

volumes:
  pg_data:

docker compose up -d
psql -h 127.0.0.1 -p 6432 -U myapp mydb

维护任务

# Manual VACUUM and ANALYZE
sudo -u postgres psql -d mydb -c "VACUUM ANALYZE;"

# Reindex a bloated index
sudo -u postgres psql -d mydb -c "REINDEX INDEX CONCURRENTLY idx_orders_user_id;"

# Check for unused indexes
sudo -u postgres psql -d mydb -c "
  SELECT indexrelname, idx_scan
  FROM pg_stat_user_indexes
  WHERE idx_scan = 0
  ORDER BY pg_relation_size(indexrelid) DESC;"

故障排除

症状	可能原因	解决方法
FATAL: too many connections	达到连接数上限	增大 max_connections 或添加 PgBouncer
大型表上的 SELECT 速度缓慢	缺少索引或统计信息过时	运行 EXPLAIN ANALYZE；添加索引；运行 ANALYZE
autovacuum 导致 CPU 使用率过高	存在大量死元组	调整 autovacuum_vacuum_cost_delay；手动运行 VACUUM
复制延迟不断增加	副本资源不足或网络瓶颈	检查 pg_stat_replication；增大 wal_keep_size
could not access file "base/..."	磁盘已满或数据目录损坏	释放磁盘空间；从 pg_basebackup 恢复
FATAL: password authentication failed	凭据错误或 pg_hba.conf 不匹配	检查 pg_hba.conf 条目并重新加载

相关技能

• mysql - 另一种关系型数据库
• database-backups - 自动备份策略
• redis - 用于减轻数据库负载的缓存层
• planetscale - 托管的 MySQL 兼容替代方案