postgresql之数据库管理

1.1概述

数据库可以被看成是SQL对象(数据库对象)的命名集合，通常而言，每个数据库对象(表、函数等)只属于一个数据库。不过对于部分系统表而言，如pg_database，是属于整个集群的。更准确地说，数据库是模式的集合，而模式包含表、函数等SQL对象。因此完整的对象层次应该是这样的：服务器、数据库、模式、表或其他类型的对象。
在与数据库服务器建立连接时，该连接只能与一个数据库形成关联，不允许在一个会话中进行多个数据库的访问。如以postgres用户登录，该用户可以访问的缺省数据库为postgres，在登录后如果执行下面的SQL语句将会收到PostgreSQL给出的相关错误信息。
postgres=# SELECT *FROM MyTest."MyUser".testtables;
ERROR: cross-databasereferences are not implemented: "otherdb.otheruser.sometable"
LINE 1: select * fromotherdb.otheruser.sometable
在PostgreSQL中，数据库在物理上是相互隔离的，对它们的访问控制也是在会话层次上进行的。然而模式只是逻辑上的对象管理结构，是否能访问某个模式的对象是由权限系统来控制的。
执行下面的基于系统表的查询语句可以列出现有的数据库集合。
SELECT datname FROMpg_database;
注：psql应用程序的l元命令和-l命令行选项也可以用来列出当前服务器中已有的数据库。
1.2 创建数据库

在PostgreSQL服务器上执行下面的SQL语句可以创建数据库。
CREATEDATABASEdb_name;
在数据库成功创建之后，当前登录角色将自动成为此新数据库的所有者。在删除该数据库时，也需要该用户的特权。如果你想让当前创建的数据库的所有者为其它角色，可以执行下面的SQL语句。db_nameOWNERrole_name;
1.3 修改数据库配置

PostgreSQL服务器提供了大量的运行时配置变量，我们可以根据自己的实际情况为某一数据库的某一配置变量指定特殊值，通过执行下面的SQL命令可以使该数据库的某一配置被设置为指定值，而不再使用缺省值。
ALTERDATABASESETvarnameTOnew_value;
这样在之后基于该数据库的会话中，被修改的配置值已经生效。如果要撤消这样的设置并恢复为原有的缺省值，可以执行下面的SQL命令。dbnameRESETvarname;

1.4 删除数据库 只有数据库的所有者和超级用户可以删除数据库。删除数据库将会删除数据库中包括的所有对象，该操作是不可恢复的。见如下删除SQL命令：DROPDATABASEdb_name;

1.5 表空间PostgreSQL中，表空间表示一组文件存放的目录位置。在创建之后，就可以在该表空间上创建数据库对象。通过使用表空间，管理员可以控制一个PostgreSQL服务器的磁盘布局。这样管理员就可以根据数据库对象的数据量和数据使用频度等参照来规划这些对象的存储位置，以便减少IO等待，从而优化系统的整体运行性能。比如，将一个使用频繁的索引放在非常可靠、高效的磁盘设备上，如固态硬盘。而将很少使用的数据库对象存放在相对较慢的磁盘系统上。下面的SQL命令用于创建表空间。CREATETABLESPACEfastspaceLOCATION'/mnt/sda1/postgresql/data';
需要说明的是，表空间指定的位置必须是一个现有的空目录，且属于PostgreSQL系统用户，如postgres。在成功创建之后，所有在该表空间上创建的对象都将被存放在这个目录下的文件里。
在PostgreSQL中只有超级用户可以创建表空间，但是在成功创建之后，就可以允许普通数据库用户在其上创建数据库对象了。要完成此操作，必须在表空间上给这些用户授予CREATE权限。表、索引和整个数据库都可以放在特定的表空间里。见如下CREATETABLEfoo(i int)TABLESPACEspace1;
此外，我们还可以通过修改default_tablespace配置变量，以使指定的表空间成为缺省表空间，这样在创建任何数据库对象时，如果没有显示指定表空间，那么该对象将被创建在缺省表空间中，如：
SET default_tablespace = space1;
CREATE TABLE foo(i int);
与数据库相关联的表空间用于存储该数据库的系统表，以及任何使用该数据库的服务器进程创建的临时文件。
要删除一个空的表空间，可以直接使用DROP TABLESPACE命令，然而要删除一个包含数据库对象的表空间，则需要先将该表空间上的所有对象全部删除后，才可以再在删除该表空间。
要检索当前系统中有哪些表空间，可以执行以下查询，其中pg_tablespace为PostgreSQL中的系统表。SELECT spcname FROMpg_tablespace;
我们还可以通过psql程序的db元命令列出现有的表空间。