目录
一、基本使用
1.1登录数据库
PostgreSQL的登陆机制是其安全性的第一道防线。默认情况下，必须使用postgres用户进行初始登录，这是系统的超级用户，具备最高权限。登录流程如下：
su - postgres
/usr/local/pgsql/bin/psql登录成功后，命令行提示符变为postgres=#，表示当前连接的数据库为postgres，
核心要点：
- postgres用户是数据库的默认超级用户，权限管理需谨慎。
- 登录后可通过\conninfo查看当前连接信息，包括数据库名、用户、端口等。
1.2数据库操作
1.2.1列出库
PostgreSQL提供多种方式查看数据库列表：
1.元命令：
- \1：列出所有数据库的基本信息（名称、所有者、编码）。
- \1+：扩展输出、增加显示数据库大小（Size）、表空间（Tablespace）和描述（Description）。
2.SQL查询
SELECT datname FROM pg_database;pg_database是系统目录表，存储所有数据库的元数据，其内容不受当前连接数据库影响。
对比分析：
- 元命令适合快速交互式操作，而SQL查询更适合自动化脚本或复杂过滤需求。
- pg_database_size()和pg_size_pretty()函数可用于精确计算数据库占用的存储空间。
1.2.2创建与删除库
- 创建库：
CREATE DATABASE mydb;默认以template1为模板，可通过TEMPLATE参数指定其他模板（如template0）。
- 删除库：
DROP DATABASE mydb;注意事项：
- 删除数据库前需确保无活动连接，否则会报错。
- 生产环境中建议先备份再执行删除操作。
1.2.3切换与查看库
- 切换库：
\c mydb切换后提示符变为mydb=#，表示当前数据库为mydb。
- 查看库的大小：
SELECT pg_size_pretty(pg_database_size('mydb'));该命令将字节转换为易读单位（如MB、GB），便于运维监控。
1.3数据表操作
1.3.1列出表
- 元命令：
- \dt:列出当前数据库的普通表（默认public模式）。
- \dt schema.*:指定模式下的表，如\dt hr .*。
- \d+ : 显示表及其附加信息（如存储参数、描述）。
- SQL查询：
SELECT * FROM pg_tables WHERE schemaname = 'public';pg_tables是系统视图，基于pg_class和pg_namespace动态生成。
1.3.2创建与复制表
- 标准建表：
CREATE TABLE test (id INT, name CHAR(10), age INT);PostgreSQL支持丰富的字段类型（如SERTAL自增、JSONB二进制JSON）。
- 复制表：
CREATE TABLE new_table AS TABLE old_table;此操作仅复制表结构和数据，不复制约束、索引等高级对象。
1.3.3删除表与查看结构
- 删除表：
DROP TABLE test2;风险提示：删除表将永久丢失数据，建议结合事务或备份操作。
- 查看表结构：
\d test输出包括字段名、类型、是否可为空、默认值等元数据。
1.4模式（Schema）操作
1.4.1模式的作用
模式是数据库内的逻辑分组，用于解决命名冲突和权限隔离。例如，同一数据库内可存在hr.employees和finance.employees两个同名表。
1.4.2创建于删除模式
- 创建模式：
CREATE SCHEMA hr;- 删除模式：
DROP SCHEMA hr CASCADE;CASCADE表示级联删除模式下的所有对象（如表、视图）。
1.4.3搜索路径（Search Path0）
- 查看搜索路径：
SHOW search_path;默认值为"$user",public,表示优先查找与当前用户同名的模式。
- 修改搜索路径：
SET search_path TO hr, public;此设置仅对当前会话有效，需通过SLTER ROLE或修改postgresql.conf持久化。
1.4.4跨模式操作
- 显示指定模式名：
SELECT * FROM schema1.users;- 动态切换模式：
SET search_path TO schema1;
SELECT * FROM users; -- 默认访问schema1.users优势：避免频繁输入模式名，提升开发效率。
二、备份与恢复
2.1 SQL转储
2.1.1 pg_dump工具
- 基本用法：
pg_dump mydb > mydb_backup.sql特性：
- 生成标准SQL脚本，兼容不同PostgreSQL版本。
- 支持并行备份（-j 参数）和自定义格式（-Fc）。
2.1.2 恢复数据
- 全量恢复：
psql mydb < mydb_backup.sql注意事项：
- 目标数据库需预先创建。
- 使用 -1参数可将恢复过程封装为事务，确保原子性。
2.2pg_dumpall工具
- 集簇级备份：
pg_dumpall > cluster_backup.sql功能：备份所有数据库及全局对象（如角色、表空间）。
恢复命令：
psql -f cluster_backup.sql postgres2.3备份策略对比
| 方法 | 优点 | 缺点 | 适用场景 |
| SQL转储 | 跨版本兼容、逻辑备份 | 恢复时间较长 | 小型数据库、迁移 |
| 文件系统备份 | 快速、物理备份 | 依赖文件系统快照功能 | 大型数据库、定期全量 |
| 连续归档 | 支持PITR（时间点恢复） | 配置复杂、需持续归档 | 高可用、容灾需求 |
三、远程连接配置
3.1修改监听地址
- 配置文件路径：
- DNF安装：/var/lib/pgsql/data/postgresql.conf
- 源码编译：/usr/local/pgsql/data/postgresql.conf
- 关键参数：
listen_addresses = '*' # 允许所有IP连接3.2配置访问权限
- 编辑pg_hba.conf:
host all all 0.0.0.0/0 scram-sha-256认证方法说明：
- trust:无需密码（仅限内网测试）。
- md5/scram-sha-256:密码加密传输（推荐生产环境使用）。
3.3验证远程连接
- 客户端命令：
psql -h 192.168.10.102 -U postgres安全建议：
- 限制IP范围（如192.168.1.0/24）。
- 定期审计pg_hba.conf配置。
四、密码重置
4.1临时信任本地连接
1.备份pg_hba.conf：
cp pg_hba.conf pg_hba.conf.bak2.修改认证方法：
host all all 127.0.0.1/32 trust3.重启服务：
systemctl restart postgresql4.2修改密码
ALTER USER postgres WITH PASSWORD 'NewPassword123!';4.3恢复配置
- 还原pg_hba.conf并重启服务，确保密码生效。
五、总结与最佳实践
5.1 核心知识点总结
- 模式隔离：通过Schema实现逻辑分组，避免命令冲突。
- 备份策略：根据业务需求选择SQL转储、文件备份或连续归档。
- 安全配置：远程连接需结合IP限制和强认证方法（如SCRAM-SHA-256）。
5.2操作建议
- 定期备份：至少每日执行一次pg_dump全量备份。
- 权限最小化：避免使用postgres超级用户执行日常操作。
- 监控与优化：通过pg_stat_activity监控连接，定期执行VACUUM和ANALYZE。
通过掌握上述技能，用户可高效管理PostgreSQL数据库，为复杂业务场景提供稳定支持。