PostgreSQL 创建数据库
PostgreSQL 创建数据库完全指南
涵盖 命令行、pgAdmin、SQL 脚本、权限、编码、模板、常见错误 等全流程。
一、创建数据库的 3 种方式
| 方式 | 适用场景 | 推荐度 |
|---|---|---|
| SQL 命令 | 脚本化、自动化 | ★★★★★ |
| psql 命令行 | 快速测试 | ★★★★ |
| pgAdmin 图形化 | 新手、学习 | ★★★★ |
二、方式 1：SQL 命令（推荐）
基本语法
CREATE DATABASE database_name;完整推荐写法（生产级）
CREATE DATABASE myapp
WITH
OWNER = appuser -- 所有者
ENCODING = 'UTF8' -- 编码（强烈推荐 UTF8）
LC_COLLATE = 'en_US.UTF-8' -- 排序规则
LC_CTYPE = 'en_US.UTF-8' -- 字符分类
TEMPLATE = template0 -- 模板（避免继承扩展）
CONNECTION LIMIT = 100; -- 最大连接数（-1 为无限制）为什么用
template0？template1可能被修改过（如安装了扩展），用template0保证干净。
三、方式 2：psql 命令行（快速）
# 1. 进入 psql（以 postgres 用户）
sudo -u postgres psql
# 或直接
psql -U postgres-- 2. 创建数据库
CREATE DATABASE shop;
-- 3. 查看所有数据库
\l
-- 4. 连接到新数据库
\c shop
-- 5. 退出
\q或者用 createdb 命令（更快）
# 创建数据库（等价于 CREATE DATABASE）
createdb -U postgres mydb
# 创建并指定编码
createdb -U postgres -E UTF8 -T template0 mydb
# 查看帮助
createdb --help四、方式 3：pgAdmin 图形化（新手友好）
- 打开 pgAdmin 4
- 右键
Databases→Create→Database... - 填写：
| 字段 | 推荐值 |
|---|---|
| Database | myapp |
| Owner | appuser |
| Encoding | UTF8 |
| Template | template0 |
| Collate / Ctype | en_US.UTF-8 |
- 点击 Save
五、创建数据库 + 用户 + 授权（完整流程）
-- 1. 创建专用用户
CREATE ROLE appuser WITH
LOGIN
PASSWORD 'secure_password_123'
CONNECTION LIMIT 50;
-- 2. 创建数据库
CREATE DATABASE app_db
WITH
OWNER = appuser
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE = template0;
-- 3. 授予权限（可选，OWNER 已拥有）
GRANT ALL PRIVILEGES ON DATABASE app_db TO appuser;
-- 4. 连接测试
\c app_db appuser六、创建数据库脚本（可保存为 .sql 文件）
-- create_app_db.sql
-- 运行：psql -U postgres -f create_app_db.sql
-- 创建用户
CREATE ROLE webapp WITH LOGIN PASSWORD 'P@ssw0rd2025!';
-- 创建数据库
CREATE DATABASE webapp_db
WITH OWNER = webapp
ENCODING = 'UTF8'
TEMPLATE = template0;
-- 授权
GRANT ALL ON DATABASE webapp_db TO webapp;
-- 连接并创建扩展（可选）
\c webapp_db
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS citext;运行脚本：
psql -U postgres -f create_app_db.sql七、常见编码与区域设置
| 编码 | 说明 |
|---|---|
UTF8 | 强烈推荐，支持所有语言 |
SQL_ASCII | 不推荐，仅 ASCII |
LATIN1 | 西欧语言 |
查看系统支持的编码
SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = 'template1';八、创建数据库时的最佳实践
| 项目 | 建议 |
|---|---|
| 编码 | 始终使用 UTF8 |
| 模板 | 生产环境用 template0 |
| 所有者 | 不要用 postgres，创建专用用户 |
| 连接限制 | 根据应用设置（如 100） |
| 命名 | 小写 + 下划线，如 user_service |
| 权限 | 最小化原则 |
九、常见错误与解决
| 错误 | 原因 | 解决 |
|---|---|---|
ERROR: database "mydb" already exists | 数据库已存在 | 用 DROP DATABASE IF EXISTS mydb; |
ERROR: permission denied to create database | 用户无权限 | 确保登录用户有 CREATEDB 权限 |
ERROR: invalid locale name | 区域设置不支持 | 查看系统支持：locale -a |
FATAL: role "appuser" does not exist | 用户未创建 | 先 CREATE ROLE |
给用户授权创建数据库
ALTER ROLE appuser CREATEDB;十、删除数据库
-- 1. 断开所有连接（重要！）
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb' AND pid <> pg_backend_pid();
-- 2. 删除
DROP DATABASE mydb;
-- 3. 安全删除（推荐）
DROP DATABASE IF EXISTS mydb;十一、查看数据库信息
-- 查看所有数据库
\l
-- 查看详细信息
\l+
-- 查看当前数据库
SELECT current_database();
-- 查看数据库大小
SELECT pg_size_pretty(pg_database_size('mydb'));十二、快速上手：3 分钟创建并使用
# 1. 创建用户和数据库（一行命令）
sudo -u postgres psql -c "CREATE USER app WITH PASSWORD '123';"
createdb -U postgres -O app myapp
# 2. 连接
psql -U app -d myapp -h localhost
# 3. 创建表
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO users (name) VALUES ('Alice');
SELECT * FROM users;小结：创建数据库一图流
1. CREATE ROLE appuser LOGIN PASSWORD '...';
2. CREATE DATABASE app_db OWNER appuser ENCODING 'UTF8' TEMPLATE template0;
3. GRANT ALL ON DATABASE app_db TO appuser;
4. \c app_db appuser现在就动手：
创建一个名为 blog 的数据库，专属用户 blog_user，编码 UTF8，然后用 psql 连接并创建一张 posts 表。
需要我生成：
- 一键创建脚本（含用户 + 数据库 + 表）？回复
一键脚本 - Docker 中创建数据库？回复
Docker - 高可用集群创建数据库？回复
集群
随时告诉我！