创建PostgreSQL数据库最佳实践

1 创建数据库

本文将介绍购买云数据库RDS实例后，如何通过数据管理服务DAS或命令行创建数据库，以及创建数据库过程中的注意事项。

1.1 数据库命名规范

postgresql的关键字长度不能超过63个字节，因此建议业务侧的数据库名的长度不超过30个字符；

数据库命名建议仅使用小写字母、下划线、数字这三类字符。禁止以pg、数字、下划线开头，禁止使用保留字，保留字请参考官方文档。

1.2 通过DAS图形化向导创建数据库

步骤 1 登录管理控制台。

步骤 2 单击管理控制台左上角，选择区域和项目。

步骤 3 选择“数据库 > 云数据库 RDS”。进入云数据库 RDS信息页面。

步骤 4 “实例管理”页面，选择目标实例，单击操作列的“登录”，进入数据管理服务实例登录界面。

您也可以在“实例管理”页面，单击目标实例名称，在页面右上角，单击“登录”，进入数据管理服务实例登录界面。

步骤 5 正确输入数据库用户名和密码，单击“登录”，即可进入您的数据库并进行管理。

步骤 6 进入首页，单击“新建数据库”，在弹出框填写数据库名称、字符集、数据库模板、Collate、Ctype等，详细信息请参考1.3节 参数说明。

步骤 7 确认填写信息正确后，单击“确定”。

1.3 通过命令行创建数据库

创建数据库时，您可以指定模板库，并为每个数据库设置不同的字符集、本地化collate等属性。

通过数据管理服务DAS查询窗口、psql或者pgadmin等工具连接到数据库（本章节以数据管理服务DAS查询窗口为例），使用命令行示例来介绍如何通过CREATE DATABASE命令设置。

本土化信息collate包括LC_COLLATE（字符排序规则） 和 LC_CTYPE（字符分类）,详细介绍请参考官方文档。

语法格式

CREATE DATABASE name

[ [ WITH ] [ OWNER [=] user_name ]

[ TEMPLATE [=] template ]

[ ENCODING [=] encoding ]

[ LC_COLLATE [=] lc_collate ]

[ LC_CTYPE [=] lc_ctype ]

[ TABLESPACE [=] tablespace_name ]

[ ALLOW_CONNECTIONS [=] allowconn ]

[ CONNECTION LIMIT [=] connlimit ]

[ IS_TEMPLATE [=] istemplate ] ]

操作步骤

步骤 1 登录管理控制台。

步骤 2 单击管理控制台左上角的，选择区域和项目。

步骤 3 选择“数据库 > 云数据库 RDS”。进入云数据库 RDS信息页面。

步骤 4 在“实例管理”页面，选择目标实例，单击操作列的“登录”，进入数据管理服务登录界面。

步骤 5 正确输入数据库用户名和密码，单击“登录”，即可进入您的数据库。

步骤 6 在“SQL窗口”输入命令创建数据库。

create database 数据库名;

参数说明

TEMPLATE

PostgreSQL数据库默认有template0、template1两个模板，默认模板为template1，使用template1模板库建库时不可指定新的字符集，否则会报错。用户也可以指定其他的自定义模板创建数据库。

ENCODING

创建数据库时可以通过WITH ENCODING指定字符集，字符集含义及支持的字符集类型请参考官方文档。

LC_COLLATE

字符排序规则，默认en_US.utf8。

不同的排序规则下，相同字符串的比较其结果可能是不同的。

例如，在en_US.utf8下， SELECT 'a'>'A'; 执行结果为false，但在'C'下，SELECT 'a'>'A'; 结果为true。如果数据库从“O”迁移到PostgreSQL，数据库排序集需使用'C'才能得到一致的预期。支持的排序规则可以查询系统表 pg_collation。

LC_CTYPE

字符集中的字符分类，用来区分字母、数字、及大小写等，支持的字符分类可以查询系统表 pg_collation。

其余参数请参考官方文档，此章节不再赘述。

参数使用方式

通过TEMPLATE指定数据库模板

使用 template1 模板库建库时不可指定新的字符集和本地化collate属性，collate属性请参见▪1.3节 设置数据库的本土化信息（collate）。

CREATE DATABASE my_db WITH TEMPLATE template1 ;

使用template0可指定字符集和本地化collate属性，collate属性请参见▪1.3节 设置数据库的本土化信息（collate）。

CREATE DATABASE my_db WITH ENCODING = 'UTF8' LC_COLLATE ='zh_CN.utf8' LC_CTYPE ='zh_CN.utf8' TEMPLATE = template0 ;

创建数据库时不指定模板，则默认模板为template1。用户也可以指定其他的自定义模板创建数据库。

CREATE DATABASE my_db WITH TEMPLATE = mytemplate;

通过WITH ENCODING指定字符集

CREATE DATABASE my_db WITH ENCODING 'UTF8';

LC_COLLATE和LC_CTYPE

查询字符集支持的LC_COLLATE和LC_CTYPE信息

SELECT pg_encoding_to_char(collencoding) AS encoding,collname,collcollate AS "LC_COLLATE",collctype AS "LC_CTYPE" FROM pg_collation;

encoding为空时，表示当前LC_COLLATE支持所有的字符集。

设置数据库的本土化信息（collate）

执行如下命令，创建一个 LC_COLLATE 和 LC_CTYPE 分别为 zh_CN.utf8 的数据库。

CREATE DATABASE my_db WITH ENCODING = 'UTF8' LC_COLLATE ='zh_CN.utf8' LC_CTYPE ='zh_CN.utf8' TEMPLATE = template0 ;

如果指定的LC_COLLATE与字符集不兼容，则会报如下错误信息。

1. 指定的LC_COLLATE和LC_CTYPE必须与目标字符集兼容，参考▪1.4.1节 查询字符集支持的LC_COLLATE和..查询出的字符集，否则会报错。

2. 目前无法直接通过ALTER DATABASE命令修改已有数据库的LC_COLLATE和LC_CTYPE信息，但可以通过创建新的数据库，然后导出再导入数据的方式进行修改。

1.4 常见问题

1.4.1 如何查看已经创建的数据库以及数据库的字符集、LC_COLLATE和LC_CTYPE信息？

通过psql元命令\ l，可以查看已经创建的数据库。

通过查询系统表pg_database。

1.4.2 创建数据库时报字符集与locale不匹配如何解决？

选择与字符集不匹配的LC_COLLATE创建数据库时会报如下错误。

CREATE DATABASE my_db2 WITH LC_COLLATE ='zh_SG' LC_CTYPE ='zh_SG' ;

解决方法：

查询模板库支持的字符集，查询方法见▪1.4.1节 查询支持的字符集，默认模板库为template1。

查询模板库字符集支持的LC_COLLATE信息，查询方法见▪1.3节 设置数据库的本土化信息（collate）。

修改为与字符集匹配的LC_COLLATE，重新创建数据库。

1.4.3 从“O”迁移到PostgreSQL时如何选择排序规则？

PostgreSQL数据库服务端不支持gbk，默认en_US.utf8。从“O”迁移到PostgreSQL，排序规则选择'C'才能得到一致的预期。

PostgreSQL 云数据库 PostgreSQL 数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。