印刻数据库

外观

PostgreSQL 数据库操作命令 ​

PostgreSQL数据库操作是DBA日常工作的重要组成部分，掌握常用的数据库操作命令对于高效管理数据库至关重要。本文将介绍PostgreSQL数据库操作的常用命令，结合实际生产运维场景，帮助DBA高效管理PostgreSQL数据库。

数据库管理命令 ​

创建数据库 ​

在生产环境中，创建数据库时需要考虑多个因素，包括编码、时区、表空间等。以下是不同场景下的数据库创建命令：

sql
-- 基本创建数据库
CREATE DATABASE app_db;

-- 指定所有者创建数据库
CREATE DATABASE app_db OWNER app_user;

-- 生产环境标准创建（指定编码、排序规则、时区）
CREATE DATABASE app_db 
    WITH 
    OWNER = app_user
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = app_tablespace
    CONNECTION LIMIT = 100
    IS_TEMPLATE = false;

-- 从模板创建数据库（适用于快速创建相同配置的数据库）
CREATE DATABASE app_db_staging TEMPLATE app_db;

-- 特殊场景：创建兼容旧版本的数据库
CREATE DATABASE legacy_db 
    WITH 
    OWNER = legacy_user
    ENCODING = 'LATIN1'
    LC_COLLATE = 'en_US'
    LC_CTYPE = 'en_US'
    TEMPLATE = template0;

删除数据库 ​

删除数据库是高危操作，生产环境中需要特别小心，确保数据已备份且没有活跃连接。

sql
-- 基本删除（需确保无连接）
DROP DATABASE app_db;

-- 安全删除（检查数据库是否存在）
DROP DATABASE IF EXISTS app_db;

-- 生产环境删除流程（终止连接后删除）
-- 1. 终止目标数据库的所有活跃连接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'app_db' AND pid <> pg_backend_pid();

-- 2. 删除数据库
DROP DATABASE app_db;

-- 3. 版本差异：PostgreSQL 13+支持IF EXISTS
-- PostgreSQL 12及以下版本不支持IF EXISTS语法

查看数据库 ​

sql
-- 查看所有数据库（psql元命令）
\l

-- 查看所有数据库（SQL查询）
SELECT datname, datdba, encoding, datcollate, datctype 
FROM pg_database 
WHERE datistemplate = false;

-- 查看数据库详细信息（包含大小、表空间等）
\l+

-- 查看单个数据库详情
SELECT * FROM pg_database WHERE datname = 'app_db';

-- 查看数据库大小（生产环境常用）
SELECT pg_size_pretty(pg_database_size('app_db')) AS database_size;

-- 查看所有数据库大小排序
SELECT 
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size,
    pg_database_size(datname) AS size_bytes
FROM pg_database 
WHERE datistemplate = false 
ORDER BY size_bytes DESC;

连接数据库 ​

bash
-- 本地连接默认数据库
psql

-- 本地连接指定数据库
psql -d app_db

-- 使用指定用户连接
psql -U app_user -d app_db

-- 连接远程数据库（生产环境常用，指定端口和主机）
psql -h db.example.com -p 5432 -U app_user -d app_db

-- 连接并执行单条命令
psql -h db.example.com -U app_user -d app_db -c "SELECT version();"

-- 从文件执行SQL脚本
psql -h db.example.com -U app_user -d app_db -f /path/to/script.sql

-- 版本差异：PostgreSQL 14+支持--set=参数传递
psql -v dbname=app_db -f script.sql

表管理命令 ​

创建表 ​

sql
-- 基本表创建
CREATE TABLE users (
    user_id serial PRIMARY KEY,
    username varchar(50) NOT NULL UNIQUE,
    email varchar(100) NOT NULL UNIQUE,
    password_hash varchar(100) NOT NULL,
    status boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP
);

-- 生产环境订单表（带外键约束）
CREATE TABLE orders (
    order_id bigserial PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    order_date timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    amount numeric(12,2) NOT NULL CHECK (amount > 0),
    status varchar(20) DEFAULT 'pending',
    CONSTRAINT chk_order_status CHECK (status IN ('pending', 'processed', 'shipped', 'delivered', 'cancelled'))
);

-- 生产环境日志表（分区表实现）
-- PostgreSQL 10+原生支持分区表
CREATE TABLE logs (
    log_id bigserial,
    log_time timestamp with time zone NOT NULL,
    level varchar(10) NOT NULL,
    message text NOT NULL,
    source varchar(50),
    PRIMARY KEY (log_id, log_time)
) PARTITION BY RANGE (log_time);

-- 创建月度分区
CREATE TABLE logs_202401 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_202402 PARTITION OF logs
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 版本差异：PostgreSQL 9.x需要使用继承实现分区

修改表 ​

sql
-- 添加列（生产环境注意：大表添加列可能锁表）
ALTER TABLE users ADD COLUMN phone varchar(20);

-- 添加列并设置默认值（PostgreSQL 11+支持快速添加）
ALTER TABLE users ADD COLUMN last_login timestamp with time zone DEFAULT CURRENT_TIMESTAMP;

-- 修改列类型（注意：可能导致表锁和数据转换）
ALTER TABLE users ALTER COLUMN email TYPE varchar(150);

-- 修改列默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT true;

-- 删除列
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone;

-- 添加约束
ALTER TABLE users ADD CONSTRAINT chk_username_length CHECK (length(username) >= 3);

-- 删除约束（需要先查询约束名称）
ALTER TABLE users DROP CONSTRAINT chk_username_length;

-- 版本差异：PostgreSQL 12+支持IDENTITY列，替代SERIAL
ALTER TABLE users ALTER COLUMN user_id ADD GENERATED ALWAYS AS IDENTITY;

删除表 ​

sql
-- 基本删除
DROP TABLE users;

-- 安全删除
DROP TABLE IF EXISTS users;

-- 级联删除（删除表和依赖对象）
DROP TABLE users CASCADE;

-- 删除分区表（先删除所有分区，再删除主表）
DROP TABLE logs_202401;
DROP TABLE logs_202402;
DROP TABLE logs;

查看表 ​

sql
-- 查看当前数据库中的表（psql元命令）
\dt

-- 查看所有模式的表
\dt *.

-- 查看表详细信息（包含大小、描述等）
\dt+

-- 查看表结构
\d users

-- 查看表结构（SQL查询）
SELECT column_name, data_type, is_nullable, column_default 
FROM information_schema.columns 
WHERE table_name = 'users' AND table_schema = 'public';

-- 查看表大小（生产环境常用）
SELECT 
    pg_size_pretty(pg_total_relation_size('users')) AS total_size,
    pg_size_pretty(pg_relation_size('users')) AS table_size,
    pg_size_pretty(pg_indexes_size('users')) AS indexes_size;

-- 查看所有表大小排序
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size
FROM pg_tables 
WHERE schemaname = 'public' 
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

用户和角色管理命令 ​

创建用户/角色 ​

sql
-- 创建普通用户（应用程序使用）
CREATE USER app_user WITH PASSWORD 'StrongPassword123!';

-- 创建带属性的用户（生产环境常用）
CREATE USER app_user 
    WITH 
    PASSWORD 'StrongPassword123!' 
    NOSUPERUSER 
    NOCREATEDB 
    NOCREATEROLE 
    INHERIT 
    LOGIN 
    CONNECTION LIMIT 100
    VALID UNTIL '2025-12-31';

-- 创建角色（用于权限管理）
CREATE ROLE db_readonly;
CREATE ROLE db_writer;

-- 创建超级用户（谨慎使用）
CREATE USER admin_user WITH PASSWORD 'AdminPassword123!' SUPERUSER;

-- 版本差异：PostgreSQL 10+支持PASSWORD EXPIRE
ALTER USER app_user PASSWORD EXPIRE;

修改用户/角色 ​

sql
-- 修改用户密码（生产环境常用）
ALTER USER app_user WITH PASSWORD 'NewStrongPassword456!';

-- 锁定/解锁用户
ALTER USER app_user ACCOUNT LOCK;
ALTER USER app_user ACCOUNT UNLOCK;

-- 修改用户连接限制
ALTER USER app_user CONNECTION LIMIT 200;

-- 重命名用户
ALTER USER app_user RENAME TO new_app_user;

-- 修改用户默认配置
ALTER USER app_user SET default_transaction_isolation = 'read committed';
ALTER USER app_user SET statement_timeout = '30000';

-- 版本差异：PostgreSQL 12+支持设置临时密码有效期
ALTER USER app_user VALID UNTIL '2025-12-31';

删除用户/角色 ​

sql
-- 删除用户
DROP USER app_user;

-- 安全删除
DROP USER IF EXISTS app_user;

-- 级联删除（删除用户和依赖对象）
DROP USER app_user CASCADE;

-- 删除角色
DROP ROLE db_readonly;

查看用户/角色 ​

sql
-- 查看所有用户（psql元命令）
\du

-- 查看所有角色
\dg

-- 查看用户详细信息
\du+

-- 查看用户权限
SELECT 
    grantee,
    privilege_type,
    table_name
FROM information_schema.role_table_grants 
WHERE grantee = 'app_user';

-- 查看用户配置
SELECT * FROM pg_user WHERE usename = 'app_user';

-- 查看角色继承关系
SELECT 
    r1.rolname AS role_name,
    r2.rolname AS member_of
FROM pg_auth_members m
JOIN pg_roles r1 ON m.member = r1.oid
JOIN pg_roles r2 ON m.roleid = r2.oid;

权限管理命令 ​

授予权限 ​

sql
-- 授予数据库连接权限
GRANT CONNECT ON DATABASE app_db TO app_user;

-- 授予模式使用权限
GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO db_writer;

-- 授予表权限（生产环境遵循最小权限原则）
GRANT SELECT ON TABLE users TO db_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO app_user;

-- 授予所有表权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin;

-- 授予序列权限（SERIAL/IDENTITY列需要）
GRANT USAGE, SELECT ON SEQUENCE users_user_id_seq TO app_user;

-- 授予未来创建的表权限（生产环境常用）
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON TABLES TO db_readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO db_writer;

-- 版本差异：PostgreSQL 9.0+支持ALTER DEFAULT PRIVILEGES

撤销权限 ​

sql
-- 撤销表权限
REVOKE DELETE ON TABLE users FROM app_user;

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON TABLE orders FROM app_user;

-- 撤销模式使用权限
REVOKE USAGE ON SCHEMA public FROM app_user;

-- 撤销数据库连接权限
REVOKE CONNECT ON DATABASE app_db FROM app_user;

角色继承 ​

sql
-- 创建角色层级
CREATE ROLE db_admin WITH SUPERUSER;
CREATE ROLE db_developer WITH CREATEDB CREATEROLE;
CREATE ROLE db_readonly;

-- 设置继承关系
GRANT db_developer TO db_admin;
GRANT db_readonly TO db_developer;

-- 用户继承角色
CREATE USER dev1 WITH PASSWORD 'dev1_password' IN ROLE db_developer;

-- 授予角色给现有用户
GRANT db_readonly TO app_user;

-- 撤销角色
REVOKE db_readonly FROM app_user;

数据操作命令 ​

插入数据 ​

sql
-- 插入单行数据
INSERT INTO users (username, email, password_hash) 
VALUES ('user1', 'user1@example.com', 'hash123');

-- 插入多行数据（生产环境批量插入常用）
INSERT INTO users (username, email, password_hash) 
VALUES 
    ('user1', 'user1@example.com', 'hash123'),
    ('user2', 'user2@example.com', 'hash456'),
    ('user3', 'user3@example.com', 'hash789');

-- 插入返回ID（应用程序常用）
INSERT INTO users (username, email, password_hash) 
VALUES ('user4', 'user4@example.com', 'hash012') 
RETURNING user_id;

-- 从其他表插入数据（生产环境数据迁移常用）
INSERT INTO users_archive (user_id, username, email, created_at) 
SELECT user_id, username, email, created_at 
FROM users 
WHERE created_at < '2023-01-01';

-- 版本差异：PostgreSQL 9.5+支持INSERT ... ON CONFLICT（UPSERT）
INSERT INTO users (username, email, password_hash) 
VALUES ('user1', 'user1@example.com', 'newhash123') 
ON CONFLICT (username) DO UPDATE 
SET password_hash = EXCLUDED.password_hash;

查询数据 ​

sql
-- 基本查询
SELECT * FROM users;

-- 查询指定列（生产环境推荐：避免SELECT *）
SELECT user_id, username, email FROM users;

-- 带条件查询
SELECT * FROM users WHERE status = true;
SELECT * FROM users WHERE created_at > '2024-01-01';

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

-- 分页查询（生产环境API常用）
SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 20;

-- 聚合查询
SELECT COUNT(*) FROM users;
SELECT status, COUNT(*) FROM users GROUP BY status;
SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 10;

-- 连接查询
SELECT u.username, o.order_id, o.amount 
FROM users u 
INNER JOIN orders o ON u.user_id = o.user_id;

-- 子查询
SELECT * FROM users 
WHERE user_id IN (SELECT user_id FROM orders WHERE amount > 100);

-- 版本差异：PostgreSQL 9.4+支持JSONB操作
SELECT * FROM products WHERE attributes->>'color' = 'red';

更新数据 ​

sql
-- 更新单行数据
UPDATE users SET status = false WHERE user_id = 1;

-- 更新多行数据（生产环境注意：大表更新可能锁表）
UPDATE users SET status = false WHERE created_at < '2023-01-01';

-- 更新带返回值
UPDATE users SET status = true WHERE user_id = 1 
RETURNING user_id, status, updated_at;

-- 使用子查询更新
UPDATE users SET email = 'new_email@example.com' 
WHERE user_id = (SELECT user_id FROM users WHERE username = 'user1');

-- 版本差异：PostgreSQL 9.5+支持UPDATE ... FROM
UPDATE users u
SET last_order_date = o.order_date
FROM orders o
WHERE u.user_id = o.user_id AND o.order_date > u.last_order_date;

删除数据 ​

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

-- 删除多行数据
DELETE FROM users WHERE created_at < '2023-01-01';

-- 删除带返回值
DELETE FROM users WHERE user_id = 1 
RETURNING user_id, username;

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

-- 截断表（快速删除所有数据，不记录WAL，生产环境谨慎使用）
TRUNCATE TABLE users;
TRUNCATE TABLE users, orders CASCADE;

-- 版本差异：PostgreSQL 11+支持TRUNCATE ... RESTART IDENTITY
TRUNCATE TABLE users RESTART IDENTITY;

索引管理命令 ​

创建索引 ​

sql
-- 创建B-Tree索引（最常用）
CREATE INDEX idx_users_email ON users (email);

-- 创建复合索引
CREATE INDEX idx_orders_user_id_order_date ON orders (user_id, order_date DESC);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_username ON users (username);

-- 创建部分索引（生产环境优化常用，只索引常用数据）
CREATE INDEX idx_orders_active ON orders (order_date) WHERE status = 'active';

-- 创建表达式索引（优化函数调用查询）
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- 创建GIN索引（用于JSONB、数组等类型）
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- 创建BRIN索引（用于超大型表的范围列）
CREATE INDEX idx_logs_log_time ON logs USING BRIN (log_time);

-- 创建GIST索引（用于地理空间数据）
CREATE INDEX idx_locations_coordinates ON locations USING GIST (coordinates);

-- 生产环境创建索引：使用CONCURRENTLY避免锁表
CREATE INDEX CONCURRENTLY idx_users_phone ON users (phone);

-- 版本差异：PostgreSQL 9.2+支持JSONB，PostgreSQL 9.4+支持GIN索引优化

删除索引 ​

sql
-- 基本删除
DROP INDEX idx_users_email;

-- 安全删除
DROP INDEX IF EXISTS idx_users_email;

-- 并发删除（PostgreSQL 13+支持）
DROP INDEX CONCURRENTLY idx_users_phone;

-- 按表删除索引
DROP INDEX idx_orders_user_id;

查看索引 ​

sql
-- 查看所有索引（psql元命令）
\di

-- 查看表的索引
\di+ users_

-- 查看索引详细信息（SQL查询）
SELECT 
    indexname,
    tablename,
    indexdef
FROM pg_indexes 
WHERE schemaname = 'public' AND tablename = 'users';

-- 查看索引使用情况（生产环境优化常用）
SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY index_scans ASC;

-- 查看未使用的索引
SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND idx_scan = 0;

事务管理命令 ​

基本事务 ​

sql
-- 开始事务
BEGIN;

-- 执行SQL语句
INSERT INTO users (username, email, password_hash) VALUES ('user5', 'user5@example.com', 'hash567');
UPDATE orders SET status = 'processed' WHERE order_id = 1;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 生产环境事务示例：转账操作
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- 验证余额
SELECT balance FROM accounts WHERE account_id IN (1, 2);
COMMIT;

保存点 ​

sql
-- 使用保存点（复杂事务常用）
BEGIN;

INSERT INTO users (username, email, password_hash) VALUES ('user6', 'user6@example.com', 'hash789');
SAVEPOINT sp1;

UPDATE orders SET status = 'shipped' WHERE order_id = 2;
-- 如果出现错误，回滚到保存点
ROLLBACK TO sp1;

-- 继续其他操作
UPDATE orders SET status = 'processed' WHERE order_id = 2;

-- 提交事务
COMMIT;

事务隔离级别 ​

sql
-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 设置会话级隔离级别
SET default_transaction_isolation = 'read committed';

-- 查看当前隔离级别
SHOW default_transaction_isolation;

-- 版本差异：PostgreSQL 9.1+支持可重复读隔离级别下的 Serializable Snapshot Isolation (SSI)

版本兼容性说明 ​

功能/命令PostgreSQL 9.xPostgreSQL 10+PostgreSQL 12+PostgreSQL 13+
分区表需继承+触发器原生分区支持增强分区管理并行分区查询
JSON支持JSONJSONBJSONB增强JSONB路径查询
索引管理基本索引并发创建索引增量排序索引并发删除索引
权限管理基本权限ALTER DEFAULT PRIVILEGES更细粒度权限增强角色管理
事务管理基本事务SSI支持增强事务控制事务诊断
命令语法基本语法IF EXISTS/IF NOT EXISTSIDENTITY列MERGE语句

生产环境最佳实践 ​

数据库设计 ​

  • 合理设计数据库架构，避免过度设计
  • 使用适当的数据类型（如使用int4而非int8存储小范围整数）
  • 为查询频繁的列添加索引，避免索引膨胀
  • 设计合理的主键和外键，考虑使用UUID或序列
  • 大表使用分区策略，按时间或业务维度分区
  • 避免使用复杂的触发器和函数，影响性能

权限管理 ​

  • 遵循最小权限原则，只授予必要的权限
  • 使用角色管理权限，便于统一管理和审计
  • 定期审查用户和角色权限，撤销不必要的权限
  • 应用程序使用专用用户，避免使用超级用户
  • 敏感操作使用临时提升权限，而非长期持有高权限

性能优化 ​

  • 避免使用SELECT *，只查询需要的列
  • 使用EXPLAIN ANALYZE分析查询执行计划
  • 优化慢查询，避免全表扫描和嵌套循环
  • 合理使用索引，定期重建碎片化索引
  • 避免长事务，设置合理的statement_timeout
  • 定期执行VACUUM和ANALYZE，维护数据库健康

安全性 ​

  • 使用强密码，定期更换密码
  • 限制用户连接来源（使用pg_hba.conf）
  • 启用SSL/TLS加密连接
  • 定期备份数据库，测试恢复流程
  • 启用审计日志，监控异常活动
  • 及时安装安全补丁，关注CVE公告

运维自动化 ​

  • 使用脚本自动化日常操作，如备份、监控、报告生成
  • 采用基础设施即代码（IaC）管理数据库配置
  • 使用CI/CD流程管理数据库变更
  • 建立完善的变更管理流程，避免人为错误
  • 定期进行灾难恢复演练，确保业务连续性

总结 ​

PostgreSQL数据库操作命令是DBA日常工作的基础，掌握这些命令对于高效管理数据库至关重要。本文介绍了PostgreSQL数据库操作的常用命令，结合生产环境实际场景，涵盖了数据库管理、表管理、用户和角色管理、权限管理、数据操作、索引管理、事务管理等方面。

在实际运维过程中，DBA应根据业务需求和实际情况灵活应用这些命令，并结合监控工具和自动化脚本，实现数据库的高效管理。同时，DBA还应关注PostgreSQL的最新发展动态，学习和应用新的功能和最佳实践，不断提升自己的数据库管理能力。

生产环境中，DBA需要特别注意：

  1. 谨慎使用高危操作（如DROP DATABASE、TRUNCATE TABLE）
  2. 大表操作时使用CONCURRENTLY避免锁表
  3. 遵循最小权限原则，加强安全管理
  4. 定期备份和测试恢复流程
  5. 监控数据库性能，及时优化
  6. 关注版本差异，选择合适的命令语法

通过合理使用PostgreSQL数据库操作命令，结合最佳实践，可以提高数据库的性能、安全性和可靠性，确保业务系统的稳定运行。