1.数据库逻辑结构介绍

在一个PostgreSQL数据库系统中，

• 数据库： 一个PostgreSQL数据库服务下可以管理多个数据库，当应用连接到一个数据库时，一般只能访问这个数据库中的数据，而不能访问其他数据库中的内容（除非使用dblink等其他手段）
• 表、索引：一个数据库中有很多表、索引。一般，在PostgreSQL中表的术语为"Relation",而在其他数据库中则叫"Table"
• 数据行：每张表中有很多行数据。在PostgreSQL中行的术语一般为"Tuple",而在其他数据库中则叫"Row"

注意: 在PostgreSQL中，一个数据库服务（实例）下可以有多个数据库，而一个数据库不能属于多个实例，这与Oracle数据库不同，在Oracle 数据库中，一个实例只能有一个数据库，但一个数据库可以在多个实例中（如RAC）。

2.创建数据库

创建数据库的语法：

 CREATE DATABASE name
     [ [ WITH ] [ OWNER [=] user_name ]  
            [ TEMPLATE [=] template ]
            [ ENCODING [=] encoding ]
            [ LOCALE [=] locale ]
            [ LC_COLLATE [=] lc_collate ]
            [ LC_CTYPE [=] lc_ctype ]
            [ TABLESPACE [=] tablespace_name ]
            [ ALLOW_CONNECTIONS [=] allowconn ]
            [ CONNECTION LIMIT [=] connlimit ]
            [ IS_TEMPLATE [=] istemplate ] ]

一般情况下，不需要上面那么多参数，最简单的建库命令：

 CREATE DATABASE testdb;

参数说明：

• [ OWNER [=] user_name ] ：指定新建的数据库属于哪个用户，如果不指定就默认属于当前执行命令的用户

• [ TEMPLATE [=] template ]：模板名（从哪个模板创建新数据库）。如果不指定，将使用默认模板template1

• [ ENCODING [=] encoding ]：创建数据库使用的字符编码。
• [ LOCALE [=] locale ]：这是同时设置LC_COLLATE和LC_CTYPE的快捷方式。如果指定此参数，则不能指定其中任何一个参数。
• [ LC_COLLATE [=] lc_collate ]：在新数据库中使用的排序顺序(LC_COLLATE)。这将影响应用于字符串的排序顺序，例如，在使用order BY的查询中，以及在文本列上的索引中使用的顺序。默认情况下使用模板数据库的排序顺序。参见下面的附加限制。
• [ LC_CTYPE [=] lc_ctype ]：字符分类(LC_CTYPE)在新数据库中使用。这将影响字符的分类，例如，小写、大写和数字。默认情况下使用模板数据库的字符分类。参见下面的附加限制。
• [ TABLESPACE [=] tablespace_name ]：指定和新数据库关联的表空间名字
• [ CONNECTION LIMIT [=] connlimit ]：数据库可以接受多少并发的连接。默认为-1，表示没有限制。

在日常使用中，很少会用到指定数据库的字符集。因为PostgreSQL数据库服务端并不支持通常汉字字符集"GBK"、"GB18030",所以一般都用"UTF8"字符集来支持中文的。

建库例子：

1.创建一个LATIN1编码的数据库
postgres=# CREATE DATABASE testdb01 ENCODING 'LATIN1' TEMPLATE template0;
ERROR:  encoding "LATIN1" does not match locale "en_US.UTF-8"
DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".
注意，上面的命令中需要指定模板数据库为template0，而不是template1.这是因为编码和区域设置必须与模板数据库的相匹配，如果模板数据库中包含了与新建的数据库之编码不匹配的数据，或者包含了排序受LC_COLLATE和LC_CTYPE影响的索引，那么复制这些数据将会导致数据库被新设置破坏。template0公认不包含任何字符集编码或排序影响的数据或索引，故可以作为创建任意字符集数据库的模板。
postgres=# CREATE DATABASE testdb01 ENCODING 'LATIN1' LC_COLLATE='aa_DJ.iso88591' LC_CTYPE='aa_DJ.iso88591' TEMPLATE template0;


查询字符集支持的 LC_COLLATE 和 LC_CTYPE 信息
postgres=# select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation ;
  encoding  |        collname        |      collcollate      |       collctype
------------+------------------------+-----------------------+-----------------------
            | default                |                       |
            | C                      | C                     | C
            | POSIX                  | POSIX                 | POSIX
 UTF8       | ucs_basic              | C                     | C
 LATIN1     | aa_DJ                  | aa_DJ                 | aa_DJ
 LATIN1     | aa_DJ.iso88591         | aa_DJ.iso88591        | aa_DJ.iso88591
 UTF8       | aa_DJ.utf8             | aa_DJ.utf8            | aa_DJ.utf8
 UTF8       | aa_ER                  | aa_ER                 | aa_ER
 UTF8       | aa_ER@saaho            | aa_ER@saaho           | aa_ER@saaho
 UTF8       | aa_ER.utf8             | aa_ER.utf8            | aa_ER.utf8
 UTF8       | aa_ER.utf8@saaho       | aa_ER.utf8@saaho      | aa_ER.utf8@saaho
 UTF8       | aa_ET                  | aa_ET                 | aa_ET
 UTF8       | aa_ET.utf8             | aa_ET.utf8            | aa_ET.utf8

2.创建数据库sals,指定用户salesapp,表空间salesspace
postgres=# CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;

3.创建不同区域和字符编码的数据库
postgres=# CREATE DATABASE music2 LOCALE 'sv_SE.iso885915' ENCODING LATIN9 TEMPLATE template0;
LOCALE和ENCODING必须匹配，否则会报错。
LOCALE名称特定于操作系统，不同的操作系统命令可能不一样。

4.创建数据库korean,编码为EUC_KR ，排序顺序和字符类型必须匹配。
postgres=# CREATE DATABASE korean WITH ENCODING 'EUC_KR' LC_COLLATE='ko_KR.euckr' LC_CTYPE='ko_KR.euckr' TEMPLATE=template0;
注意，ENCODING，LC_COLLATE，LC_CTYPE三个参数需要你保持一致，因为不一致可能会由于编码字符集不匹配而导致乱码等问题；

5.创建支持中文的数据库
postgres=# CREATE DATABASE chinese WITH ENCODING 'UTF8';

查看创建的数据库
postgres=# \l
                                      List of databases
   Name    |  Owner   | Encoding |     Collate     |      Ctype      |   Access privileges
-----------+----------+----------+-----------------+-----------------+-----------------------
 chinese   | postgres | UTF8     | en_US.UTF-8     | en_US.UTF-8     |
 korean    | postgres | EUC_KR   | ko_KR.euckr     | ko_KR.euckr     |
 music2    | postgres | LATIN9   | sv_SE.iso885915 | sv_SE.iso885915 |
 postgres  | postgres | UTF8     | en_US.UTF-8     | en_US.UTF-8     |
 template0 | postgres | UTF8     | en_US.UTF-8     | en_US.UTF-8     | =c/postgres          +
           |          |          |                 |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8     | en_US.UTF-8     | =c/postgres          +
           |          |          |                 |                 | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8     | en_US.UTF-8     |
 testdb01  | postgres | LATIN1   | aa_DJ.iso88591  | aa_DJ.iso88591  |
(8 rows)

3.修改数据库

修改数据库语法：

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

ALTER DATABASE name RENAME TO new_name

ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

例子:

1.改变数据库testdb01的最大连接数为10
postgres=# alter database testdb01 CONNECTION LIMIT 10;

2.改变数据库testdb01的名称为prodb01
postgres=# alter database testdb01 rename to prodb01;
ALTER DATABASE
postgres=# \l
                                      List of databases
   Name    |  Owner   | Encoding |     Collate     |      Ctype      |   Access privileges
-----------+----------+----------+-----------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8     | en_US.UTF-8     |
 prodb01   | postgres | LATIN1   | aa_DJ.iso88591  | aa_DJ.iso88591  |
 template0 | postgres | UTF8     | en_US.UTF-8     | en_US.UTF-8     | =c/postgres          +
           |          |          |                 |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8     | en_US.UTF-8     | =c/postgres          +
           |          |          |                 |                 | postgres=CTc/postgres

3.关闭testdb01上的默认索引扫描
postgres=# alter database testdb01 set enable_indexscan TO off;

4.清除所有的数据库设置
postgres=# alter database testdb01 reset all;

4.删除数据库

1.直接删除数据库
postgres=# drop database prodb01;
DROP DATABASE

2.如果数据库存在，则将其删除，如果不存在，使用删除命令也不报错
postgres=# drop database if exists prodb01;
NOTICE:  database "prodb01" does not exist, skipping
DROP DATABASE

注意：如果还有人连接在这个数据库上，将不能删除该数据库
postgres=# drop database music2;
ERROR:  database "music2" is being accessed by other users
DETAIL: there is 1 other session using the database.

5.常见问题

1. 不能在事务块中创建或删除数据库
postgres=# begin;
BEGIN
postgres=*# create database testdb01;
ERROR:  CREATE DATABASE cannot run inside a transaction block
postgres=!# drop database chinese;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

2.可以在事务块中修改数据库
postgres=*# alter database music2 rename to music3;
ALTER DATABASE
postgres=*# \l
                                      List of databases
   Name    |  Owner   | Encoding |     Collate     |      Ctype      |   Access privileges
-----------+----------+----------+-----------------+-----------------+-----------------------
 chinese   | postgres | UTF8     | en_US.UTF-8     | en_US.UTF-8     |
 korean    | postgres | EUC_KR   | ko_KR.euckr     | ko_KR.euckr     |
 music3    | postgres | LATIN9   | sv_SE.iso885915 | sv_SE.iso885915 |
 
postgres=*# rollback;
ROLLBACK
postgres=# \l
                                      List of databases
   Name    |  Owner   | Encoding |     Collate     |      Ctype      |   Access privileges
-----------+----------+----------+-----------------+-----------------+-----------------------
 chinese   | postgres | UTF8     | en_US.UTF-8     | en_US.UTF-8     |
 korean    | postgres | EUC_KR   | ko_KR.euckr     | ko_KR.euckr     |
 music2    | postgres | LATIN9   | sv_SE.iso885915 | sv_SE.iso885915 |