PostgreSQL 安装与日常使用
PostgreSQL 是一款功能强大、开源的对象关系型数据库，支持高级 SQL 标准、扩展功能、事务完整性和高并发。
本指南涵盖 安装、配置、日常使用、性能优化、常见问题 等完整流程，适用于开发与生产环境。
一、安装指南
1.1 Linux 系统（以 Ubuntu/Debian 为例）
sudo apt update
sudo apt install postgresql postgresql-contrib
启动与开机自启：
sudo systemctl start postgresql
sudo systemctl enable postgresql
连接 PostgreSQL：
sudo -u postgres psql
1.2 Windows 系统
- 从 PostgreSQL 官网 下载对应版本安装包
- 运行安装向导
- 设置超级用户（postgres）密码
- 选择端口（默认 5432）和语言环境
- 安装完成后，可通过 pgAdmin 或命令行连接数据库
1.3 macOS 系统（Homebrew 安装）
brew install postgresql
brew services start postgresql
1.4 Docker 安装（推荐开发环境）
docker run -d \
--name postgres \
-e POSTGRES_PASSWORD=123456 \
-e POSTGRES_USER=postgres \
-e POSTGRES_DB=mydb \
-p 5432:5432 \
-v /data/postgres:/var/lib/postgresql/data \
postgres:16
二、基本配置
2.1 修改密码
ALTER USER postgres WITH PASSWORD '新密码';
2.2 创建用户与数据库
CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydb OWNER myuser;
2.3 远程访问设置
- 编辑 postgresql.conf（启用远程连接）
listen_addresses = '*'
- 编辑 pg_hba.conf（允许远程 IP）
# 允许任何 IP（生产环境建议限制 IP 范围）
host all all 0.0.0.0/0 md5
- 重启服务：
sudo systemctl restart postgresql
三、日常使用
3.1 常用命令速查表
命令 | 作用 |
\l | 列出所有数据库 |
\c 数据库名 | 切换数据库 |
\dt | 列出所有表 |
\d 表名 | 查看表结构 |
\q | 退出 psql |
3.2 数据操作
-- 创建表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT
);
-- 插入数据
INSERT INTO users (name, age) VALUES ('Tom', 25);
-- 查询数据
SELECT * FROM users WHERE age > 20;
-- 更新数据
UPDATE users SET age = 30 WHERE name = 'Tom';
-- 删除数据
DELETE FROM users WHERE id = 1;
3.3 视图与索引
CREATE VIEW adult_users AS SELECT * FROM users WHERE age >= 18;
CREATE INDEX idx_users_age ON users(age);
3.4 事务
BEGIN;
UPDATE users SET age = age + 1 WHERE id = 1;
COMMIT; -- 或 ROLLBACK;
3.5 备份与恢复
# 导出
pg_dump -U postgres -d mydb > backup.sql
# 恢复
psql -U postgres -d mydb < backup.sql
四、进阶功能
4.1 表空间（存储到指定路径）
CREATE TABLESPACE fastspace LOCATION '/mnt/ssd/pgdata';
CREATE DATABASE fastdb TABLESPACE fastspace;
4.2 定时备份（Linux crontab）
0 2 * * * pg_dump -U postgres mydb > /backup/mydb_$(date +\%F).sql
4.3 高可用（简述）
- 流复制（Streaming Replication）
- Patroni / repmgr 管理集群
- 负载均衡：PgBouncer、HAProxy
五、性能优化建议
参数 | 建议值（参考） | 说明 |
shared_buffers | 系统内存的 25% | 缓存数据页 |
work_mem | 4MB~64MB | 单次查询内存 |
maintenance_work_mem | 64MB~512MB | VACUUM/索引构建内存 |
max_connections | 根据并发需求调整 | 连接数限制 |
effective_cache_size | 系统可用内存的 50%~75% | 查询优化器参考值 |
优化技巧：
EXPLAIN ANALYZE SELECT ...;
VACUUM FULL;
ANALYZE;
六、常见问题解决
- 无法连接
- 检查 pg_hba.conf 和 postgresql.conf
- 确认防火墙是否放行 5432
- 性能下降
- 定期执行 VACUUM 和 ANALYZE
- 检查慢查询日志
- 忘记密码
- 修改 pg_hba.conf 为 trust 模式，重启后无需密码登录
- 重置密码后改回 md5 模式