PostgreSQL 创建数据库学习笔记

创建数据库概述

在 PostgreSQL 中，数据库是一个独立的数据容器，用于组织和管理相关的表、视图、函数等数据库对象。每个数据库都是相互隔离的，这为多租户应用、开发测试环境隔离等场景提供了良好的支持。

创建数据库的基本方法

1. 使用 SQL 命令创建数据库

最基本的创建数据库方法是使用 CREATE DATABASE 命令：

sql

-- 基本语法
CREATE DATABASE name;

-- 实际示例：创建电商系统数据库
CREATE DATABASE ecommerce_db;

-- 创建带有特定编码的数据库
CREATE DATABASE crm_system 
    ENCODING = 'UTF8' 
    LC_COLLATE = 'zh_CN.UTF-8' 
    LC_CTYPE = 'zh_CN.UTF-8';

业务场景示例：多环境数据库管理

假设您正在管理一个电商平台，需要为开发、测试和生产环境创建独立的数据库：

sql

-- 开发环境数据库
CREATE DATABASE shop_dev 
    ENCODING = 'UTF8'
    CONNECTION LIMIT = 20;  -- 限制并发连接数

-- 测试环境数据库
CREATE DATABASE shop_test 
    ENCODING = 'UTF8'
    CONNECTION LIMIT = 10;

-- 生产环境数据库
CREATE DATABASE shop_prod 
    ENCODING = 'UTF8'
    CONNECTION LIMIT = 100;

2. 使用 createdb 工具

createdb 是一个命令行工具，提供了更便捷的数据库创建方式：

bash

# 基本用法 - 创建与当前用户同名的数据库
createdb

# 创建指定名称的数据库
createdb myapp_db

# 创建数据库并指定所有者
createdb -O alice alice_blog

# 创建数据库并添加注释
createdb -O developer -E UTF8 --locale=zh_CN.UTF-8 inventory_system -c "库存管理系统数据库"

完整示例：为微服务架构创建数据库

bash

#!/bin/bash
# 创建微服务架构所需的多个数据库

# 用户服务数据库
createdb -O microservice_user -E UTF8 user_service_db -c "用户认证与管理服务"

# 订单服务数据库
createdb -O microservice_order -E UTF8 order_service_db -c "订单处理服务"

# 库存服务数据库
createdb -O microservice_inventory -E UTF8 inventory_service_db -c "库存管理服务"

# 支付服务数据库
createdb -O microservice_payment -E UTF8 payment_service_db -c "支付处理服务"

echo "微服务数据库创建完成！"

数据库创建权限管理

权限要求

创建数据库需要特定的权限。以下是权限管理的核心概念：

权限管理示例

sql

-- 创建一个具有创建数据库权限的角色
CREATE ROLE developer WITH LOGIN PASSWORD 'dev123' CREATEDB;

-- 创建一个没有创建数据库权限的普通用户
CREATE ROLE app_user WITH LOGIN PASSWORD 'app123';

-- 授予现有用户创建数据库的权限
ALTER ROLE app_user CREATEDB;

-- 撤销创建数据库的权限
ALTER ROLE developer NOCREATEDB;

为其他用户创建数据库

在实际业务中，经常需要为不同的团队或应用创建独立的数据库：

sql

-- 场景1：为开发团队创建数据库
CREATE DATABASE dev_playground OWNER dev_team;

-- 场景2：为特定应用创建数据库，并设置连接限制
CREATE DATABASE mobile_app_db 
    OWNER mobile_team
    CONNECTION LIMIT 50
    ENCODING 'UTF8';

-- 场景3：创建数据库并立即授予权限
CREATE DATABASE analytics_db OWNER data_team;
GRANT CONNECT ON DATABASE analytics_db TO analyst_role;
GRANT CREATE ON DATABASE analytics_db TO data_engineer;

使用 createdb 工具为其他用户创建数据库：

bash

# 为用户 alice 创建博客数据库
createdb -O alice alice_blog_db

# 为团队创建项目数据库，指定模板和编码
createdb -O project_team -T template0 -E UTF8 project_mgmt_db

模板数据库的使用

PostgreSQL 使用模板数据库机制来创建新数据库，这提供了强大的定制能力：

template1 vs template0

INFO

template1：默认模板，可以自定义。新数据库默认从它克隆。

template0：原始模板，保持不变。用于创建"干净"的数据库。

实际应用示例：定制企业模板数据库

sql

-- 1. 连接到 template1
\c template1

-- 2. 创建企业标准扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";     -- UUID 生成
CREATE EXTENSION IF NOT EXISTS "pgcrypto";      -- 加密功能
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements"; -- 性能监控

-- 3. 创建审计日志表模板
CREATE TABLE audit_log_template (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    action VARCHAR(10) NOT NULL,
    user_name VARCHAR(100) NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    old_data JSONB,
    new_data JSONB
);

-- 4. 创建通用函数
CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 现在创建的新数据库都会包含这些对象
CREATE DATABASE app_with_standards;

使用 template0 创建干净数据库

当需要创建不包含任何自定义内容的数据库时：

sql

-- 使用 template0 创建数据库
CREATE DATABASE clean_db TEMPLATE template0 ENCODING 'UTF8';

-- 或使用 createdb
-- createdb -T template0 -E UTF8 clean_database

第一个数据库的创建流程

理解 PostgreSQL 如何创建第一个数据库对于系统管理很重要：

初始化和创建第一个用户数据库的完整流程

bash

# 1. 初始化数据库集群（通常在安装时自动完成）
initdb -D /var/lib/postgresql/data -E UTF8 --locale=zh_CN.UTF-8

# 2. 启动 PostgreSQL 服务
pg_ctl -D /var/lib/postgresql/data start

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

# 4. 在 psql 中创建第一个应用数据库
CREATE DATABASE my_first_app;

# 5. 创建应用用户并授权
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE my_first_app TO app_user;

常见问题和解决方案

问题 1：ERROR: database already exists

sql

-- 问题重现
CREATE DATABASE test_db;
CREATE DATABASE test_db;  -- ERROR: database "test_db" already exists

-- 解决方案1：先删除再创建
DROP DATABASE IF EXISTS test_db;
CREATE DATABASE test_db;

-- 解决方案2：使用条件创建（需要自定义函数）
CREATE OR REPLACE FUNCTION create_database_if_not_exists(dbname text) 
RETURNS void AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = dbname) THEN
        EXECUTE format('CREATE DATABASE %I', dbname);
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT create_database_if_not_exists('test_db');

问题 2：ERROR: permission denied to create database

bash

# 检查当前用户权限
psql -U current_user -c "\du"

# 解决方案：使用有权限的用户授权
psql -U postgres << EOF
ALTER USER current_user CREATEDB;
EOF

# 或者让超级用户创建数据库
psql -U postgres -c "CREATE DATABASE needed_db OWNER current_user;"

问题 3：连接数限制问题

sql

-- 查看数据库连接限制
SELECT datname, datconnlimit 
FROM pg_database 
WHERE datname NOT LIKE 'template%';

-- 修改连接限制
ALTER DATABASE myapp_db CONNECTION LIMIT 200;

-- 无限制连接（-1 表示无限制）
ALTER DATABASE myapp_db CONNECTION LIMIT -1;

最佳实践总结

TIP

命名规范：使用小写字母和下划线，避免特殊字符
编码设置：始终指定 UTF8 编码以支持多语言
权限最小化：只授予必要的权限
模板定制：为企业标准创建自定义模板
监控连接：合理设置连接限制，避免资源耗尽

企业级数据库创建脚本示例

bash

#!/bin/bash
# 企业标准数据库创建脚本

DB_NAME=$1
DB_OWNER=$2
DB_ENV=$3  # dev, test, prod

if [ -z "$DB_NAME" ] || [ -z "$DB_OWNER" ] || [ -z "$DB_ENV" ]; then
    echo "Usage: $0 <db_name> <db_owner> <environment>"
    exit 1
fi

# 根据环境设置连接限制
case $DB_ENV in
    dev)
        CONN_LIMIT=20
        ;;
    test)
        CONN_LIMIT=50
        ;;
    prod)
        CONN_LIMIT=200
        ;;
    *)
        echo "Invalid environment. Use: dev, test, or prod"
        exit 1
        ;;
esac

# 创建数据库
psql -U postgres << EOF
-- 创建数据库
CREATE DATABASE ${DB_NAME}_${DB_ENV}
    OWNER ${DB_OWNER}
    ENCODING 'UTF8'
    LC_COLLATE = 'zh_CN.UTF-8'
    LC_CTYPE = 'zh_CN.UTF-8'
    CONNECTION LIMIT = ${CONN_LIMIT};

-- 添加注释
COMMENT ON DATABASE ${DB_NAME}_${DB_ENV} IS '${DB_NAME} - ${DB_ENV} 环境';

-- 设置默认权限
GRANT CONNECT ON DATABASE ${DB_NAME}_${DB_ENV} TO ${DB_OWNER};
GRANT CREATE ON DATABASE ${DB_NAME}_${DB_ENV} TO ${DB_OWNER};

-- 创建审计模式
\c ${DB_NAME}_${DB_ENV}
CREATE SCHEMA IF NOT EXISTS audit;
GRANT ALL ON SCHEMA audit TO ${DB_OWNER};

EOF

echo "数据库 ${DB_NAME}_${DB_ENV} 创建成功！"

通过掌握这些数据库创建技术，您可以高效地管理 PostgreSQL 数据库环境，满足各种业务需求。

PostgreSQL 创建数据库学习笔记 ​

创建数据库概述 ​

创建数据库的基本方法 ​

1. 使用 SQL 命令创建数据库 ​

2. 使用 createdb 工具 ​

数据库创建权限管理 ​

权限要求 ​

为其他用户创建数据库 ​

模板数据库的使用 ​

template1 vs template0 ​

使用 template0 创建干净数据库 ​

第一个数据库的创建流程 ​

常见问题和解决方案 ​

问题 1：ERROR: database already exists ​

问题 2：ERROR: permission denied to create database ​

问题 3：连接数限制问题 ​

最佳实践总结 ​

PostgreSQL 创建数据库学习笔记

创建数据库概述

创建数据库的基本方法

1. 使用 SQL 命令创建数据库

2. 使用 createdb 工具

数据库创建权限管理

权限要求

为其他用户创建数据库

模板数据库的使用

template1 vs template0

使用 template0 创建干净数据库

第一个数据库的创建流程

常见问题和解决方案

问题 1：ERROR: database already exists

问题 2：ERROR: permission denied to create database

问题 3：连接数限制问题

最佳实践总结