PostgreSQL 日常维护

一、基本使用

1. 连接数据库

• 使用 PostgreSQL 自带的命令行客户端psql是最基本的方式：

  psql -U <username> -d <database_name> -h <host> -p <port>

  • -U: 指定用户名
  • -d: 指定要连接的数据库名
  • -h: 指定数据库服务器主机地址（本地可省略或用localhost）
  • -p: 指定数据库服务器端口（默认5432可省略）
• 连接成功后，会进入psql的交互式命令行界面，提示符通常类似database_name=>。

2. 用户与权限管理

• 创建用户：

  CREATE USER username WITH PASSWORD 'your_password';

  • 更精细控制可使用CREATE ROLE。
• 修改用户密码：

  ALTER USER username WITH PASSWORD 'new_password';

• 授权：授予用户对特定数据库对象的访问权限（如SELECT,INSERT,UPDATE,DELETE,ALL PRIVILEGES）。

  GRANT privilege_type ON database_name.table_name TO username; -- 示例：授予 SELECT 权限 GRANT SELECT ON employees TO report_user;

• 撤销权限：

  REVOKE privilege_type ON database_name.table_name FROM username;

• 查看用户权限：可通过系统视图information_schema.role_table_grants或pg_roles查询。

3. 数据库操作

• 创建数据库：

  CREATE DATABASE database_name;

  • 可指定所有者OWNER username。
• 切换当前数据库（在psql内）：

  \c database_name

• 列出所有数据库：

  \l

• 删除数据库：

  DROP DATABASE database_name;

  • 注意：删除数据库操作不可逆，需谨慎。

4. 表操作

• 创建表：

  CREATE TABLE table_name ( column1 datatype [constraints], column2 datatype [constraints], ... );

  • 常用数据类型：INTEGER,BIGINT,NUMERIC,VARCHAR(n),TEXT,DATE,TIMESTAMP,BOOLEAN等。
  • 常用约束：PRIMARY KEY,FOREIGN KEY,NOT NULL,UNIQUE,CHECK。
• 查看表结构：

  \d table_name

• 修改表（添加列）：

  ALTER TABLE table_name ADD COLUMN new_column datatype;

• 重命名表：

  ALTER TABLE old_table_name RENAME TO new_table_name;

• 删除表：

  DROP TABLE table_name;

5. 数据操作

• 插入数据：

  INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

• 查询数据：

  SELECT column1, column2, ... FROM table_name [WHERE condition] [ORDER BY ...] [LIMIT ...];

• 更新数据：

  UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

  • 重要：务必使用WHERE子句限定范围，否则会更新所有行！
• 删除数据：

  DELETE FROM table_name WHERE condition;

  • 重要：务必使用WHERE子句限定范围，否则会删除所有数据！

6. 索引管理

• 创建索引（加速查询）：

  CREATE INDEX index_name ON table_name (column_name);

  • 可创建唯一索引CREATE UNIQUE INDEX ...。
  • 可创建多列索引CREATE INDEX ... ON table_name (col1, col2)。
• 查看索引：

  \d table_name -- 会列出表相关的索引

• 删除索引：

  DROP INDEX index_name;

7. 备份与恢复

• 逻辑备份 (pg_dump)：导出数据库结构和数据（文本或自定义格式）。
  • 备份单个数据库：

    pg_dump -U username -d database_name -f backup_file.sql

  • 备份所有数据库（需超级用户权限）：

    pg_dumpall -U postgres -f all_databases.sql

• 恢复逻辑备份：

  psql -U username -d database_name -f backup_file.sql

• 物理备份 (文件系统级)：复制PGDATA目录下的数据文件。通常需要结合基础备份 (Base Backup)和WAL 归档 (Write-Ahead Log Archiving)来实现持续恢复（PITR）。这属于更高级的维护范畴。

8. 查看系统信息

• 查看版本：

  SELECT version();

• 查看活动连接：

  SELECT * FROM pg_stat_activity;

• 查看数据库大小：

  SELECT pg_size_pretty(pg_database_size('database_name'));

• 查看表大小：

SELECT pg_size_pretty(pg_total_relation_size('table_name'));

9.远程连接

修改pg_hba.conf文件

路径通常为/etc/postgresql/{版本}/main/pg_hba.conf

添加规则：

host all all {客户端IP或网段} md5

示例（允许所有 IP）：

host all all 0.0.0.0/0 md5

修改postgresql.conf文件

路径通常为/etc/postgresql/{版本}/main/postgresql.conf

启用监听：

listen_addresses = '*' # 监听所有网络接口

重启 PostgreSQL 服务

sudo systemctl restart postgresql

配置防火墙

开放默认端口5432：

sudo ufw allow 5432/tcp

客户端连接命令

psql -h {服务器IP} -U {用户名} -d {数据库名}

示例：

psql -h 192.168.1.100 -U postgres -d testdb

关键注意事项

安全建议

避免使用0.0.0.0/0（可替换为具体 IP 段）。

生产环境建议使用证书认证（cert替代md5）。

常见问题排查

检查服务状态：sudo systemctl status postgresql

验证端口监听：netstat -tuln | grep 5432

客户端测试连通性：telnet {服务器IP} 5432

密码认证

若使用md5，需确保用户密码已设置：

ALTER USER postgres PASSWORD 'your_password';