PostgreSQL 从入门到主从备份

2026年8月5日 · 数据库

MySQL 还没学透就开始学 PostgreSQL 了，因为看到不少项目都在用 PG，而且它的高级特性确实很吸引人。这个月花了些时间从零开始学习。

安装

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

# 检查状态
sudo systemctl status postgresql

基础操作

PG 默认有一个 postgres 系统用户，需要用这个用户才能操作数据库：

# 切换到 postgres 用户
sudo -i -u postgres

# 进入 psql 交互界面
psql

# 创建数据库
CREATE DATABASE myapp;

# 创建用户
CREATE USER myuser WITH PASSWORD 'mypassword';

# 授权
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

# 退出
\q

配置远程连接

默认 PG 只允许本地访问，要远程连接需要改两个文件：

# 1. postgresql.conf 监听所有地址
listen_addresses = '*'

# 2. pg_hba.conf 允许远程连接
host    all             all             0.0.0.0/0               md5

# 重启生效
sudo systemctl restart postgresql

备份与恢复

学会了三种备份方式：

pg_dump（逻辑备份）：

# 备份单个数据库
pg_dump -U myuser myapp > myapp_backup.sql

# 恢复
psql -U myuser myapp < myapp_backup.sql

pg_basebackup（物理备份，用于主从）：

# 在主库上执行
pg_basebackup -h 主库IP -D /var/lib/postgresql/16/main -U replicator -P -v

# 在从库上配置 recovery.conf 然后启动
sudo systemctl start postgresql

主从复制配置（简化版）

高可用的基础：

主库创建复制用户：CREATE USER replicator WITH REPLICATION;
主库配置 wal_level = replica、max_wal_senders = 5
从库用 pg_basebackup 同步数据
从库配置 primary_conninfo 指向主库

心得体会

PG 给我的感觉是非常"规矩"，SQL 标准支持得很好。它的 MVCC 机制和 MySQL 不太一样，但理解了之后觉得更清晰。不过入门曲线确实比 MySQL 陡一点，配置文件也更复杂。但学完后写复杂查询时，PG 的能力就体现出来了——窗口函数、CTE、JSON 支持，真的强。