如何创建PostgreSQL数据库：详细步骤指南

发表时间: 2021-02-28 11:46

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.utf82.创建数据库sals,指定用户salesapp,表空间salesspacepostgres=# 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 istemplateALTER DATABASE name RENAME TO new_nameALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }ALTER DATABASE name SET TABLESPACE new_tablespaceALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }ALTER DATABASE name SET configuration_parameter FROM CURRENTALTER DATABASE name RESET configuration_parameterALTER DATABASE name RESET ALL

例子:

1.改变数据库testdb01的最大连接数为10postgres=# alter database testdb01 CONNECTION LIMIT 10;2.改变数据库testdb01的名称为prodb01postgres=# alter database testdb01 rename to prodb01;ALTER DATABASEpostgres=# \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/postgres3.关闭testdb01上的默认索引扫描postgres=# alter database testdb01 set enable_indexscan TO off;4.清除所有的数据库设置postgres=# alter database testdb01 reset all;

4.删除数据库

1.直接删除数据库postgres=# drop database prodb01;DROP DATABASE2.如果数据库存在，则将其删除，如果不存在，使用删除命令也不报错postgres=# drop database if exists prodb01;NOTICE:  database "prodb01" does not exist, skippingDROP DATABASE注意：如果还有人连接在这个数据库上，将不能删除该数据库postgres=# drop database music2;ERROR:  database "music2" is being accessed by other usersDETAIL: there is 1 other session using the database.

5.常见问题

1. 不能在事务块中创建或删除数据库postgres=# begin;BEGINpostgres=*# create database testdb01;ERROR:  CREATE DATABASE cannot run inside a transaction blockpostgres=!# drop database chinese;ERROR:  current transaction is aborted, commands ignored until end of transaction block2.可以在事务块中修改数据库postgres=*# alter database music2 rename to music3;ALTER DATABASEpostgres=*# \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;ROLLBACKpostgres=# \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 |