PostgreSQL / 系统课时

创建与删除数据库

创建与删除数据库

本课完成数据库的完整生命周期操作:创建、查看、重命名和删除。你将同时掌握 SQL 与命令行工具,并能处理活动连接、事务块和权限不足等常见失败。

学习目标

  • 使用 CREATE DATABASE、ALTER DATABASE 和 DROP DATABASE
  • 使用 createdb、psql 与 dropdb 完成同类操作
  • 在删除前识别连接占用与不可恢复风险

操作前检查

数据库操作需要连接到一个已经存在的数据库,通常选择维护库 postgres。创建数据库的角色必须是超级用户,或拥有 CREATEDB 属性;重命名与删除还要求满足所有权等条件。先确认当前身份和连接目标:

sql
SELECT current_user AS user_name,
       current_database() AS database_name;

预期结果:

text
 user_name | database_name
-----------+---------------
 postgres  | postgres
(1 row)

实际用户名可能不同。不要连接到准备删除的目标数据库执行删除命令,因为当前会话本身就是活动连接。

使用 SQL 管理数据库

下面创建一个 UTF-8 数据库,并显式指定所有者。数据库名称和角色名称可按实际环境替换。

sql
CREATE ROLE app_owner NOLOGIN;

CREATE DATABASE demo_shop
    WITH OWNER = app_owner
         ENCODING = 'UTF8'
         TEMPLATE = template0;

SELECT datname, pg_get_userbyid(datdba) AS owner
FROM pg_database
WHERE datname = 'demo_shop';

预期结果:

text
  datname  |   owner
-----------+-----------
 demo_shop | app_owner
(1 row)

CREATE DATABASE 不能放在显式事务块中,因此不要在 BEGIN 与 COMMIT 之间执行。使用 template0 可获得较干净的模板环境;普通创建也可以省略模板参数。

重命名数据库前,应连接到其他数据库,并确保没有会话仍在使用目标库。

sql
ALTER DATABASE demo_shop RENAME TO demo_store;

预期结果:

text
ALTER DATABASE

删除是不可逆操作。确认备份和名称后,再从其他数据库执行:

sql
DROP DATABASE demo_store WITH (FORCE);

预期结果:

text
DROP DATABASE

WITH (FORCE) 会尝试终止现有连接,但仍可能因特定会话或权限问题失败。生产环境应先通知使用方、停止应用连接池并核对备份,而不是把强制删除当作常规步骤。

使用命令行工具

createdb 与 dropdb 是 SQL 命令的便捷封装,psql -l 可列出数据库。以下命令假设服务器在本机,端口为默认值;远程环境应补充 -h <HOST> 和 -p <PORT>。

bash
createdb -U postgres -O app_owner demo_shop
psql -U postgres -l
dropdb -U postgres --if-exists demo_shop

预期结果:

text
List of databases
   Name    |   Owner
-----------+-----------
 demo_shop | app_owner
...

列表还会包含模板库和其他数据库,列宽与排序可能因版本及终端设置不同。dropdb 成功时通常不输出正文,进程退出码为 0。

典型失败处理

出现 CREATE DATABASE cannot run inside a transaction block 时,应退出事务后单独执行创建语句。迁移框架若默认把脚本包在事务中,需要把数据库创建步骤移到框架外部。

出现 database is being accessed by other users 时,先停止应用连接池,再查询连接来源:

sql
SELECT pid, usename, client_addr, application_name
FROM pg_stat_activity
WHERE datname = 'demo_shop';

预期结果:

text
 pid  | usename | client_addr | application_name
------+---------+-------------+-----------------
 <ID> | app     | <HOST>      | application

动态值会随环境变化。若创建失败并提示数据目录权限或磁盘空间问题,应检查数据库服务账号的文件权限和磁盘容量,而不是反复重试 SQL。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

创建与删除数据库

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通