postgresql

PostgreSQL 数据库管理

概述

PostgreSQL 数据库管理、扩展使用、查询优化等技能。

连接管理

# 本地连接
psql -U postgres
psql -U username -d database

# 远程连接
psql -h hostname -p 5432 -U username -d database

# 执行 SQL 文件
psql -U username -d database -f script.sql

# 执行单条命令
psql -U username -d database -c "SELECT version();"

psql 常用命令

\l              -- 列出数据库
\c dbname       -- 切换数据库
\dt             -- 列出表
\d tablename    -- 表结构
\du             -- 列出用户
\dn             -- 列出 schema
\df             -- 列出函数
\di             -- 列出索引
\q              -- 退出
\?              -- 帮助
\timing         -- 显示执行时间
\x              -- 扩展显示模式

用户与权限

-- 创建用户
CREATE USER username WITH PASSWORD 'password';
CREATE ROLE username WITH LOGIN PASSWORD 'password';

-- 创建超级用户
CREATE USER admin WITH SUPERUSER PASSWORD 'password';

-- 授权
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO username;
GRANT USAGE ON SCHEMA schema_name TO username;

-- 设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO readonly_user;

-- 查看权限
\du username
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';

-- 修改密码
ALTER USER username WITH PASSWORD 'newpassword';

数据库操作

-- 创建数据库
CREATE DATABASE dbname;
CREATE DATABASE dbname OWNER username ENCODING 'UTF8';

-- 删除数据库
DROP DATABASE dbname;

-- 查看数据库大小
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) 
FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;

-- 查看表大小
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) 
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

备份与恢复

pg_dump

# 备份单个数据库
pg_dump -U username dbname > backup.sql
pg_dump -U username -Fc dbname > backup.dump    # 自定义格式

# 备份所有数据库
pg_dumpall -U postgres > all_backup.sql

# 只备份结构
pg_dump -U username --schema-only dbname > schema.sql

# 只备份数据
pg_dump -U username --data-only dbname > data.sql

# 备份特定表
pg_dump -U username -t tablename dbname > table.sql

# 并行备份（大数据库）
pg_dump -U username -Fd -j 4 dbname -f backup_dir/