PostgreSQL学习笔记12 －end

最新推荐文章于 2024-02-07 22:09:49 发布

An_angel_of_joy

最新推荐文章于 2024-02-07 22:09:49 发布

阅读量4.9k

点赞数

分类专栏： PostgreSQL 文章标签： c c++ database postgresql sql

本文链接：https://blog.csdn.net/u010258235/article/details/41009915

版权

PostgreSQL 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

PostgreSQL学习笔记（十二）数据库维护

一、恢复磁盘空间

　　在PostgreSQL中，使用delete和update语句删除或更新的数据行并没有被实际删除，而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期。因此当数据表中的数据变化极为频繁时，那么在一段时间之后该表所占用的空间将会变得很大，然而数据量却可能变化不大。要解决该问题，需要定期对数据变化频繁的数据表执行VACUUM操作。

　　VACUUM命令存在两种形式，VACUUM和VACUUM FULL，它们之间的区别见如下表格：

	无VACUUM	VACUUM	VACUUM FULL
删除大量数据之后	只是将删除数据的状态置为已删除，该空间不能记录被重新使用。	如果删除的记录位于表的末端，其所占用的空间将会被物理释放并归还操作系统。如果不是末端数据，该命令会将指定表或索引中被删除数据所占用空间重新置为可用状态，那么在今后有新数据插入时，将优先使用该空间，直到所有被重用的空间用完时，再考虑使用新增的磁盘页面。	不论被删除的数据是否处于数据表的末端，这些数据所占用的空间都将被物理的释放并归还于操作系统。之后再有新数据插入时，将分配新的磁盘页面以供使用。
执行效率		由于只是状态置为操作，因此效率较高。	在当前版本的PostgreSQL(v9.1)中，该命令会为指定的表或索引重新生成一个数据文件，并将原有文件中可用的数据导入到新文件中，之后再删除原来的数据文件。因此在导入过程中，要求当前磁盘有更多的空间可用于此操作。由此可见，该命令的执行效率相对较低。
被删除的数据所占用的物理空间是否被重新规划给操作系统。	不会	不会	会
在执行VACUUM命令时，是否可以并发执行针对该表的其他操作。		由于该操作是共享锁，因此可以与其他操作并行进行。	由于该操作需要在指定的表上应用排它锁，因此在执行该操作期间，任何基于该表的操作都将被挂起，知道该操作完成。
推荐使用方式	在进行数据清空是，可以使用truncate操作，因为该操作将会物理的清空数据表，并将其所占用的空间直接归还于操作系统。	为了保证数据表的磁盘页面数量能够保持在一个相对稳定值，可以定期执行该操作，如每天或每周中数据操作相对较少的时段。	考虑到该操作的开销，以及对其他错误的排斥，推荐的方式是，定期监控数据量变化较大的表，只有确认其磁盘页面占有量接近临界值时，才考虑执行一次该操作。即便如此，也需要注意尽量选择数据操作较少的时段来完成该操作。
执行后其它操作的效率	对于查询而言，由于存在大量的磁盘页面碎片，因此效率会逐步降低。	相比于不执行任何VACUUM操作，其效率更高，但是插入的效率会有所降低。	在执行完该操作后，所有基于该表的操作效率都会得到极大的提升。

二、更新规划器统计：

　　我们可以为特定的表，甚至是表中特定的字段运行ANALYZE命令，这样我们就可以根据实际情况，只对更新比较频繁的部分信息执行ANALYZE操作，这样不仅可以节省统计信息所占用的空间，也可以提高本次ANALYZE操作的执行效率。这里需要额外说明的是，ANALYZE是一项相当快的操作，即使是在数据量较大的表上也是如此，因为它使用了统计学上的随机采样的方法进行行采样，而不是把每一行数据都读取进来并进行分析。因此，可以考虑定期对整个数据库执行该命令。

　　事实上，我们甚至可以通过下面的命令来调整指定字段的抽样率，如：

　　ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200;

　　注意：该值的取值范围是0--1000，其中值越低采样比例就越低，分析结果的准确性也就越低，但是ANALYZE命令执行的速度却更快。如果将该值设置为-1，那么该字段的采样比率将恢复到系统当前默认的采样值，我们可以通过下面的命令获取当前系统的缺省采样值。

postgres=# show default_statistics_target;
 default_statistics_target
---------------------------
 100
(1 row)

　　从上面的结果可以看出，该数据库的缺省采样值为100(10%)。

三、VACUUM和ANALYZE的示例

    #1. 创建测试数据表。
    postgres=# CREATE TABLE testtable (i integer);
    CREATE TABLE
    #2. 为测试表创建索引。
    postgres=# CREATE INDEX testtable_idx ON testtable(i);
    CREATE INDEX
    #3. 创建批量插入测试数据的函数。
    postgres=# CREATE OR REPLACE FUNCTION test_insert() returns integer AS $$
    DECLARE
        min integer;
        max integer;
    BEGIN
        SELECT COUNT(*) INTO min from testtable;
        max := min + 10000;
        FOR i IN min..max LOOP
            INSERT INTO testtable VALUES(i);
        END LOOP;
        RETURN 0;
    END;
    $$ LANGUAGE plpgsql;
    CREATE FUNCTION
    #4. 批量插入数据到测试表(执行四次)
    postgres=# SELECT test_insert();
    test_insert
    -------------
               0
    (1 row)
    #5. 确认四次批量插入都成功。
    postgres=# SELECT COUNT(*) FROM testtable;
    count
    -------
    40004
    (1 row)
    #6. 分析测试表，以便有关该表的统计信息被更新到PostgreSQL的系统表。
    postgres=# ANALYZE testtable;
    ANALYZE
    #7. 查看测试表和索引当前占用的页面数量(通常每个页面为8k)。
    postgres=# SELECT relname, relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname   | relfilenode    | relpages
    ---------------+-------------+----------
    testtable        |       17601 |      157
    testtable_idx |       17604   |       90
    #8. 批量删除数据。
    postgres=# DELETE FROM testtable WHERE i < 30000;
    DELETE 30003
    #9. 执行vacuum和analyze，以便更新系统表，同时为该表和索引记录高水标记。
    #10. 这里需要额外说明的是，上面删除的数据均位于数据表的前部，如果删除的是末尾部分，
    #      如where i > 10000，那么在执行VACUUM ANALYZE的时候，数据表将会被物理的缩小。
    postgres=# VACUUM ANALYZE testtable;
    ANALYZE
    #11. 查看测试表和索引在删除后，再通过VACUUM ANALYZE更新系统统计信息后的结果(保持不变)。
    postgres=# SELECT relname, relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname | relfilenode     | relpages
    ---------------+-------------+----------
    testtable        |       17601   |      157
    testtable_idx |       17604   |       90
    (2 rows)
    #12. 再重新批量插入两次，之后在分析该表以更新其统计信息。
    postgres=# SELECT test_insert(); --执行两次。
    test_insert
    -------------
               0
    (1 row)
    postgres=# ANALYZE testtable;
    ANALYZE
    #13. 此时可以看到数据表中的页面数量仍然为之前的高水标记数量，索引页面数量的增加
    #      是和其内部实现方式有关，但是在后面的插入中，索引所占的页面数量就不会继续增加。
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname       | relfilenode    | relpages
    ---------------+-------------+----------
    testtable    |       17601   |      157
    testtable_idx |       17604 |      173
    (2 rows)
    postgres=# SELECT test_insert();
    test_insert
    -------------
               0
    (1 row)
    postgres=# ANALYZE testtable;
    ANALYZE
    #14. 可以看到索引的页面数量确实没有继续增加。
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname      | relfilenode    | relpages
    ---------------+-------------+----------
    testtable    |       17601   |      157
    testtable_idx |       17604   |      173
    (2 rows)
    #15. 重新批量删除数据。
    postgres=# DELETE FROM testtable WHERE i < 30000;
    DELETE 19996
    #16. 从后面的查询可以看出，在执行VACUUM FULL命令之后，测试表和索引所占用的页面数量
    #      确实降低了，说明它们占用的物理空间已经缩小了。
    postgres=# VACUUM FULL testtable;
    VACUUM
    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
        relname      | relfilenode     | relpages
    ---------------+-------------+----------
    testtable    |       17602 |      118
    testtable_idx |       17605 |       68
    (2 rows)

四、定期重建索引：

　　在PostgreSQL中，为数据更新频繁的数据表定期重建索引(REINDEX INDEX)是非常有必要的。对于B-Tree索引，只有那些已经完全清空的索引页才会得到重复使用，对于那些仅部分空间可用的索引页将不会得到重用，如果一个页面中大多数索引键值都被删除，只留下很少的一部分，那么该页将不会被释放并重用。在这种极端的情况下，由于每个索引页面的利用率极低，一旦数据量显著增加，将会导致索引文件变得极为庞大，不仅降低了查询效率，而且还存在整个磁盘空间被完全填满的危险。
　　对于重建后的索引还存在另外一个性能上的优势，因为在新建立的索引上，逻辑上相互连接的页面在物理上往往也是连在一起的，这样可以提高磁盘页面被连续读取的几率，从而提高整个操作的IO效率。见如下示例：
    #1. 此时已经在该表中插入了大约6万条数据，下面的SQL语句将查询该索引所占用的磁盘空间。
    postgres=# SELECT relname, pg_relation_size(oid)/1024 || 'K' AS size FROM pg_class WHERE relkind='i' AND relname = 'testtable_idx';
       relname   | size
   ----------------+------
   testtable_idx | 1240K
   (1 row)
   #2. 删除数据表中大多数的数据。
   postgres=# DELETE FROM testtable WHERE i > 20000;
   DELETE 50006
   #3. 分析一下该表，以便于后面的SQL语句继续查看该索引占用的空间。
   postgres=# ANALYZE testtable;
   ANALYZE
   #4. 从该查询结果可以看出，该索引所占用的空间并未减少，而是和之前的完全一样。
   postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;
   size
   ------
   1240K
   (1 row)
    #5. 重建索引。
   postgres=# REINDEX INDEX testtable_idx;
   REINDEX
   #6. 查看重建后的索引实际占用的空间，从结果中可以看出索引的尺寸已经减少。
   postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;
   size
   ------
   368K
   (1 row)
   #7. 最后一点需要记住的是，在索引重建后一定要分析数据表。
   postgres=# ANALYZE testtable;
   ANALYZE

五、观察磁盘使用情况

   1. 查看数据表所占用的磁盘页面数量。
   #relpages只能被VACUUM、ANALYZE和几个DDL命令更新，如CREATE INDEX。通常一个页面的长度为8K字节。
   postgres=# SELECT relfilenode, relpages FROM pg_class WHERE relname = 'testtable';
   relfilenode | relpages
   -------------+----------
           16412 |       79
   (1 row)

   2. 查看指定数据表的索引名称和索引占用的磁盘页面数量。
   postgres=# SELECT c2.relname, c2.relpages FROM pg_class c, pg_class c2, pg_index i
        WHERE c.relname = 'testtable' AND c.oid = i.indrelid AND c2.oid = i.indexrelid
        ORDER BY c2.relname;
        relname    | relpages
   ---------------+----------
   testtable_idx |       46
   (1 row)

PostgreSQL学习手册(十三) 系统表

一、pg_class:

该系统表记录了数据表、索引(仍然需要参阅pg_index)、序列、视图、复合类型和一些特殊关系类型的元数据。注意：不是所有字段对所有对象类型都有意义。

名字	类型	引用	描述
relname	name		数据类型名字。
relnamespace	oid	pg_namespace.oid	包含这个对象的名字空间(模式)的OI。
reltype	oid	pg_type.oid	对应这个表的行类型的数据类型。
relowner	oid	pg_authid.oid	对象的所有者。
relam	oid	pg_am.oid	对于索引对象，表示该索引的类型(B-tree，hash)。
relfilenode	oid		对象存储在磁盘上的文件名，如果没有则为0。
reltablespace	oid	pg_tablespace.oid	对象所在的表空间。如果为零，则表示使用该数据库的缺省表空间。(如果对象在磁盘上没有文件，这个字段就没有什么意义)
relpages	int4		该数据表或索引所占用的磁盘页面数量，查询规划器会借助该值选择最优路径。
reltuples	float4		表中行的数量，该值只是被规划器使用的一个估计值。
reltoastrelid	oid	pg_class.oid	与此表关联的TOAST表的OID，如果没有为0。TOAST表在一个从属表里"离线"存储大字段。
reltoastidxid	oid	pg_class.oid	如果是TOAST表，该字段为它索引的OID，如果不是TOAST表则为0。
relhasindex	bool		如果这是一个数据表而且至少有(或者最近有过)一个索引，则为真。它是由CREATE INDEX设置的，但DROP INDEX不会立即将它清除。如果VACUUM发现一个表没有索引，那么它清理 relhasindex。
relisshared	bool		如果该表在整个集群中由所有数据库共享，则为真。
relkind	char		r = 普通表，i = 索引，S = 序列，v = 视图， c = 复合类型，s = 特殊，t = TOAST表
relnatts	int2		数据表中用户字段的数量(除了系统字段以外，如oid)。在pg_attribute里肯定有相同数目的数据行。见pg_attribute.attnum.
relchecks	int2		表中检查约束的数量，参阅pg_constraint表。
reltriggers	int2		表中触发器的数量；参阅pg_trigger表。
relhasoids	bool		如果我们为对象中的每行都生成一个OID，则为真。
relhaspkey	bool		如果该表存在主键，则为真。
relhasrules	bool		如表有规则就为真；参阅pg_rewrite表。
relhassubclass	bool		如果该表有子表，则为真。
relacl	aclitem[]		访问权限。

    见如下应用示例：
    #查看指定表对象testtable的模式
   postgres=# SELECT relname,relnamespace,nspname FROM pg_class c,pg_namespace n WHERE relname = 'testtable' AND relnamespace = n.oid;
      relname | relnamespace | nspname
   -------------+--------------+---------
   testtable |         2200    | public
   (1 row)
    #查看指定表对象testtable的owner(即role)。
   postgres=# select relname,rolname from pg_class c,pg_authid au where relname = 'testtable' and relowner = au.oid;
      relname | rolname
   -------------+----------
   testtable | postgres
   (1 row)

二、pg_attribute:

该系统表存储所有表(包括系统表，如pg_class)的字段信息。数据库中的每个表的每个字段在pg_attribute表中都有一行记录。

名字	类型	引用	描述
attrelid	oid	pg_class.oid	此字段所属的表。
attname	name		字段名。
atttypid	oid	pg_type.oid	字段的数据类型。
attstattarget	int4		attstattarget控制ANALYZE为这个字段设置的统计细节的级别。零值表示不收集统计信息，负数表示使用系统缺省的统计对象。正数值的确切信息是和数据类型相关的。
attlen	int2		该字段所属类型的长度。(pg_type.typlen的拷贝)
attnum	int2		字段的编号，普通字段是从1开始计数的。系统字段，如oid，是任意的负数。
attndims	int4		如果该字段是数组，该值表示数组的维数，否则是0。
attcacheoff	int4		在磁盘上总是-1，但是如果装载入内存中的行描述器中，它可能会被更新为缓冲在行中字段的偏移量。
atttypmod	int4		表示数据表在创建时提供的类型相关的数据(比如，varchar字段的最大长度)。其值对那些不需要atttypmod的类型而言通常为-1。
attbyval	bool		pg_type.typbyval字段值的拷贝。
attstorage	char		pg_type.typstorage字段值的拷贝。
attalign	char		pg_type.typalign字段值的拷贝。
attnotnull	bool		如果该字段带有非空约束，则为真，否则为假。
atthasdef	bool		该字段是否存在缺省值，此时它对应pg_attrdef表里实际定义此值的记录。
attisdropped	bool		该字段是否已经被删除。如果被删除，该字段在物理上仍然存在表中，但会被分析器忽略，因此不能再通过SQL访问。
attislocal	bool		该字段是否局部定义在对象中的。
attinhcount	int4		该字段所拥有的直接祖先的个数。如果一个字段的祖先个数非零，那么它就不能被删除或重命名。

三、pg_attrdef:

该系统表主要存储字段缺省值，字段中的主要信息存放在pg_attribute系统表中。注意：只有明确声明了缺省值的字段在该表中才会有记录。

名字	类型	引用	描述
adrelid	oid	pg_class.oid	这个字段所属的表
adnum	int2	pg_attribute.attnum	字段编号，其规则等同于pg_attribute.attnum
adbin	text		字段缺省值的内部表现形式。
adsrc	text		缺省值的人可读的表现形式。

    见如下应用示例：
   #查看指定表有哪些字段存在缺省值，同时显示出字段名和缺省值的定义方式
   postgres=# CREATE TABLE testtable2 (i integer DEFAULT 100);
   CREATE TABLE
   postgres=# SELECT c.relname, a.attname, ad.adnum, ad.adsrc FROM pg_class c, pg_attribute a, pg_attrdef ad WHERE relname = 'testtable2' AND ad.adrelid = c.oid AND adnum = a.attnum AND attrelid = c.oid;
      relname | attname | adnum | adsrc
   -------------+----------+---------+-------
   testtable2 | i        |         1 | 100
   (1 row)

四、pg_authid:

该系统表存储有关数据库认证的角色信息，在PostgreSQL中角色可以表现为用户和组两种形式。对于用户而言只是设置了rolcanlogin标志的角色。由于该表包含口令数据，所以它不是公共可读的。PostgreSQL中提供了另外一个建立在该表之上的系统视图pg_roles，该视图将口令字段填成空白。

名字	类型	引用	描述
rolname	name		角色名称。
rolsuper	bool		角色是否拥有超级用户权限。
rolcreaterole	bool		角色是否可以创建其它角色。
rolcreatedb	bool		角色是否可以创建数据库。
rolcatupdate	bool		角色是否可以直接更新系统表(如果该设置为假，即使超级用户也不能更新系统表)。
rolcanlogin	bool		角色是否可以登录，换句话说，这个角色是否可以给予会话认证标识符。
rolpassword	text		口令(可能是加密的)；如果没有则为NULL。
rolvaliduntil	timestamptz		口令失效时间(只用于口令认证)；如果没有失效期，则为NULL。
rolconfig	text[]		运行时配置变量的会话缺省。

    见如下应用示例：
   #从输出结果可以看出口令字段已经被加密。
   postgres=# SELECT rolname,rolpassword FROM pg_authid;
   rolname |             rolpassword
   -----------+-------------------------------------
   postgres | md5a3556571e93b0d20722ba62be61e8c2d

五、pg_auth_members:

该系统表存储角色之间的成员关系。

名字	类型	引用	描述
roleid	oid	pg_authid.oid	组角色的ID。
member	oid	pg_authid.oid	属于组角色roleid的成员角色的ID。
grantor	oid	pg_authid.oid	赋予此成员关系的角色的ID。
admin_option	bool		如果具有把其它成员角色加入组角色的权限，则为真。

    见如下应用示例：
   #1. 先查看角色成员表中有哪些角色之间的隶属关系，在当前结果集中只有一个成员角色隶属于一个组角色，
   #    如果有多个成员角色隶属于同一个组角色，这样将会有多条记录。
   postgres=# SELECT * FROM pg_auth_members ;
   roleid | member | grantor | admin_option
   --------+--------+---------+--------------
      16446 | 16445 |      10 | f
   (1 row)
   #2. 查看组角色的名字。
   postgres=# SELECT rolname FROM pg_authid a,pg_auth_members am WHERE a.oid = am.roleid;
   rolname
   ---------
   mygroup
   (1 row)
   #3. 查看成员角色的名字。
   #4. 如果需要用一个结果集获取角色之间的隶属关系，可以将这两个结果集作为子查询后再进行关联。
   postgres=# SELECT rolname FROM pg_authid a,pg_auth_members am WHERE a.oid = am.member;
   rolname
   ---------
   myuser
   (1 row)

六、pg_constraint:

该系统表存储PostgreSQL中表对象的检查约束、主键、唯一约束和外键约束。

名字	类型	引用	描述
conname	name		约束名字(不一定是唯一的)。
connamespace	oid	pg_namespace.oid	包含这个约束的名字空间(模式)的OID。
contype	char		c = 检查约束， f = 外键约束， p = 主键约束， u = 唯一约束
condeferrable	bool		该约束是否可以推迟。
condeferred	bool		缺省时这个约束是否是推迟的？
conrelid	oid	pg_class.oid	该约束所在的表，如果不是表约束则为0。
contypid	oid	pg_type.oid	该约束所在的域，如果不是域约束则为0。
confrelid	oid	pg_class.oid	如果为外键，则指向参照的表，否则为0。
confupdtype	char		外键更新动作代码。
confdeltype	char		外键删除动作代码。
confmatchtype	char		外键匹配类型。
conkey	int2[]	pg_attribute.attnum	如果是表约束，则是约束控制的字段列表。
confkey	int2[]	pg_attribute.attnum	如果是外键，则是参照字段的列表。
conbin	text		如果是检查约束，则表示表达式的内部形式。
consrc	text		如果是检查约束，则是表达式的人可读的形式。

七、pg_tablespace:

该系统表存储表空间的信息。注意：表可以放在特定的表空间里，以帮助管理磁盘布局和解决IO瓶颈。

名字	类型	引用	描述
spcname	name		表空间名称。
spcowner	oid	pg_authid.oid	表空间的所有者，通常是创建它的角色。
spclocation	text		表空间的位置(目录路径)。
spcacl	aclitem[]		访问权限。

    见如下应用示例：
   #1. 创建表空间。
   postgres=# CREATE TABLESPACE my_tablespace LOCATION '/opt/PostgreSQL/9.1/mydata';
   CREATE TABLESPACE
   #2. 将新建表空间的CREATE权限赋予public。
   postgres=# GRANT CREATE ON TABLESPACE my_tablespace TO public;
   GRANT
   #3. 查看系统内用户自定义表空间的名字、文件位置和创建它的角色名称。
   #4. 系统创建时自动创建的两个表空间(pg_default和pg_global)的文件位置为空(不是NULL)。
   postgres=# SELECT spcname,rolname,spclocation FROM pg_tablespace ts,pg_authid a WHERE ts.spcowner = a.oid AND spclocation <> '';
        spcname    | rolname |        spclocation
   ---------------+----------+----------------------------
   my_tablespace | postgres | /opt/PostgreSQL/9.1/mydata
   (1 row)

八、pg_namespace:

该系统表存储名字空间(模式)。

名字	类型	引用	描述
nspname	name		名字空间(模式)的名称。
nspowner	oid	pg_authid.oid	名字空间(模式)的所有者
nspacl	aclitem[]		访问权限。

    见如下应用示例：
   #查看当前数据库public模式的创建者的名称。
   postgres=# SELECT nspname,rolname FROM pg_namespace n, pg_authid a WHERE nspname = 'public' AND nspowner = a.oid;
   nspname | rolname
   ----------+----------
   public    | postgres
   (1 row)

九、pg_database:

该系统表存储数据库的信息。和大多数系统表不同的是，在一个集群里该表是所有数据库共享的，即每个集群只有一份pg_database拷贝，而不是每个数据库一份。

名字	类型	引用	描述
datname	name		数据库名称。
datdba	oid	pg_authid.oid	数据库所有者，通常为创建该数据库的角色。
encoding	int4		数据库的字符编码方式。
datistemplate	bool		如果为真，此数据库可以用于CREATE DATABASE TEMPLATE子句，把新数据库创建为此数据库的克隆。
datallowconn	bool		如果为假，则没有人可以联接到这个数据库。
datlastsysoid	oid		数据库里最后一个系统OID，此值对pg_dump特别有用。
datvacuumxid	xid
datfrozenxid	xid
dattablespace	text	pg_tablespace.oid	该数据库的缺省表空间。在这个数据库里，所有pg_class.reltablespace为零的表都将保存在这个表空间里，特别要指出的是，所有非共享的系统表也都存放在这里。
datconfig	text[]		运行时配置变量的会话缺省值。
datacl	aclitem[]		访问权限。

十、pg_index:

该系统表存储关于索引的一部分信息。其它的信息大多数存储在pg_class。

名字	类型	引用	描述
indexrelid	oid	pg_class.oid	该索引在pg_class里的记录的OID。
indrelid	oid	pg_class.oid	索引所在表在pg_class里的记录的OID。
indnatts	int2		索引中的字段数量(拷贝的pg_class.relnatts)。
indisunique	bool		如果为真，该索引是唯一索引。
indisprimary	bool		如果为真，该索引为该表的主键。
indisclustered	bool		如果为真，那么该表在这个索引上建了簇。
indkey	int2vector	pg_attribute.attnum	该数组的元素数量为indnatts，数组元素值表示建立这个索引时所依赖的字段编号，如1 3，表示第一个字段和第三个字段构成这个索引的键值。如果为0，则表示是表达式索引，而不是基于简单字段的索引。
indclass	oidvector	pg_opclass.oid	对于构成索引键值的每个字段，这个字段都包含一个指向所使用的操作符表的OID。
indexprs	text		表达式树用于那些非简单字段引用的索引属性。它是一个列表，在indkey里面的每个零条目一个元素。如果所有索引属性都是简单的引用，则为空。
indpred	text		部分索引断言的表达式树。如果不是部分索引，则是空字串。

    见如下应用示例：
    #查看该索引所在表的名称，以及构成该索引的键值数量和具体键值的字段编号。
   postgres=# SELECT indnatts,indkey,relname FROM pg_index i, pg_class c WHERE c.relname = 'testtable2' AND indrelid = c.oid;
   indnatts | indkey | relname
   ----------+--------+------------
            2 | 1 3    | testtable2
   (1 row)
   #查看指定表包含的索引，同时列出索引的名称。
   postgres=# SELECT t.relname AS table_name, c.relname AS index_name FROM (SELECT relname,indexrelid FROM pg_index i, pg_class c WHERE c.relname = 'testtable2' AND indrelid = c.oid) t, pg_index i,pg_class c WHERE t.indexrelid = i.indexrelid AND i.indexrelid = c.oid;
   table_name |   index_name
   ------------+----------------
   testtable2 | testtable2_idx
   (1 row)

PostgreSQL学习手册(十四) 系统视图

一、pg_tables:

该视图提供了对有关数据库中每个表的有用信息地访问。

名字	类型	引用	描述
schemaname	name	pg_namespace.nspname	包含表的模式名字。
tablename	name	pg_class.relname	表的名字。
tableowner	name	pg_authid.rolname	表的所有者的名字。
tablespace	name	pg_tablespace.spcname	包含表的表空间名字(如果是数据库缺省，则为 NULL)。
hasindexes	bool	pg_class.relhasindex	如果表拥有(或者最近拥有)任何索引，则为真。
hasrules	bool	pg_class.relhasrules	如果表存在规则，则为真。
hastriggers	bool	pg_class.reltriggers	如果表有触发器，则为真。

二、pg_indexes:

该视图提供对数据库中每个索引的有用信息的访问。

名字	类型	引用	描述
schemaname	name	pg_namespace.nspname	包含表和索引的模式的名字。
tablename	name	pg_class.relname	索引所在表的名字。
indexname	name	pg_class.relname	索引的名字。
tablespace	name	pg_tablespace.spcname	包含索引的表空间名字(如果是数据库缺省，则为NULL)。
indexdef	text		索引定义(一个重建的创建命令)。

三、pg_views:

该视图提供了对数据库里每个视图的有用信息的访问途径。

名字	类型	引用	描述
schemaname	name	pg_namespace.nspname	包含此视图的模式名字。
viewname	name	pg_class.relname	视图的名字。
viewowner	name	pg_authid.rolname	视图的所有者的名字。
definition	text		视图定义(一个重建的SELECT查询)。

四、pg_user:

该视图提供了对数据库用户的相关信息的访问。这个视图只是pg_shadow表的公众可读的部分的视图化，但是不包含口令字段。

名字	类型	引用	描述
usename	name		用户名。
usesysid	int4		用户ID(用于引用这个用户的任意数字)。
usecreatedb	bool		用户是否可以创建数据库。
usesuper	bool		用户是否是一个超级用户。
usecatupd	bool		用户是否可以更新系统表。(即使超级用户也不能这么干，除非这个字段为真。)
passwd	text		口令(可能加密了)。
valuntil	abstime		口令失效的时间(只用于口令认证)。
useconfig	text[]		运行时配置参数的会话缺省。

五、pg_roles:

该视图提供访问数据库角色有关信息的接口。这个视图只是pg_authid表的公开可读部分的视图化，同时把口令字段用空白填充。

名字	类型	引用	描述
rolname	name		角色名。
rolsuper	bool		是否有超级用户权限的角色。
rolcreaterole	bool		是否可以创建更多角色的角色。
rolcreatedb	bool		是否可以创建数据库的角色。
rolcatupdate	bool		是否可以直接更新系统表的角色。
rolcanlogin	bool		如果为真，表示是可以登录的角色。
rolpassword	text		不是口令(总是 ********)。
rolvaliduntil	timestamptz		口令失效日期(只用于口令认证)；如果没有失效期，为NULL。
rolconfig	text[]		运行时配置变量的会话缺省。

六、pg_rules:

该视图提供对查询重写规则的有用信息访问的接口。

名字	类型	引用	描述
schemaname	name	pg_namespace.nspname	包含表的模式的名字。
tablename	name	pg_class.relname	规则施加影响的表的名字。
rulename	name	pg_rewrite.rulename	规则的名字。
definition	text		规则定义(一个重新构造的创建命令)。

七、pg_settings:

该视图提供了对服务器运行时参数的访问。它实际上是SHOW和SET命令的另外一种方式。它还提供一些用SHOW不能直接获取的参数的访问，比如最大和最小值。

名字	类型	引用	描述
name	text		运行时配置参数名。
setting	text		参数的当前值。
category	text		参数的逻辑组。
short_desc	text		参数的一个简短的描述。
extra_desc	text		有关参数的额外的、更详细的信息。
context	text		设置这个参数的值要求的环境。
vartype	text		参数类型(bool、integer、real和string)。
source	text		当前参数值的来源。
min_val	text		该参数允许的最小值(非数字值为NULL)。
max_val	text		该参数允许的最大值(非数字值为NULL)。

我们不能对pg_settings视图进行插入或者删除，只能更新。对pg_settings中的一行进行UPDATE等效于在该命名参数上执行SET命令。这个修改值影响当前会话使用的数值。如果在一个最后退出的事务中发出了UPDATE命令，那么UPDATE命令的效果将在事务回滚之后消失。一旦包围它的事务提交，这个效果将固化，直到会话结束。

PostgreSQL学习手册(十五) 客户端命令

零、口令文件：
   在给出其它PostgreSQL客户端命令之前，我们需要先介绍一下PostgreSQL中的口令文件。之所以在这里提前说明该文件，是因为我们在后面的示例代码中会大量应用该文件，从而保证我们的脚本能够自动化完成。换句话说，如果在客户端命令执行时没有提供该文件，PostgreSQL的所有客户端命令均会被口令输入提示中断。
   在当前用户的HOME目录下，我们需要手工创建文件名为 .pgpass的口令文件，这样就可以在我们连接PostgreSQL服务器时，客户端命令自动读取该文件已获得登录时所需要的口令信息。该文件的格式如下：
   hostname:port:database:username:password
   以上数据是用冒号作为分隔符，总共分为五个字段，分别表示服务器主机名(IP)、服务器监听的端口号、登录访问的数据库名、登录用户名和密码，其中前四个字段都可以使用星号(*)来表示匹配任意值。见如下示例：
   /> cat > .pgpass
   *:5432:postgres:postgres:123456
   CTRL+D
   #.pgpass文件的权限必须为0600，从而防止任何全局或者同组的用户访问，否则这个文件将被忽略。
   /> chmod 0600 .pgpass
   在学习后面的客户端命令之前，我们需要根据自己的应用环境手工创建该文件，以便后面所有的示例代码都会用到该口令文件，这样它们就都可以以批处理的方式自动完成。

一、createdb：
   创建一个新的PostgreSQL数据库。该命令的使用方式如下：
   createdb [option...] [dbname] [description]
   1. 命令行选项列表：

选项	说明
-D(--tablespace=tablespace)	指定数据库的缺省表空间。
-e(--echo)	回显createdb生成的命令并且把它发送到服务器。
-E(--encoding=encoding)	指定用于此数据库的字符编码方式。
-l(--locale=locale)	指定用于此数据库的本地化设置。
-O(--owner=owner)	指定新建数据库的拥有者，如果未指定此选项，该值为当前登录的用户。
-T(--template=template)	指定创建此数据库的模板数据库。
-h(--host=host)	指定PostgreSQL服务器的主机名。
-p(--port=port)	指定服务器的侦听端口，如不指定，则为缺省的5432。
-U(--username=username)	本次操作的登录用户名，如果-O选项没有指定，此数据库的Owner将为该登录用户。
-w(--no-password)	如果当前登录用户没有密码，可以指定该选项直接登录。

    2. 应用示例：
    #1. 以postgres的身份登录。(详情参照上面口令文件的内容)
   /> psql
    #2. 创建表空间。
   postgres=# CREATE TABLESPACE my_tablespace LOCATION '/opt/PostgreSQL/9.1/mydata';
   CREATE TABLESPACE
   #3. 创建新数据库的owner。
   postgres=# CREATE ROLE myuser LOGIN PASSWORD '123456';
   CREATE ROLE
   postgres=# \q
   #4. 创建新数据库，其中本次连接的登录用户为postgres，新数据库的owner为myuser，表空间为my_tablespace，新数据库名为mydatabase。
   /> createdb -U postgres -O myuser -D my_tablespace -e mydatabase
   CREATE DATABASE mydatabase OWNER myuser TABLESPACE my_tablespace;
    #5. 重新登录，通过查询系统表查看该数据库是否创建成功，以及表空间和所有者是否一致。
   /> psql
   postgres=# SELECT datname,rolname,spcname FROM pg_database db, pg_authid au, pg_tablespace ts WHERE datname = 'mydatabase' AND datdba = au.oid AND dattablespace = ts.oid;
      datname   | rolname |    spcname
   ------------+---------+---------------
   mydatabase | myuser | my_tablespace
   (1 row)

二、dropdb：
   删除一个现有PostgreSQL数据库。
   dropdb [option...] dbname
    1. 命令行选项列表：

选项	说明
-e(--echo)	回显dropdb生成的命令并且把它发送到服务器。
-i(--interactive)	在做任何破坏性动作前提示。
-q(--quiet)	不显示响应。
-h(--host=host)	指定PostgreSQL服务器的主机名。
-p(--port=port)	指定服务器的监听端口，如不指定，则为缺省的5432。
-U(--username=username)	本次操作的登录用户名。
-w(--no-password)	如果当前登录用户没有密码，可以指定该选项直接登录。

    2. 应用示例：
   #以postgres的身份连接服务器，删除mydatabase数据库。
   /> dropdb -U postgres -e mydatabase
   DROP DATABASE mydatabase;
   #通过查看系统表验证该数据库是否已经被删除。
   /> psql
   postgres=# SELECT count(*) FROM pg_database WHERE datname = 'mydatabase';
   count
   -------
         0
   (1 row)

三、reindexdb：
   为一个指定的PostgreSQL数据库重建索引。
   reindexdb [connection-option...] [--table | -t table ] [--index | -i index ] [dbname]
   reindexdb [connection-option...] [--all | -a]
   reindexdb [connection-option...] [--system | -s] [dbname]
    1. 命令行选项列表：

选项	说明
-a(-all)	重建整个数据库的索引。
-e(--echo)	回显reindexdb生成的命令并且把它发送到服务器。
-i(--index=index)	仅重建指定的索引。
-q(--quiet)	不显示响应。
-s(--system)	重建数据库系统表的索引。
-t(--table=table)	仅重建指定数据表的索引。
-h(--host=host)	指定PostgreSQL服务器的主机名。
-p(--port=port)	指定服务器的监听端口，如不指定，则为缺省的5432。
-U(--username=username)	本次操作的登录用户名。
-w(--no-password)	如果当前登录用户没有密码，可以指定该选项直接登录。

    2. 应用示例：
    #仅重建数据表testtable上的全部索引。
   /> reindexdb -t testtable -e -U postgres postgres
   REINDEX TABLE testtable;
   #仅重建指定索引testtable_idx
   /> reindexdb -i testtable_idx -e -U postgres postgres
    REINDEX INDEX testtable_idx;
    #重建指定数据库mydatabase的全部索引。
   /> reindexdb mydatabase

四、vacuumdb：
   收集垃圾并且分析一个PostgreSQL数据库。
   vacuumdb [-options] [--full | -f] [--verbose | -v] [--analyze | -z] [-t table [(column [,...])]] [dbname]
   vacuumdb [-options] [--all | -a] [--full | -f] [--verbose | -v] [--analyze | -z]
    1. 命令行选项列表：

选项	说明
-a(--all)	清理所有数据库。
-e(--echo)	回显vacuumdb生成的命令并且把它发送到服务器。
-f(--full)	执行完全清理。
-q(--quiet)	不显示响应。
-t table [(column[,...])]	仅仅清理或分析指定的数据表，字段名只是在与--analyze选项联合使用时才需要声明。
-v(--verbose)	在处理过程中打印详细信息。
-z(--analyze)	计算用于规划器的统计值。
-h(--host=host)	指定PostgreSQL服务器的主机名。
-p(--port=port)	指定服务器的监听端口，如不指定，则为缺省的5432。
-U(--username=username)	本次操作的登录用户名。
-w(--no-password)	如果当前登录用户没有密码，可以指定该选项直接登录。

    2. 应用示例：
    #清理整个数据库mydatabase。
   /> vacuumdb -e mydatabase
   VACUUM;
   #清理并分析postgres数据库中的testtable表。
   /> vacuumdb -e --analyze --table 'testtable' postgres
   VACUUM ANALYZE testtable;
   #清理并分析postgres数据库中的testtable表的i字段。
   /> vacuumdb -e --analyze -t 'testtable(i)' postgres
   VACUUM ANALYZE testtable(i);

五、createuser:
   定义一个新的PostgreSQL用户帐户，需要说明的是只有超级用户或者是带有CREATEROLE权限的用户才可以执行该命令。如果希望创建的是超级用户，那么只能以超级用户的身份执行该命令，换句话说，带有CREATEROLE权限的普通用户无法创建超级用户。该命令的使用方式如下：
    createuser [option...] [username]
    1. 命令行选项列表：

选项	说明
-c number	设置新创建用户的最大连接数，缺省为没有限制。
-d(--createdb)	允许该新建用户创建数据库。
-D(--no-createdb)	禁止该新建用户创建数据库。
-e(--echo)	回显createuser生成的命令并且把它发送到服务器。
-E(--encrypted)	对保存在数据库里的用户口令加密。如果没有声明，则使用缺省值。
-i(--inherit)	新创建的角色将自动继承它的组角色的权限。
-I(--no-inherit)	新创建的角色不会自动继承它的组角色的权限。
-l(--login)	新角色将被授予登录权限，该选项为缺省选项。
-L(--no-login)	新角色没有被授予登录权限。
-N(--unencrypted)	不对保存在数据库里的用户口令加密。如果没有声明，则使用缺省值。
-P(--pwprompt)	如果给出该选项，在创建用户时将提示设置口令。
-r(--createrole)	新角色被授予创建角色的权限。
-R(--no-createrole)	新角色没有被授予创建角色的权限。
-s(--superuser)	新角色为超级用户。
-S(--no-superuser)	新角色不是超级用户。
-h(--host=host)	指定PostgreSQL服务器的主机名。
-p(--port=port)	指定服务器的监听端口，如不指定，则为缺省的5432。
-U(--username=username)	本次操作的登录用户名。
-w(--no-password)	如果当前登录用户没有密码，可以指定该选项直接登录。

    2. 应用示例：
    # 对于有些没有缺省设置的选项，如-(d/D)、-(s/S)和-(r/R)，如果在命令行中没有直接指定，那么在执行该命令是将会给出提示信息。
   # 需要注意的是该提示将会挂起自动化脚本，直到输入后命令才会继续执行。
   /> createuser -U postgres myuser
   Shall the new role be a superuser? (y/n) n
   Shall the new role be allowed to create databases? (y/n) y
   Shall the new role be allowed to create more new roles? (y/n) n
   CREATE ROLE myuser NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN;
    # 通过psql登录后查看系统视图，以验证该用户是否成功创建，以及新角色的权限是否正确。
   /> psql
   postgres=# SELECT rolname,rolsuper,rolinherit,rolcreaterole,rolcreatedb,rolcanlogin FROM pg_roles WHERE rolname = 'myuser';
   rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin
   ---------+----------+------------+---------------+-------------+-------------
   myuser | f         | t         | f            | t             | t
   (1 row)
    # 为了保证自动化脚本不会被该命令的提示挂起，我们需要在执行该命令时指定所有没有缺省值的选项。
   /> createuser -U postgres -e -S -D -R myuser2
   CREATE ROLE myuser2 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
   # 我们可以在创建用户时即刻指定该用户的密码，该操作由-P选项完成，然而这样的用法一定会挂起自动化脚本，
   # 因此我们可以采用一种折中的办法，即在创建用户时不指定密码，在自动化脚本执行成功后再手工改用户的密码。
   /> createuser -P -s -e myuser3
   Enter password for new role:
   Enter it again:
   CREATE ROLE myuser3 PASSWORD 'md5fe54c4f3129f2a766f53e4f4c9d2a698' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

六、dropuser:
   删除一个PostgreSQL用户帐户，需要说明的是只有超级用户或带有CREATEROLE权限的用户可以执行该命令，如果要删除超级用户，只能通过超级用户的身份执行该命令。该命令的使用方式如下：
    dropuser [option...] [username]
    1. 命令行选项列表：

选项	说明
-e(--echo)	回显dropuser生成的命令并且把它发送到服务器。
-i(--interactive)	在做任何破坏性动作前提示。
-h(--host=host)	指定PostgreSQL服务器的主机名。
-p(--port=port)	指定服务器的监听端口，如不指定，则为缺省的5432。
-U(--username=username)	本次操作的登录用户名。
-w(--no-password)	如果当前登录用户没有密码，可以指定该选项直接登录。

    2. 应用示例：
    # 直接删除指定用户。
   /> dropuser -e myuser3
   DROP ROLE myuser3;
    # 在删除指定用户时，该命令会给出提示信息，以免误操作。
   /> dropuser -e -i myuser2
   Role "myuser2" will be permanently removed.
   Are you sure? (y/n) y
   DROP ROLE myuser2;

七、pg_dump:
    pg_dump是一个用于备份PostgreSQL数据库的工具。它甚至可以在数据库正在并发使用时进行完整一致的备份，而不会阻塞其它用户对数据库的访问。该工具生成的转储格式可以分为两种，脚本和归档文件。其中脚本格式是包含许多SQL命令的纯文本格式，这些SQL命令可以用于重建该数据库并将之恢复到生成此脚本时的状态，该操作需要使用psql来完成。至于归档格式，如果需要重建数据库就必须和pg_restore工具一起使用。在重建过程中，可以对恢复的对象进行选择，甚至可以在恢复之前对需要恢复的条目进行重新排序。该命令的使用方式如下：
    pg_dump [option...] [dbname]
   1. 命令行选项列表：

选项	说明
-a(--data-only)	只输出数据，不输出模式(数据对象的定义)。这个选项只是对纯文本格式有意义。对于归档格式，你可以在调用 pg_restore时指定选项。
-b(--blobs)	在dump中包含大对象。
-c(--clean)	在输出创建数据库对象的SQL命令之前，先输出删除该数据库对象的SQL命令。这个选项只是对纯文本格式有意义。对于归档格式，你可以在调用 pg_restore时指定选项。
-C(--create)	先输出创建数据库的命令，之后再重新连接新创建的数据库。对于此种格式的脚本，在运行之前是和哪个数据库进行连接就不这么重要了。这个选项只是对纯文本格式有意义。对于归档格式，你可以在调用pg_restore时指定选项。
-E encoding	以指定的字符集创建该dump文件。
-f file	输出到指定文件，如果没有该选项，则输出到标准输出。
-F format	p(plain): 纯文本格式的SQL脚本文件(缺省)。c(custom): 输出适合于pg_restore的自定义归档格式。这是最灵活的格式，它允许对装载的数据和对象定义进行重新排列。这个格式缺省的时候是压缩的。t(tar): 输出适合于 pg_restore的tar归档文件。使用这个归档允许在恢复数据库时重新排序和/或把数据库对象排除在外。同时也可以在恢复的时候限制对哪些数据进行恢复。
-n schema	只转储schema的内容。如果没有声明该选项，目标数据库中的所有非系统模式都会被转储。该选项也可以被多次指定，以指定不同pattern的模式。
-N schema	不转储匹配schema的内容，其他规则和-n一致。
-o(--oids)	作为数据的一部分，为每个表都输出对象标识(OID)。
-O(--no-owner)	不输出设置对象所有权的SQL命令。
-s(--schema-only)	只输出对象定义(模式)，不输出数据。
-S username	指定关闭触发器时需要用到的超级用户名。它只有在使用--disable-triggers的时候才有关系。
-t table	只输出表的数据。很可能在不同模式里面有多个同名表，如果这样，那么所有匹配的表都将被转储。通过多次指定该参数，可以一次转储多张表。这里还可以指定和psql一样的pattern，以便匹配更多的表。(关于pattern，基本的使用方式是可以将它视为unix的通配符，即表示任意字符，?表示任意单个字符，.(dot)表示schema和object 之间的分隔符，如a.b*，表示以a开头的schema和以b开头的数据库对象。如果没有.(dot)，将只是表示数据库对象。这里也可以使用基本的正则表达式，如[0-9]表示数字。)
-T table	排除指定的表，其他规则和-t选项一致。
-x(--no-privileges)	不导出访问权限信息(grant/revoke命令)。
-Z 0..9	声明在那些支持压缩的格式中使用的压缩级别。 (目前只有自定义格式支持压缩)
--column-inserts	导出数据用insert into table_name(columns_list) values(values_list)命令表示，这样的操作相对其它操作而言是比较慢的，但是在特殊情况下，如数据表字段的位置有可能发生变化或有新的字段插入到原有字段列表的中间等。由于columns_list被明确指定，因此在导入时不会出现数据被导入到错误字段的问题。
--inserts	导出的数据用insert命令表示，而不是copy命令。即便使用insert要比copy慢一些，但是对于今后导入到其他非PostgreSQL的数据库是比较有意义的。
--no-tablespaces	不输出设置表空间的命令，如果带有这个选项，所有的对象都将恢复到执行pg_restore时的缺省表空间中。
--no-unlogged-table-data	对于不计入日志(unlogged)的数据表，不会导出它的数据，至于是否导出其Schema信息，需要依赖其他的选项而定。
-h(--host=host)	指定PostgreSQL服务器的主机名。
-p(--port=port)	指定服务器的侦听端口，如不指定，则为缺省的5432。
-U(--username=username)	本次操作的登录用户名，如果-O选项没有指定，此数据库的Owner将为该登录用户。
-w(--no-password)	如果当前登录用户没有密码，可以指定该选项直接登录。

    2. 应用示例：
    # -h: PostgreSQL服务器的主机为192.168.149.137。
   # -U: 登录用户为postgres。
   # -t: 导出表名以test开头的数据表，如testtable。
   # -a: 仅仅导出数据，不导出对象的schema信息。
   # -f: 输出文件是当前目录下的my_dump.sql
   # mydatabase是此次操作的目标数据库。
   /> pg_dump -h 192.168.149.137 -U postgres -t test* -a -f ./my_dump.sql mydatabase
    #-c: 先输出删除数据库对象的SQL命令，在输出创建数据库对象的SQL命令，这对于部署干净的初始系统或是搭建测试环境都非常方便。
   /> pg_dump -h 192.168.220.136 -U postgres -c -f ./my_dump.sql mydatabase
   #导出mydatabase数据库的信息。在通过psql命令导入时可以重新指定数据库，如：/> psql -d newdb -f my_dump.sql
   /> pg_dump -h 192.168.220.136 -U postgres -f ./my_dump.sql mydatabase
   #导出模式为my_schema和以test开头的数据库对象名，但是不包括my_schema.employee_log对象。
   /> pg_dump -t 'my_schema.test*' -T my_schema.employee_log mydatabase > my_dump.sql
   #导出east和west模式下的所有数据库对象。下面两个命令是等同的，只是后者使用了正则。
   /> pg_dump -n 'east' -n 'west' mydatabase -f my_dump.sql
   /> pg_dump -n '(east|west)' mydatabase -f my_dump.sql

八、pg_restore:

    pg_restore用于恢复pg_dump导出的任何非纯文本格式的文件，它将数据库重建成保存它时的状态。对于归档格式的文件，pg_restore可以进行有选择的恢复，甚至也可以在恢复前重新排列数据的顺序。
    pg_restore可以在两种模式下操作。如果指定数据库，归档将直接恢复到该数据库。否则，必须先手工创建数据库，之后再通过pg_restore恢复数据到该新建的数据库中。该命令的使用方式如下：
    pg_restore [option...] [filename]
    1. 命令行选项列表：

选项	说明
filename	指定要恢复的备份文件，如果没有声明，则使用标准输入。
-a(--data-only)	只恢复数据，而不恢复表模式(数据对象定义)。
-c(--clean)	创建数据库对象前先清理(删除)它们。
-C(--create)	在恢复数据库之前先创建它。(在使用该选项时，数据库名需要由-d选项指定，该选项只是执行最基本的CREATE DATABASE命令。需要说明的是，归档文件中所有的数据都将恢复到归档文件里指定的数据库中)。
-d dbname	与数据库dbname建立连接并且直接恢复数据到该数据库中。
-e(--exit-on-error)	如果在向数据库发送SQL命令的时候遇到错误，则退出。缺省是继续执行并且在恢复结束时显示一个错误计数。
-F format	指定备份文件的格式。由于pg_restore会自动判断格式，因此指定格式并不是必须的。如果指定，它可以是以下格式之一：t(tar): 使用该格式允许在恢复数据库时重新排序和/或把表模式信息排除出去，同时还可能在恢复时限制装载的数据。 c(custom):该格式是来自pg_dump的自定义格式。这是最灵活的格式，因为它允许重新对数据排序，也允许重载表模式信息，缺省情况下这个格式是压缩的。
-I index	只恢复指定的索引。
-l(--list)	列出备份中的内容，这个操作的输出可以作为-L选项的输入。注意，如果过滤选项-n或-t连同-l选项一起使用的话，他们也将限制列出的条目。
-L list-file	仅恢复在list-file中列出的条目，恢复的顺序为各个条目在该文件中出现的顺序，你也可以手工编辑该文件，并重新排列这些条目的位置，之后再进行恢复操作，其中以分号(;)开头的行为注释行，注释行不会被导入。
-n namespace	仅恢复指定模式(Schema)的数据库对象。该选项可以和-t选项联合使用，以恢复指定的数据对象。
-O(--no-owner)	不输出设置对象所有权的SQL命令。
-P function-name(argtype [, ...])	只恢复指定的命名函数。该名称应该和转储的内容列表中的完全一致。
-s(--schema-only)	只恢复表结构(数据定义)。不恢复数据，序列值将重置。
-S username	指定关闭触发器时需要用到的超级用户名。它只有在使用--disable-triggers的时候才有关系。
-t table	只恢复指定表的Schema和/或数据，该选项也可以连同-n选项指定模式。
-x(--no-privileges)	不恢复访问权限信息(grant/revoke命令)。
-1(--single-transaction)	在一个单一事物中执行恢复命令。这个选项隐含包括了--exit-on-error选项。
--no-tablespaces	不输出设置表空间的命令，如果带有这个选项，所有的对象都将恢复到执行pg_restore时的缺省表空间中。
--no-data-for-failed-tables	缺省情况下，即使创建表失败了，如该表已经存在，数据加载的操作也不会停止，这样的结果就是很容易导致大量的重复数据被插入到该表中。如果带有该选项，那么一旦出现针对该表的任何错误，对该数据表的加载将被忽略。
--role=rolename	以指定的角色名执行restore的操作。通常而言，如果连接角色没有足够的权限用于本次恢复操作，那么就可以利用该选项在建立连接之后再切换到有足够权限的角色。
-h(--host=host)	指定PostgreSQL服务器的主机名。
-p(--port=port)	指定服务器的侦听端口，如不指定，则为缺省的5432。
-U(--username=username)	本次操作的登录用户名，如果-O选项没有指定，此数据库的Owner将为该登录用户。
-w(--no-password)	如果当前登录用户没有密码，可以指定该选项直接登录。

    2. 应用示例：
   #先通过createdb命令，以myuser用户的身份登录，创建带恢复的数据newdb
   /> createdb -U myuser newdb
   #用pg_restore命令的-l选项导出my_dump.dat备份文件中导出数据库对象的明细列表。
   /> pg_restore -l my_dump.dat > db.list
   /> cat db.list
   2; 145344 TABLE species postgres
   4; 145359 TABLE nt_header postgres
   6; 145402 TABLE species_records postgres
   8; 145416 TABLE ss_old postgres
   10; 145433 TABLE map_resolutions postgres
   #将以上列表文件中的内容修改为以下形式。
   #主要的修改是注释掉编号为2、4和8的三个数据库对象，同时编号10的对象放到该文件的头部，这样在基于该列表
   #文件导入时，2、4和8等三个对象将不会被导入，在恢复的过程中将先导入编号为10的对象的数据，再导入对象6的数据。
   /> cat new_db.list
   10; 145433 TABLE map_resolutions postgres
   ;2; 145344 TABLE species postgres
   ;4; 145359 TABLE nt_header postgres
   6; 145402 TABLE species_records postgres
   ;8; 145416 TABLE ss_old postgres
   #恢复时指定的数据库是newdb，导入哪些数据库对象和导入顺序将会按照new_db.list文件中提示的规则导入。
   /> pg_restore -d newdb -L new_db.list my_dump.dat

九、psql:
   PostgreSQL的交互终端，等同于Oracle中的sqlplus。
    1. 常用命令行选项列表：

选项	说明
-c command	指定psql执行一条SQL命令command(用双引号括起)，执行后退出。
-d dbname	待连接的数据库名称。
-E	回显由\d和其他反斜杠命令生成的实际查询。
-f filename	使用filename文件中的数据作为命令输入源，而不是交互式读入查询。在处理完文件后，psql结束并退出。
-h hostname	声明正在运行服务器的主机名
-l	列出所有可用的数据库，然后退出。
-L filename	除了正常的输出源之外，把所有查询记录输出到文件filename。
-o filename	将所有查询重定向输出到文件filename。
-p port	指定PostgreSQL服务器的监听端口。
-q --quiet	让psql安静地执行所处理的任务。缺省时psql将输出打印欢迎和许多其他信息。
-t --tuples-only	关闭打印列名称和结果行计数脚注等信息。
-U username	以用户username代替缺省用户与数据库建立连接。

    2. 命令行选项应用示例：
   #-d: 指定连接的数据库。
   #-U: 指定连接的用户。
   #-c: 后面的SQL语句是本次操作需要执行的命令。
   /> psql -d posgres -U postgres -c "select * from testtable"
   i
   ---
   1
   2
   3
   5
   (4 rows)
   #-t: 没有输出上面输出结果中的字段标题信息和行数统计信息。
   #-q：该选项和-t选项联合使用，非常有利于自动化脚本。如：
   #    select 'copy ' || tablename || ' to ' || tablename || '.sql' from pg_tables
   #由以上sql语句生成的结果集，在重定向到输出文件后，可以作为下一次psql的输入执行。
   /> psql -t -q -c "select * from testtable"
   1
   2
   3
   5
   #-l: 列出当前系统中可用的数据库。
   /> psql -l
                                      List of databases
        Name       | Owner   | Encoding | Collation    |    Ctype        |   Access privileges
   ------------+----------+----------+-------------+-------------+-----------------------
   mydatabase | myuser   | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
   postgres     | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

　　... ...

    (4 rows)
   #-o: 将查询语句的数据结果输出到指定文件。
   /> psql -c "select * from testtable" -o out
   /> cat out
   i
   ---
   1
   2
   3
   5
   (4 rows)

   3. 内置命令列表：
   psql内置命令的格式为反斜杠后面紧跟一个命令动词，之后是任意参数。参数与命令动词以及其他参数之间可以用空白符隔开，如果参数里面包含空白符，该参数必须用单引号括起，如果参数内包含单引号，则需要用反斜杠进行转义，此外单引号内的参数还支持类似C语言printf函数所支持的转义关键字，如\t、\n等。

命令	说明
\a	如果目前的表输出格式是不对齐的，切换成对齐的。如果是对齐的，则切换成不对齐。
\cd [directory]	把当前工作目录切换到directory。没有参数则切换到当前用户的主目录。
\C [title]	为查询结果添加表头(title)，如果没有参数则取消当前的表头。
\c [dbname [username] ]	连接新的数据库，同时断开当前连接。如果dbname参数为-，表示仍然连接当前数据库。如果忽略username，则表示继续使用当前的用户名。
\copy	其参数类似于SQL copy，功能则几乎等同于SQL copy，一个重要的差别是该内置命令可以将表的内容导出到本地，或者是从本地导入到数据库指定的表，而SQL copy则是将表中的数据导出到服务器的某个文件，或者是从服务器的文件导入到数据表。由此可见，SQL copy的效率要优于该内置命令。
\d [pattern]	显示和pattern匹配的数据库对象，如表、视图、索引或者序列。显示所有列，它们的类型，表空间(如果不是缺省的)和任何特殊属性。
\db [pattern]	列出所有可用的表空间。如果声明了pattern，那么只显示那些匹配模式的表空间。
\db+ [pattern]	和上一个命令相比，还会新增显示每个表空间的权限信息。
\df [pattern]	列出所有可用函数，以及它们的参数和返回的数据类型。如果声明了pattern，那么只显示匹配(正则表达式)的函数。
\df+ [pattern]	和上一个命令相比，还会新增显示每个函数的附加信息，包括语言和描述。
\distvS [pattern]	这不是一个单独命令名称：字母 i、s、t、v、S 分别代表索引(index)、序列(sequence)、表(table)、视图(view)和系统表(system table)。你可以以任意顺序声明部分或者所有这些字母获得这些对象的一个列表。
\dn [pattern]	列出所有可用模式。如果声明了pattern，那么只列出匹配模式的模式名。
\dn+ [pattern]	和上一个命令相比，还会新增显示每个对象的权限和注释。
\dp [pattern]	生成一列可用的表和它们相关的权限。如果声明了pattern，那么只列出名字可以匹配模式的表。
\dT [pattern]	列出所有数据类型或只显示那些匹配pattern的。
\du [pattern]	列出所有已配置用户或者只列出那些匹配pattern的用户。
\echo text [ ... ]	向标准输出打印参数，用一个空格分隔并且最后跟着一个新行。如：\echo `date`
\g [{filename\| \|command}]	把当前的查询结果缓冲区的内容发送给服务器并且把查询的输出存储到可选的filename或者把输出定向到一个独立的在执行 command的Unix shell。
\i filename	从文件filename中读取并把其内容当作从键盘输入的那样执行查询。
\l	列出服务器上所有数据库的名字和它们的所有者以及字符集编码。
\o [{filename\| \|command}]	把后面的查询结果保存到文件filename里或者把后面的查询结果定向到一个独立的shell command。
\p	打印当前查询缓冲区到标准输出。
\q	退出psql程序。
\r	重置(清空)查询缓冲区。
\s [filename]	将命令行历史打印出或是存放到filename。如果省略filename，历史将输出到标准输出。
\t	切换是否输出列/字段名的信息头和行记数脚注。
\w {filename\| \|command}	将当前查询缓冲区输出到文件filename或者定向到Unix命令command。
\z [pattern]	生成一个带有访问权限列表的数据库中所有表，视图和序列的列表。如果给出任何pattern，则被当成一个规则表达式，只显示匹配的表，视图和序列。
\! [command]	返回到一个独立的Unix shell或者执行Unix命令command。参数不会被进一步解释，shell将看到全部参数。

    4. 内置命令应用示例：
   在psql中，大部分的内置命令都比较易于理解，因此这里只是给出几个我个人认为相对容易混淆的命令。
    # \c: 其中横线(-)表示仍然连接当前数据库，myuser是新的用户名。
   postgres=# \c - myuser
   Password for user myuser:
   postgres=> SELECT user;
   current_user
   --------------
   myuser
   (1 row)
   # 执行任意SQL语句。
   postgres=# SELECT * FROM testtable WHERE i = 2;
   i
   ---
   2
   (1 row)
   # \g 命令会将上一个SQL命令的结果输出到指定文件。
   postgres=# \g my_file_for_command_g
   postgres=# \! cat my_file_for_command_g
   i
   ---
   2
   (1 row)
   # \g 命令会将上一个SQL命令的结果从管道输出到指定的Shell命令，如cat。
   postgres=# \g | cat
   i
   ---
   2
   (1 row)
   # \p 打印上一个SQL命令。
   postgres=# \p
   SELECT * FROM testtable WHERE i = 2;
   # \w 将上一个SQL命令输出到指定的文件。
   postgres=# \w my_file_for_option_w
   postgres=# \! cat my_file_for_option_w
   SELECT * FROM testtable WHERE i = 2;
   # \o 和\g相反，该命令会将后面psql命令的输出结果输出到指定的文件，直到遇到下一个独立的\o，
   # 此后的命令结果将不再输出到该文件。
   postgres=# \o my_file_for_option_o
   postgres=# SELECT * FROM testtable WHERE i = 1;
   # 终止后面的命令结果也输出到my_file_for_option_o文件中。
   postgres=# \o
   postgres=# \! cat my_file_for_option_o
   i
   ---
   1
   (1 row)

PostgreSQL学习手册(十六) SQL语言函数

一、基本概念：
   SQL函数可以包含任意数量的查询，但是函数只返回最后一个查询(必须是SELECT)的结果。在简单情况下，返回最后一条查询结果的第一行。如果最后一个查询不返回任何行，那么该函数将返回NULL值。如果需要该函数返回最后一条SELECT语句的所有行，可以将函数的返回值定义为集合，即SETOF sometype。
   SQL函数的函数体应该是用分号分隔的SQL语句列表，其中最后一条语句之后的分号是可选的。除非函数声明为返回void，否则最后一条语句必须是 SELECT。事实上，在SQL函数中，不仅可以包含SELECT查询语句，也可以包含INSERT、UPDATE和DELETE等其他标准的SQL语句，但是和事物相关的语句不能包含其中，如BEGIN、COMMIT、ROLLBACK和SAVEPOINT等。
   CREATE FUNCTION命令的语法要求函数体写成一个字符串文本。通常来说，该文本字符串常量使用美元符($$)围住，如：
   CREATE FUNCTION clean_emp() RETURNS void AS $$
        DELETE FROM emp WHERE salary < 0;
   $$ LANGUAGE SQL;
   最后需要说明的是SQL函数中的参数，PostgreSQL定义$1表示第一个参数，$2为第二个参数并以此类推。如果参数是复合类型，则可以使用点表示法，即$1.name访问复合类型参数中的name字段。需要注意的是函数参数只能用作数据值，而不能用于标识符，如：
   INSERT INTO mytable VALUES ($1);   --合法
   INSERT INTO $1 VALUES (42);          --不合法(表名属于标示符之一)

二、基本类型：
   最简单的SQL函数可能就是没有参数且返回基本类型的函数了，如：
   CREATE FUNCTION one() RETURNS integer AS $$
        SELECT 1 AS result;
   $$ LANGUAGE SQL;
    下面的例子声明了基本类型作为函数的参数。
   CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
        SELECT $1 + $2;
   $$ LANGUAGE SQL;
   # 通过select调用函数。
   postgres=# SELECT add_em(1,2) AS answer;
   answer
   --------
          3
   (1 row)
   在下面的例子中，函数体内包含多个SQL语句，它们之间是用分号进行分隔的。
   CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
        UPDATE bank SET balance = balance - $2 WHERE accountno = $1;
        SELECT balance FROM bank WHERE accountno = $1;
   $$ LANGUAGE SQL;

三、复合类型：
   见如下示例：
   1). 创建数据表，这样与之对应的复合类型也随之生成。
   CREATE TABLE emp (
        name        text,
        salary      numeric,
        age         integer,
   );
   2). 创建函数，其参数为复合类型。在函数体内，可以像引用基本类型参数那样引用复合类型，如$1。访问复合类型的字段使用点表达式即可，如：$1.salary。
   CREATE FUNCTION double_salary(emp) RETURNS integer AS $$
        SELECT ($1.salary * 2)::integer AS salary;
   $$ LANGUAGE SQL;
   3). 在select语句中，可以使用emp.*表示emp表的一整行数据。
   SELECT name, double_salary(emp.*) AS dream FROM emp WHERE age > 30;
   4). 我们也可以使用ROW表达式构造自定义的复合类型，如：
   SELECT name, double_salary(ROW(name, salary*1.1, age)) AS dream FROM emp;
   5). 创建一个函数，其返回值为复合类型，如：
   CREATE FUNCTION new_emp() RETURNS emp AS $$
        SELECT ROW('None', 1000.0, 25)::emp;
   $$ LANGUAGE SQL;
   6). 调用返回复合类型的函数。
    SELECT new_emp();
    7). 调用返回复合类型的函数，同时访问该返回值的某个字段。
    SELECT (new_emp()).name;

四、带输出参数的函数：
   还有一种方法可以用于返回函数执行的结果，即输出参数，如：
   CREATE FUNCTION add_em2 (IN x int, IN y int, OUT sum int) AS $$
       SELECT $1 + $2
   $$ LANGUAGE SQL;
    调用方法和返回结果与add_em(带有返回值的函数)完全一致，如：
   SELECT add_em(3,7);

   这个带有输出参数的函数和之前的add_em函数没有本质的区别。事实上，输出参数的真正价值在于它为函数提供了返回多个字段的途径。如，
   CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) AS $$
       SELECT $1 + $2, $1 * $2
   $$ LANGUAGE SQL;
   调用方式没有改变，只是返回结果多出一列。
   SELECT * FROM sum_n_product(11,42);
   sum | product
   -----+---------
      53 |     462
   (1 row)
   在上面的示例中，IN用于表示该函数参数为输入参数(缺省值，可以忽略)，OUT则表示该参数为输出参数。

五、返回结果作为表数据源：
   所有SQL函数都可以在查询的FROM子句里使用。该方法对于返回复合类型的函数而言特别有用，如果该函数定义为返回一个基本类型，那么该函数生成一个单字段表，如果该函数定义为返回一个复合类型，那么该函数生成一个复合类型里每个属性组成的行。见如下示例：
    1). 创建一个数据表。
   CREATE TABLE foo (
       fooid    int,
       foosubid int,
       fooname text
   );
    2). 创建SQL函数，其返回值为与foo表对应的复合类型。
   CREATE FUNCTION getfoo(int) RETURNS foo AS $$
        SELECT * FROM foo WHERE fooid = $1;
   $$ LANGUAGE SQL;
    3). 在FROM子句中调用该函数。
   SELECT *, upper(fooname) FROM getfoo(1) AS t1;

六、返回集合的SQL函数：
   如果SQL函数的返回值为SETOF sometype，那么在调用该函数时，将返回最后一个SELECT查询的全部数据。这个特性通常用于把函数放在FROM子句里调用，见如下示例：
   CREATE FUNCTION getfoo(int) RETURNS setof foo AS $$
        SELECT * FROM foo WHERE fooid = $1;
   $$ LANGUAGE SQL;
   在FROM子句中调用了返回复合类型集合的函数，其结果等同于：SELECT * FROM (SELECT * FROM foo WHERE fooid = 1) t1;
   SELECT * FROM getfoo(1) AS t1;

七、多态的SQL函数：
   SQL函数可以声明为接受多态类型(anyelement和anyarray)的参数或返回多态类型的返回值，见如下示例：
   1). 函数参数和返回值均为多态类型。
   CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
        SELECT ARRAY[$1, $2];
   $$ LANGUAGE SQL;
   其调用方式和调用其它类型的SQL函数完全相同，只是在传递字符串类型的参数时，需要显式转换到目标类型，否则将会被视为unknown类型，如：
   SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
   2). 函数的参数为多态类型，而返回值则为基本类型。
   CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
        SELECT $1 > $2;
   $$ LANGUAGE SQL;
   3). 多态类型用于函数的输出参数。
   CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray) AS $$
       SELECT $1, ARRAY[$1,$1]
   $$ LANGUAGE sql;

八、函数重载：
   多个函数可以定义成相同的函数名，但是它们的参数一定要有所区分。换句话说，函数名可以重载，此规则有些类似于面向对象语言中的函数重载，见如下示例：
   CREATE FUNCTION test(int, real) RETURNS ...
   CREATE FUNCTION test(smallint, double) RETURNS ...

   由于在PostgreSQL中函数支持重载，因此在删除函数时，也必须指定参数列表，如：
   DROP FUNCTION test(int, real);
   DROP FUNCTION test(smallint,double);

PostgreSQL学习手册(十七) PL/pgSQL过程语言

一、概述：
    PL/pgSQL函数在第一次被调用时，其函数内的源代码(文本)将被解析为二进制指令树，但是函数内的表达式和SQL命令只有在首次用到它们的时候，PL/pgSQL解释器才会为其创建一个准备好的执行规划，随后对该表达式或SQL命令的访问都将使用该规划。如果在一个条件语句中，有部分SQL命令或表达式没有被用到，那么PL/pgSQL解释器在本次调用中将不会为其准备执行规划，这样的好处是可以有效地减少为PL/pgSQL函数里的语句生成分析和执行规划的总时间，然而缺点是某些表达式或SQL命令中的错误只有在其被执行到的时候才能发现。
    由于PL/pgSQL在函数里为一个命令制定了执行计划，那么在本次会话中该计划将会被反复使用，这样做往往可以得到更好的性能，但是如果你动态修改了相关的数据库对象，那么就有可能产生问题，如：
   CREATE FUNCTION populate() RETURNS integer AS $$
   DECLARE
        -- 声明段
   BEGIN
        PERFORM my_function();
   END;
   $$ LANGUAGE plpgsql;
   在调用以上函数时，PERFORM语句的执行计划将引用my_function对象的OID。在此之后，如果你重建了my_function函数，那么 populate函数将无法再找到原有my_function函数的OID。要解决该问题，可以选择重建populate函数，或者重新登录建立新的会话，以使PostgreSQL重新编译该函数。要想规避此类问题的发生，在重建my_function时可以使用CREATE OR REPLACE FUNCTION命令。
    鉴于以上规则，在PL/pgSQL里直接出现的SQL命令必须在每次执行时均引用相同的表和字段，换句话说，不能将函数的参数用作SQL命令的表名或字段名。如果想绕开该限制，可以考虑使用PL/pgSQL中的EXECUTE语句动态地构造命令，由此换来的代价是每次执行时都要构造一个新的命令计划。
    使用PL/pgSQL函数的一个非常重要的优势是可以提高程序的执行效率，由于原有的SQL调用不得不在客户端与服务器之间反复传递数据，这样不仅增加了进程间通讯所产生的开销，而且也会大大增加网络IO的开销。

二、PL/pgSQL的结构：
   PL/pgSQL是一种块结构语言，函数定义的所有文本都必须在一个块内，其中块中的每个声明和每条语句都是以分号结束，如果某一子块在另外一个块内，那么该子块的END关键字后面必须以分号结束，不过对于函数体的最后一个END关键字，分号可以省略，如：
   [ <<label>> ]
   [ DECLARE declarations ]
   BEGIN
        statements
   END [ label ];
   在PL/pgSQL中有两种注释类型，双破折号(--)表示单行注释。/* */表示多行注释，该注释类型的规则等同于C语言中的多行注释。
   在语句块前面的声明段中定义的变量在每次进入语句块(BEGIN)时都会将声明的变量初始化为它们的缺省值，而不是每次函数调用时初始化一次。如：
   CREATE FUNCTION somefunc() RETURNS integer AS $$
   DECLARE
       quantity integer := 30;
   BEGIN
       RAISE NOTICE 'Quantity here is %', quantity;      --在这里的数量是30
       quantity := 50;
       --
       -- 创建一个子块
       --
       DECLARE
          quantity integer := 80;
       BEGIN
          RAISE NOTICE 'Quantity here is %', quantity;   --在这里的数量是80
       END;
       RAISE NOTICE 'Quantity here is %', quantity;      --在这里的数量是50
       RETURN quantity;
   END;
   $$ LANGUAGE plpgsql;
    #执行该函数以进一步观察其执行的结果。
   postgres=# select somefunc();
   NOTICE: Quantity here is 30
   NOTICE: Quantity here is 80
   NOTICE: Quantity here is 50
   somefunc
   ----------
           50
   (1 row)
   最后需要说明的是，目前版本的PostgreSQL并不支持嵌套事务，函数中的事物总是由外层命令(函数的调用者)来控制的，它们本身无法开始或提交事务。

三、声明：
   所有在块里使用的变量都必须在块的声明段里先进行声明，唯一的例外是FOR循环里的循环计数变量，该变量被自动声明为整型。变量声明的语法如下：
   variable_name [ CONSTANT ] variable_type [ NOT NULL ] [ { DEFAULT | := } expression ];
   1). SQL中的数据类型均可作为PL/pgSQL变量的数据类型，如integer、varchar和char等。
   2). 如果给出了DEFAULT子句，该变量在进入BEGIN块时将被初始化为该缺省值，否则被初始化为SQL空值。缺省值是在每次进入该块时进行计算的。因此，如果把now()赋予一个类型为timestamp的变量，那么该变量的缺省值将为函数实际调用时的时间，而不是函数预编译时的时间。
   3). CONSTANT选项是为了避免该变量在进入BEGIN块后被重新赋值，以保证该变量为常量。
   4). 如果声明了NOT NULL，那么赋予NULL数值给该变量将导致一个运行时错误。因此所有声明为NOT NULL的变量也必须在声明时定义一个非空的缺省值。

    1. 函数参数的别名：
   传递给函数的参数都是用$1、$2这样的标识符来表示的。为了增加可读性，我们可以为其声明别名。之后别名和数字标识符均可指向该参数值，见如下示例：
   1). 在函数声明的同时给出参数变量名。
   CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
   BEGIN
        RETURN subtotal * 0.06;
   END;
   $$ LANGUAGE plpgsql;
   2). 在声明段中为参数变量定义别名。
   CREATE FUNCTION sales_tax(REAL) RETURNS real AS $$
   DECLARE
        subtotal ALIAS FOR $1;
   BEGIN
        RETURN subtotal * 0.06;
   END;
   $$ LANGUAGE plpgsql;
   3). 对于输出参数而言，我们仍然可以遵守1)和2)中的规则。
   CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
   BEGIN
        tax := subtotal * 0.06;
   END;
   $$ LANGUAGE plpgsql;
   4). 如果PL/pgSQL函数的返回类型为多态类型(anyelement或anyarray)，那么函数就会创建一个特殊的参数：$0。我们仍然可以为该变量设置别名。
   CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
   RETURNS anyelement AS $$
   DECLARE
        result ALIAS FOR $0;
   BEGIN
        result := v1 + v2 + v3;
        RETURN result;
   END;
   $$ LANGUAGE plpgsql;

    2. 拷贝类型：
   见如下形式的变量声明：
   variable%TYPE
   %TYPE表示一个变量或表字段的数据类型，PL/pgSQL允许通过该方式声明一个变量，其类型等同于variable或表字段的数据类型，见如下示例：
   user_id users.user_id%TYPE;
   在上面的例子中，变量user_id的数据类型等同于users表中user_id字段的类型。
   通过使用%TYPE，一旦引用的变量类型今后发生改变，我们也无需修改该变量的类型声明。最后需要说明的是，我们可以在函数的参数和返回值中使用该方式的类型声明。

    3. 行类型：
   见如下形式的变量声明：
   name table_name%ROWTYPE;
   name composite_type_name;
   table_name%ROWTYPE表示指定表的行类型，我们在创建一个表的时候，PostgreSQL也会随之创建出一个与之相应的复合类型，该类型名等同于表名，因此，我们可以通过以上两种方式来声明行类型的变量。由此方式声明的变量，可以保存SELECT返回结果中的一行。如果要访问变量中的某个域字段，可以使用点表示法，如rowvar.field，但是行类型的变量只能访问自定义字段，无法访问系统提供的隐含字段，如OID等。对于函数的参数，我们只能使用复合类型标识变量的数据类型。最后需要说明的是，推荐使用%ROWTYPE的声明方式，这样可以具有更好的可移植性，因为在Oracle 的PL/SQL中也存在相同的概念，其声明方式也为%ROWTYPE。见如下示例：
   CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
   DECLARE
        t2_row table2%ROWTYPE;
   BEGIN
        SELECT * INTO t2_row FROM table2 WHERE id = 1 limit 1;
        RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
   END;
   $$ LANGUAGE plpgsql;

    4. 记录类型：
   见如下形式的变量声明：
   name RECORD;
   记录变量类似于行类型变量，但是它们没有预定义的结构，只能通过SELECT或FOR命令来获取实际的行结构，因此记录变量在被初始化之前无法访问，否则将引发运行时错误。
   注：RECORD不是真正的数据类型，只是一个占位符。

四、基本语句：
   1. 赋值：
   PL/pgSQL中赋值语句的形式为：identIFier := expression，等号两端的变量和表达式的类型或者一致，或者可以通过PostgreSQL的转换规则进行转换，否则将会导致运行时错误，见如下示例：
   user_id := 20;
   tax := subtotal * 0.06;

    2. SELECT INTO：
   通过该语句可以为记录变量或行类型变量进行赋值，其表现形式为：SELECT INTO target select_expressions FROM ...，该赋值方式一次只能赋值一个变量。表达式中的target可以表示为是一个记录变量、行变量，或者是一组用逗号分隔的简单变量和记录/行字段的列表。select_expressions以及剩余部分和普通SQL一样。
   如果将一行或者一个变量列表用做目标，那么选出的数值必需精确匹配目标的结构，否则就会产生运行时错误。如果目标是一个记录变量，那么它自动将自己构造成命令结果列的行类型。如果命令返回零行，目标被赋予空值。如果命令返回多行，那么将只有第一行被赋予目标，其它行将被忽略。在执行SELECT INTO语句之后，可以通过检查内置变量FOUND来判断本次赋值是否成功，如：
   SELECT INTO myrec * FROM emp WHERE empname = myname;
   IF NOT FOUND THEN
        RAISE EXCEPTION 'employee % not found', myname;
   END IF;
   要测试一个记录/行结果是否为空，可以使用IS NULL条件进行判断，但是对于返回多条记录的情况则无法判断，如：
   DECLARE
        users_rec RECORD;
   BEGIN
        SELECT INTO users_rec * FROM users WHERE user_id = 3;
        IF users_rec.homepage IS NULL THEN
            RETURN 'http://';
        END IF;
   END;

   3. 执行一个没有结果的表达式或者命令：
   在调用一个表达式或执行一个命令时，如果对其返回的结果不感兴趣，可以考虑使用PERFORM语句：PERFORM query，该语句将执行PERFORM之后的命令并忽略其返回的结果。其中query的写法和普通的SQL SELECT命令是一样的，只是把开头的关键字SELECT替换成PERFORM，如：
    PERFORM create_mv('cs_session_page_requests_mv', my_query);

    4. 执行动态命令：
   如果在PL/pgSQL函数中操作的表或数据类型在每次调用该函数时都可能会发生变化，在这样的情况下，可以考虑使用PL/pgSQL提供的EXECUTE语句：EXECUTE command-string [ INTO target ]，其中command-string是用一段文本表示的表达式，它包含要执行的命令。而target是一个记录变量、行变量或者一组用逗号分隔的简单变量和记录/行域的列表。这里需要特别注意的是，该命令字符串将不会发生任何PL/pgSQL变量代换，变量的数值必需在构造命令字符串时插入到该字符串中。
   和所有其它PL/pgSQL命令不同的是，一个由EXECUTE语句运行的命令在服务器内并不会只prepare和保存一次。相反，该语句在每次运行的时候，命令都会prepare一次。因此命令字符串可以在函数里动态的生成以便于对各种不同的表和字段进行操作，从而提高函数的灵活性。然而由此换来的却是性能上的折损。见如下示例：
   EXECUTE 'UPDATE tbl SET ' || quote_ident(columnname) || ' = ' || quote_literal(newvalue);

五、控制结构：
   1. 函数返回：
   1). RETURN expression
   该表达式用于终止当前的函数，然后再将expression的值返回给调用者。如果返回简单类型，那么可以使用任何表达式，同时表达式的类型也将被自动转换成函数的返回类型，就像我们在赋值中描述的那样。如果要返回一个复合类型的数值，则必须让表达式返回记录或者匹配的行变量。
    2). RETURN NEXT expression
   如果PL/pgSQL函数声明为返回SETOF sometype，其行记录是通过RETURN NEXT命令进行填充的，直到执行到不带参数的RETURN时才表示该函数结束。因此对于RETURN NEXT而言，它实际上并不从函数中返回，只是简单地把表达式的值保存起来，然后继续执行PL/pgSQL函数里的下一条语句。随着RETURN NEXT命令的迭代执行，结果集最终被建立起来。该类函数的调用方式如下：
   SELECT * FROM some_func();
   它被放在FROM子句中作为数据源使用。最后需要指出的是，如果结果集数量很大，那么通过该种方式来构建结果集将会导致极大的性能损失。

    2. 条件：
   在PL/pgSQL中有以下三种形式的条件语句。
   1). IF-THEN
   IF boolean-expression THEN
        statements
   END IF;
   2). IF-THEN-ELSE
   IF boolean-expression THEN
        statements
   ELSE
        statements
   END IF;
   3). IF-THEN-ELSIF-ELSE
   IF boolean-expression THEN
        statements
   ELSIF boolean-expression THEN
        statements
   ELSIF boolean-expression THEN
        statements
   ELSE
        statements
   END IF;
   关于条件语句，这里就不在做过多的赘述了。

    3. 循环：
   1). LOOP
   LOOP
        statements
   END LOOP [ label ];
   LOOP定义一个无条件的循环，直到由EXIT或者RETURN语句终止。可选的label可以由EXIT和CONTINUE语句使用，用于在嵌套循环中声明应该应用于哪一层循环。
   2). EXIT
   EXIT [ label ] [ WHEN expression ];
   如果没有给出label，就退出最内层的循环，然后执行跟在END LOOP后面的语句。如果给出label，它必须是当前或更高层的嵌套循环块或语句块的标签。之后该命名块或循环就会终止，而控制则直接转到对应循环/块的END语句后面的语句上。
   如果声明了WHEN，EXIT命令只有在expression为真时才被执行，否则将直接执行EXIT后面的语句。见如下示例：
   LOOP
        -- do something
        EXIT WHEN count > 0;
   END LOOP;
   3). CONTINUE
   CONTINUE [ label ] [ WHEN expression ];
   如果没有给出label，CONTINUE就会跳到最内层循环的开始处，重新进行判断，以决定是否继续执行循环内的语句。如果指定label，则跳到该 label所在的循环开始处。如果声明了WHEN，CONTINUE命令只有在expression为真时才被执行，否则将直接执行CONTINUE后面的语句。见如下示例：
   LOOP
        -- do something
        EXIT WHEN count > 100;
        CONTINUE WHEN count < 50;
   END LOOP;
   4). WHILE
   [ <<label>> ]
   WHILE expression LOOP
        statements
   END LOOP [ label ];
   只要条件表达式为真，其块内的语句就会被循环执行。条件是在每次进入循环体时进行判断的。见如下示例：
   WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
        --do something
   END LOOP;
   5). FOR
   [ <<label>> ]
   FOR name IN [ REVERSE ] expression .. expression LOOP
        statements
   END LOOP [ label ];
   变量name自动被定义为integer类型，其作用域仅为FOR循环的块内。表示范围上下界的两个表达式只在进入循环时计算一次。每次迭代name值自增1，但如果声明了REVERSE，name变量在每次迭代中将自减1，见如下示例：
   FOR i IN 1..10 LOOP
        --do something
        RAISE NOTICE 'i IS %', i;
   END LOOP;

   FOR i IN REVERSE 10..1 LOOP
        --do something
   END LOOP;

   4. 遍历命令结果：
   [ <<label>> ]
   FOR record_or_row IN query LOOP
        statements
   END LOOP [ label ];
   这是另外一种形式的FOR循环，在该循环中可以遍历命令的结果并操作相应的数据，见如下示例：
   FOR rec IN SELECT * FROM some_table LOOP
       PERFORM some_func(rec.one_col);
   END LOOP;
   PL/pgSQL还提供了另外一种遍历命令结果的方式，和上面的方式相比，唯一的差别是该方式将SELECT语句存于字符串文本中，然后再交由EXECUTE命令动态的执行。和前一种方式相比，该方式的灵活性更高，但是效率较低。
   [ <<label>> ]
   FOR record_or_row IN EXECUTE text_expression LOOP
        statements
   END LOOP [ label ];

    5. 异常捕获：
   在PL/pgSQL函数中，如果没有异常捕获，函数会在发生错误时直接退出，与其相关的事物也会随之回滚。我们可以通过使用带有EXCEPTION子句的BEGIN块来捕获异常并使其从中恢复。见如下声明形式：
   [ <<label>> ]
   [ DECLARE
        declarations ]
   BEGIN
        statements
   EXCEPTION
        WHEN condition [ OR condition ... ] THEN
            handler_statements
        WHEN condition [ OR condition ... ] THEN
            handler_statements
   END;
   如果没有错误发生，只有BEGIN块中的statements会被正常执行，然而一旦这些语句中有任意一条发生错误，其后的语句都将被跳过，直接跳转到 EXCEPTION块的开始处。此时系统将搜索异常条件列表，寻找匹配该异常的第一个条件，如果找到匹配，则执行相应的 handler_statements，之后再执行END的下一条语句。如果没有找到匹配，该错误就会被继续向外抛出，其结果与没有EXCEPTION子句完全等同。如果此时handler_statements中的语句发生新错误，它将不能被该EXCEPTION子句捕获，而是继续向外传播，交由其外层的EXCEPTION子句捕获并处理。见如下示例：
    INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
    BEGIN
        UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
        x := x + 1;
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN
            RAISE NOTICE 'caught division_by_zero';
            RETURN x;
    END;
    当以上函数执行到y := x / 0语句时，将会引发一个异常错误，代码将跳转到EXCEPTION块的开始处，之后系统会寻找匹配的异常捕捉条件，此时division_by_zero 完全匹配，这样该条件内的代码将会被继续执行。需要说明的是，RETURN语句中返回的x值为x := x + 1执行后的新值，但是在除零之前的update语句将会被回滚，BEGIN之前的insert语句将仍然生效。

六、游标：
    1. 声明游标变量：
    在PL/pgSQL中对游标的访问都是通过游标变量实现的，其数据类型为refcursor。创建游标变量的方法有以下两种：
    1). 和声明其他类型的变量一样，直接声明一个游标类型的变量即可。
    2). 使用游标专有的声明语法，如：
    name CURSOR [ ( arguments ) ] FOR query;
    其中arguments为一组逗号分隔的name datatype列表，见如下示例：
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
    在上面三个例子中，只有第一个是未绑定游标，剩下两个游标均已被绑定。

    2. 打开游标：
    游标在使用之前必须先被打开，在PL/pgSQL中有三种形式的OPEN语句，其中两种用于未绑定的游标变量，另外一种用于绑定的游标变量。
    1). OPEN FOR:
    其声明形式为：
    OPEN unbound_cursor FOR query;
    该形式只能用于未绑定的游标变量，其查询语句必须是SELECT，或其他返回记录行的语句，如EXPLAIN。在PostgreSQL中，该查询和普通的SQL命令平等对待，即先替换变量名，同时也将该查询的执行计划缓存起来，以供后用。见如下示例：
    OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
    2). OPEN FOR EXECUTE
    其声明形式为：
    OPEN unbound_cursor FOR EXECUTE query-string;
    和上面的形式一样，该形式也仅适用于未绑定的游标变量。EXECUTE将动态执行其后以文本形式表示的查询字符串。
    OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
    3). 打开一个绑定的游标
    其声明形式为：
    OPEN bound_cursor [ ( argument_values ) ];
    该形式仅适用于绑定的游标变量，只有当该变量在声明时包含接收参数，才能以传递参数的形式打开该游标，这些参数将被实际代入到游标声明的查询语句中，见如下示例：
    OPEN curs2;
    OPEN curs3(42);

    3. 使用游标：
    游标一旦打开，就可以按照以下方式进行读取。然而需要说明的是，游标的打开和读取必须在同一个事物内，因为在PostgreSQL中，如果事物结束，事物内打开的游标将会被隐含的关闭。
    1). FETCH
    其声明形式为：
    FETCH cursor INTO target;
    FETCH命令从游标中读取下一行记录的数据到目标中，其中目标可以是行变量、记录变量，或者是一组逗号分隔的普通变量的列表，读取成功与否，可通过PL/pgSQL内置变量FOUND来判断，其规则等同于SELECT INTO。见如下示例：
    FETCH curs1 INTO rowvar;  --rowvar为行变量
    FETCH curs2 INTO foo, bar, baz;
    2). CLOSE
    其声明形式为：
    CLOSE cursor;
    关闭当前已经打开的游标，以释放其占有的系统资源，见如下示例：
    CLOSE curs1;

七、错误和消息：
    在PostgreSQL中可以利用RAISE语句报告信息和抛出错误，其声明形式为：
    RAISE level 'format' [, expression [, ...]];
    这里包含的级别有DEBUG(向服务器日志写信息)、LOG(向服务器日志写信息，优先级更高)、INFO、NOTICE和WARNING(把信息写到服务器日志以及转发到客户端应用，优先级逐步升高)和EXCEPTION抛出一个错误(通常退出当前事务)。某个优先级别的信息是报告给客户端还是写到服务器日志，还是两个均有，是由log_min_messages和client_min_messages这两个系统初始化参数控制的。
    在format部分中，%表示为占位符，其实际值仅在RAISE命令执行时由后面的变量替换，如果要在format中表示%自身，可以使用%%的形式表示，见如下示例：
    RAISE NOTICE 'Calling cs_create_job(%)',v_job_id;  --v_job_id变量的值将替换format中的%。
    RAISE EXCEPTION 'Inexistent ID --> %',user_id;

PostgreSQL的generate_series函数应用

一、简介

PostgreSQL 中有一个很有用处的内置函数generate_series，可以按不同的规则产生一系列的填充数据。

二、语法

函数	参数类型	返回类型	描述
generate_series(start, stop)	int 或 bigint	setof int 或 setof bigint(与参数类型相同)	生成一个数值序列，从start 到 stop，步进为一
generate_series(start, stop, step)	int 或 bigint	setof int 或 setof bigint(与参数类型相同)	生成一个数值序列，从start 到 stop，步进为step
generate_series(start, stop, step_interval)	timestamp or timestamp with time zone	timestamp 或 timestamp with time zone(same as argument type)	生成一个数值序列，从start 到 stop，步进为step

三、实例

3.1) int 类型

a. 不写步进时默认为1

复制代码

david=# select generate_series(1, 10);
 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)

david=#

复制代码

b. 设置步进

复制代码

david=# select generate_series(1, 10, 3);
 generate_series 
-----------------
               1
               4
               7
              10
(4 rows)

david=#

复制代码

c. 如果step 是正数，而start 大于stop，那么返回零行。相反，如果step 是负数，start 小于stop，则返回零行。如果是NULL 输入，也产生零行。step 为零则是一个错误。

david=# select generate_series(5,1);
 generate_series 
-----------------
(0 rows)

david=#

NULL inputs

david=# select generate_series(5,null);
 generate_series 
-----------------
(0 rows)

david=#

step 为零

david=# select generate_series(5,1,0);
ERROR:  step size cannot equal zero
david=#

start 大于stop，step 是负数

复制代码

david=# select generate_series(5,1,-1);
 generate_series 
-----------------
               5
               4
               3
               2
               1
(5 rows)

david=#

复制代码

3.2) 时间类型

复制代码

david=# select generate_series(now(), now() + '7 days', '1 day');
        generate_series        
-------------------------------
 2013-04-03 14:22:26.391852+08
 2013-04-04 14:22:26.391852+08
 2013-04-05 14:22:26.391852+08
 2013-04-06 14:22:26.391852+08
 2013-04-07 14:22:26.391852+08
 2013-04-08 14:22:26.391852+08
 2013-04-09 14:22:26.391852+08
 2013-04-10 14:22:26.391852+08
(8 rows)

david=#

复制代码

david=# select generate_series(to_date('20130403','yyyymmdd'), to_date('20130404','yyyymmdd'), '3 hours');  
    generate_series     
------------------------
 2013-04-03 00:00:00+08
 2013-04-03 03:00:00+08
 2013-04-03 06:00:00+08
 2013-04-03 09:00:00+08
 2013-04-03 12:00:00+08
 2013-04-03 15:00:00+08
 2013-04-03 18:00:00+08
 2013-04-03 21:00:00+08
 2013-04-04 00:00:00+08
(9 rows)

david=#

复制代码

3.3) IP类型

a. 建表

david=# create table tbl_david(id int, ip_start inet, ip_stop inet);
CREATE TABLE
david=#

b. 插入数据

复制代码

david=# insert into tbl_david values (1, '192.168.1.6', '192.168.1.10');   
INSERT 0 1
david=# insert into tbl_david values (2, '192.168.2.16', '192.168.2.20');  
INSERT 0 1
david=# insert into tbl_david values (3, '192.168.3.116', '192.168.3.120'); 
INSERT 0 1
david=#

复制代码

c. 查看数据

复制代码

david=# select * from tbl_david ;
 id |   ip_start    |    ip_stop    
----+---------------+---------------
  1 | 192.168.1.6   | 192.168.1.10
  2 | 192.168.2.16  | 192.168.2.20
  3 | 192.168.3.116 | 192.168.3.120
(3 rows)

david=#

复制代码

d. generate_series 生成序列

复制代码

david=# select id, generate_series(0, ip_stop-ip_start)+ip_start as ip_new from tbl_david ;
 id |    ip_new     
----+---------------
  1 | 192.168.1.6
  1 | 192.168.1.7
  1 | 192.168.1.8
  1 | 192.168.1.9
  1 | 192.168.1.10
  2 | 192.168.2.16
  2 | 192.168.2.17
  2 | 192.168.2.18
  2 | 192.168.2.19
  2 | 192.168.2.20
  3 | 192.168.3.116
  3 | 192.168.3.117
  3 | 192.168.3.118
  3 | 192.168.3.119
  3 | 192.168.3.120
(15 rows)

david=#

复制代码

四、总结

PostgreSQL的generate_series函数对生成测试数据，批量更新一定规则的数据有比较多的应用场景，使用得当可提升开发效率，另外IP的序列生成也是PG的一个亮点。

PostgreSQL分区表（Table Partitioning）应用

一、简介

　　在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，PostgreSQL也不例外。

　　PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。

　　分区的具体好处是：

某些类型的查询性能可以得到极大提升。
更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。
批量删除可以用简单的删除某个分区来实现。
可以将很少用的数据移动到便宜的、转速慢的存储介质上。

　　在PG里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。无论何时，都应保证主表里面是空的。

　　小表分区不实际，表在多大情况下才考虑分区呢？PostgresSQL官方给出的建议是：当表本身大小超过了机器物理内存的实际大小时（the size of the table should exceed the physical memory of the database server），可以考虑分区。

　　PG目前（9.2.2）仅支持范围分区和列表分区，尚未支持散列分区。

二、环境

系统环境：CentOS release 6.3 (Final)

PostgreSQL版本：PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

三、实现分区

3.1 创建主表

复制代码

david=# create table tbl_partition (
david(# id integer,
david(# name varchar(20),
david(# gender boolean,
david(# join_date date,
david(# dept char(4));
CREATE TABLE
david=#

复制代码

3.2 创建分区表

复制代码

david=# create table tbl_partition_201211 (
check ( join_date >= DATE '2012-11-01' AND join_date < DATE '2012-12-01' )       
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201212 (
check ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' )      
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201301 (
check ( join_date >= DATE '2013-01-01' AND join_date < DATE '2013-02-01' )      
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201302 (
check ( join_date >= DATE '2013-02-01' AND join_date < DATE '2013-03-01' )  
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201303 (
check ( join_date >= DATE '2013-03-01' AND join_date < DATE '2013-04-01' ) 
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201304 (
check ( join_date >= DATE '2013-04-01' AND join_date < DATE '2013-05-01' )  
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201305 (
check ( join_date >= DATE '2013-05-01' AND join_date < DATE '2013-06-01' )  
) INHERITS (tbl_partition);
CREATE TABLE
david=#

复制代码

3.3 分区键上建索引

复制代码

david=# create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);
CREATE INDEX
david=# create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date); 
CREATE INDEX
david=# create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);    
CREATE INDEX
david=# create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date); 
CREATE INDEX
david=# create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date); 
CREATE INDEX
david=# create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date); 
CREATE INDEX
david=# create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date); 
CREATE INDEX
david=#

复制代码

对于开发人员来说，希望数据库是透明的，只管 insert into tbl_partition。对于数据插向哪个分区，则希望由DB决定。这点，ORACLE实现了，但是PG不行，需要前期人工处理下。

3.4 创建触发器函数

复制代码

david=# CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()                      
RETURNS TRIGGER AS $$  
BEGIN  
    IF ( NEW.join_date >= DATE '2012-11-01' AND    
         NEW.join_date < DATE '2012-12-01' ) THEN  
        INSERT INTO tbl_partition_201211 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2012-12-01' AND  
            NEW.join_date < DATE '2013-01-01' ) THEN  
        INSERT INTO tbl_partition_201212 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-01-01' AND  
            NEW.join_date < DATE '2013-02-01' ) THEN  
        INSERT INTO tbl_partition_201301 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-02-01' AND  
            NEW.join_date < DATE '2013-03-01' ) THEN  
        INSERT INTO tbl_partition_201302 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-03-01' AND  
            NEW.join_date < DATE '2013-04-01' ) THEN  
        INSERT INTO tbl_partition_201303 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-04-01' AND  
            NEW.join_date < DATE '2013-05-01' ) THEN  
        INSERT INTO tbl_partition_201304 VALUES (NEW.*);
    ELSIF ( NEW.join_date >= DATE '2013-05-01' AND
            NEW.join_date < DATE '2013-06-01' ) THEN
        INSERT INTO tbl_partition_201305 VALUES (NEW.*); 
    ELSE  
        RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';  
    END IF;  
    RETURN NULL;  
END;  
$$  
LANGUAGE plpgsql;
CREATE FUNCTION
david=#

复制代码

说明：如果不想丢失数据，上面的ELSE 条件可以改成 INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同时需要创建一张结构和tbl_partition 一样的表tbl_partition_error_join_date，这样，错误的join_date 数据就可以插入到这张表中而不是报错了。

3.5 创建触发器

david=# CREATE TRIGGER insert_tbl_partition_trigger
david-#     BEFORE INSERT ON tbl_partition
david-#     FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger();
CREATE TRIGGER
david=#

四、查看表

4.1 查看所有表

复制代码

david=# \dt
                List of relations
 Schema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------
 public | tbl_partition        | table | postgres
 public | tbl_partition_201211 | table | postgres
 public | tbl_partition_201212 | table | postgres
 public | tbl_partition_201301 | table | postgres
 public | tbl_partition_201302 | table | postgres
 public | tbl_partition_201303 | table | postgres
 public | tbl_partition_201304 | table | postgres
 public | tbl_partition_201305 | table | postgres
(8 rows)

david=#

复制代码

4.2 查看主表

复制代码

david=# \d tbl_partition
         Table "public.tbl_partition"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 id        | integer               | 
 name      | character varying(20) | 
 gender    | boolean               | 
 join_date | date                  | 
 dept      | character(4)          | 
Triggers:
    insert_tbl_partition_trigger BEFORE INSERT ON tbl_partition FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger()
Number of child tables: 7 (Use \d+ to list them.)

david=#

复制代码

4.3 查看分区表

复制代码

david=# \d tbl_partition_201304
      Table "public.tbl_partition_201304"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 id        | integer               | 
 name      | character varying(20) | 
 gender    | boolean               | 
 join_date | date                  | 
 dept      | character(4)          | 
Indexes:
    "tbl_partition_201304_joindate" btree (join_date)
Check constraints:
    "tbl_partition_201304_join_date_check" CHECK (join_date >= '2013-04-01'::date AND join_date < '2013-05-01'::date)
Inherits: tbl_partition

david=#

复制代码

五、测试

5.1 插入数据

复制代码

david=# insert into tbl_partition values (1, 'David', '1', '2013-01-10', 'TS');
INSERT 0 0
david=# insert into tbl_partition values (2, 'Sandy', '0', '2013-02-10', 'TS');
INSERT 0 0
david=# insert into tbl_partition values (3, 'Eagle', '1', '2012-11-01', 'TS');
INSERT 0 0
david=# insert into tbl_partition values (4, 'Miles', '1', '2012-12-15', 'SD');
INSERT 0 0
david=# insert into tbl_partition values (5, 'Simon', '1', '2012-12-10', 'SD');
INSERT 0 0
david=# insert into tbl_partition values (6, 'Rock', '1', '2012-11-10', 'SD');
INSERT 0 0
david=# insert into tbl_partition values (7, 'Peter', '1', '2013-01-11', 'SD');
INSERT 0 0
david=# insert into tbl_partition values (8, 'Sally', '0', '2013-03-10', 'BCSC');
INSERT 0 0
david=# insert into tbl_partition values (9, 'Carrie', '0', '2013-04-02', 'BCSC');
INSERT 0 0
david=# insert into tbl_partition values (10, 'Lee', '1', '2013-01-05', 'BMC');
INSERT 0 0
david=# insert into tbl_partition values (11, 'Nicole', '0', '2012-11-10', 'PROJ');
INSERT 0 0
david=# insert into tbl_partition values (12, 'Renee', '0', '2013-01-10', 'TS');
INSERT 0 0
david=#

复制代码

5.2 查看主表数据

复制代码

david=# select * from tbl_partition;
 id |  name  | gender | join_date  | dept 
----+--------+--------+------------+------
  3 | Eagle  | t      | 2012-11-01 | TS  
  6 | Rock   | t      | 2012-11-10 | SD  
 11 | Nicole | f      | 2012-11-10 | PROJ
  4 | Miles  | t      | 2012-12-15 | SD  
  5 | Simon  | t      | 2012-12-10 | SD  
  1 | David  | t      | 2013-01-10 | TS  
  7 | Peter  | t      | 2013-01-11 | SD  
 10 | Lee    | t      | 2013-01-05 | BMC 
 12 | Renee  | f      | 2013-01-10 | TS  
  2 | Sandy  | f      | 2013-02-10 | TS  
  8 | Sally  | f      | 2013-03-10 | BCSC
  9 | Carrie | f      | 2013-04-02 | BCSC
(12 rows)

david=#

复制代码

5.3 查看分区表数据

复制代码

david=# select * from tbl_partition_201301 ;
 id | name  | gender | join_date  | dept 
----+-------+--------+------------+------
  1 | David | t      | 2013-01-10 | TS  
  7 | Peter | t      | 2013-01-11 | SD  
 10 | Lee   | t      | 2013-01-05 | BMC 
 12 | Renee | f      | 2013-01-10 | TS  
(4 rows)

david=#

复制代码

六、管理分区

6.1 移除数据/分区

实现分区表之后，我们就可以很容易地移除不再使用的旧数据了，最简单的方法就是：

david=# drop table tbl_partition_201304;

这样可以快速移除大量数据，而不是逐条删除数据。

另一个推荐做法是将分区从分区表中移除，但是保留访问权限。

david=# alter table tbl_partition_201304 no inherit tbl_partition;
ALTER TABLE
david=#

和直接DROP 相比，该方式仅仅是使子表脱离了原有的主表，而存储在子表中的数据仍然可以得到访问，因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说，就可以在此时对该表进行必要的维护操作，如数据清理、归档等，在完成诸多例行性的操作之后，就可以考虑是直接删除该表(DROP TABLE)，还是先清空该表的数据(TRUNCATE TABLE)，之后再让该表重新继承主表。

david=# alter table tbl_partition_201304 inherit tbl_partition;                                                            
ALTER TABLE
david=#

6.2 增加分区

我们可以像之前那样增加一个分区

david=# create table tbl_partition_201306 (
check ( join_date >= DATE '2013-06-01' AND join_date < DATE '2013-07-01' )  
) INHERITS (tbl_partition);

david=# create index tbl_partition_201306_joindate on tbl_partition_201306 (join_date);

同时，需要修改触发器函数，将插入条件改成相应的值。

说明：创建触发器函数时，最好把插入条件写更未来一点，比如多写十年，这样以后增加新分区时就不需要重新创建触发器函数了，也可以避免一些不必要的错误。

另外，还可以如下增加新的分区：

复制代码

david=# create table tbl_partition_201307
david-# (LIKE tbl_partition INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
CREATE TABLE
david=#

david=# alter table tbl_partition_201307 add constraint tbl_partition_201307_join_date_check 
david-# check ( join_date >= DATE '2013-07-01' AND join_date < DATE '2013-08-01' );
ALTER TABLE
david=#

david=# create index tbl_partition_201307_joindate on tbl_partition_201307 (join_date);

david=# copy tbl_partition_201307 from '/tmp/tbl_partition_201307.sql';      //从文件中拷贝数据，这些数据可以是事前准备的

david=# alter table tbl_partition_201307 inherit tbl_partition;

复制代码

七、约束排除

约束排除（Constraint exclusion）是一种查询优化技巧，它改进了用上面方法定义的表分区的性能。

确保postgresql.conf 里的配置参数constraint_exclusion 是打开的。没有这个参数，查询不会按照需要进行优化。这里我们需要做的是确保该选项在配置文件中没有被注释掉。

如果没有约束排除，查询会扫描tbl_partition 表中的每一个分区。打开了约束排除之后，规划器将检查每个分区的约束然后再试图证明该分区不需要被扫描，因为它不能包含任何符合WHERE子句条件的数据行。如果规划器可以证明这个，它就把该分区从查询规划里排除出去。

可以使用EXPLAIN 命令显示一个规划在constraint_exclusion 关闭和打开情况下的不同：

7.1 约束排除关闭

复制代码

david=# set constraint_exclusion = off;                                                 
SET
david=# explain select count(*) from tbl_partition where join_date >= DATE '2013-04-01';
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Aggregate  (cost=172.80..172.81 rows=1 width=0)
   ->  Append  (cost=0.00..167.62 rows=2071 width=0)
         ->  Seq Scan on tbl_partition  (cost=0.00..0.00 rows=1 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201211 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201212 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201301 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201302 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201303 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201305 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201304 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201306 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201307 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
(22 rows)

david=#

复制代码

从上面的查询计划中可以看出，PostgreSQL 扫描了所有分区。下面我们再看一下打开约束排除之后的查询计划：

7.2 约束排除开启

复制代码

david=# set constraint_exclusion = on;                                                  
SET
david=# explain select count(*) from tbl_partition where join_date >= DATE '2013-04-01';
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Aggregate  (cost=76.80..76.81 rows=1 width=0)
   ->  Append  (cost=0.00..74.50 rows=921 width=0)
         ->  Seq Scan on tbl_partition  (cost=0.00..0.00 rows=1 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201305 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201304 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201306 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201307 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
(12 rows)

david=#

复制代码

可以看到，PostgreSQL 只扫描四月份以后的分区表。

八、可选的分区方式

还可以通过建立规则的方式进行分区。

复制代码

CREATE RULE insert_tbl_partition_201211 AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2012-11-01' AND join_date < DATE '2012-12-01' )
DO INSTEAD
    INSERT INTO tbl_partition_201211 VALUES (NEW.*);

CREATE RULE insert_tbl_partition_201212 AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' )
DO INSTEAD
    INSERT INTO tbl_partition_201212 VALUES (NEW.*);

...
    
CREATE RULE insert_tbl_partition_201306 AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2013-06-01' AND join_date < DATE '2013-07-01' )
DO INSTEAD
    INSERT INTO tbl_partition_201306 VALUES (NEW.*);
    
CREATE RULE insert_tbl_partition_201307 AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2013-07-01' AND join_date < DATE '2013-08-01' )
DO INSTEAD
    INSERT INTO tbl_partition_201307 VALUES (NEW.*);
    
CREATE RULE insert_tbl_partition_error_join_date AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2013-08-01' OR join_date < DATE '2012-11-01' )
DO INSTEAD
    INSERT INTO tbl_partition_error_join_date VALUES (NEW.*);

复制代码

九、注意事项

VACUUM 或 ANALYZE tbl_partition 只会对主表起作用，要想分析表，需要分别分析每个分区表。

PostgreSQL 序列（SEQUENCE）

一、简介

序列对象（也叫序列生成器）就是用CREATE SEQUENCE 创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。

二、创建序列

方法一：直接在表中指定字段类型为serial 类型

david=# create table tbl_xulie (
david(# id serial,
david(# name text);
NOTICE:  CREATE TABLE will create implicit sequence "tbl_xulie_id_seq" for serial column "tbl_xulie.id"
CREATE TABLE
david=#

方法二：先创建序列名称，然后在新建的表中列属性指定序列就可以了，该列需int 类型

创建序列的语法：

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table.column | NONE } ]

实例：

david=# create sequence tbl_xulie2_id_seq increment by 1 minvalue 1 no maxvalue start with 1;     
CREATE SEQUENCE
david=#

david=# create table tbl_xulie2 (
david(# id int4 not null default nextval('tbl_xulie2_id_seq'),
david(# name text);
CREATE TABLE
david=#

三、查看序列

复制代码

david=# \d tbl_xulie
                         Table "public.tbl_xulie"
 Column |  Type   |                       Modifiers                        
--------+---------+--------------------------------------------------------
 id     | integer | not null default nextval('tbl_xulie_id_seq'::regclass)
 name   | text    | 

david=# \d tbl_xulie2
                         Table "public.tbl_xulie2"
 Column |  Type   |                        Modifiers                        
--------+---------+---------------------------------------------------------
 id     | integer | not null default nextval('tbl_xulie2_id_seq'::regclass)
 name   | text    | 

david=#

复制代码

查看序列属性

复制代码

david=# \d tbl_xulie_id_seq
      Sequence "public.tbl_xulie_id_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | tbl_xulie_id_seq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f
Owned by: public.tbl_xulie.id

david=#

复制代码

david=# select * from tbl_xulie2_id_seq;
   sequence_name   | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
-------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 tbl_xulie2_id_seq |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f
(1 row)

david=#

复制代码

四、序列应用

4.1 在INSERT 命令中使用序列

复制代码

david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');      
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# select * from tbl_xulie;
 id | name  
----+-------
  1 | David
  2 | Sandy
(2 rows)

david=#

复制代码

4.2 数据迁移后更新序列

复制代码

david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# 
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Eagle');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Miles');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Simon');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Rock'); 
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Peter');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sally');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Nicole');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Monica');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Renee'); 
INSERT 0 1
david=# select * from tbl_xulie;
 id |  name  
----+--------
 15 | Sandy
 16 | David
 17 | Eagle
 18 | Miles
 19 | Simon
 20 | Rock
 21 | Peter
 22 | Sally
 23 | Nicole
 24 | Monica
 25 | Renee
(11 rows)

david=# copy tbl_xulie to '/tmp/tbl_xulie.sql';
COPY 11
david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# alter sequence tbl_xulie_id_seq restart with 100;
ALTER SEQUENCE
david=# select currval('tbl_xulie_id_seq');
 currval 
---------
      25
(1 row)

david=# select nextval('tbl_xulie_id_seq');
 nextval 
---------
     100
(1 row)

david=# select nextval('tbl_xulie_id_seq');
 nextval 
---------
     101
(1 row)

david=# begin;
BEGIN
david=# copy tbl_xulie from '/tmp/tbl_xulie.sql';
COPY 11
david=# select setval('tbl_xulie_id_seq', max(id)) from tbl_xulie;
 setval 
--------
     25
(1 row)

david=# end;
COMMIT
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Flash');
INSERT 0 1
david=# select * from tbl_xulie;
 id |  name  
----+--------
 15 | Sandy
 16 | David
 17 | Eagle
 18 | Miles
 19 | Simon
 20 | Rock
 21 | Peter
 22 | Sally
 23 | Nicole
 24 | Monica
 25 | Renee
 26 | Flash
(12 rows)

david=# select nextval('tbl_xulie_id_seq');
 nextval 
---------
      27
(1 row)

david=#

复制代码

五、序列函数

下面序列函数，为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法。

函数	返回类型	描述
nextval(regclass)	bigint	递增序列对象到它的下一个数值并且返回该值。这个动作是自动完成的。即使多个会话并发运行nextval，每个进程也会安全地收到一个唯一的序列值。
currval(regclass)	bigint	在当前会话中返回最近一次nextval抓到的该序列的数值。(如果在本会话中从未在该序列上调用过 nextval，那么会报告一个错误。)请注意因为此函数返回一个会话范围的数值，而且也能给出一个可预计的结果，因此可以用于判断其它会话是否执行过nextval。
lastval()	bigint	返回当前会话里最近一次nextval返回的数值。这个函数等效于currval，只是它不用序列名为参数，它抓取当前会话里面最近一次nextval使用的序列。如果当前会话还没有调用过nextval，那么调用lastval将会报错。
setval(regclass, bigint)	bigint	重置序列对象的计数器数值。设置序列的last_value字段为指定数值并且将其is_called字段设置为true，表示下一次nextval将在返回数值之前递增该序列。
setval(regclass, bigint, boolean)	bigint	重置序列对象的计数器数值。功能等同于上面的setval函数，只是is_called可以设置为true或false。如果将其设置为false，那么下一次nextval将返回该数值，随后的nextval才开始递增该序列。

5.1 查看下一个序列值

复制代码

david=# select nextval('tbl_xulie_id_seq');
 nextval 
---------
       3
(1 row)

david=# select nextval('tbl_xulie_id_seq');
 nextval 
---------
       4
(1 row)

david=#

复制代码

5.2 查看序列最近使用值

复制代码

david=# select nextval('tbl_xulie_id_seq');
 nextval 
---------
       4
(1 row)

david=# select currval('tbl_xulie_id_seq');
 currval 
---------
       4
(1 row)

david=# select currval('tbl_xulie_id_seq');
 currval 
---------
       4
(1 row)

david=#

复制代码

5.3 重置序列

方法一：使用序列函数

a. setval(regclass, bigint)

复制代码

david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# select setval('tbl_xulie_id_seq', 1);
 setval 
--------
      1
(1 row)

david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');                  
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');     
INSERT 0 1
david=# select * from tbl_xulie;
 id | name  
----+-------
  2 | Sandy
  3 | David
(2 rows)

david=# select currval('tbl_xulie_id_seq');
 currval 
---------
       3
(1 row)

david=# select nextval('tbl_xulie_id_seq');
 nextval 
---------
       4
(1 row)

david=#

复制代码

b. setval(regclass, bigint, boolean)

b.1 setval(regclass, bigint, true)

复制代码

david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# select setval('tbl_xulie_id_seq', 1, true);
 setval 
--------
      1
(1 row)

david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# select * from tbl_xulie;
 id | name  
----+-------
  2 | Sandy
  3 | David
(2 rows)

david=#

复制代码

效果同a. setval(regclass, bigint)

b.2 setval(regclass, bigint, false)

复制代码

david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# select setval('tbl_xulie_id_seq', 1, false);
 setval 
--------
      1
(1 row)

david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# select * from tbl_xulie;
 id | name  
----+-------
  1 | Sandy
  2 | David
(2 rows)

david=#

复制代码

方法二：修改序列

修改序列的语法：

复制代码

ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ]
    [ RESTART [ [ WITH ] restart ] ]
    [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table.column | NONE } ]
ALTER SEQUENCE name OWNER TO new_owner
ALTER SEQUENCE name RENAME TO new_name
ALTER SEQUENCE name SET SCHEMA new_schema

复制代码

实例：

复制代码

david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# alter sequence tbl_xulie_id_seq restart with 0;
ERROR:  RESTART value (0) cannot be less than MINVALUE (1)
david=# alter sequence tbl_xulie_id_seq restart with 1;
ALTER SEQUENCE
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# select * from tbl_xulie;
 id | name  
----+-------
  1 | David
  2 | Sandy
(2 rows)

david=# select nextval('tbl_xulie_id_seq');
 nextval 
---------
       3
(1 row)

david=#

复制代码

六、删除序列

语法：

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

当有表字段使用到PG序列时，不能直接删除。

复制代码

david=# drop sequence tbl_xulie2_id_seq;
ERROR:  cannot drop sequence tbl_xulie2_id_seq because other objects depend on it
DETAIL:  default for table tbl_xulie2 column id depends on sequence tbl_xulie2_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
david=# drop table tbl_xulie2;
DROP TABLE
david=# drop sequence tbl_xulie2_id_seq;
DROP SEQUENCE
david=#

复制代码

说明：对于序列是由建表时指定serial 创建的，删除该表的同时，对应的序列也会被删除。

PostgreSQL的时间/日期函数使用

PostgreSQL的常用时间函数使用整理如下：

一、获取系统时间函数

1.1 获取当前完整时间

select now();

复制代码

david=# select now();
              now              
-------------------------------
 2013-04-12 15:39:40.399711+08
(1 row)

david=#

复制代码

current_timestamp 同 now() 函数等效。

复制代码

david=# select current_timestamp;
              now              
-------------------------------
 2013-04-12 15:40:22.398709+08
(1 row)

david=#

复制代码

1.2 获取当前日期

select current_date;

复制代码

david=# select current_date;
    date    
------------
 2013-04-12
(1 row)

david=#

复制代码

1.3 获取当前时间

select current_time;

复制代码

david=# select current_time;
       timetz       
--------------------
 15:43:31.101726+08
(1 row)

david=#

复制代码

二、时间的计算

复制代码

david=# select now();
              now              
-------------------------------
 2013-04-12 15:47:13.244721+08
(1 row)

david=#

复制代码

2.1 两年后

复制代码

david=# select now() + interval '2 years';
           ?column?            
-------------------------------
 2015-04-12 15:49:03.168851+08
(1 row)

david=# select now() + interval '2 year'; 
           ?column?            
-------------------------------
 2015-04-12 15:49:12.378727+08
(1 row)

david=# select now() + interval '2 y';   
           ?column?           
------------------------------
 2015-04-12 15:49:25.46986+08
(1 row)

david=# select now() + interval '2 Y';
           ?column?            
-------------------------------
 2015-04-12 15:49:28.410853+08
(1 row)

david=# select now() + interval '2Y'; 
           ?column?            
-------------------------------
 2015-04-12 15:49:31.122831+08
(1 row)

david=#

复制代码

2.2 一个月后

复制代码

david=# select now() + interval '1 month';  
           ?column?           
------------------------------
 2013-05-12 15:51:22.24373+08
(1 row)

david=# select now() + interval 'one month';
ERROR:  invalid input syntax for type interval: "one month"
LINE 1: select now() + interval 'one month';
                                ^
david=#

复制代码

2.3 三周前

复制代码

david=# select now() - interval '3 week';
           ?column?            
-------------------------------
 2013-03-22 16:00:04.203735+08
(1 row)

david=#

复制代码

2.4 十分钟后

复制代码

david=# select now() + '10 min';                 
           ?column?            
-------------------------------
 2013-04-12 16:12:47.445744+08
(1 row)

david=#

复制代码

说明：

interval 可以不写，其值可以是：

Abbreviation	Meaning
Y	Years
M	Months (in the date part)
W	Weeks
D	Days
H	Hours
M	Minutes (in the time part)
S	Seconds

2.5 计算两个时间差

使用 age(timestamp, timestamp)

复制代码

david=# select age(now(), timestamp '1989-02-05');
                  age                   
----------------------------------------
 24 years 2 mons 7 days 17:05:49.119848
(1 row)

david=#

复制代码

david=# select age(timestamp '2007-09-15');       
          age           
------------------------
 5 years 6 mons 27 days
(1 row)

david=#

复制代码

三、时间字段的截取

在开发过程中，经常要取日期的年，月，日，小时等值，PostgreSQL 提供一个非常便利的EXTRACT函数。

EXTRACT(field FROM source)

field 表示取的时间对象，source 表示取的日期来源，类型为 timestamp、time 或 interval。

3.1 取年份

复制代码

david=# select extract(year from now());
 date_part 
-----------
      2013
(1 row)

david=#

复制代码

3.2 取月份

复制代码

david=# select extract(month from now());    
 date_part 
-----------
         4
(1 row)

david=#

复制代码

david=# select extract(day from timestamp '2013-04-13');
 date_part 
-----------
        13
(1 row)

david=#

复制代码

david=# SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
 date_part 
-----------
        40
(1 row)

david=#

复制代码

3.3 查看今天是一年中的第几天

复制代码

david=# select extract(doy from now());
 date_part 
-----------
       102
(1 row)

david=#

复制代码

3.4 查看现在距1970-01-01 00:00:00 UTC 的秒数

复制代码

david=# select extract(epoch from now());
    date_part     
------------------
 1365755907.94474
(1 row)

david=#

复制代码

3.5 把epoch 值转换回时间戳

复制代码

david=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1369755555 * INTERVAL '1 second'; 
        ?column?        
------------------------
 2013-05-28 23:39:15+08
(1 row)

david=#

复制代码

以上是基本的PG时间/日期函数使用，可满足一般的开发运维应用。

PostgreSQL删除重复数据

去重的方法一般是找到重复数据中的一条，以某一唯一条件去掉其他重复值。

Oracle 去重的方法很多，常用的是根据 rowid 进行去重。

PostgreSQL 库如何去除单表重复数据呢？可以通过 ctid 进行，下面是实验过程。

一、创建测试表

david=# create table emp (
david(# id int,
david(# name varchar);
CREATE TABLE
david=#

二、插入测试数据

复制代码

david=# insert into emp values (1, 'david');
INSERT 0 1
david=# insert into emp values (1, 'david');
INSERT 0 1
david=# insert into emp values (1, 'david');
INSERT 0 1
david=# insert into emp values (2, 'sandy');
INSERT 0 1
david=# insert into emp values (2, 'sandy');
INSERT 0 1
david=# insert into emp values (3, 'renee'); 
INSERT 0 1
david=# insert into emp values (4, 'jack');  
INSERT 0 1
david=# insert into emp values (5, 'rose'); 
INSERT 0 1
david=#

复制代码

三、查询初始化数据

复制代码

david=# select ctid, * from emp;
 ctid  | id | name  
-------+----+-------
 (0,1) |  1 | david
 (0,2) |  1 | david
 (0,3) |  1 | david
 (0,4) |  2 | sandy
 (0,5) |  2 | sandy
 (0,6) |  3 | renee
 (0,7) |  4 | jack
 (0,8) |  5 | rose
(8 rows)

david=#

复制代码

查询重复数据数

复制代码

david=# select distinct id, count(*) from emp group by id having count(*) > 1;
 id | count 
----+-------
  1 |     3
  2 |     2
(2 rows)

david=#

复制代码

查询出 id 为1的记录有3条，id 为2的记录有2条。

四、查询要保留的数据

以 min(ctid) 或 max(ctid) 为准。

复制代码

david=# select ctid, * from emp where ctid in (select min(ctid) from emp group by id);
 ctid  | id | name  
-------+----+-------
 (0,1) |  1 | david
 (0,4) |  2 | sandy
 (0,6) |  3 | renee
 (0,7) |  4 | jack
 (0,8) |  5 | rose
(5 rows)

david=#

复制代码

五、删除重复数据

david=# delete from emp where ctid not in (select min(ctid) from emp group by id);
DELETE 3
david=#

六、查看最后结果

复制代码

david=# select ctid, * from emp;
 ctid  | id | name  
-------+----+-------
 (0,1) |  1 | david
 (0,4) |  2 | sandy
 (0,6) |  3 | renee
 (0,7) |  4 | jack
 (0,8) |  5 | rose
(5 rows)

david=#

复制代码

说明：如果表中已经有标明唯一的序列主键值，可以把该值替换上述的ctid直接删除。

七、其他方法

也可以使用以下SQL删除重复数据。

复制代码

david=# delete from emp a
david-# where a.ctid <>
david-# (
david(# select min(b.ctid) from emp b
david(# where a.id = b.id
david(# );
DELETE 3
david=#

复制代码

说明：在表数据量较大的情况下，这种删除方法效率很高。

PostgreSQL 查看数据库，索引，表，表空间大小

一、简介

PostgreSQL 提供了多个系统管理函数来查看表，索引，表空间及数据库的大小，下面详细介绍一下。

二、数据库对象尺寸函数

函数名	返回类型	描述
pg_column_size(any)	int	存储一个指定的数值需要的字节数（可能压缩过）
pg_database_size(oid)	bigint	指定OID的数据库使用的磁盘空间
pg_database_size(name)	bigint	指定名称的数据库使用的磁盘空间
pg_indexes_size(regclass)	bigint	关联指定表OID或表名的表索引的使用总磁盘空间
pg_relation_size(relation regclass, fork text)	bigint	指定OID或名的表或索引，通过指定fork('main', 'fsm' 或'vm')所使用的磁盘空间
pg_relation_size(relation regclass)	bigint	pg_relation_size(..., 'main')的缩写
pg_size_pretty(bigint)	text	Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units
pg_size_pretty(numeric)	text	把以字节计算的数值转换成一个人类易读的尺寸单位
pg_table_size(regclass)	bigint	指定表OID或表名的表使用的磁盘空间，除去索引（但是包含TOAST，自由空间映射和可视映射）
pg_tablespace_size(oid)	bigint	指定OID的表空间使用的磁盘空间
pg_tablespace_size(name)	bigint	指定名称的表空间使用的磁盘空间
pg_total_relation_size(regclass)	bigint	指定表OID或表名使用的总磁盘空间，包括所有索引和TOAST数据

三、实例讲解

3.1 查看存储一个指定的数值需要的字节数

复制代码

david=# select pg_column_size(1);     
 pg_column_size 
----------------
              4
(1 row)

david=# select pg_column_size(10000);
 pg_column_size 
----------------
              4
(1 row)

david=# select pg_column_size('david');
 pg_column_size 
----------------
              6
(1 row)

david=# select pg_column_size('hello,world');
 pg_column_size 
----------------
             12
(1 row)

david=# select pg_column_size('2013-04-18 15:17:21.622885+08');
 pg_column_size 
----------------
             30
(1 row)

david=# select pg_column_size('中国');                         
 pg_column_size 
----------------
              7
(1 row)

david=#

复制代码

3.2 查看数据库大小

查看原始数据

复制代码

david=# \d test
              Table "public.test"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 id        | integer               | 
 name      | character varying(20) | 
 gender    | boolean               | 
 join_date | date                  | 
 dept      | character(4)          | 
Indexes:
    "idx_join_date_test" btree (join_date)
    "idx_test" btree (id)

david=# select count(1) from test;
  count  
---------
 1835008
(1 row)

david=#

复制代码

查看david 数据库大小

复制代码

david=# select pg_database_size('david');
 pg_database_size 
------------------
        190534776
(1 row)

david=#

复制代码

查看所有数据库大小

复制代码

david=# select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;                
  datname  |    size     
-----------+-------------
 template0 |     6513156
 postgres  |     6657144
 jboss     |     6521348
 bugs      |     6521348
 david     |   190534776
 BMCV3     | 28147135608
 mydb      |    10990712
 template1 |     6521348
(8 rows)

david=#

复制代码

这样查出来的结果，看上去太长了，不太容易读数。

3.3 以人性化的方式显示大小

复制代码

david=# select pg_size_pretty(pg_database_size('david'));
 pg_size_pretty 
----------------
 182 MB
(1 row)

david=#

复制代码

3.4 查看单索引大小

复制代码

david=# select pg_relation_size('idx_test');
 pg_relation_size 
------------------
         41238528
(1 row)

david=# select pg_size_pretty(pg_relation_size('idx_test'));
 pg_size_pretty 
----------------
 39 MB
(1 row)

david=#

复制代码

david=# select pg_size_pretty(pg_relation_size('idx_join_date_test'));
 pg_size_pretty 
----------------
 39 MB
(1 row)

david=#

复制代码

3.5 查看指定表中所有索引大小

复制代码

david=# select pg_indexes_size('test');                  
 pg_indexes_size 
-----------------
        82477056
(1 row)

david=# select pg_size_pretty(pg_indexes_size('test'));
 pg_size_pretty 
----------------
 79 MB
(1 row)

david=#

复制代码

idx_test 和idx_join_date_test 两个索引大小加起来差不多等于上面pg_indexes_size() 查询出来的索引大小。

3.6 查看指定schema 里所有的索引大小，按从大到小的顺序排列。

复制代码

david=# select * from pg_namespace;
      nspname       | nspowner |               nspacl                
--------------------+----------+-------------------------------------
 pg_toast           |       10 | 
 pg_temp_1          |       10 | 
 pg_toast_temp_1    |       10 | 
 pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
 information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 public             |       10 | {postgres=UC/postgres,=UC/postgres}
(6 rows)

david=# select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
         indexrelname          | pg_size_pretty 
-------------------------------+----------------
 idx_join_date_test            | 91 MB
 idx_test                      | 91 MB
 testtable_idx                 | 1424 kB
 city_pkey                     | 256 kB
 city11                        | 256 kB
 countrylanguage_pkey          | 56 kB
 sale_pkey                     | 8192 bytes
 track_pkey                    | 8192 bytes
 tbl_partition_201211_joindate | 8192 bytes
 tbl_partition_201212_joindate | 8192 bytes
 tbl_partition_201301_joindate | 8192 bytes
 tbl_partition_201302_joindate | 8192 bytes
 tbl_partition_201303_joindate | 8192 bytes
 customer_pkey                 | 8192 bytes
 album_pkey                    | 8192 bytes
 item_pkey                     | 8192 bytes
 tbl_partition_201304_joindate | 8192 bytes
 tbl_partition_201307_joindate | 8192 bytes
 tbl_partition_201305_joindate | 0 bytes
 tbl_partition_201306_joindate | 0 bytes
(20 rows)

david=#

复制代码

3.7 查看指定表大小

复制代码

david=# select pg_relation_size('test');                
 pg_relation_size 
------------------
         95748096
(1 row)

david=# select pg_size_pretty(pg_relation_size('test'));
 pg_size_pretty 
----------------
 91 MB
(1 row)

david=#

复制代码

使用pg_table_size() 函数查看

复制代码

david=# select pg_table_size('test');                   
 pg_table_size 
---------------
      95789056
(1 row)

david=# select pg_size_pretty(pg_table_size('test'));   
 pg_size_pretty 
----------------
 91 MB
(1 row)

david=#

复制代码

3.8 查看指定表的总大小

复制代码

david=# select pg_total_relation_size('test');       
 pg_total_relation_size 
------------------------
              178266112
(1 row)

david=# select pg_size_pretty(pg_total_relation_size('test'));
 pg_size_pretty 
----------------
 170 MB
(1 row)

david=#

复制代码

3.9 查看指定schema 里所有的表大小，按从大到小的顺序排列。

复制代码

david=# select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
            relname            | pg_size_pretty 
-------------------------------+----------------
 test                          | 91 MB
 testtable                     | 1424 kB
 city                          | 256 kB
 countrylanguage               | 56 kB
 country                       | 40 kB
 testcount                     | 8192 bytes
 tbl_partition_201302          | 8192 bytes
 tbl_partition_201303          | 8192 bytes
 person                        | 8192 bytes
 customer                      | 8192 bytes
 american_state                | 8192 bytes
 tbl_david                     | 8192 bytes
 emp                           | 8192 bytes
 tbl_partition_201212          | 8192 bytes
 tbl_partition_201304          | 8192 bytes
 tbl_partition_error_join_date | 8192 bytes
 tbl_partition_201211          | 8192 bytes
 album                         | 8192 bytes
 tbl_partition_201307          | 8192 bytes
 tbl_xulie                     | 8192 bytes
 tbl_partition_201301          | 8192 bytes
 sale                          | 8192 bytes
 item                          | 8192 bytes
 track                         | 8192 bytes
 tbl_partition_201306          | 0 bytes
 tbl_partition                 | 0 bytes
 tbl_partition_201305          | 0 bytes
 person2                       | 0 bytes
(28 rows)

david=#

复制代码

3.10 查看表空间大小

复制代码

david=# select spcname from pg_tablespace;
  spcname   
------------
 pg_default
 pg_global
(2 rows)

david=# select pg_tablespace_size('pg_default');                
 pg_tablespace_size 
--------------------
        28381579760
(1 row)

david=# select pg_size_pretty(pg_tablespace_size('pg_default'));
 pg_size_pretty 
----------------
 26 GB
(1 row)

david=#

复制代码

另一种查看方法：

复制代码

david=# select pg_tablespace_size('pg_default')/1024/1024 as "SIZE M";     
 SIZE M 
--------
  27066
(1 row)

david=# select pg_tablespace_size('pg_default')/1024/1024/1024 as "SIZE G"; 
 SIZE G 
--------
     26
(1 row)

david=#

PostgreSQL 角色管理

一、角色与用户的区别

角色就相当于岗位：角色可以是经理，助理。

用户就是具体的人：比如陈XX经理，朱XX助理，王XX助理。

在PostgreSQL 里没有区分用户和角色的概念，"CREATE USER" 为 "CREATE ROLE" 的别名，这两个命令几乎是完全相同的，唯一的区别是"CREATE USER" 命令创建的用户默认带有LOGIN属性，而"CREATE ROLE" 命令创建的用户默认不带LOGIN属性(CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not)。

1.1 创建角色与用户

CREATE ROLE 语法

复制代码

CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

复制代码

创建david 角色和sandy 用户

复制代码

postgres=# CREATE ROLE david;　　//默认不带LOGIN属性
CREATE ROLE
postgres=# CREATE USER sandy;　　//默认具有LOGIN属性
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 david     | Cannot login                                   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}

postgres=# 
postgres=# SELECT rolname from pg_roles ;
 rolname  
----------
 postgres
 david
 sandy
(3 rows)

postgres=# SELECT usename from pg_user;         //角色david 创建时没有分配login权限，所以没有创建用户
 usename  
----------
 postgres
 sandy
(2 rows)

postgres=#

复制代码

1.2 验证LOGIN属性

复制代码

postgres@CS-DEV:~> psql -U david
psql: FATAL:  role "david" is not permitted to log in
postgres@CS-DEV:~> psql -U sandy
psql: FATAL:  database "sandy" does not exist
postgres@CS-DEV:~> psql -U sandy -d postgres
psql (9.1.0)
Type "help" for help.

postgres=> \dt
No relations found.
postgres=>

复制代码

用户sandy 可以登录，角色david 不可以登录。

1.3 修改david 的权限，增加LOGIN权限

复制代码

postgres=# ALTER ROLE david LOGIN ;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}

postgres=# SELECT rolname from pg_roles ;
 rolname  
----------
 postgres
 sandy
 david
(3 rows)

postgres=# SELECT usename from pg_user;　　//给david 角色分配login权限，系统将自动创建同名用户david
 usename  
----------
 postgres
 sandy
 david
(3 rows)

postgres=#

复制代码

1.4 再次验证LOGIN属性

复制代码

postgres@CS-DEV:~> psql -U david -d postgres
psql (9.1.0)
Type "help" for help.

postgres=> \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}

postgres=>

复制代码

david 现在也可以登录了。

二、查看角色信息

psql 终端可以用\du 或\du+ 查看，也可以查看系统表 select * from pg_roles;

复制代码

postgres=> \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 david     | Cannot login                                   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}

postgres=> \du+
                                    List of roles
 Role name |                   Attributes                   | Member of | Description 
-----------+------------------------------------------------+-----------+-------------
 david     | Cannot login                                   | {}        | 
 postgres  | Superuser, Create role, Create DB, Replication | {}        | 
 sandy     |                                                | {}        | 

postgres=> SELECT * from pg_roles;
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig |  oid  
----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------+-----------+-------
 postgres | t        | t          | t             | t           | t            | t           | t              |           -1 | ********    |               |           |    10
 david    | f        | t          | f             | f           | f            | f           | f              |           -1 | ********    |               |           | 49438
 sandy    | f        | t          | f             | f           | f            | t           | f              |           -1 | ********    |               |           | 49439
(3 rows)

postgres=>

复制代码

三、角色属性（Role Attributes）

一个数据库角色可以有一系列属性，这些属性定义了他的权限。

属性	说明
login	只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。
superuser	数据库超级用户
createdb	创建数据库权限
createrole	允许其创建或删除其他普通的用户角色(超级用户除外)
replication	做流复制的时候用到的一个用户属性，一般单独设定。
password	在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关
inherit	用户组对组员的一个继承标志，成员可以继承用户组的权限特性
...	...

四、创建用户时赋予角色属性

从pg_roles 表里查看到的信息，在上面创建的david 用户时，默认没有创建数据库等权限。

复制代码

postgres@CS-DEV:~> psql -U david -d postgres
psql (9.1.0)
Type "help" for help.

postgres=> \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}

postgres=> CREATE DATABASE test;
ERROR:  permission denied to create database
postgres=>

复制代码

如果要在创建角色时就赋予角色一些属性，可以使用下面的方法。

首先切换到postgres 用户。

4.1 创建角色bella 并赋予其CREATEDB 的权限。

复制代码

postgres=# CREATE ROLE bella CREATEDB ;
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB, Cannot login                        | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}

postgres=#

复制代码

4.2 创建角色renee 并赋予其创建数据库及带有密码登录的属性。

复制代码

postgres=# CREATE ROLE renee CREATEDB PASSWORD 'abc123' LOGIN;
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB, Cannot login                        | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create DB                                      | {}
 sandy     |                                                | {}

postgres=#

复制代码

4.3 测试renee 角色

a. 登录

postgres@CS-DEV:~> psql -U renee -d postgres
psql (9.1.0)
Type "help" for help.

postgres=>

用renee 用户登录数据库，发现不需要输入密码既可登录，不符合实际情况。

b. 查找原因

在角色属性中关于password的说明，在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关。

查看pg_hba.conf 文件，发现local 的METHOD 为trust，所以不需要输入密码。

将local 的METHOD 更改为password，然后保存重启postgresql。

c. 再次验证

提示输入密码，输入正确密码后进入到数据库。

d. 测试创建数据库

创建成功。

五、给已存在用户赋予各种权限

使用ALTER ROLE 命令。

ALTER ROLE 语法：

复制代码

ALTER ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'

ALTER ROLE name RENAME TO new_name

ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE name [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE name [ IN DATABASE database_name ] RESET ALL

复制代码

5.1 赋予bella 登录权限

a. 查看现在的角色属性

复制代码

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB, Cannot login                        | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create DB                                      | {}
 sandy     |                                                | {}

postgres=#

复制代码

b. 赋予登录权限

复制代码

postgres=# ALTER ROLE bella WITH LOGIN;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create DB                                      | {}
 sandy     |                                                | {}

postgres=#

复制代码

5.2 赋予renee 创建角色的权限

复制代码

postgres=# ALTER ROLE renee WITH CREATEROLE;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}

postgres=#

复制代码

5.3 赋予david 带密码登录权限

postgres=# ALTER ROLE david WITH PASSWORD 'ufo456';
ALTER ROLE
postgres=#

5.4 设置sandy 角色的有效期

复制代码

postgres=# ALTER ROLE sandy VALID UNTIL '2014-04-24';
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}

postgres=# SELECT * from pg_roles ;
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword |     rolvaliduntil      | rolconfig |  oid  
----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+------------------------+-----------+-------
 postgres | t        | t          | t             | t           | t            | t           | t              |           -1 | ********    |                        |           |    10
 bella    | f        | t          | f             | t           | f            | t           | f              |           -1 | ********    |                        |           | 49440
 renee    | f        | t          | t             | t           | f            | t           | f              |           -1 | ********    |                        |           | 49442
 david    | f        | t          | f             | f           | f            | t           | f              |           -1 | ********    |                        |           | 49438
 sandy    | f        | t          | f             | f           | f            | t           | f              |           -1 | ********    | 2014-04-24 00:00:00+08 |           | 49439
(5 rows)

postgres=#

复制代码

六、角色赋权/角色成员

在系统的角色管理中，通常会把多个角色赋予一个组，这样在设置权限时只需给该组设置即可，撤销权限时也是从该组撤销。在PostgreSQL中，首先需要创建一个代表组的角色，之后再将该角色的membership 权限赋给独立的角色即可。

6.1 创建组角色

复制代码

postgres=# CREATE ROLE father login nosuperuser nocreatedb nocreaterole noinherit encrypted password 'abc123';
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 father    | No inheritance                                 | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}

postgres=#

复制代码

6.2 给father 角色赋予数据库test 连接权限和相关表的查询权限。

复制代码

postgres=# GRANT CONNECT ON DATABASE test to father;
GRANT
postgres=# \c test renee
You are now connected to database "test" as user "renee".
test=> \dt
No relations found.
test=> CREATE TABLE emp (
test(> id serial,
test(> name text);
NOTICE:  CREATE TABLE will create implicit sequence "emp_id_seq" for serial column "emp.id"
CREATE TABLE
test=> INSERT INTO emp (name) VALUES ('david');  
INSERT 0 1
test=> INSERT INTO emp (name) VALUES ('sandy');
INSERT 0 1
test=> SELECT * from emp;
 id | name  
----+-------
  1 | david
  2 | sandy
(2 rows)

test=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | emp  | table | renee
(1 row)

test=> GRANT USAGE ON SCHEMA public to father;
WARNING:  no privileges were granted for "public"
GRANT
test=> GRANT SELECT on public.emp to father;
GRANT
test=>

复制代码

6.3 创建成员角色

test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# CREATE ROLE son1 login nosuperuser nocreatedb nocreaterole inherit encrypted password 'abc123';
CREATE ROLE
postgres=#

这里创建了son1 角色，并开启inherit 属性。PostgreSQL 里的角色赋权是通过角色继承（INHERIT）的方式实现的。

6.4 将father 角色赋给son1

postgres=# GRANT father to son1;
GRANT ROLE
postgres=#

还有另一种方法，就是在创建用户的时候赋予角色权限。

postgres=# CREATE ROLE son2 login nosuperuser nocreatedb nocreaterole inherit encrypted password 'abc123' in role father;
CREATE ROLE
postgres=#

6.5 测试son1 角色

复制代码

postgres=# \c test son1
You are now connected to database "test" as user "son1".
test=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | emp  | table | renee
(1 row)

test=> SELECT * from emp;
 id | name  
----+-------
  1 | david
  2 | sandy
(2 rows)

test=>

复制代码

用renee 角色新创建一张表，再次测试

复制代码

test=> \c test renee
You are now connected to database "test" as user "renee".
test=> CREATE TABLE dept (
test(> deptid integer,
test(> deptname text);
CREATE TABLE
test=> INSERT INTO dept (deptid, deptname) values(1, 'ts');
INSERT 0 1
test=> \c test son1
You are now connected to database "test" as user "son1".
test=> SELECT * from dept ;
ERROR:  permission denied for relation dept
test=>

复制代码

son1 角色只能查询emp 表的数据，而不能查询dept 表的数据，测试成功。

6.6 查询角色组信息

复制代码

test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# 
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 father    | No inheritance                                 | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}
 son1      |                                                | {father}
 son2      |                                                | {father}

postgres=#

复制代码

“ Member of ” 项表示son1 和son2 角色属于father 角色组。

An_angel_of_joy

关注

0
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
PostgreSQL学习笔记12 －end

PostgreSQL学习笔记（十二）数据库维护一、恢复磁盘空间　　在PostgreSQL中，使用delete和update语句删除或更新的数据行并没有被实际删除，而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期。因此当数据表中的数据变化极为频繁时，那么在一段时间之后该表所占用的空间将会变得很大，然而数据量却可能变化不大。要解决该问题，需要定期对数据变化频繁的数据表执行VAC...
复制链接

扫一扫