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示例允许所有 IPhost all all 0.0.0.0/0 md5修改postgresql.conf文件路径通常为/etc/postgresql/{版本}/main/postgresql.conf启用监听listen_addresses * # 监听所有网络接口重启 PostgreSQL 服务sudo systemctl restart postgresql配置防火墙开放默认端口5432sudo 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;