1. PostgreSQL连接与基础配置刚接触PostgreSQL时第一步就是要学会如何连接数据库。我刚开始做DBA的时候经常被各种连接参数搞得晕头转向后来才发现其实掌握几个关键参数就够了。最基础的连接命令是这样的psql -h 127.0.0.1 -p 5432 -U postgres -d postgres这里解释下每个参数-h指定服务器地址如果是本地可以省略-p指定端口默认5432-U指定用户名-d指定要连接的数据库第一次连接可能会遇到密码认证失败的问题这时候需要检查pg_hba.conf文件。这个文件控制着客户端的认证方式位置通常在PostgreSQL的数据目录下。找到类似这样的行# TYPE DATABASE USER ADDRESS METHOD host all all 127.0.0.1/32 md5把method改成trust就可以免密登录了生产环境千万别这么干。改完后记得用pg_ctl reload重新加载配置。连接成功后你会看到提示符变成postgres#这时候就可以开始操作数据库了。我习惯先用\conninfo看看当前的连接信息避免操作错数据库。2. 数据库与表操作实战2.1 数据库管理在PostgreSQL中数据库是最顶层的对象。刚接手新环境时我通常会先用\l命令查看所有数据库\l这个命令相当于MySQL的show databases会列出所有数据库名、所有者、编码等信息。创建新数据库有两种方式-- SQL方式 CREATE DATABASE mydb WITH OWNER myuser ENCODING UTF8; -- 命令行方式 createdb -U postgres -E UTF8 -O myuser mydb我更喜欢用SQL方式因为可读性更好。创建时记得指定正确的编码不然中文可能会乱码。删除数据库要特别小心DROP DATABASE IF EXISTS mydb;加IF EXISTS可以避免数据库不存在时报错。注意删除前要确保没有活跃连接否则会失败。2.2 表操作技巧表是存储数据的核心。查看当前数据库所有表\dt查看表结构\d users这个命令会显示字段名、类型、约束等详细信息比MySQL的desc更全面。创建表时我建议加上注释方便后期维护CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); COMMENT ON TABLE users IS 用户基本信息表; COMMENT ON COLUMN users.username IS 用户名唯一标识;修改表结构是常见操作比如添加字段ALTER TABLE users ADD COLUMN phone VARCHAR(20);重命名字段ALTER TABLE users RENAME COLUMN phone TO mobile;删除字段前一定要确认数据可以丢失ALTER TABLE users DROP COLUMN mobile;3. 用户权限精细化管理3.1 用户与角色PostgreSQL的用户权限系统非常强大但也比较复杂。首先查看所有用户\du创建新用户CREATE ROLE dev_user WITH LOGIN PASSWORD secure123;我习惯用ROLE而不是USER因为实际上两者是等价的。WITH LOGIN表示允许登录。修改密码ALTER ROLE dev_user WITH PASSWORD newpassword;删除用户前要撤销所有权限DROP ROLE IF EXISTS dev_user;3.2 权限控制实战授权是门艺术。给用户数据库权限GRANT ALL PRIVILEGES ON DATABASE mydb TO dev_user;授权特定表的CRUD权限GRANT SELECT, INSERT, UPDATE ON users TO dev_user;更细粒度的列级授权GRANT UPDATE(username, email) ON users TO dev_user;查看已有权限\z users撤销权限同样重要REVOKE DELETE ON users FROM dev_user;生产环境中我建议遵循最小权限原则只授予必要的权限。4. 数据导入导出技巧4.1 导出数据pg_dump是备份利器。导出整个数据库pg_dump -h localhost -U postgres -F c -f mydb.dump mydb-F c表示自定义格式压缩率高-f指定输出文件只导出表结构pg_dump -s -t users mydb users_schema.sql导出特定表数据pg_dump -t users mydb users_data.sql4.2 导入数据恢复数据库pg_restore -h localhost -U postgres -d newdb mydb.dump执行SQL文件psql -h localhost -U postgres -d mydb -f data.sql在psql中也可以执行\i /path/to/data.sql我遇到过编码问题导致导入失败的情况这时可以指定编码PGCLIENTENCODINGUTF8 psql -U postgres -d mydb -f data.sql5. 备份与恢复方案5.1 基础备份PostgreSQL提供了多种备份方式。最简单的SQL转储pg_dumpall backup.sql这个命令会备份所有数据库包括用户权限等全局对象。对于大型数据库建议使用并行备份pg_dump -j 4 -F d -f /backup/mydb mydb-j指定并行度-F d表示目录格式5.2 时间点恢复(PITR)配置归档是PITR的前提。修改postgresql.confwal_level replica archive_mode on archive_command test ! -f /backup/wal/%f cp %p /backup/wal/%f然后做基础备份pg_basebackup -D /backup/base -Ft -z -P恢复时需要指定恢复目标restore_command cp /backup/wal/%f %p recovery_target_time 2023-06-01 12:00:006. 性能监控与日常维护6.1 监控关键指标查看活跃查询SELECT * FROM pg_stat_activity;我经常用这个命令找出长事务或锁等待。数据库统计信息SELECT * FROM pg_stat_database;表级别的统计SELECT * FROM pg_stat_user_tables;索引使用情况SELECT * FROM pg_stat_user_indexes;6.2 日常维护命令定期执行VACUUM回收空间VACUUM (VERBOSE, ANALYZE) users;对于大表建议使用VACUUM FULL VERBOSE ANALYZE users;重建索引提高查询性能REINDEX TABLE users;查看锁情况SELECT * FROM pg_locks;杀死卡住的查询SELECT pg_cancel_backend(pid); -- 强制终止 SELECT pg_terminate_backend(pid);7. 高级运维技巧7.1 连接池管理查看当前连接数SELECT count(*) FROM pg_stat_activity;调整最大连接数需要重启max_connections 100我建议配合连接池使用比如pgBouncer。7.2 参数调优关键参数调整shared_buffers 4GB # 25% of total RAM work_mem 16MB # for complex queries maintenance_work_mem 256MB # for VACUUM etc. random_page_cost 1.1 # for SSD storage修改后需要重启或重载配置pg_ctl reload7.3 日志分析配置日志log_destination stderr logging_collector on log_directory pg_log log_filename postgresql-%Y-%m-%d.log log_rotation_age 1d查看慢查询log_min_duration_statement 1000 # ms
【PostgreSQL运维实战：从连接到备份的完整命令手册】
1. PostgreSQL连接与基础配置刚接触PostgreSQL时第一步就是要学会如何连接数据库。我刚开始做DBA的时候经常被各种连接参数搞得晕头转向后来才发现其实掌握几个关键参数就够了。最基础的连接命令是这样的psql -h 127.0.0.1 -p 5432 -U postgres -d postgres这里解释下每个参数-h指定服务器地址如果是本地可以省略-p指定端口默认5432-U指定用户名-d指定要连接的数据库第一次连接可能会遇到密码认证失败的问题这时候需要检查pg_hba.conf文件。这个文件控制着客户端的认证方式位置通常在PostgreSQL的数据目录下。找到类似这样的行# TYPE DATABASE USER ADDRESS METHOD host all all 127.0.0.1/32 md5把method改成trust就可以免密登录了生产环境千万别这么干。改完后记得用pg_ctl reload重新加载配置。连接成功后你会看到提示符变成postgres#这时候就可以开始操作数据库了。我习惯先用\conninfo看看当前的连接信息避免操作错数据库。2. 数据库与表操作实战2.1 数据库管理在PostgreSQL中数据库是最顶层的对象。刚接手新环境时我通常会先用\l命令查看所有数据库\l这个命令相当于MySQL的show databases会列出所有数据库名、所有者、编码等信息。创建新数据库有两种方式-- SQL方式 CREATE DATABASE mydb WITH OWNER myuser ENCODING UTF8; -- 命令行方式 createdb -U postgres -E UTF8 -O myuser mydb我更喜欢用SQL方式因为可读性更好。创建时记得指定正确的编码不然中文可能会乱码。删除数据库要特别小心DROP DATABASE IF EXISTS mydb;加IF EXISTS可以避免数据库不存在时报错。注意删除前要确保没有活跃连接否则会失败。2.2 表操作技巧表是存储数据的核心。查看当前数据库所有表\dt查看表结构\d users这个命令会显示字段名、类型、约束等详细信息比MySQL的desc更全面。创建表时我建议加上注释方便后期维护CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); COMMENT ON TABLE users IS 用户基本信息表; COMMENT ON COLUMN users.username IS 用户名唯一标识;修改表结构是常见操作比如添加字段ALTER TABLE users ADD COLUMN phone VARCHAR(20);重命名字段ALTER TABLE users RENAME COLUMN phone TO mobile;删除字段前一定要确认数据可以丢失ALTER TABLE users DROP COLUMN mobile;3. 用户权限精细化管理3.1 用户与角色PostgreSQL的用户权限系统非常强大但也比较复杂。首先查看所有用户\du创建新用户CREATE ROLE dev_user WITH LOGIN PASSWORD secure123;我习惯用ROLE而不是USER因为实际上两者是等价的。WITH LOGIN表示允许登录。修改密码ALTER ROLE dev_user WITH PASSWORD newpassword;删除用户前要撤销所有权限DROP ROLE IF EXISTS dev_user;3.2 权限控制实战授权是门艺术。给用户数据库权限GRANT ALL PRIVILEGES ON DATABASE mydb TO dev_user;授权特定表的CRUD权限GRANT SELECT, INSERT, UPDATE ON users TO dev_user;更细粒度的列级授权GRANT UPDATE(username, email) ON users TO dev_user;查看已有权限\z users撤销权限同样重要REVOKE DELETE ON users FROM dev_user;生产环境中我建议遵循最小权限原则只授予必要的权限。4. 数据导入导出技巧4.1 导出数据pg_dump是备份利器。导出整个数据库pg_dump -h localhost -U postgres -F c -f mydb.dump mydb-F c表示自定义格式压缩率高-f指定输出文件只导出表结构pg_dump -s -t users mydb users_schema.sql导出特定表数据pg_dump -t users mydb users_data.sql4.2 导入数据恢复数据库pg_restore -h localhost -U postgres -d newdb mydb.dump执行SQL文件psql -h localhost -U postgres -d mydb -f data.sql在psql中也可以执行\i /path/to/data.sql我遇到过编码问题导致导入失败的情况这时可以指定编码PGCLIENTENCODINGUTF8 psql -U postgres -d mydb -f data.sql5. 备份与恢复方案5.1 基础备份PostgreSQL提供了多种备份方式。最简单的SQL转储pg_dumpall backup.sql这个命令会备份所有数据库包括用户权限等全局对象。对于大型数据库建议使用并行备份pg_dump -j 4 -F d -f /backup/mydb mydb-j指定并行度-F d表示目录格式5.2 时间点恢复(PITR)配置归档是PITR的前提。修改postgresql.confwal_level replica archive_mode on archive_command test ! -f /backup/wal/%f cp %p /backup/wal/%f然后做基础备份pg_basebackup -D /backup/base -Ft -z -P恢复时需要指定恢复目标restore_command cp /backup/wal/%f %p recovery_target_time 2023-06-01 12:00:006. 性能监控与日常维护6.1 监控关键指标查看活跃查询SELECT * FROM pg_stat_activity;我经常用这个命令找出长事务或锁等待。数据库统计信息SELECT * FROM pg_stat_database;表级别的统计SELECT * FROM pg_stat_user_tables;索引使用情况SELECT * FROM pg_stat_user_indexes;6.2 日常维护命令定期执行VACUUM回收空间VACUUM (VERBOSE, ANALYZE) users;对于大表建议使用VACUUM FULL VERBOSE ANALYZE users;重建索引提高查询性能REINDEX TABLE users;查看锁情况SELECT * FROM pg_locks;杀死卡住的查询SELECT pg_cancel_backend(pid); -- 强制终止 SELECT pg_terminate_backend(pid);7. 高级运维技巧7.1 连接池管理查看当前连接数SELECT count(*) FROM pg_stat_activity;调整最大连接数需要重启max_connections 100我建议配合连接池使用比如pgBouncer。7.2 参数调优关键参数调整shared_buffers 4GB # 25% of total RAM work_mem 16MB # for complex queries maintenance_work_mem 256MB # for VACUUM etc. random_page_cost 1.1 # for SSD storage修改后需要重启或重载配置pg_ctl reload7.3 日志分析配置日志log_destination stderr logging_collector on log_directory pg_log log_filename postgresql-%Y-%m-%d.log log_rotation_age 1d查看慢查询log_min_duration_statement 1000 # ms
