PostgreSQL / 系统课时
创建与删除数据库
创建与删除数据库
本课完成数据库的完整生命周期操作:创建、查看、重命名和删除。你将同时掌握 SQL 与命令行工具,并能处理活动连接、事务块和权限不足等常见失败。
学习目标
- 使用
CREATE DATABASE、ALTER DATABASE和DROP DATABASE - 使用
createdb、psql与dropdb完成同类操作 - 在删除前识别连接占用与不可恢复风险
操作前检查
数据库操作需要连接到一个已经存在的数据库,通常选择维护库 postgres。创建数据库的角色必须是超级用户,或拥有 CREATEDB 属性;重命名与删除还要求满足所有权等条件。先确认当前身份和连接目标:
SELECT current_user AS user_name,
current_database() AS database_name;预期结果:
user_name | database_name
-----------+---------------
postgres | postgres
(1 row)实际用户名可能不同。不要连接到准备删除的目标数据库执行删除命令,因为当前会话本身就是活动连接。
使用 SQL 管理数据库
下面创建一个 UTF-8 数据库,并显式指定所有者。数据库名称和角色名称可按实际环境替换。
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';预期结果:
datname | owner
-----------+-----------
demo_shop | app_owner
(1 row)CREATE DATABASE 不能放在显式事务块中,因此不要在 BEGIN 与 COMMIT 之间执行。使用 template0 可获得较干净的模板环境;普通创建也可以省略模板参数。
重命名数据库前,应连接到其他数据库,并确保没有会话仍在使用目标库。
ALTER DATABASE demo_shop RENAME TO demo_store;预期结果:
ALTER DATABASE删除是不可逆操作。确认备份和名称后,再从其他数据库执行:
DROP DATABASE demo_store WITH (FORCE);预期结果:
DROP DATABASEWITH (FORCE) 会尝试终止现有连接,但仍可能因特定会话或权限问题失败。生产环境应先通知使用方、停止应用连接池并核对备份,而不是把强制删除当作常规步骤。
使用命令行工具
createdb 与 dropdb 是 SQL 命令的便捷封装,psql -l 可列出数据库。以下命令假设服务器在本机,端口为默认值;远程环境应补充 -h <HOST> 和 -p <PORT>。
createdb -U postgres -O app_owner demo_shop
psql -U postgres -l
dropdb -U postgres --if-exists demo_shop预期结果:
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 时,先停止应用连接池,再查询连接来源:
SELECT pid, usename, client_addr, application_name
FROM pg_stat_activity
WHERE datname = 'demo_shop';预期结果:
pid | usename | client_addr | application_name
------+---------+-------------+-----------------
<ID> | app | <HOST> | application动态值会随环境变化。若创建失败并提示数据目录权限或磁盘空间问题,应检查数据库服务账号的文件权限和磁盘容量,而不是反复重试 SQL。