管理数据库
每个正在运行的PostgreSQL 服务器实例都管理着一个或多个数据库。因此,在组织SQL对象(“数据库对象”)的层次中,数据库位于最顶层。
本章描述数据库的属性,以及如何创建、管理、删除它们。
数据库是一些SQL对象(“数据库对象”) 的命名集合。通常每个数据库对象(表、函数等)属于并且只属于一个数据库。
(不过有几个系统表如pg_database 属于整个集群并且可以在集群之内的每个数据库中访问)
因此完整的层次是这样的:服务器、数据库、模式、表 (或者某些其他对象类型,如函数)。
模式只是一个纯粹的逻辑结构, 并且谁能访问某个模式由权限系统管理。
postgres=# SELECT datname FROM pg_database;
datname
-----------
template1
template0
postgres
exampledb
postgres-# \l
CREATE DATABASE name;
其他人创建一个数据库,并且使其成为新数据库的所有者
CREATE DATABASE dbname OWNER rolename;
CREATE DATABASE实际上通过拷贝一个已有数据库进行工作。 默认情况下,它拷贝名为template1 的标准系统数据库。所以该数据库是创建新数据库的“模板”。
如果你为template1数据库增加对象, 这些对象将被拷贝到后续创建的用户数据库中。 这种行为允许对数据库中标准对象集合的站点本地修改。
postgres-# create database hq owner dbuser;
ERROR: syntax error at or near "-"
LINE 1: -l
^
postgres=# SELECT rolname FROM pg_roles;
rolname
----------
postgres
dbuser
(2 rows)
postgres=# CREATE DATABASE hq OWNER dbuser;
CREATE DATABASE
系统里还有名为template0 的第二个标准系统数据库。这个数据库包含和template1 初始内容一样的数据,也就是说,
只包含你的PostgreSQL 版本预定义的标准对象。在数据库集簇被初始化之后,不应该对template0 做任何修改。
通过指示CREATE DATABASE使用template0 而不是template1进行拷贝,你可以创建一个“纯净的”用户数据库, 它不会包
含任何template1中的站点本地附加物。
这一点在恢复一个pg_dump转储时非常方便: 转储脚本应该在一个纯净的数据库中恢复以确保我们重建被转储数据库的正确内容, 而不和任何
现在可能已经被加入到template1中的附加物相冲突。
CREATE DATABASE dbname TEMPLATE template0;
可以创建额外的模板数据库,并且实际上可以通过将集簇中任意数据库指定为 CREATEDATABASE的模板来从该数据库拷贝
如果在CREATE DATABASE开始时存在任何其它连接,那么该命令将会失败。 在拷贝操作期间,到源数据库的新连接将被阻止。
对于每一个数据库在pg_database中存在两个有用的标志:datistemplate和datallowconn列。
datistemplate可以被设置来指示该数据库是不是要作为 CREATE DATABASE的模板。如果设置了这个标志,那么该数据库可以被任何
有 CREATEDB权限的用户克隆;如果没有被设置, 那么只有超级用户和该数据库的所有者可以克隆它。
如果datallowconn为假, 那么将不允许与该数据库建立任何新的连接(但已有的会话不会因为把该标志设置为假而被中止)。
template0数据库通常被标记为datallowconn = false来阻止对它的修改。 template0和template1通常总是被标记为datistemplate = true。
注意: 除了template1是CREATE DATABASE的默认源数据库名之外, template1和template0没有任何特殊的状态。
例如, 我们可以删除template1然后从template0 重新创建它而不会有任何不良效果。
如果我们不小心在template1 中增加了一堆垃圾,那么我们就会建议做这样的操作(要删除template1, 它必须有pg_database.datistemplate = false)
PostgreSQL 服务器提供了大量的运行时配置变量。
例如,如果由于某种原因,你想禁用指定数据库上的GEQO优化器, 正常情况下你不得不对
所有数据库禁用它,或者确保每个连接的客户端小心地发出了 SET geqo TO off。要令这
个设置在一个特定数据库中成为默认值, 你可以执行下面的命令:
ALTER DATABASE mydb SET geqo TO off;
这样将保存该设置(但不是立即设置它)。 在后续建立的到该数据库的连接中它将表现得像
在会话开始后马上调用 SET geqo TO off;。注意用户仍然可以在该会话中更改这个设置,
它只是默认值。要撤消这样的设置,使用 ALTER DATABASE dbname RESET varname。
DROP DATABASE name;
只有数据库的所有者或者超级用户才可以删除数据库。 删除数据库会移除其中包括的所有对象。数据库的删除不能被撤销。
你不能在与目标数据库连接时执行DROP DATABASE命令。
为了方便,有一个在shell上运行的命令可以删除数据库, dropdb:
dropdb dbname