PostgreSQL教程(十三)：数据库管理详解

一、概述：

数据库可以被看成是SQL对象(数据库对象)的命名集合，通常而言，每个数据库对象(表、函数等)只属于一个数据库。不过对于部分系统表而言，如pg_database，是属于整个集群的。更准确地说，数据库是模式的集合，而模式包含表、函数等SQL对象。因此完整的对象层次应该是这样的：服务器、数据库、模式、表或其他类型的对象。

在与数据库服务器建立连接时，该连接只能与一个数据库形成关联，不允许在一个会话中进行多个数据库的访问。如以postgres用户登录，该用户可以访问的缺省数据库为postgres，在登录后如果执行下面的SQL语句将会收到PostgreSQL给出的相关错误信息。

代码如下:

postgres=# SELECT * FROM MyTest."MyUser".testtables;
    ERROR:  cross-database references are not implemented: "otherdb.otheruser.sometable"
    LINE 1: select * from otherdb.otheruser.sometable

在PostgreSQL中，数据库在物理上是相互隔离的，对它们的访问控制也是在会话层次上进行的。然而模式只是逻辑上的对象管理结构，是否能访问某个模式的对象是由权限系统来控制的。
    执行下面的基于系统表的查询语句可以列出现有的数据库集合。
 

代码如下:

SELECT datname FROM pg_database;

注：psql应用程序的\l元命令和-l命令行选项也可以用来列出当前服务器中已有的数据库。 
   
二、创建数据库：

在PostgreSQL服务器上执行下面的SQL语句可以创建数据库。
 

代码如下:

CREATE DATABASE db_name;

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

代码如下:

CREATE DATABASE db_name OWNER role_name;

三、修改数据库配置：

PostgreSQL服务器提供了大量的运行时配置变量，我们可以根据自己的实际情况为某一数据库的某一配置变量指定特殊值，通过执行下面的SQL命令可以使该数据库的某一配置被设置为指定值，而不再使用缺省值。
 

代码如下:

ALTER DATABASE db_name SET varname TO new_value;

这样在之后基于该数据库的会话中，被修改的配置值已经生效。如果要撤消这样的设置并恢复为原有的缺省值，可以执行下面的SQL命令。
 

代码如下:

ALTER DATABASE dbname RESET varname;

四、删除数据库：

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

代码如下:

DROP DATABASE db_name;

五、表空间：

在PostgreSQL中，表空间表示一组文件存放的目录位置。在创建之后，就可以在该表空间上创建数据库对象。通过使用表空间，管理员可以控制一个PostgreSQL服务器的磁盘布局。这样管理员就可以根据数据库对象的数据量和数据使用频度等参照来规划这些对象的存储位置，以便减少IO等待，从而优化系统的整体运行性能。比如，将一个使用频繁的索引放在非常可靠、高效的磁盘设备上，如固态硬盘。而将很少使用的数据库对象存放在相对较慢的磁盘系统上。下面的SQL命令用于创建表空间。
 

代码如下:

CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';

需要说明的是，表空间指定的位置必须是一个现有的空目录，且属于PostgreSQL系统用户，如postgres。在成功创建之后，所有在该表空间上创建的对象都将被存放在这个目录下的文件里。
    在PostgreSQL中只有超级用户可以创建表空间，但是在成功创建之后，就可以允许普通数据库用户在其上创建数据库对象了。要完成此操作，必须在表空间上给这些用户授予CREATE权限。表、索引和整个数据库都可以放在特定的表空间里。见如下SQL命令：
 

代码如下:

CREATE TABLE foo(i int) TABLESPACE space1;

此外，我们还可以通过修改default_tablespace配置变量，以使指定的表空间成为缺省表空间，这样在创建任何数据库对象时，如果没有显示指定表空间，那么该对象将被创建在缺省表空间中，如：
 

代码如下:

SET default_tablespace = space1;
    CREATE TABLE foo(i int);

与数据库相关联的表空间用于存储该数据库的系统表，以及任何使用该数据库的服务器进程创建的临时文件。
    要删除一个空的表空间，可以直接使用DROP TABLESPACE命令，然而要删除一个包含数据库对象的表空间，则需要先将该表空间上的所有对象全部删除后，才可以再在删除该表空间。

要检索当前系统中有哪些表空间，可以执行以下查询，其中pg_tablespace为PostgreSQL中的系统表。
 

代码如下:

SELECT spcname FROM pg_tablespace;

我们还可以通过psql程序的\db元命令列出现有的表空间。

赞 (0)

相关推荐

  • PostgreSQL教程(三)：表的继承和分区表详解

    一.表的继承: 这个概念对于很多已经熟悉其他数据库编程的开发人员而言会多少有些陌生,然而它的实现方式和设计原理却是简单易懂,现在就让我们从一个简单的例子开始吧.     1. 第一个继承表:   复制代码 代码如下: CREATE TABLE cities (   --父表         name        text,         population float,         altitude     int     );     CREATE TABLE capitals (

  • PostgreSQL教程(十)：性能提升技巧

    一.使用EXPLAIN: PostgreSQL为每个查询都生成一个查询规划,因为选择正确的查询路径对性能的影响是极为关键的.PostgreSQL本身已经包含了一个规划器用于寻找最优规划,我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划.     PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树,其中最底层的节点为表扫描节点,用于从数据表中返回检索出的数据行.然而,不同的扫描节点类型代表着不同的表访问模式,如:顺序扫描.索引扫描,以及位图索引扫描等.如果查

  • PostgreSQL教程(四)：数据类型详解

    PostgreSQL教程(四)：数据类型详解

    一.数值类型: 下面是PostgreSQL所支持的数值类型的列表和简单说明: 1. 整数类型: 类型smallint.integer和bigint存储各种范围的全部是数字的数,也就是没有小数部分的数字.试图存储超出范围以外的数值将导致一个错误.常用的类型是integer,因为它提供了在范围.存储空间和性能之间的最佳平衡.一般只有在磁盘空间紧张的时候才使用smallint.而只有在integer的范围不够的时候才使用bigint,因为前者(integer)绝对快得多.     2. 任意精度数值:

  • PostgreSQL教程(八)：索引详解

    一.索引的类型: PostgreSQL提供了多 种索引类型:B-Tree.Hash.GiST和GIN,由于它们使用了不同的算法,因此每种索引类型都有其适合的查询类型,缺省时,CREATE INDEX命令将创建B-Tree索引.         1. B-Tree:   复制代码 代码如下: CREATE TABLE test1 (         id integer,         content varchar     );     CREATE INDEX test1_id_index

  • PostgreSQL教程(一)：数据表详解

    一.表的定义: 对于任何一种关系型数据库而言,表都是数据存储的最核心.最基础的对象单元.现在就让我们从这里起步吧.     1. 创建表:   复制代码 代码如下: CREATE TABLE products (         product_no integer,         name text,         price numeric     );     2. 删除表:   复制代码 代码如下: DROP TABLE products;     3. 创建带有缺省值的表:   复

  • PostgreSQL教程(六)：函数和操作符详解（2）

    六.模式匹配: PostgreSQL中提供了三种实现模式匹配的方法:SQL LIKE操作符,更近一些的SIMILAR TO操作符,和POSIX-风格正则表达式.     1. LIKE:   复制代码 代码如下: string LIKE pattern [ ESCAPE escape-character ]     string NOT LIKE pattern [ ESCAPE escape-character ] 每个pattern定义一个字串的集合.如果该string包含在pattern代

  • PostgreSQL教程(九)：事物隔离介绍

    在SQL的标准中事物隔离级别分为以下四种:     1. 读未提交(Read uncommitted)     2. 读已提交(Read committed)     3. 可重复读(Repeatable read)     4. 可串行化(Serializable)     然而PostgreSQL在9.1之前的版本中只是实现了其中两种,即读已提交和可串行化,如果在实际应用中选择了另外两种,那么PostgreSQL将会自动向更严格的隔离级别调整.在PostgreSQL v9.1的版本中提供了三

  • PostgreSQL教程(十一)：服务器配置

    一.服务器进程的启动和关闭: 下面是pg_ctl命令的使用方法和常用选项,需要指出的是,该命令是postgres命令的封装体,因此在使用上比直接使用postgres更加方便. 复制代码 代码如下: pg_ctl init[db] [-D DATADIR] [-s] [-o "OPTIONS"]     pg_ctl start     [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]    

  • PostgreSQL教程(十二)：角色和权限管理介绍

    PostgreSQL是通过角色来管理数据库访问权限的,我们可以将一个角色看成是一个数据库用户,或者一组数据库用户.角色可以拥有数据库对象,如表.索引,也可以把这些对象上的权限赋予其它角色,以控制哪些用户对哪些对象拥有哪些权限.     一.数据库角色: 1. 创建角色:   复制代码 代码如下: CREATE ROLE role_name; 2. 删除角色:   复制代码 代码如下: DROP ROLE role_name; 3. 查询角色: 检查系统表pg_role,如:   复制代码 代码如

  • PostgreSQL教程(十四)：数据库维护

    一.恢复磁盘空间: 在PostgreSQL中,使用delete和update语句删除或更新的数据行并没有被实际删除,而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期.因此当数据表中的数据变化极为频繁时,那么在一段时间之后该表所占用的空间将会变得很大,然而数据量却可能变化不大.要解决该问题,需要定期对数据变化频繁的数据表执行VACUUM操作. VACUUM命令存在两种形式,VACUUM和VACUUM FULL,它们之间的区别见如下表格:   无VACUUM VACUUM VACUUM

  • PostgreSQL教程(七)：函数和操作符详解（3）

    九.序列操作函数: 序列对象(也叫序列生成器)都是用CREATE SEQUENCE创建的特殊的单行表.一个序列对象通常用于为行或者表生成唯一的标识符.下面序列函数,为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法. 函数 返回类型 描述 nextval(regclass) bigint 递增序列对象到它的下一个数值并且返回该值.这个动作是自动完成的.即使多个会话并发运行nextval,每个进程也会安全地收到一个唯一的序列值. currval(regclass) bigint 在当

  • PostgreSQL教程(五)：函数和操作符详解（1）

    PostgreSQL教程(五)：函数和操作符详解（1）

    一.逻辑操作符: 常用的逻辑操作符有:AND.OR和NOT.其语义与其它编程语言中的逻辑操作符完全相同. 二.比较操作符: 下面是PostgreSQL中提供的比较操作符列表: 比较操作符可以用于所有可以比较的数据类型.所有比较操作符都是双目操作符,且返回boolean类型.除了比较操作符以外,我们还可以使用BETWEEN语句,如:     a BETWEEN x AND y 等效于 a >= x AND a <= y        a NOT BETWEEN x AND y 等效于 a <

  • PostgreSQL教程(二)：模式Schema详解

    一个数据库包含一个或多个命名的模式,模式又包含表.模式还包含其它命名的对象,包括数据类型.函数,以及操作符.同一个对象名可以在不同的模式里使用而不会导致冲突: 比如,schema1和myschema都可以包含叫做mytable的表.和数据库不同,模式不是严格分离的:一个用户可以访问他所连接的数据库中的任意模式中的对象,只要他有权限. 我们需要模式有以下几个主要原因: 1). 允许多个用户使用一个数据库而不会干扰其它用户.     2). 把数据库对象组织成逻辑组,让它们更便于管理.     3)

随机推荐