PostgreSQL日常运维

目录

一、PostgreSQL基础操作

1.1 登录数据库

1.2 数据库管理

1.3 数据表操作

二、数据备份与恢复

2.1 备份工具pg_dump

2.2 恢复工具pg_restore与psql

2.3 备份策略建议

三、模式（Schema）

3.1 模式的核心作用

3.2 模式操作全流程

四、远程连接配置

4.1 pg_hba.conf配置

4.2 安全认证方法对比

4.3 防火墙配置

五、密码重置

安全重置流程：避免权限滥用

六、实践

6.1 日常维护 checklist

6.2 性能优化初阶：几个简单有效的调整

6.3 学习资源推荐

七、总结

---

一、PostgreSQL基础操作

1.1 登录数据库

• 用户权限逻辑：
  PostgreSQL默认创建系统用户​​​postgres​​​，该用户拥有数据库超级权限。登录时需先切换至​​postgres​​​系统用户，再通过​​psql​​客户端连接数据库。

# 切换到postgres用户（需root权限）
[root@bogon ~]# su - postgres
# 启动psql终端（默认连接到postgres数据库）
[postgres@bogon ~]$ /usr/local/pgsql/bin/psql
# 成功登录后显示版本信息及提示符：postgres=#

• 关键说明：

• ​​psql​​​是PG的交互式终端，支持SQL语句与元命令（以​​\​​开头）混合使用。
• 若提示“command not found”，需检查PG安装路径是否添加到系统环境变量。

1.2 数据库管理

1.2.1 列出数据库：三种方式的适用场景

方法	命令	输出特点	使用场景
元命令（简洁）	​​\l​​	仅显示数据库名称、所有者	快速查看列表
扩展元命令	​​\l+​​	包含Size（字节）、Tablespace、Description	需了解存储详情
SQL查询	​​SELECT datname FROM pg_database;​​	基于系统表查询，可过滤条件	脚本化批量处理

• 系统表揭秘：
  ​​​pg_database​​​存储于​​pg_catalog​​​模式，属于系统目录表，无需指定模式即可查询（因​​pg_catalog​​默认在搜索路径首位）。

1.2.2 创建与删除数据库

-- 创建数据库（默认编码为UTF8，模板为template1）
CREATE DATABASE mydb WITH ENCODING 'UTF8' TEMPLATE template1;
-- 删除数据库（需确保无用户连接）
DROP DATABASE IF EXISTS mydb; -- 添加IF EXISTS避免报错

1.2.3 切换与查看数据库大小

\c mydb; -- 切换数据库，提示符变为mydb=#
-- 以友好格式显示数据库大小（自动转换为KB/MB/GB）
SELECT pg_size_pretty(pg_database_size('mydb')); 
-- 输出示例：7417 kB

1.3 数据表操作

1.3.1 表的CRUD操作

• 创建表
  PG除支持标准SQL类型（如​​​INT​​​、​​VARCHAR​​​、​​TIMESTAMP​​​），还提供几何类型（如​​POINT​​​、​​POLYGON​​​）、数组类型（如​​INT[]​​）及用户自定义类型。

CREATE TABLE employees (id SERIAL PRIMARY KEY, -- SERIAL自动生成唯一标识符name TEXT NOT NULL, -- TEXT类型存储长文本（无长度限制）salary NUMERIC(10, 2), -- 精确数值类型（总长度10，小数位2）hire_date DATE DEFAULT CURRENT_DATE -- 默认值为当前日期
);

• 复制表

-- 仅复制表结构（不含数据）
CREATE TABLE employees_backup AS TABLE employees WITH NO DATA;
-- 复制结构+数据
CREATE TABLE employees_backup AS TABLE employees;

1.3.2 表的元命令查询

元命令	功能	示例输出
​​\dt​​	列出当前数据库public模式下的表	架构模式
​​\d+​​	列出表、视图、序列并显示详细属性（如字段类型、注释）	栏位
​​\dt my_schema.*​​	列出指定模式下的所有表	架构模式

二、数据备份与恢复

2.1 备份工具pg_dump

2.1.1 全库备份

# 备份整个mydb数据库到文件（需指定用户名，-W提示输入密码）
pg_dump -U postgres -h localhost -W mydb > full_backup.sql
# 压缩备份文件（节省存储空间）
pg_dump -U postgres mydb | gzip > full_backup.sql.gz

2.1.2 增量备份

# 备份自上次全量备份以来的所有变化（需结合WAL日志）
pg_dump -U postgres --schema-only mydb > schema_backup.sql # 备份模式结构
pg_dump -U postgres --data-only mydb > data_backup.sql # 备份数据

2.1.3 跨版本备份

# 备份为纯SQL脚本（兼容低版本PG）
pg_dump -U postgres --schema-and-data --format=p mydb > legacy_backup.sql

2.2 恢复工具pg_restore与psql

2.2.1 文本格式恢复：最通用的恢复方式

# 步骤1：创建目标数据库（基于template0避免继承无关数据）
createdb -T template0 -O postgres new_mydb
# 步骤2：执行恢复（遇到错误立即终止）
psql -U postgres -d new_mydb -f full_backup.sql --set ON_ERROR_STOP=on

2.2.2 自定义格式恢复：更高效率的二进制方案

# 备份时使用自定义格式（-F c）
pg_dump -U postgres -F c mydb > mydb.dump
# 恢复时指定格式并选择性恢复（如仅恢复hr模式）
pg_restore -U postgres -d new_mydb -n hr mydb.dump

2.3 备份策略建议

场景	备份类型	频率	存储位置
生产环境	全量备份+增量备份	全量每周，增量每日	异地磁盘+云存储
开发测试	全量备份	按需	本地存储
关键业务	连续归档（结合pg_basebackup）	实时	独立备份服务器

三、模式（Schema）

3.1 模式的核心作用

• 避免命名冲突：同一数据库中不同模式可存在同名表（如​​hr.employees​​​与​​finance.employees​​）。
• 权限隔离：通过模式权限控制（如​​GRANT USAGE ON SCHEMA hr TO app_user​​），实现数据分层访问。
• 架构清晰化：按业务模块划分模式（如​​public​​​存放公共表，​​admin​​存放管理表）。

3.2 模式操作全流程

3.2.1 创建与删除模式

-- 创建模式并指定所有者
CREATE SCHEMA hr AUTHORIZATION app_admin;
-- 删除模式及其中所有对象（CASCADE级联删除）
DROP SCHEMA IF EXISTS hr CASCADE;

3.2.2 跨模式查询

-- 显式指定模式（推荐方式，避免歧义）
SELECT * FROM hr.employees WHERE salary > 10000;
-- 通过搜索路径隐式查询（需先设置search_path）
SET search_path TO hr, public; -- 优先搜索hr模式
SELECT * FROM employees; -- 等价于SELECT * FROM hr.employees;

3.2.3 模式与权限结合：细粒度访问控制

-- 授予用户使用模式的权限
GRANT USAGE ON SCHEMA hr TO app_user;
-- 授予用户查询hr模式下所有表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA hr TO app_user;

四、远程连接配置

4.1 pg_hba.conf配置

# 规则格式：<连接类型> <数据库> <用户> <地址/掩码> <认证方法>
# 示例1：允许IP段192.168.1.0/24内的所有用户通过md5密码认证连接mydb数据库
host mydb all 192.168.1.0/24 md5
# 示例2：允许任意IP的postgres用户通过scram-sha-256认证连接所有数据库（生产推荐）
host all postgres 0.0.0.0/0 scram-sha-256

4.2 安全认证方法对比

认证方法	原理	安全性	配置要点
​​trust​​	无需认证直接连接	低	仅用于本地或受信任网络（如127.0.0.1/32）
​​md5​​	密码加密传输（MD5哈希）	中	需设置用户密码（​​ALTER USER WITH PASSWORD​​）
​​scram-sha-256​​	安全密码哈希（SHA-256算法）	高	PG 10+默认推荐，需客户端支持
​​ident​​	基于系统用户名认证	中	适用于Linux本地用户与数据库用户同名场景

4.3 防火墙配置

# Linux系统（以firewalld为例）开放PG默认端口5432
firewall-cmd --add-port=5432/tcp --permanent
firewall-cmd --reload

五、密码重置

安全重置流程：避免权限滥用

备份配置文件（关键！防止操作失误）：

cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.bak

临时信任本地连接：
修改​​​pg_hba.conf​​​，将本地连接规则改为​​trust​​：

# 原规则（可能为scram-sha-256或md5）
host all all 127.0.0.1/32 scram-sha-256
# 修改后（临时允许本地无密码登录）
host all all 127.0.0.1/32 trust

重启服务并登录：

systemctl restart postgresql
psql -U postgres # 无需密码直接进入

修改密码（强密码原则：8位以上，含大小写、数字、符号）：

ALTER USER postgres WITH PASSWORD 'P@ssw0rd!';

恢复配置并验证：

# 还原pg_hba.conf
mv /var/lib/pgsql/data/pg_hba.conf.bak /var/lib/pgsql/data/pg_hba.conf
systemctl restart postgresql
# 验证连接（需输入新密码）
psql -U postgres -d postgres -W

六、实践

6.1 日常维护 checklist

• 每周执行全量备份，每日检查备份日志
• 每月审计用户权限（通过​​\du​​​查看用户角色，​​\z​​查看表权限）
• 定期清理无用数据库与表（使用​​DROP DATABASE​​​/​​DROP TABLE​​​，结合​​VACUUM ANALYZE​​回收空间）

6.2 性能优化初阶：几个简单有效的调整

• 设置搜索路径：将常用模式置于首位（如​​SET search_path TO hr, public;​​减少跨模式查询开销）
• 避免全表扫描：为高频查询字段添加索引（​​CREATE INDEX idx_employees_name ON employees(name);​​）
• 合理使用视图：通过​​CREATE VIEW​​封装复杂查询，提升SQL复用性

6.3 学习资源推荐

• 官方文档：​​PostgreSQL Documentation​​（权威但需一定基础）
• 社区工具：​​pgAdmin​​​（图形化管理工具，适合新手）、​​pg_stat_statements​​（查询分析扩展）
• 实战项目：尝试用PG搭建博客系统或数据分析平台，实践库表设计与备份策略

七、总结

通过本文，你已掌握PostgreSQL从基础操作到安全配置的核心技能。无论是数据库的增删改查、模式的逻辑隔离，还是远程连接的安全控制与密码重置的应急处理，均需结合实际场景反复练习。记住：数据库运维的核心是严谨性与预防性——提前制定备份策略、定期审计权限、关注版本更新（PG每年发布一个大版本，含重要安全修复），才能让你的数据库系统在复杂环境中稳健运行。