AWS Aurora Postgres 12 自定义排序规则建库与服务端配置问题

核心概念纠正 #

你遇到的报错本质是混淆了两个不同层级的locale相关概念：

• 操作系统级locale：CREATE DATABASE语句的lc_collate参数仅接受操作系统预装的locale标识，由系统C库或ICU组件提供，不是PostgreSQL内部创建的自定义排序规则对象，直接传入自定义排序规则名ndcoll必然触发invalid locale name报错
• PostgreSQL自定义排序规则对象：通过CREATE COLLATION创建的ndcoll是库级别的数据库对象，仅在创建它的数据库，以及继承该数据库作为模板生成的新库中可用，不能直接赋值给lc_collate参数

1. 使用自定义排序规则创建新数据库的方法 #

按以下步骤操作即可：

1. 先连接到你指定的模板库bar，执行自定义排序规则创建语句，确认pg_collation表中已存在ndcoll条目：

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);
SELECT * FROM pg_collation WHERE collname = 'ndcoll';

2. 创建新数据库时无需指定lc_collate参数，直接基于模板库bar创建即可，新库会自动继承模板库中的所有自定义排序规则：

CREATE DATABASE "foo" WITH TEMPLATE 'bar';

3. 新库创建完成后，可以按需配置排序规则生效范围：

• 字段级生效：建表时给字符类型字段显式指定排序规则

  CREATE TABLE test (content text COLLATE ndcoll);
  

• 库级默认生效：执行以下语句设置当前库的默认排序规则，后续新建的字符字段默认使用该规则

  ALTER DATABASE foo SET default_collation = ndcoll;
  

• 会话级临时生效：当前会话内新建对象默认使用该排序规则

  SET default_collation = ndcoll;
  

2. Aurora PostgreSQL 12 设置服务端全局非确定性排序规则的方法 #

首先明确：Aurora PostgreSQL 集群的全局lc_collate参数在集群初始化时就已固定，无法通过参数组修改，要全局生效自定义非确定性排序规则，按以下步骤操作：

1. 连接到Aurora的默认模板库template1，执行CREATE COLLATION语句创建ndcoll排序规则，后续所有默认基于template1创建的新库都会自带该排序规则对象
2. 进入AWS RDS控制台，找到你的集群绑定的自定义参数组，搜索default_collation参数，将参数值设置为ndcoll
3. 重启Aurora集群使参数配置生效

注意限制：非确定性排序规则（deterministic = false）不支持用于主键、唯一约束、分区键的排序场景，这类场景需要显式指定确定性排序规则，否则会触发报错。

内容的提问来源于stack exchange，提问作者Eshara Mondal