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 系统

1. 从 PostgreSQL 官网 下载对应版本安装包
2. 运行安装向导
3. 设置超级用户（postgres）密码
4. 选择端口（默认 5432）和语言环境
5. 安装完成后，可通过 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 远程访问设置

1. 编辑 postgresql.conf（启用远程连接）

listen_addresses = '*'

2. 编辑 pg_hba.conf（允许远程 IP）

# 允许任何 IP（生产环境提议限制 IP 范围）
host    all    all    0.0.0.0/0    md5

3. 重启服务：

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;

六、常见问题解决

1. 无法连接

• 检查 pg_hba.conf 和 postgresql.conf
• 确认防火墙是否放行 5432

2. 性能下降

• 定期执行 VACUUM 和 ANALYZE
• 检查慢查询日志

3. 忘记密码

• 修改 pg_hba.conf 为 trust 模式，重启后无需密码登录
• 重置密码后改回 md5 模式