七、事务与并发控制
7.1 事务管理
-- 显式事务
BEGIN;
-- 或
START TRANSACTION;
-- 事务操作
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- 保存点
SAVEPOINT before_check;
-- 检查余额
SELECT balance FROM accounts WHERE account_id = 1;
-- 回滚到保存点
ROLLBACK TO SAVEPOINT before_check;
-- 提交
COMMIT;
-- 回滚
ROLLBACK;
-- 设置事务特性
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
-- 设置事务超时
SET statement_timeout = '5s';
-- 设置空闲事务超时
SET idle_in_transaction_session_timeout = '10s';
7.2 隔离级别
-- 查看当前隔离级别
SHOW transaction_isolation;
-- 设置隔离级别
-- READ UNCOMMITTED（实际为READ COMMITTED）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- READ COMMITTED（默认）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 演示可重复读
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM employees WHERE employee_id = 1;
-- 其他会话修改数据
SELECT * FROM employees WHERE employee_id = 1; -- 返回相同结果
COMMIT;
-- 演示序列化异常
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
-- 可能因序列化失败而回滚
7.3 锁机制
-- 行级锁（SELECT FOR UPDATE）
BEGIN;
SELECT * FROM employees WHERE employee_id = 1 FOR UPDATE;
-- 更新操作
UPDATE employees SET salary = 10000 WHERE employee_id = 1;
COMMIT;
-- 共享锁（FOR SHARE）
BEGIN;
SELECT * FROM employees WHERE department_id = 10 FOR SHARE;
-- 其他会话可以读但不能修改
COMMIT;
-- 带超时的锁
BEGIN;
SELECT * FROM employees WHERE employee_id = 1 FOR UPDATE NOWAIT;
SELECT * FROM employees WHERE employee_id = 2 FOR UPDATE WAIT 5;
COMMIT;
-- 跳过锁定的行
BEGIN;
SELECT * FROM employees WHERE is_active = true FOR UPDATE SKIP LOCKED;
COMMIT;
-- 表级锁
LOCK TABLE employees IN ACCESS SHARE MODE;
LOCK TABLE employees IN ROW SHARE MODE;
LOCK TABLE employees IN ROW EXCLUSIVE MODE;
LOCK TABLE employees IN SHARE UPDATE EXCLUSIVE MODE;
LOCK TABLE employees IN SHARE MODE;
LOCK TABLE employees IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE employees IN EXCLUSIVE MODE;
LOCK TABLE employees IN ACCESS EXCLUSIVE MODE;
-- 查看锁信息
SELECT
locktype,
relation::regclass,
mode,
granted,
pid,
query
FROM pg_locks
LEFT JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE locktype NOT IN ('virtualxid', 'transactionid');
-- 查看阻塞查询
SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
ON blocked.wait_event_type = 'Lock'
AND blocking.pid = ANY(pg_blocking_pids(blocked.pid));
-- 终止会话
SELECT pg_terminate_backend(pid);
八、备份与恢复
8.1 逻辑备份
# pg_dump - 备份单个数据库
pg_dump -U postgres -d mydb > mydb_backup.sql
# pg_dump - 备份特定表
pg_dump -U postgres -d mydb -t employees -t departments > tables_backup.sql
# pg_dump - 只备份结构
pg_dump -U postgres -d mydb --schema-only > schema_backup.sql
# pg_dump - 只备份数据
pg_dump -U postgres -d mydb --data-only > data_backup.sql
# pg_dump - 使用压缩
pg_dump -U postgres -d mydb | gzip > mydb_backup.sql.gz
# pg_dumpall - 备份所有数据库
pg_dumpall -U postgres > all_dbs_backup.sql
# 恢复
psql -U postgres -d mydb < mydb_backup.sql
# 从压缩文件恢复
gunzip -c mydb_backup.sql.gz | psql -U postgres -d mydb
# 恢复所有数据库
psql -U postgres -f all_dbs_backup.sql postgres
8.2 物理备份
# 基础备份
# 开启归档
# postgresql.conf
archive_mode = on
archive_command = 'cp %p /archive/%f'
# 执行基础备份
psql -c "SELECT pg_start_backup('backup_label');"
tar -czf /backup/base_backup.tar.gz /var/lib/postgresql/16/main
psql -c "SELECT pg_stop_backup();"
# 恢复
# 停止服务
# 清理数据目录
# 恢复基础备份
tar -xzf /backup/base_backup.tar.gz -C /var/lib/postgresql/16/main
# 创建 recovery.signal 文件
touch /var/lib/postgresql/16/main/recovery.signal
# 配置恢复
# 启动服务
# 使用 pg_basebackup
pg_basebackup -U postgres -D /backup/base_backup -Fp -Xs -P -R
# 使用 Barman 备份
barman backup main
barman list-backup main
barman recover main latest /recovery/directory
8.3 PITR（时间点恢复）
-- 开启 WAL 归档
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command = 'cp %p /archive/%f';
SELECT pg_reload_conf();
-- 执行基础备份
SELECT pg_start_backup('pitr_backup');
-- 复制数据目录
SELECT pg_stop_backup();
-- 配置恢复
-- 创建 recovery.conf（PostgreSQL 12之前）
-- 或创建 recovery.signal（PostgreSQL 12+）
-- recovery.conf 示例
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2024-01-01 12:00:00'
recovery_target_timeline = 'latest'
-- 启动恢复
-- 查看恢复状态
SELECT pg_is_in_recovery();
-- 完成恢复后，数据库自动打开
-- 取消恢复
SELECT pg_wal_replay_resume();
九、复制与高可用
9.1 流复制
# 主库配置（postgresql.conf）
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
hot_standby = on
# 主库配置（pg_hba.conf）
host replication replicator 192.168.1.0/24 md5
# 创建复制用户
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'password';
# 备库配置
# 停止备库
pg_ctl stop
# 从主库备份
pg_basebackup -h master_ip -U replicator -D /var/lib/postgresql/16/main -Fp -Xs -P -R
# 备库配置（postgresql.conf）
hot_standby = on
primary_conninfo = 'host=master_ip port=5432 user=replicator password=password'
# 启动备库
pg_ctl start
# 查看复制状态
SELECT * FROM pg_stat_replication;
SELECT pg_is_in_recovery();
# 查看 WAL 发送位置
SELECT application_name, state, sync_state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
9.2 逻辑复制
-- 发布端配置
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
-- 创建发布
CREATE PUBLICATION my_publication FOR TABLE employees, departments;
CREATE PUBLICATION all_tables FOR ALL TABLES;
CREATE PUBLICATION filter_pub FOR TABLE employees WHERE department_id = 10;
-- 查看发布
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;
-- 订阅端配置
max_replication_slots = 10
max_logical_replication_workers = 10
-- 创建订阅
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=master_ip port=5432 dbname=mydb user=replicator password=password'
PUBLICATION my_publication;
-- 查看订阅
SELECT * FROM pg_subscription;
SELECT * FROM pg_stat_subscription;
-- 刷新订阅
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;
-- 禁用/启用订阅
ALTER SUBSCRIPTION my_subscription DISABLE;
ALTER SUBSCRIPTION my_subscription ENABLE;
十、安全管理
10.1 用户与角色
-- 创建角色
CREATE ROLE app_user WITH LOGIN PASSWORD 'password';
CREATE ROLE readonly;
CREATE ROLE readwrite;
-- 创建用户（等同 CREATE ROLE + LOGIN）
CREATE USER app_user WITH PASSWORD 'password';
-- 授予角色权限
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
-- 创建超级用户
CREATE ROLE admin WITH SUPERUSER LOGIN;
-- 设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;
-- 查看角色
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole
FROM pg_roles;
-- 删除角色
DROP ROLE app_user;
-- 修改角色属性
ALTER ROLE app_user WITH SUPERUSER;
ALTER ROLE app_user WITH NOSUPERUSER;
ALTER ROLE app_user VALID UNTIL '2024-12-31';
ALTER ROLE app_user SET statement_timeout = '5min';
10.2 权限管理
-- 数据库权限
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT CREATE, TEMP ON DATABASE mydb TO app_user;
-- 模式权限
GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO app_user;
-- 表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO app_user;
GRANT ALL PRIVILEGES ON employees TO admin;
-- 列权限
GRANT SELECT (employee_id, first_name, last_name) ON employees TO app_user;
-- 序列权限
GRANT USAGE ON SEQUENCE employees_employee_id_seq TO app_user;
-- 函数权限
GRANT EXECUTE ON FUNCTION get_employee_salary(INTEGER) TO app_user;
-- 查看权限
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'app_user';
-- 撤销权限
REVOKE DELETE ON employees FROM app_user;
-- 行级安全策略（RLS）
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
CREATE POLICY employee_policy ON employees
USING (department_id = current_setting('app.current_department')::INTEGER);
CREATE POLICY employee_select_policy ON employees
FOR SELECT
USING (is_active = true);
10.3 SSL/TLS加密
# 生成SSL证书
openssl req -new -text -nodes -subj '/CN=localhost' -out server.req
openssl rsa -in privkey.pem -out server.key
openssl req -x509 -in server.req -text -key server.key -out server.crt
# 配置postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
# 配置pg_hba.conf
hostssl all all 0.0.0.0/0 md5
# 强制SSL连接
ALTER ROLE app_user SET ssl = 'on';
