PostgreSQL 日常管理命令

核心概念

PostgreSQL日常管理命令可分为以下几类：

• 连接与会话管理：用于连接数据库和管理会话
• 数据库对象管理：用于创建、修改和删除数据库对象
• 用户与权限管理：用于管理数据库用户和权限
• 性能监控与优化：用于监控数据库性能和进行优化
• 备份与恢复：用于数据库备份和恢复操作
• 系统管理：用于管理PostgreSQL服务和配置

连接与会话管理

psql连接命令

psql是PostgreSQL的命令行客户端工具，用于连接数据库并执行SQL命令。以下是常用的连接命令：

# 本地连接到默认数据库（默认使用当前系统用户）
psql

# 指定数据库、用户和端口连接到远程数据库
# -d: 指定数据库名
# -U: 指定用户名
# -p: 指定端口号
# -h: 指定主机地址
psql -d mydb -U myuser -p 5432 -h localhost

# 使用URL格式连接数据库
# 格式：postgresql://用户名:密码@主机地址:端口/数据库名
psql postgresql://myuser:mypassword@localhost:5432/mydb

# 连接后执行单个命令并退出，适用于脚本自动化
psql -d mydb -c "SELECT version();"

# 从SQL文件执行命令，适用于批量操作
psql -d mydb -f script.sql

会话管理命令

会话是客户端与PostgreSQL服务器之间的连接，以下是管理会话的常用命令：

-- 查看当前会话的进程ID
SELECT pg_backend_pid();

-- 查看当前会话的用户名和数据库
SELECT current_user, current_database();

-- 查看所有活跃会话的详细信息
-- pid: 会话进程ID
-- usename: 用户名
-- datname: 数据库名
-- application_name: 应用程序名称
-- client_addr: 客户端IP地址
-- state: 会话状态（active, idle, idle in transaction等）
-- query: 正在执行或最近执行的SQL查询
SELECT pid, usename, datname, application_name, client_addr, state, query 
FROM pg_stat_activity;

-- 终止指定进程ID的会话（需要相应权限）
SELECT pg_terminate_backend(12345);

-- 设置会话级别的参数
-- 设置工作内存大小为64MB
SET work_mem = '64MB';
-- 设置默认搜索路径为myschema和public
SET search_path TO myschema, public;

-- 查看所有会话参数
SHOW all;
-- 查看特定会话参数
SHOW work_mem;

数据库管理

数据库创建与删除

数据库是PostgreSQL中存储数据的容器，以下是数据库创建、删除和管理的常用命令：

-- 创建一个基本的数据库
CREATE DATABASE mydb;

-- 创建一个指定参数的数据库
-- OWNER: 指定数据库所有者
-- ENCODING: 指定字符编码
-- LC_COLLATE: 指定排序规则
-- LC_CTYPE: 指定字符分类
CREATE DATABASE mydb WITH OWNER = myuser 
    ENCODING = 'UTF8' 
    LC_COLLATE = 'zh_CN.UTF-8' 
    LC_CTYPE = 'zh_CN.UTF-8';

-- 安全删除数据库（如果存在）
DROP DATABASE IF EXISTS mydb;

-- 使用psql元命令查看所有数据库
\l

-- 使用SQL查询查看所有数据库
SELECT datname FROM pg_database;

-- 使用psql元命令切换到另一个数据库
\c mydb

表空间管理

表空间是PostgreSQL中用于管理物理存储位置的机制，以下是表空间管理的常用命令：

-- 创建表空间，指定物理存储位置
CREATE TABLESPACE mytablespace LOCATION '/path/to/tablespace';

-- 安全删除表空间（如果存在）
DROP TABLESPACE IF EXISTS mytablespace;

-- 使用psql元命令查看所有表空间的详细信息
\db+

-- 使用SQL查询查看表空间大小
SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) AS size FROM pg_tablespace;

用户与权限管理

PostgreSQL使用角色（Role）来管理用户和权限，角色可以是用户或组。以下是角色创建和权限管理的常用命令：

角色创建与管理

-- 创建一个普通用户角色
-- LOGIN: 允许该角色登录数据库
-- PASSWORD: 设置登录密码
CREATE ROLE myuser WITH LOGIN PASSWORD 'secure_password';

-- 创建一个管理员角色
-- SUPERUSER: 赋予超级用户权限（谨慎使用）
-- CREATEDB: 允许创建数据库
-- CREATEROLE: 允许创建其他角色
CREATE ROLE dbadmin WITH SUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD 'admin_password';

-- 创建一个组角色（无登录权限）
-- 用于管理多个用户的权限集合
CREATE ROLE read_group;

-- 修改角色属性
-- 移除超级用户和创建数据库权限
ALTER ROLE myuser WITH NOSUPERUSER NOCREATEDB;

-- 修改角色密码
ALTER ROLE myuser WITH PASSWORD 'new_password';

-- 安全删除角色（如果存在）
DROP ROLE IF EXISTS myuser;

-- 使用psql元命令查看所有角色的详细信息
\du+

-- 使用SQL查询查看角色的关键属性
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb FROM pg_roles;

权限管理

权限管理是数据库安全的核心，PostgreSQL提供了细粒度的权限控制：

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE mydb TO myuser;

-- 授予模式使用和创建权限
-- USAGE: 允许访问模式
-- CREATE: 允许在模式中创建对象
GRANT USAGE, CREATE ON SCHEMA public TO myuser;

-- 授予表的基本操作权限
-- SELECT: 允许查询数据
-- INSERT: 允许插入数据
-- UPDATE: 允许更新数据
-- DELETE: 允许删除数据
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mytable TO myuser;

-- 授予表的所有权限给管理员
GRANT ALL PRIVILEGES ON TABLE mytable TO dbadmin;

-- 授予模式下所有现有表的SELECT权限给只读组
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_group;

-- 授予新创建表的默认SELECT权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_group;

-- 回收表的DELETE权限
REVOKE DELETE ON TABLE mytable FROM myuser;

-- 使用psql元命令查看表的权限分配
\dp mytable

-- 使用SQL查询查看表的权限分配
SELECT grantee, privilege_type, table_name FROM information_schema.role_table_grants WHERE table_name = 'mytable';

数据库对象管理

表管理

-- 创建表
CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 修改表结构
ALTER TABLE mytable ADD COLUMN email VARCHAR(255);
ALTER TABLE mytable ALTER COLUMN name TYPE VARCHAR(200);
ALTER TABLE mytable DROP COLUMN email;

-- 删除表
DROP TABLE IF EXISTS mytable;

-- 查看表结构
\d mytable
\d+ mytable

-- 查看所有表
\dt+
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

索引管理

-- 创建索引
CREATE INDEX idx_mytable_name ON mytable(name);
CREATE UNIQUE INDEX idx_mytable_email ON mytable(email);
CREATE INDEX CONCURRENTLY idx_mytable_created_at ON mytable(created_at);

-- 删除索引
DROP INDEX IF EXISTS idx_mytable_name;
DROP INDEX CONCURRENTLY IF EXISTS idx_mytable_created_at;

-- 查看索引
\di+
SELECT indexname, tablename, indexdef FROM pg_indexes WHERE schemaname = 'public';

-- 重建索引
REINDEX INDEX idx_mytable_name;
REINDEX TABLE mytable;
REINDEX DATABASE mydb;

视图与物化视图

-- 创建视图
CREATE VIEW myview AS SELECT id, name FROM mytable WHERE active = true;

-- 创建物化视图
CREATE MATERIALIZED VIEW mymatview AS SELECT COUNT(*) FROM mytable GROUP BY category;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW mymatview;
REFRESH MATERIALIZED VIEW CONCURRENTLY mymatview;

-- 删除视图
DROP VIEW IF EXISTS myview;
DROP MATERIALIZED VIEW IF EXISTS mymatview;

-- 查看视图
\dv+

函数与存储过程

-- 创建函数
CREATE OR REPLACE FUNCTION get_user_count() 
RETURNS INTEGER AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;

-- 创建存储过程（PostgreSQL 11+）
CREATE OR REPLACE PROCEDURE reset_user_password(user_id INTEGER, new_password TEXT) 
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users SET password = new_password WHERE id = user_id;
    COMMIT;
END;
$$;

-- 调用函数
SELECT get_user_count();

-- 调用存储过程
CALL reset_user_password(1, 'new_secure_password');

-- 删除函数/存储过程
DROP FUNCTION IF EXISTS get_user_count();
DROP PROCEDURE IF EXISTS reset_user_password(INTEGER, TEXT);

-- 查看函数
\df+

性能监控与优化

系统状态查看

-- 查看系统负载
SELECT * FROM pg_stat_bgwriter;

-- 查看连接数统计
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- 查看锁信息
SELECT locked_relid::regclass, mode, granted, pid, usename, query 
FROM pg_locks JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid;

-- 查看表大小
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, 
       pg_size_pretty(pg_relation_size(relid)) AS table_size, 
       pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;

-- 查看数据库大小
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database;

查询性能分析

-- 查看慢查询日志（需要配置log_min_duration_statement）
-- 启用查询计划分析
EXPLAIN SELECT * FROM mytable WHERE name = 'test';
EXPLAIN ANALYZE SELECT * FROM mytable WHERE name = 'test';

-- 查看缓存命中率
SELECT 
  round(blks_hit * 100.0 / (blks_hit + blks_read), 2) AS cache_hit_rate
FROM pg_stat_database WHERE datname = current_database();

-- 查看索引使用情况
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch 
FROM pg_stat_user_indexes JOIN pg_indexes ON pg_stat_user_indexes.indexrelid = pg_indexes.indexname::regclass
ORDER BY idx_scan DESC;

备份与恢复

逻辑备份

# 使用pg_dump备份单个数据库
pg_dump -d mydb -U myuser -h localhost -F c -b -v -f mydb.backup

# 备份特定表
pg_dump -d mydb -t mytable -t mytable2 -U myuser -f tables.sql

# 备份模式
pg_dump -d mydb -n myschema -U myuser -f schema.sql

# 备份所有数据库
pg_dumpall -U postgres -f alldb.sql

# 压缩备份
pg_dump -d mydb -U myuser | gzip > mydb.sql.gz

物理备份

# 使用pg_basebackup进行基础备份
pg_basebackup -D /backup/base -h localhost -U replication -F t -z -P

# 增量备份（使用wal-g）
wal-g backup-push /var/lib/pgsql/14/data

# 查看备份列表
wal-g backup-list

恢复操作

# 恢复逻辑备份
pg_restore -d mydb -U myuser -v mydb.backup

# 从SQL文件恢复
psql -d mydb -U myuser -f mydb.sql

# 恢复压缩备份
gunzip -c mydb.sql.gz | psql -d mydb -U myuser

# 物理备份恢复
# 1. 停止PostgreSQL服务
systemctl stop postgresql-14
# 2. 清空数据目录
rm -rf /var/lib/pgsql/14/data/*
# 3. 恢复基础备份
tar -xzf /backup/base/base.tar.gz -C /var/lib/pgsql/14/data/
# 4. 配置恢复参数
# 5. 启动PostgreSQL服务
systemctl start postgresql-14

日志管理

查看日志配置

-- 查看日志配置
SHOW log_destination;
SHOW logging_collector;
SHOW log_directory;
SHOW log_filename;
SHOW log_min_duration_statement;

-- 修改日志配置
ALTER SYSTEM SET log_min_duration_statement = '100ms';
ALTER SYSTEM SET log_statement = 'ddl';
SELECT pg_reload_conf();

查看日志内容

# 查看PostgreSQL日志文件
# 默认位置：/var/lib/pgsql/14/data/log/
ls -la /var/lib/pgsql/14/data/log/

# 实时查看日志
tail -f /var/lib/pgsql/14/data/log/postgresql-2025-01-24.log

# 搜索特定内容
grep -i error /var/lib/pgsql/14/data/log/postgresql-2025-01-24.log

系统管理

PostgreSQL服务管理

# 启动服务
systemctl start postgresql-14

# 停止服务
systemctl stop postgresql-14

# 重启服务
systemctl restart postgresql-14

# 查看服务状态
systemctl status postgresql-14

# 启用/禁用自动启动
systemctl enable postgresql-14
systemctl disable postgresql-14

配置文件管理

# 查找PostgreSQL配置文件
pg_config --sysconfdir
pg_config --pkglibdir

# 查看配置文件内容
cat /var/lib/pgsql/14/data/postgresql.conf
cat /var/lib/pgsql/14/data/pg_hba.conf

# 验证配置文件语法
pg_ctl -D /var/lib/pgsql/14/data/ config

常见问题（FAQ）

Q1：如何快速查看表的所有索引？

A1：使用以下命令：

\di+ mytable
-- 或
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'mytable';

Q2：如何查看PostgreSQL的版本信息？

A2：使用以下命令：

# 命令行直接查看
psql --version

# 连接后查看
psql -c "SELECT version();"

# 连接后使用元命令
psql -c "\VERSION"

Q3：如何查找占用资源最多的查询？

A3：使用以下查询：

SELECT pid, usename, datname, query_start, state, 
       now() - query_start AS duration, query 
FROM pg_stat_activity 
WHERE state = 'active' 
ORDER BY duration DESC 
LIMIT 10;

Q4：如何查看表的大小？

A4：使用以下命令：

-- 查看表大小（包括索引）
SELECT pg_size_pretty(pg_total_relation_size('mytable'));

-- 查看表大小（仅表数据）
SELECT pg_size_pretty(pg_relation_size('mytable'));

-- 查看索引大小
SELECT pg_size_pretty(pg_indexes_size('mytable'));

Q5：如何修复损坏的数据库？

A5：可以使用以下方法尝试修复：

1. 首先备份数据库
2. 使用pg_dump导出可恢复的数据
3. 创建新数据库
4. 导入导出的数据
5. 对于严重损坏，可能需要从基础备份恢复

# 尝试修复
pg_dump -O -x -f corrupted_data.sql corrupted_db
createdb new_db
psql -d new_db -f corrupted_data.sql

Q6：如何查看PostgreSQL的当前配置？

A6：使用以下命令：

-- 查看所有配置
SHOW all;

-- 查看特定配置
SHOW shared_buffers;
SHOW work_mem;

-- 从配置文件查看
SELECT name, setting, source FROM pg_settings WHERE source = 'file';