PostgreSQL 常用开发命令

概述

PostgreSQL 提供了丰富的命令行工具和 SQL 命令，用于数据库的开发、管理和运维。本文将汇总 PostgreSQL 开发和运维中常用的命令，包括数据库连接、数据库和表管理、数据操作、索引管理、权限管理、备份和恢复、性能监控以及系统管理等方面的命令，方便开发人员和运维人员参考使用。

数据库连接命令

psql 连接命令

psql 是 PostgreSQL 自带的命令行客户端工具，用于连接和管理 PostgreSQL 数据库。

bash

# 基本连接命令
psql -h <host> -p <port> -U <username> -d <database_name>

# 示例：连接本地数据库
psql -h localhost -p 5432 -U postgres -d mydb

# 简化连接（默认端口 5432，本地连接）
psql -U postgres mydb

# 使用连接字符串
psql "host=localhost port=5432 dbname=mydb user=postgres password=mypassword"

# 连接到默认数据库
psql -U postgres

# 执行单个 SQL 命令
psql -h localhost -U postgres -d mydb -c "SELECT * FROM users LIMIT 10;"

# 执行 SQL 文件
psql -h localhost -U postgres -d mydb -f script.sql

# 从标准输入执行 SQL
cat script.sql | psql -h localhost -U postgres -d mydb

# 以 CSV 格式输出查询结果
psql -h localhost -U postgres -d mydb -c "SELECT * FROM users;" -F ',' -A -t > users.csv

pg_dump 连接命令

pg_dump 用于备份 PostgreSQL 数据库，支持多种备份格式。

bash

# 备份整个数据库
pg_dump -h <host> -p <port> -U <username> -d <database_name> -f backup.sql

# 示例：备份本地数据库
pg_dump -h localhost -p 5432 -U postgres -d mydb -f mydb_backup.sql

# 备份为自定义格式（压缩率高，支持并行恢复）
pd_dump -h localhost -U postgres -d mydb -F c -f mydb_backup.dump

# 备份为 tar 格式
pg_dump -h localhost -U postgres -d mydb -F t -f mydb_backup.tar

# 仅备份数据结构（不包含数据）
pd_dump -h localhost -U postgres -d mydb -s -f mydb_schema.sql

# 仅备份数据（不包含数据结构）
pd_dump -h localhost -U postgres -d mydb -a -f mydb_data.sql

# 备份特定表
pg_dump -h localhost -U postgres -d mydb -t users -t orders -f mydb_tables_backup.sql

# 排除特定表
pg_dump -h localhost -U postgres -d mydb -T temp_table -f mydb_backup.sql

pg_restore 连接命令

pg_restore 用于恢复 PostgreSQL 数据库备份，支持自定义格式和 tar 格式的备份文件。

bash

# 恢复自定义格式备份
pg_restore -h <host> -p <port> -U <username> -d <database_name> backup.dump

# 示例：恢复本地数据库
pg_restore -h localhost -p 5432 -U postgres -d mydb mydb_backup.dump

# 恢复到新数据库
createdb -h localhost -U postgres newdb
pg_restore -h localhost -p 5432 -U postgres -d newdb mydb_backup.dump

# 仅恢复数据结构
pg_restore -h localhost -U postgres -d mydb -s mydb_backup.dump

# 仅恢复数据
pg_restore -h localhost -U postgres -d mydb -a mydb_backup.dump

# 恢复特定表
pg_restore -h localhost -U postgres -d mydb -t users mydb_backup.dump

# 并行恢复（使用 4 个并行进程，PostgreSQL 8.4+ 支持）
pd_restore -h localhost -U postgres -d mydb -j 4 mydb_backup.dump

数据库和表管理命令

数据库管理

sql

-- 创建数据库
CREATE DATABASE mydb;

-- 创建数据库并指定所有者
CREATE DATABASE mydb OWNER myuser;

-- 创建数据库并指定编码和模板
CREATE DATABASE mydb
WITH
  OWNER = myuser
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8'
  TABLESPACE = pg_default
  CONNECTION LIMIT = -1;

-- 删除数据库
DROP DATABASE mydb;

-- 重命名数据库（PostgreSQL 9.1+ 支持）
ALTER DATABASE olddb RENAME TO newdb;

-- 修改数据库所有者
ALTER DATABASE mydb OWNER TO newowner;

-- 查看所有数据库
\l

-- 连接到数据库
\c mydb

表管理

sql

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 删除表
DROP TABLE users;

-- 重命名表
ALTER TABLE old_table RENAME TO new_table;

-- 添加列
ALTER TABLE users ADD COLUMN age INT;

-- 修改列
ALTER TABLE users ALTER COLUMN age SET NOT NULL;
ALTER TABLE users ALTER COLUMN age SET DEFAULT 0;

-- 删除列
ALTER TABLE users DROP COLUMN age;

-- 重命名列
ALTER TABLE users RENAME COLUMN email TO email_address;

-- 查看表结构
\d users

-- 查看表列表
\dt

-- 查看表的详细信息（包含大小、索引等）
\d+ users

约束管理

sql

-- 添加主键约束
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE (email);

-- 添加外键约束
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- 添加检查约束
ALTER TABLE users ADD CHECK (age >= 18);

-- 删除约束
ALTER TABLE users DROP CONSTRAINT users_email_key;

-- 添加非空约束
ALTER TABLE users ALTER COLUMN name SET NOT NULL;

-- 删除非空约束
ALTER TABLE users ALTER COLUMN name DROP NOT NULL;

数据操作命令

插入数据

sql

-- 插入单行数据
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

-- 插入多行数据
INSERT INTO users (name, email) VALUES
    ('Jane Doe', 'jane@example.com'),
    ('Bob Smith', 'bob@example.com'),
    ('Alice Johnson', 'alice@example.com');

-- 插入数据并返回插入的行
INSERT INTO users (name, email) VALUES ('Charlie Brown', 'charlie@example.com')
RETURNING *;

-- 从其他表插入数据
INSERT INTO users_archive (name, email, created_at)
SELECT name, email, created_at FROM users WHERE created_at < '2023-01-01';

-- 批量插入（PostgreSQL 9.5+ 支持 ON CONFLICT）
INSERT INTO users (id, name, email) VALUES
    (1, 'John Doe', 'john@example.com'),
    (2, 'Jane Doe', 'jane@example.com')
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email;

查询数据

sql

-- 查询所有列
SELECT * FROM users;

-- 查询特定列
SELECT id, name, email FROM users;

-- 带条件查询
SELECT * FROM users WHERE age > 18;

-- 带多个条件查询
SELECT * FROM users WHERE age > 18 AND status = 'active';

-- 排序查询
SELECT * FROM users ORDER BY created_at DESC;

-- 限制查询结果
SELECT * FROM users LIMIT 10;

-- 限制查询结果并跳过
SELECT * FROM users OFFSET 10 LIMIT 10;

-- 分组查询
SELECT status, COUNT(*) AS user_count FROM users GROUP BY status;

-- 分组查询并过滤
SELECT status, COUNT(*) AS user_count FROM users GROUP BY status HAVING COUNT(*) > 10;

-- 连接查询
SELECT u.name, o.id AS order_id, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 左连接查询
SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 1000);

-- 分页查询（使用 LIMIT 和 OFFSET）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- 窗口函数查询（PostgreSQL 9.4+ 支持）
SELECT 
    id, name, email, 
    ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num,
    RANK() OVER (PARTITION BY status ORDER BY created_at DESC) AS rank
FROM users;

更新数据

sql

-- 更新单行数据
UPDATE users SET name = 'John Smith' WHERE id = 1;

-- 更新多行数据
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';

-- 更新多个列
UPDATE users SET name = 'John Doe', email = 'john.doe@example.com' WHERE id = 1;

-- 更新数据并返回更新的行
UPDATE users SET status = 'active' WHERE id = 1
RETURNING *;

-- 使用子查询更新
UPDATE users SET status = 'vip' WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 5000);

-- 使用 FROM 子句更新（PostgreSQL 支持）
UPDATE users u
SET last_order_date = o.order_date
FROM orders o
WHERE u.id = o.user_id AND o.status = 'completed';

删除数据

sql

-- 删除单行数据
DELETE FROM users WHERE id = 1;

-- 删除多行数据
DELETE FROM users WHERE status = 'inactive';

-- 删除所有数据
DELETE FROM users;

-- 删除数据并返回删除的行
DELETE FROM users WHERE id = 1
RETURNING *;

-- 使用子查询删除
DELETE FROM users WHERE id NOT IN (SELECT user_id FROM orders);

-- 清空表（比 DELETE 更快，不记录日志）
TRUNCATE TABLE users;

-- 清空表并重置序列
TRUNCATE TABLE users RESTART IDENTITY;

-- 级联清空多个表
TRUNCATE TABLE users, orders CASCADE;

索引管理命令

创建索引

sql

-- 创建单例索引
CREATE INDEX idx_users_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_users_name_email ON users(name, email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 创建部分索引（仅索引满足条件的行）
CREATE INDEX idx_users_active ON users(email) WHERE status = 'active';

-- 创建表达式索引（索引函数结果）
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- 创建 GIN 索引（用于 JSONB、数组等，PostgreSQL 9.4+ 支持 JSONB）
CREATE INDEX idx_users_preferences ON users USING GIN (preferences);

-- 创建 GiST 索引（用于空间数据、全文搜索等）
CREATE INDEX idx_users_location ON users USING GiST (location);

-- 创建 BRIN 索引（用于大型表的范围查询，PostgreSQL 9.5+ 支持）
CREATE INDEX idx_orders_order_date ON orders USING BRIN (order_date);

-- 创建哈希索引（用于等值查询，PostgreSQL 10+ 支持）
CREATE INDEX idx_users_id_hash ON users USING HASH (id);

管理索引

sql

-- 查看索引
\di

-- 查看表的索引
\d users

-- 删除索引
DROP INDEX idx_users_email;

-- 重命名索引
ALTER INDEX idx_old_name RENAME TO idx_new_name;

-- 重建索引
REINDEX INDEX idx_users_email;

-- 重建表的所有索引
REINDEX TABLE users;

-- 重建数据库的所有索引
REINDEX DATABASE mydb;

-- 查看索引使用情况
SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS scan_count,
    idx_tup_read AS read_count,
    idx_tup_fetch AS fetch_count
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

权限管理命令

角色管理

sql

-- 创建角色
CREATE ROLE myrole;

-- 创建可登录角色
CREATE ROLE myuser LOGIN PASSWORD 'mypassword';

-- 创建超级用户角色
CREATE ROLE mysuperuser SUPERUSER LOGIN PASSWORD 'mypassword';

-- 删除角色
DROP ROLE myrole;

-- 重命名角色
ALTER ROLE old_role RENAME TO new_role;

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

-- 授予角色权限
GRANT CONNECT ON DATABASE mydb TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE users TO myuser;
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO myuser;

-- 撤销角色权限
REVOKE UPDATE, DELETE ON TABLE users FROM myuser;

-- 授予角色给其他角色
GRANT myrole TO myuser;

-- 查看角色
\du

-- 查看角色权限
\dp users

权限查询

sql

-- 查看表的权限
SELECT * FROM information_schema.table_privileges WHERE table_name = 'users';

-- 查看列的权限
SELECT * FROM information_schema.column_privileges WHERE table_name = 'users';

-- 查看角色的权限
SELECT 
    grantee,
    privilege_type,
    table_name
FROM information_schema.role_table_grants
WHERE grantee = 'myuser';

备份和恢复命令

数据库备份

bash

# 完整备份（包含数据和结构）
pg_dump -h localhost -U postgres mydb > mydb_backup.sql

# 仅备份结构
pg_dump -h localhost -U postgres -s mydb > mydb_schema.sql

# 仅备份数据
pg_dump -h localhost -U postgres -a mydb > mydb_data.sql

# 压缩备份
pg_dump -h localhost -U postgres mydb | gzip > mydb_backup.sql.gz

# 备份所有数据库
pg_dumpall -h localhost -U postgres > all_databases_backup.sql

# 自定义格式备份（支持并行恢复）
pg_dump -h localhost -U postgres -F c mydb > mydb_backup.dump

# 备份特定表
pg_dump -h localhost -U postgres -t users -t orders mydb > mydb_tables_backup.sql

数据库恢复

bash

# 恢复完整备份
psql -h localhost -U postgres mydb < mydb_backup.sql

# 恢复压缩备份
gunzip -c mydb_backup.sql.gz | psql -h localhost -U postgres mydb

# 恢复自定义格式备份
pg_restore -h localhost -U postgres -d mydb mydb_backup.dump

# 恢复到新数据库
createdb -h localhost -U postgres newdb
pg_restore -h localhost -U postgres -d newdb mydb_backup.dump

# 并行恢复（使用 4 个并行进程）
pg_restore -h localhost -U postgres -d mydb -j 4 mydb_backup.dump

# 恢复所有数据库
psql -h localhost -U postgres < all_databases_backup.sql

表级备份和恢复

bash

# 备份表结构和数据
pg_dump -h localhost -U postgres -t users mydb > users_backup.sql

# 恢复表
psql -h localhost -U postgres mydb < users_backup.sql

# 仅备份表数据
pg_dump -h localhost -U postgres -t users -a mydb > users_data.sql

# 仅恢复表数据
psql -h localhost -U postgres mydb < users_data.sql

性能监控命令

系统状态监控

sql

-- 查看当前连接
SELECT * FROM pg_stat_activity;

-- 查看长事务（超过5分钟）
SELECT 
    pid,
    usename,
    datname,
    now() - xact_start AS duration,
    query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
AND now() - xact_start > interval '5 minutes';

-- 查看锁信息
SELECT 
    l.pid,
    l.mode,
    l.granted,
    l.relation::regclass,
    a.usename,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
ORDER BY l.granted DESC;

-- 查看等待锁的事务
SELECT 
    l1.pid AS waiting_pid,
    l2.pid AS blocking_pid,
    l1.mode AS waiting_mode,
    l2.mode AS blocking_mode,
    l1.relation::regclass AS relation,
    a1.query AS waiting_query,
    a2.query AS blocking_query
FROM pg_locks l1
JOIN pg_locks l2 ON l1.relation = l2.relation AND l1.locktype = l2.locktype
JOIN pg_stat_activity a1 ON l1.pid = a1.pid
JOIN pg_stat_activity a2 ON l2.pid = a2.pid
WHERE l1.granted = false AND l2.granted = true;

-- 查看表统计信息
SELECT 
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables;

-- 查看数据库统计信息
SELECT 
    datname,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted
FROM pg_stat_database;

查询性能监控

sql

-- 安装 pg_stat_statements 扩展（需要在 postgresql.conf 中启用）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查看慢查询（按总执行时间排序）
SELECT 
    queryid,
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    stddev_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- 分析查询执行计划并显示实际执行时间
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

-- 分析查询执行计划并显示缓冲区使用情况
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'john@example.com';

-- 分析查询执行计划的详细信息（JSON 格式）
EXPLAIN (VERBOSE, ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM users WHERE email = 'john@example.com';

系统资源监控

bash

# 查看 PostgreSQL 进程
ps aux | grep postgres

# 查看 PostgreSQL 内存使用
top -p $(pgrep -d',' postgres)

# 查看磁盘 I/O
iostat -x 1

# 查看系统负载
uptime

# 查看内存使用
free -h

# 查看网络连接
netstat -tuln | grep 5432
ss -tuln | grep 5432

系统管理命令

服务管理

bash

# 启动 PostgreSQL 服务（Linux systemd）
sudo systemctl start postgresql

# 停止 PostgreSQL 服务（Linux systemd）
sudo systemctl stop postgresql

# 重启 PostgreSQL 服务（Linux systemd）
sudo systemctl restart postgresql

# 查看 PostgreSQL 服务状态（Linux systemd）
sudo systemctl status postgresql

# 设置 PostgreSQL 服务开机自启（Linux systemd）
sudo systemctl enable postgresql

# 启动 PostgreSQL 服务（Windows）
net start postgresql-x64-15

# 停止 PostgreSQL 服务（Windows）
net stop postgresql-x64-15

# 查看 PostgreSQL 服务状态（Windows）
sc query postgresql-x64-15

# 使用 pg_ctl 启动服务
pg_ctl -D /var/lib/postgresql/15/main start

# 使用 pg_ctl 停止服务
pg_ctl -D /var/lib/postgresql/15/main stop

# 使用 pg_ctl 重启服务
pg_ctl -D /var/lib/postgresql/15/main restart

# 使用 pg_ctl 查看服务状态
pg_ctl -D /var/lib/postgresql/15/main status

配置管理

bash

# 查看 PostgreSQL 配置文件位置
psql -U postgres -c "SHOW config_file;"

# 查看数据目录位置
psql -U postgres -c "SHOW data_directory;"

# 查看日志文件位置
psql -U postgres -c "SHOW log_directory;"

# 查看所有配置参数
psql -U postgres -c "SHOW ALL;"

# 查看特定配置参数
psql -U postgres -c "SHOW shared_buffers;"
psql -U postgres -c "SHOW work_mem;"
psql -U postgres -c "SHOW max_connections;"

# 重新加载配置
pg_ctl -D /var/lib/postgresql/15/main reload
# 或
psql -U postgres -c "SELECT pg_reload_conf();"

维护命令

sql

-- 执行 VACUUM（回收死元组空间）
VACUUM users;

-- 执行 FULL VACUUM（需要锁表，重建表）
VACUUM FULL users;

-- 执行 VACUUM ANALYZE（回收空间并更新统计信息）
VACUUM ANALYZE users;

-- 执行 ANALYZE（更新表统计信息）
ANALYZE users;

-- 执行 ANALYZE VERBOSE（显示详细信息）
ANALYZE VERBOSE users;

-- 查看自动 VACUUM 状态
SELECT * FROM pg_stat_autovacuum;

-- 查看表的 VACUUM 统计信息
SELECT 
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables;

其他常用命令

扩展管理

sql

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;  -- 加密扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- 查询统计扩展
CREATE EXTENSION IF NOT EXISTS postgis;  -- 空间数据扩展
CREATE EXTENSION IF NOT EXISTS timescaledb;  -- 时序数据库扩展

-- 卸载扩展
DROP EXTENSION pgcrypto;

-- 查看已安装的扩展
\dx

-- 查看可用的扩展
\dx+

序列管理

sql

-- 创建序列
CREATE SEQUENCE my_sequence START WITH 1 INCREMENT BY 1;

-- 使用序列
SELECT nextval('my_sequence');  -- 获取下一个值
SELECT currval('my_sequence');  -- 获取当前值
SELECT setval('my_sequence', 100);  -- 设置序列值

-- 删除序列
DROP SEQUENCE my_sequence;

-- 查看序列
\ds

-- 查看表的序列
\d users

视图和物化视图

sql

-- 创建视图
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

-- 创建物化视图（存储查询结果，PostgreSQL 9.3+ 支持）
CREATE MATERIALIZED VIEW user_counts AS
SELECT status, COUNT(*) AS count FROM users GROUP BY status;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW user_counts;

-- 刷新物化视图（并发，需要唯一索引，PostgreSQL 9.4+ 支持）
REFRESH MATERIALIZED VIEW CONCURRENTLY user_counts;

-- 删除视图
DROP VIEW active_users;

-- 删除物化视图
DROP MATERIALIZED VIEW user_counts;

-- 查看视图
\dv

-- 查看物化视图
\dm

函数和存储过程

sql

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

-- 调用函数
SELECT get_user_count();

-- 创建存储过程（PostgreSQL 11+ 支持）
CREATE OR REPLACE PROCEDURE update_user_status(
    p_user_id INTEGER,
    p_status VARCHAR(50)
) AS $$
BEGIN
    UPDATE users SET status = p_status WHERE id = p_user_id;
END;
$$ LANGUAGE plpgsql;

-- 调用存储过程
CALL update_user_status(1, 'active');

-- 删除函数
DROP FUNCTION get_user_count();

-- 删除存储过程
DROP PROCEDURE update_user_status(INTEGER, VARCHAR);

-- 查看函数和存储过程
\df

常见问题

如何查看 PostgreSQL 版本？

bash

# 使用 psql 命令
psql --version

# 连接到数据库后查看
SELECT version();

# 使用 pg_config 命令
pg_config --version

如何修改 PostgreSQL 监听地址？

编辑 PostgreSQL 配置文件 postgresql.conf，修改 listen_addresses 参数：

ini

listen_addresses = '*'  # 监听所有地址
# 或
listen_addresses = 'localhost,192.168.1.100'  # 监听特定地址

然后重新加载配置：

bash

pg_ctl -D /var/lib/postgresql/15/main reload

如何修改 PostgreSQL 默认端口？

编辑 PostgreSQL 配置文件 postgresql.conf，修改 port 参数：

ini

port = 5433  # 修改为新端口

然后重新加载配置：

bash

pg_ctl -D /var/lib/postgresql/15/main reload

如何重置 PostgreSQL 超级用户密码？

bash

# 停止 PostgreSQL 服务
sudo systemctl stop postgresql

# 以单用户模式启动 PostgreSQL
sudo -u postgres postgres --single -D /var/lib/postgresql/15/main

# 在单用户模式下修改密码
ALTER USER postgres WITH PASSWORD 'newpassword';
\q

# 启动 PostgreSQL 服务
sudo systemctl start postgresql

如何查看 PostgreSQL 错误日志？

bash

# 查看日志文件位置
psql -U postgres -c "SHOW log_directory;"

# 查看日志文件
tail -f /var/log/postgresql/postgresql-15-main.log

# 或根据配置的 log_directory 查看
ls -la /var/lib/postgresql/15/main/pg_log/
tail -f /var/lib/postgresql/15/main/pg_log/postgresql-*.log

如何查看表的大小？

sql

-- 查看所有表的大小（按大小排序）
SELECT 
    schemaname,
    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 pg_size_pretty(pg_total_relation_size('users'));

如何优化慢查询？

使用 EXPLAIN ANALYZE 分析查询执行计划
确保查询使用了合适的索引
优化查询语句，避免全表扫描
考虑使用物化视图缓存频繁查询的结果
调整 PostgreSQL 配置参数，如 shared_buffers、work_mem 等
使用 pg_stat_statements 识别慢查询

如何处理锁等待问题？

使用 pg_locks 和 pg_stat_activity 查看锁信息和等待进程
识别阻塞进程（granted = true）和等待进程（granted = false）
分析阻塞查询，优化查询逻辑
考虑终止长时间运行的阻塞进程（SELECT pg_terminate_backend(pid);）
优化应用程序，减少事务持有时间

总结

PostgreSQL 提供了丰富的命令行工具和 SQL 命令，用于数据库的开发、管理和运维。本文汇总了 PostgreSQL 开发和运维中常用的命令，包括数据库连接、数据库和表管理、数据操作、索引管理、权限管理、备份和恢复、性能监控以及系统管理等方面的命令。

这些命令涵盖了 PostgreSQL 开发和运维的各个方面，掌握这些命令可以帮助开发人员和运维人员更高效地管理和维护 PostgreSQL 数据库。在实际使用中，建议根据具体的业务需求和系统环境选择合适的命令，并结合 PostgreSQL 文档进行深入学习。

通过熟练使用这些常用命令，可以提高 PostgreSQL 数据库的开发效率和运维质量，确保数据库的稳定性、安全性和高性能。

PostgreSQL 常用开发命令 ​

概述 ​

数据库连接命令 ​

psql 连接命令 ​

pg_dump 连接命令 ​

pg_restore 连接命令 ​

数据库和表管理命令 ​

数据库管理 ​

表管理 ​

约束管理 ​

数据操作命令 ​

插入数据 ​

查询数据 ​

更新数据 ​

删除数据 ​

索引管理命令 ​

创建索引 ​

管理索引 ​

权限管理命令 ​

角色管理 ​

权限查询 ​

备份和恢复命令 ​

数据库备份 ​

数据库恢复 ​

表级备份和恢复 ​

性能监控命令 ​

系统状态监控 ​

查询性能监控 ​

系统资源监控 ​

系统管理命令 ​

服务管理 ​

配置管理 ​

维护命令 ​

其他常用命令 ​

扩展管理 ​

序列管理 ​

视图和物化视图 ​

函数和存储过程 ​

常见问题 ​

如何查看 PostgreSQL 版本？ ​

如何修改 PostgreSQL 监听地址？ ​

如何修改 PostgreSQL 默认端口？ ​

如何重置 PostgreSQL 超级用户密码？ ​

如何查看 PostgreSQL 错误日志？ ​

如何查看表的大小？ ​

如何优化慢查询？ ​

如何处理锁等待问题？ ​

总结 ​

PostgreSQL 常用开发命令

概述

数据库连接命令

psql 连接命令

pg_dump 连接命令

pg_restore 连接命令

数据库和表管理命令

数据库管理

表管理

约束管理

数据操作命令

插入数据

查询数据

更新数据

删除数据

索引管理命令

创建索引

管理索引

权限管理命令

角色管理

权限查询

备份和恢复命令

数据库备份

数据库恢复

表级备份和恢复

性能监控命令

系统状态监控

查询性能监控

系统资源监控

系统管理命令

服务管理

配置管理

维护命令

其他常用命令

扩展管理

序列管理

视图和物化视图

函数和存储过程

常见问题

如何查看 PostgreSQL 版本？

如何修改 PostgreSQL 监听地址？

如何修改 PostgreSQL 默认端口？

如何重置 PostgreSQL 超级用户密码？

如何查看 PostgreSQL 错误日志？

如何查看表的大小？

如何优化慢查询？

如何处理锁等待问题？

总结