PostgreSQL 学习手册(数据库管理)
一、概述：
数据库可以被看成是 SQL 对象(数据库对象)的命名集合，通常而言，每个数据库对象(表、函数等)只属于一个数据库。不过对于 部分系统表而言，如 pg_database，是属于整个集群的。更准确地说，数据库是模式的集合，而模式包含表、函数等 SQL 对象。因 此完整的对象层次应该是这样的：服务器、数据库、模式、表或其他类型的对象。
在与数据库服务器建立连接时，该连接只能与一个数据库形成关联，不允许在一个会话中进行多个数据库的访问。如以 postgres 用户登录，该用户可以访问的缺省数据库为 postgres，在登录后如果执行下面的 SQL 语句将会收到 PostgreSQL 给出的相关错误信 息。
postgres=# SELECT * FROM MyTest."MyUser".testtables;
ERROR: cross-database references are not implemented: "otherdb.otheruser.sometable"
LINE 1: select * from otherdb.otheruser.sometable
在 PostgreSQL 中，数据库在物理上是相互隔离的，对它们的访问控制也是在会话层次上进行的。然而模式只是逻辑上的对象管 理结构，是否能访问某个模式的对象是由权限系统来控制的。
执行下面的基于系统表的查询语句可以列出现有的数据库集合。
SELECT datname FROM pg_database;
注：psql 应用程序的\l 元命令和-l 命令行选项也可以用来列出当前服务器中已有的数据库。
二、创建数据库：
在 PostgreSQL 服务器上执行下面的 SQL 语句可以创建数据库。
CREATE DATABASE db_name;
在数据库成功创建之后，当前登录角色将自动成为此新数据库的所有者。在删除该数据库时，也需要该用户的特权。如果你想让当 前创建的数据库的所有者为其它角色，可以执行下面的 SQL 语句。
CREATE DATABASE db_name OWNER role_name;
三、修改数据库配置：
PostgreSQL 服务器提供了大量的运行时配置变量，我们可以根据自己的实际情况为某一数据库的某一配置变量指定特殊值，通过 执行下面的 SQL 命令可以使该数据库的某一配置被设置为指定值，而不再使用缺省值。
ALTER DATABASE db_name SET varname TO new_value;
这样在之后基于该数据库的会话中，被修改的配置值已经生效。如果要撤消这样的设置并恢复为原有的缺省值，可以执行下面的 SQL 命令。
ALTER DATABASE dbname RESET varname;
四、删除数据库：
只有数据库的所有者和超级用户可以删除数据库。删除数据库将会删除数据库中包括的所有对象，该操作是不可恢复的。见如下删 除 SQL 命令：
DROP DATABASE db_name;
五、表空间：
在 PostgreSQL 中，表空间表示一组文件存放的目录位置。在创建之后，就可以在该表空间上创建数据库对象。通过使用表空间， 管理员可以控制一个 PostgreSQL 服务器的磁盘布局。这样管理员就可以根据数据库对象的数据量和数据使用频度等参照来规划这些 对象的存储位置，以便减少 IO 等待，从而优化系统的整体运行性能。比如，将一个使用频繁的索引放在非常可靠、高效的磁盘设备 上，如固态硬盘。而将很少使用的数据库对象存放在相对较慢的磁盘系统上。下面的 SQL 命令用于创建表空间。 CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
需要说明的是，表空间指定的位置必须是一个现有的空目录，且属于 PostgreSQL 系统用户，如 postgres。在成功创建之后，所 有在该表空间上创建的对象都将被存放在这个目录下的文件里。
在 PostgreSQL 中只有超级用户可以创建表空间，但是在成功创建之后，就可以允许普通数据库用户在其上创建数据库对象了。 要完成此操作，必须在表空间上给这些用户授予 CREATE 权限。表、索引和整个数据库都可以放在特定的表空间里。见如下 SQL 命 令：
CREATE TABLE foo(i int) TABLESPACE space1;
此外，我们还可以通过修改 default_tablespace 配置变量，以使指定的表空间成为缺省表空间，这样在创建任何数据库对象 时，如果没有显示指定表空间，那么该对象将被创建在缺省表空间中，如：
SET default_tablespace = space1;
CREATE TABLE foo(i int);
与数据库相关联的表空间用于存储该数据库的系统表，以及任何使用该数据库的服务器进程创建的临时文件。
要删除一个空的表空间，可以直接使用 DROP TABLESPACE 命令，然而要删除一个包含数据库对象的表空间，则需要先将该 表空间上的所有对象全部删除后，才可以再在删除该表空间。
要检索当前系统中有哪些表空间，可以执行以下查询，其中 pg_tablespace 为 PostgreSQL 中的系统表。
SELECT spcname FROM pg_tablespace; 我们还可以通过 psql 程序的\db 元命令列出现有的表空间。
PostgreSQL 学习手册(数据库维护)
一、恢复磁盘空间：
在 PostgreSQL 中，使用 delete 和 update 语句删除或更新的数据行并没有被实际删除，而只是在旧版本数据行的物理地址上将 该行的状态置为已删除或已过期。因此当数据表中的数据变化极为频繁时，那么在一段时间之后该表所占用的空间将会变得很大，然 而数据量却可能变化不大。要解决该问题，需要定期对数据变化频繁的数据表执行 VACUUM 操作。
VACUUM 命令存在两种形式，VACUUM 和 VACUUM FULL，它们之间的区别见如下表格：
二、更新规划器统计：
PostgreSQL 查询规划器在选择最优路径时，需要参照相关数据表的统计信息用以为查询生成最合理的规划。这些统计是通过 ANALYZE 命令获得的，你可以直接调用该命令，或者把它当做 VACUUM 命令里的一个可选步骤来调用，如 VACUUM ANAYLYZE table_name，该命令将会先执行 VACUUM 再执行 ANALYZE。与回收空间(VACUUM)一样，对数据更新频繁的表保 持一定频度的 ANALYZE，从而使该表的统计信息始终处于相对较新的状态，这样对于基于该表的查询优化将是极为有利的。然而对 于更新并不频繁的数据表，则不需要执行该操作。
我们可以为特定的表，甚至是表中特定的字段运行 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)、序列、视图、复合类型和一些特殊关系类型的元数据。注意：不是所有 字段对所有对象类型都有意义。 
见如下应用示例： # 查看指定表对象 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 表中都有一行记录。 
见如下应用示例： # 查看指定表中包含的字段名和字段编号。
postgres=# SELECT relname, attname,attnum FROM pg_class c,pg_attribute attr WHERE relname = 'testtable' AND c.oid = attr.attrelid;
relname | attname | attnum
-------------+----------+--------
testtable | tableoid | -7
testtable | cmax | -6
testtable | xmax | -5
testtable | cmin | -4
testtable | xmin | -3
testtable | ctid | -1
testtable | i | 1
(7 rows)
# 只查看用户自定义字段的类型
postgres=# SELECT relname,attname,typname FROM pg_class c,pg_attribute a,pg_type t WHERE c.relname = 'testtable' AND c.oid = attrelid AND atttypid = t.oid AND attnum > 0;
relname | attname | typname
-------------+----------+---------
testtable | i | int4
(7 rows)
三、pg_attrdef:
该系统表主要存储字段缺省值，字段中的主要信息存放在 pg_attribute 系统表中。注意：只有明确声明了缺省值的字段在该表中 才会有记录。 
见如下应用示例：
# 查看指定表有哪些字段存在缺省值，同时显示出字段名和缺省值的定义方式
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，该视图将口令字段填成空白。 
见如下应用示例：
# 从输出结果可以看出口令字段已经被加密。
postgres=# SELECT rolname,rolpassword FROM pg_authid;
rolname | rolpassword
-----------+-------------------------------------
postgres | md5a3556571e93b0d20722ba62be61e8c2d
五、pg_auth_members:
该系统表存储角色之间的成员关系。 
见如下应用示例：
#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 中表对象的检查约束、主键、唯一约束和外键约束。 
七、pg_tablespace:
该系统表存储表空间的信息。注意：表可以放在特定的表空间里，以帮助管理磁盘布局和解决 IO 瓶颈。 
见如下应用示例： #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:
该系统表存储名字空间(模式)。 
见如下应用示例： # 查看当前数据库 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 拷贝，而不是每个数据库一份。 
十、pg_index:
该系统表存储关于索引的一部分信息。其它的信息大多数存储在 pg_class。 
见如下应用示例：
# 查看该索引所在表的名称，以及构成该索引的键值数量和具体键值的字段编号。
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:
该视图提供了对有关数据库中每个表的有用信息地访问。 
二、pg_indexes:
该视图提供对数据库中每个索引的有用信息的访问。 
三、pg_views:
该视图提供了对数据库里每个视图的有用信息的访问途径。 
四、pg_user:
该视图提供了对数据库用户的相关信息的访问。 这个视图只是pg_shadow表的公众可读的部分的视图化，但是不包含口令字段。 
五、pg_roles:
该视图提供访问数据库角色有关信息的接口。这个视图只是 pg_authid 表的公开可读部分的视图化，同时把口令字段用空白填充。 
六、pg_rules:
该视图提供对查询重写规则的有用信息访问的接口。 
七、pg_settings:
该视图提供了对服务器运行时参数的访问。它实际上是 SHOW 和 SET 命令的另外一种方式。它还提供一些用 SHOW 不能直接获 取的参数的访问，比如最大和最小值。 
我们不能对 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. 命令行选项列表： 
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. 命令行选项列表：
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. 命令行选项列表： 
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. 命令行选项列表： 
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. 命令行选项列表： 
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. 命令行选项列表： 
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;
PostgreSQL 学习手册(客户端命令<二>)
七、pg_dump:
pg_dump 是一个用于备份 PostgreSQL 数据库的工具。它甚至可以在数据库正在并发使用时进行完整一致的备份，而不会阻塞 其它用户对数据库的访问。该工具生成的转储格式可以分为两种，脚本和归档文件。其中脚本格式是包含许多 SQL 命令的纯文本格式， 这些 SQL 命令可以用于重建该数据库并将之恢复到生成此脚本时的状态，该操作需要使用 psql 来完成。至于归档格式，如果需要重 建数据库就必须和 pg_restore 工具一起使用。在重建过程中，可以对恢复的对象进行选择，甚至可以在恢复之前对需要恢复的条目 进行重新排序。该命令的使用方式如下：
pg_dump [option...] [dbname]
1. 命令行选项列表： 
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. 命令行选项列表： 
2. 应用示例：
# 先通过 createdb 命令，以 myuser
用户的身份登录，创建带恢复的数据 newdb
/> createdb -U myuser newdb www.linuxidc.com
# 用 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. 常用命令行选项列表： 
2. 命令行选项应用示例：
#-d: 指定连接的数据库。
#-U: 指定连接的用户。
#-c: 后面的 SQL 语句是本次操作需要执行的命令。
/> psql -d posgres -U postgres -c "select * from testtable" www.linuxidc.com
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 等。 
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)
5626
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
