PostgreSQL 数据库创建、修改与删除

核心概念

PostgreSQL 数据库创建、修改与删除是数据库管理的基础操作，涉及以下核心概念：

• 数据库模板：创建新数据库时使用的模板，默认模板为template1
• 数据库编码：数据库使用的字符编码，如UTF8、GBK等
• 表空间：数据库对象存储的位置，可以为不同数据库指定不同的表空间
• 连接限制：允许同时连接到数据库的最大连接数
• 权限控制：数据库级别的权限管理，控制用户对数据库的访问权限

数据库创建

1. 基本创建语法

-- 创建基本数据库
CREATE DATABASE dbname;

-- 创建带参数的数据库
CREATE DATABASE dbname
    WITH
    OWNER = username            -- 指定数据库所有者
    TEMPLATE = template0       -- 使用template0模板（推荐用于自定义编码）
    ENCODING = 'UTF8'          -- 指定字符编码
    LC_COLLATE = 'zh_CN.UTF-8' -- 指定排序规则
    LC_CTYPE = 'zh_CN.UTF-8'   -- 指定字符分类
    TABLESPACE = tablespacename -- 指定表空间
    CONNECTION LIMIT = 100;    -- 限制最大连接数

2. 常用创建示例

-- 创建UTF8编码的数据库
CREATE DATABASE mydb
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'zh_CN.UTF-8'
    LC_CTYPE = 'zh_CN.UTF-8'
    TEMPLATE = template0
    CONNECTION LIMIT = -1; -- -1表示无限制

-- 从现有数据库复制创建
CREATE DATABASE db_copy
    WITH
    TEMPLATE = original_db
    OWNER = new_owner;

3. 创建数据库的系统命令

除了SQL语句，还可以使用createdb命令行工具创建数据库：

# 基本用法
createdb mydb

# 指定所有者和编码
createdb -O postgres -E UTF8 mydb

# 从远程主机创建数据库
createdb -h 192.168.1.100 -U postgres mydb

数据库修改

1. 修改数据库属性

-- 修改数据库所有者
ALTER DATABASE dbname OWNER TO new_owner;

-- 修改数据库名称
ALTER DATABASE dbname RENAME TO new_dbname;

-- 修改数据库连接限制
ALTER DATABASE dbname CONNECTION LIMIT = 200;

-- 修改数据库默认表空间
ALTER DATABASE dbname SET TABLESPACE new_tablespace;

-- 设置数据库级别的参数
ALTER DATABASE dbname SET search_path TO schema1, schema2, public;
ALTER DATABASE dbname SET statement_timeout = '30s';

2. 修改数据库权限

-- 授予用户连接权限
GRANT CONNECT ON DATABASE dbname TO username;

-- 授予用户创建schema权限
GRANT CREATE ON DATABASE dbname TO username;

-- 撤销用户权限
REVOKE CREATE ON DATABASE dbname FROM username;

3. 维护操作

-- 重新加载数据库配置
ALTER DATABASE dbname RELOAD;

-- 关闭数据库中的所有连接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'dbname' AND pid <> pg_backend_pid();

数据库删除

1. 基本删除语法

-- 删除数据库
DROP DATABASE [IF EXISTS] dbname;

-- 强制删除数据库（关闭所有连接后删除）
DROP DATABASE dbname WITH (FORCE);

2. 删除数据库的系统命令

使用dropdb命令行工具删除数据库：

# 基本用法
dropdb mydb

# 如果数据库不存在也不报错
dropdb -i mydb

# 从远程主机删除数据库
dropdb -h 192.168.1.100 -U postgres mydb

最佳实践

1. 生产环境创建建议

1. 使用合适的模板：

   • 对于自定义编码，使用template0模板
   • 对于继承现有数据库属性，使用template1或现有数据库作为模板

2. 合理设置连接限制：

   • 根据数据库服务器的资源情况设置合理的连接限制
   • 避免设置过大的连接数导致资源耗尽

3. 规划表空间：

   • 为不同类型的数据库分配不同的表空间
   • 将频繁访问的数据库放在高性能存储上

4. 统一编码设置：

   • 建议使用UTF8编码，支持多语言
   • 确保LC_COLLATE和LC_CTYPE设置一致

2. 修改数据库注意事项

1. 修改数据库名称的影响：

   • 会影响所有连接到该数据库的应用
   • 需要更新应用配置中的数据库名称

2. 修改连接限制：

   • 现有连接不受影响，只影响新连接
   • 建议在低峰期进行修改

3. 修改表空间：

   • 只有新创建的对象会使用新表空间
   • 现有对象需要手动迁移

3. 删除数据库注意事项

1. 备份重要数据：

   • 删除数据库前一定要备份重要数据
   • 考虑使用pg_dump进行逻辑备份

2. 关闭所有连接：

   • 删除数据库前确保没有活动连接
   • 使用pg_terminate_backend强制关闭连接

3. 使用IF EXISTS子句：

   • 避免因数据库不存在导致的错误
   • 提高脚本的健壮性

常见问题（FAQ）

Q1：如何创建指定编码的数据库？

解决方案：

使用CREATE DATABASE语句并指定ENCODING、LC_COLLATE和LC_CTYPE参数：

CREATE DATABASE mydb
    WITH
    OWNER = postgres
    TEMPLATE = template0
    ENCODING = 'UTF8'
    LC_COLLATE = 'zh_CN.UTF-8'
    LC_CTYPE = 'zh_CN.UTF-8';

Q2：如何修改数据库的所有者？

解决方案：

使用ALTER DATABASE语句修改数据库所有者：

-- 修改数据库所有者
ALTER DATABASE dbname OWNER TO new_owner;

-- 确保新所有者有创建数据库的权限
GRANT CREATEDB TO new_owner;

Q3：如何删除有活动连接的数据库？

解决方案：

首先关闭所有活动连接，然后删除数据库：

-- 关闭所有连接（除了当前连接）
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'dbname' AND pid <> pg_backend_pid();

-- 删除数据库
DROP DATABASE dbname;

-- 或者使用FORCE选项
DROP DATABASE dbname WITH (FORCE);

Q4：如何限制数据库的最大连接数？

解决方案：

使用CONNECTION LIMIT参数设置数据库的最大连接数：

-- 创建数据库时设置
CREATE DATABASE dbname CONNECTION LIMIT = 100;

-- 修改现有数据库
ALTER DATABASE dbname CONNECTION LIMIT = 200;

-- 无限制连接
ALTER DATABASE dbname CONNECTION LIMIT = -1;

Q5：如何查看数据库的详细信息？

解决方案：

使用以下查询查看数据库的详细信息：

-- 查看所有数据库的基本信息
\l+ 

-- 或使用SQL查询
SELECT 
    datname,
    datdba::regrole,
    encoding,
    datcollate,
    datctype,
    datistemplate,
    datallowconn,
    datconnlimit,
    datTablespace
FROM pg_database;

-- 查看特定数据库的大小
SELECT pg_size_pretty(pg_database_size('dbname'));