如何在CloudFormation模板中为AWS RDS PostgreSQL指定LC_COLLATE与LC_CTYPE

AWS RDS PostgreSQL的LC_COLLATE和LC_CTYPE参数无法直接通过CloudFormation的AWS::RDS::DBInstance资源或数据库参数组设置——这两个参数属于实例级区域设置，由RDS创建实例时的底层数据库模板决定，默认值为en_US.UTF-8，且实例创建后无法修改。

不过你可以通过创建自定义数据库的方式，在数据库层面覆盖这两个参数，具体实现步骤如下：

1. 核心思路 #

RDS允许在创建具体数据库时指定LC_COLLATE和LC_CTYPE，覆盖实例级默认值。我们可以在CloudFormation中配合Lambda自定义资源，在RDS实例启动后自动执行创建数据库的SQL命令。

2. CloudFormation模板示例 #

2.1 定义RDS PostgreSQL实例 #

先创建基础的RDS实例资源，配置必要的访问和认证信息：

Resources:
  MyRDSInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      DBInstanceClass: db.t3.micro
      Engine: postgres
      EngineVersion: "15.3"
      MasterUsername: !Ref DBAdminUser
      MasterUserPassword: !Ref DBAdminPassword
      AllocatedStorage: 20
      PubliclyAccessible: false
      VPCSecurityGroups: [!Ref DBSecurityGroup]

2.2 添加Lambda自定义资源执行SQL #

创建Lambda函数和自定义资源，在实例可用后执行创建数据库的命令：

CreateCustomDBFunction:
    Type: AWS::Lambda::Function
    Properties:
      Runtime: python3.11
      Handler: index.lambda_handler
      Role: !GetAtt LambdaExecutionRole.Arn
      Code:
        ZipFile: |
          import boto3
          import psycopg2
          import os
          import cfnresponse

          def lambda_handler(event, context):
              try:
                  if event['RequestType'] == 'Create':
                      # 获取RDS实例端点信息
                      rds = boto3.client('rds')
                      instance = rds.describe_db_instances(DBInstanceIdentifier=os.environ['DB_INSTANCE_ID'])
                      endpoint = instance['DBInstances'][0]['Endpoint']['Address']
                      port = instance['DBInstances'][0]['Endpoint']['Port']

                      # 连接默认postgres数据库，创建自定义数据库
                      conn = psycopg2.connect(
                          host=endpoint,
                          port=port,
                          user=os.environ['DB_USER'],
                          password=os.environ['DB_PASSWORD'],
                          dbname='postgres'
                      )
                      conn.autocommit = True
                      cursor = conn.cursor()
                      # 替换为目标区域设置和数据库名
                      cursor.execute("CREATE DATABASE my_custom_db WITH LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8' OWNER = %s;", (os.environ['DB_USER'],))
                      cursor.close()
                      conn.close()

                  cfnresponse.send(event, context, cfnresponse.SUCCESS, {})
              except Exception as e:
                  print(f"Error: {str(e)}")
                  cfnresponse.send(event, context, cfnresponse.FAILED, {'Error': str(e)})
      Environment:
        Variables:
          DB_INSTANCE_ID: !Ref MyRDSInstance
          DB_USER: !Ref DBAdminUser
          DB_PASSWORD: !Ref DBAdminPassword

  LambdaExecutionRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: "2012-10-17"
        Statement:
          - Effect: Allow
            Principal:
              Service: lambda.amazonaws.com
            Action: sts:AssumeRole
      Policies:
        - PolicyName: RDSPermissions
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: Allow
                Action: rds:DescribeDBInstances
                Resource: !GetAtt MyRDSInstance.Arn
              - Effect: Allow
                Action:
                  - logs:CreateLogGroup
                  - logs:CreateLogStream
                  - logs:PutLogEvents
                Resource: arn:aws:logs:*:*:*

  CustomDBResource:
    Type: AWS::CloudFormation::CustomResource
    Properties:
      ServiceToken: !GetAtt CreateCustomDBFunction.Arn
      DependsOn: MyRDSInstance

2.3 模板参数补充 #

添加必要的参数定义：

Parameters:
  DBAdminUser:
    Type: String
    Default: masteruser
  DBAdminPassword:
    Type: String
    NoEcho: true
  DBSecurityGroup:
    Type: AWS::EC2::SecurityGroup::Id

3. 关键注意事项 #

• 区域设置兼容性：确保指定的LC_COLLATE和LC_CTYPE值在RDS实例底层操作系统中存在（如zh_CN.UTF-8、fr_FR.UTF-8等），RDS PostgreSQL通常支持常见的UTF-8区域设置。
• 权限配置：Lambda执行角色需要rds:DescribeDBInstances权限和CloudWatch日志权限，同时RDS安全组要允许Lambda所在VPC的IP访问数据库端口。
• 不可修改性：数据库创建完成后，LC_COLLATE和LC_CTYPE无法修改，需提前确认业务需求。

内容的提问来源于stack exchange，提问作者Kingston X