Aurora Global Databaseのマイナーバージョンアップとメジャーバージョンアップをやってみた

# シークレットの取得
$ get_secrets_value=$(aws secretsmanager get-secret-value \
    --secret-id AuroraSecret7ACECA7F-GpcIOvXusUVh \
    --region us-east-1 \
    | jq -r .SecretString)

# 環境変数に埋め込み
$ export PGHOST=$(echo "${get_secrets_value}" | jq -r .host)
$ export PGPORT=$(echo "${get_secrets_value}" | jq -r .port)
$ export PGDATABASE=$(echo "${get_secrets_value}" | jq -r .dbname)
$ export PGUSER=$(echo "${get_secrets_value}" | jq -r .username)
$ export PGPASSWORD=$(echo "${get_secrets_value}" | jq -r .password)

# プライマリクラスターに接続
$ psql
psql (15.0, server 14.5)
SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, compression: off)
Type "help" for help.

# 現在のテーブル一覧
testDB=> \dt
Did not find any relations.

# テーブルの作成
testDB=> CREATE TABLE test (
  id integer,
  name varchar(10)
);
CREATE TABLE

# テーブルが作成されたことを確認
testDB-> \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)

# シークレットの取得
$ get_secrets_value=$(aws secretsmanager get-secret-value \
    --secret-id AuroraSecret7ACECA7F-GpcIOvXusUVh \
    --region us-east-1 \
    | jq -r .SecretString)

# 環境変数に埋め込み
$ export PGHOST='db-cluster.cluster-ro-cy14ft6ffvhw.us-east-2.rds.amazonaws.com'
$ export PGPORT=$(echo "${get_secrets_value}" | jq -r .port)
$ export PGDATABASE=$(echo "${get_secrets_value}" | jq -r .dbname)
$ export PGUSER=$(echo "${get_secrets_value}" | jq -r .username)
$ export PGPASSWORD=$(echo "${get_secrets_value}" | jq -r .password)

$ psql
psql (15.0, server 14.5)
SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, compression: off)
Type "help" for help.

testDB=> \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)

    const dbCluster = new cdk.aws_rds.DatabaseCluster(this, "Default", {
      engine: cdk.aws_rds.DatabaseClusterEngine.auroraPostgres({
        version: cdk.aws_rds.AuroraPostgresEngineVersion.VER_14_6,
      }),
      writer: cdk.aws_rds.ClusterInstance.serverlessV2("Instance", {
        allowMajorVersionUpgrade: false,
        autoMinorVersionUpgrade: true,
        enablePerformanceInsights: true,
        parameterGroup: dbParameterGroup14,
        performanceInsightRetention:
          cdk.aws_rds.PerformanceInsightRetention.DEFAULT,
        publiclyAccessible: false,
        instanceIdentifier: "db-instance",
        caCertificate: cdk.aws_rds.CaCertificate.RDS_CA_RDS4096_G1,
      }),
      serverlessV2MaxCapacity: 1.0,
      serverlessV2MinCapacity: 0.5,
      backup: {
        retention: cdk.Duration.days(7),
        preferredWindow: "16:00-16:30",
      },
      cloudwatchLogsExports: ["postgresql"],
      cloudwatchLogsRetention: cdk.aws_logs.RetentionDays.ONE_YEAR,
      clusterIdentifier: "db-cluster",
      copyTagsToSnapshot: true,
      defaultDatabaseName: "testDB",
      deletionProtection: false,
      iamAuthentication: false,
      monitoringInterval: cdk.Duration.minutes(1),
      monitoringRole,
      parameterGroup: dbClusterParameterGroup14,
      preferredMaintenanceWindow: "Sat:17:00-Sat:17:30",
      storageEncrypted: true,
      storageEncryptionKey: cdk.aws_kms.Alias.fromAliasName(
        this,
        "DefaultRdsKey",
        "alias/aws/rds"
      ),
      vpc: props.vpc,
      securityGroups: [props.dbClusterSg],
      subnetGroup,
    });

$ npx cdk diff AuroraStack
Stack AuroraStack
Resources
[~] AWS::RDS::DBCluster Aurora/Default Aurora2CBAB212
 └─ [~] EngineVersion
     ├─ [-] 14.5
     └─ [+] 14.6

14:47:57 | UPDATE_FAILED        | AWS::RDS::DBCluster                         | Aurora/Default
Resource handler returned message: "Cannot upgrade DB cluster 'db-cluster' to engine version 14.6 as global replica(s) are running lower version. P
lease upgrade global replicas first before upgrading the primary member. (Service: Rds, Status Code: 400, Request ID: ff42852d-9c68-41aa-ab6d-69c45
e7cebd8)" (RequestToken: 4b360b42-3049-6b2e-a28a-1383e480d941, HandlerErrorCode: InvalidRequest)

$ npx cdk diff AuroraSecondaryStack
Stack AuroraSecondaryStack
Resources
[~] AWS::RDS::DBCluster Aurora/Default Aurora2CBAB212
 └─ [~] EngineVersion
     ├─ [-] 14.5
     └─ [+] 14.6


✨  Number of stacks with differences: 1

$ npx cdk deploy AuroraSecondaryStack

✨  Synthesis time: 7.58s

AuroraSecondaryStack:  start: Building 2fa1f0da2a231bc9f81703a6a13394cfe4baad06db2023da247e70ccec6ecc5c:<AWSアカウントID>-us-east-2
AuroraSecondaryStack:  success: Built 2fa1f0da2a231bc9f81703a6a13394cfe4baad06db2023da247e70ccec6ecc5c:<AWSアカウントID>-us-east-2
AuroraSecondaryStack:  start: Publishing 2fa1f0da2a231bc9f81703a6a13394cfe4baad06db2023da247e70ccec6ecc5c:<AWSアカウントID>-us-east-2
AuroraSecondaryStack:  success: Published 2fa1f0da2a231bc9f81703a6a13394cfe4baad06db2023da247e70ccec6ecc5c:<AWSアカウントID>-us-east-2
AuroraSecondaryStack: deploying... [1/1]
AuroraSecondaryStack: creating CloudFormation changeset...

 ✅  AuroraSecondaryStack

✨  Deployment time: 409.09s

Stack ARN:
arn:aws:cloudformation:us-east-2:<AWSアカウントID>:stack/AuroraSecondaryStack/66d5f6f0-5b4a-11ee-84c1-0254663157f3

✨  Total time: 416.67s

$ psql
psql (15.0, server 14.6)
SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, compression: off)
Type "help" for help.

testDB=> SELECT * FROM VERSION();
                                                   version
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.6 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 7.5.0, 64-bit
(1 row)

testDB=> SELECT * FROM test;
 id | name
----+------
(0 rows)

testDB=> INSERT INTO test VALUES (1, 'value');
INSERT 0 1

testDB=> SELECT * FROM test;
 id | name
----+-------
  1 | value
(1 row)

testDB=> SELECT * FROM test;
 id | name
----+-------
  1 | value
(1 row)

testDB=> SELECT * FROM aurora_global_db_instance_status();
  server_id  |              session_id              | aws_region | durable_lsn | highest_lsn_rcvd | feedback_epoch | feedback_xmin | oldest_read_view_lsn | visibility_lag_in_msec
-------------+--------------------------------------+------------+-------------+------------------+----------------+---------------+----------------------+------------------------
 db-instance | MASTER_SESSION_ID                    | us-east-2  |   143576716 |                  |                |               |                      |
 db-instance | 91c5f409-dd33-4408-a8a9-ffdc98f6e76b | us-east-1  |   143576707 |        143576716 |              0 |         37026 |            143576704 |                 0
(2 rows)

    // DB Cluster
    const dbCluster = new cdk.aws_rds.DatabaseCluster(this, "Default", {
      engine: cdk.aws_rds.DatabaseClusterEngine.auroraPostgres({
        version: cdk.aws_rds.AuroraPostgresEngineVersion.VER_15_2,
      }),
      writer: cdk.aws_rds.ClusterInstance.serverlessV2("Instance", {
        allowMajorVersionUpgrade: false,
        autoMinorVersionUpgrade: true,
        enablePerformanceInsights: true,
        parameterGroup: dbParameterGroup15,
        performanceInsightRetention:
          cdk.aws_rds.PerformanceInsightRetention.DEFAULT,
        publiclyAccessible: false,
        instanceIdentifier: "db-instance",
        caCertificate: cdk.aws_rds.CaCertificate.RDS_CA_RDS4096_G1,
      }),
      serverlessV2MaxCapacity: 1.0,
      serverlessV2MinCapacity: 0.5,
      backup: {
        retention: cdk.Duration.days(7),
        preferredWindow: "16:00-16:30",
      },
      cloudwatchLogsExports: ["postgresql"],
      cloudwatchLogsRetention: cdk.aws_logs.RetentionDays.ONE_YEAR,
      clusterIdentifier: "db-cluster",
      copyTagsToSnapshot: true,
      defaultDatabaseName: "testDB",
      deletionProtection: false,
      iamAuthentication: false,
      monitoringInterval: cdk.Duration.minutes(1),
      monitoringRole,
      parameterGroup: dbClusterParameterGroup15,
      preferredMaintenanceWindow: "Sat:17:00-Sat:17:30",
      storageEncrypted: true,
      storageEncryptionKey: cdk.aws_kms.Alias.fromAliasName(
        this,
        "DefaultRdsKey",
        "alias/aws/rds"
      ),
      vpc: props.vpc,
      securityGroups: [props.dbClusterSg],
      subnetGroup,
    });

$ npx cdk diff AuroraStack
Stack AuroraStack
Resources
[-] AWS::RDS::DBClusterParameterGroup Aurora/DbClusterParameterGroup14 AuroraDbClusterParameterGroup145F7E6F5D destroy
[-] AWS::RDS::DBParameterGroup Aurora/DbParameterGroup14 AuroraDbParameterGroup146D0E289A destroy
[~] AWS::RDS::DBCluster Aurora/Default Aurora2CBAB212
 ├─ [~] DBClusterParameterGroupName
 │   └─ [~] .Ref:
 │       ├─ [-] AuroraDbClusterParameterGroup145F7E6F5D
 │       └─ [+] AuroraDbClusterParameterGroup150C75B72F
 └─ [~] EngineVersion
     ├─ [-] 14.6
     └─ [+] 15.2
[~] AWS::RDS::DBInstance Aurora/Default/Instance AuroraInstance6F9B6C0C may be replaced
 └─ [~] DBParameterGroupName (may cause replacement)
     └─ [~] .Ref:
         ├─ [-] AuroraDbParameterGroup146D0E289A
         └─ [+] AuroraDbParameterGroup158895769D


✨  Number of stacks with differences: 1

$ npx cdk deploy AuroraStack

✨  Synthesis time: 7.22s

AuroraStack:  start: Building d7578814d56d40bc3891c2ccd49a83dc7a51513106100ab2681e7ffa072813e8:<AWSアカウントID>-us-east-1
AuroraStack:  success: Built d7578814d56d40bc3891c2ccd49a83dc7a51513106100ab2681e7ffa072813e8:<AWSアカウントID>-us-east-1
AuroraStack:  start: Publishing d7578814d56d40bc3891c2ccd49a83dc7a51513106100ab2681e7ffa072813e8:<AWSアカウントID>-us-east-1
AuroraStack:  success: Published d7578814d56d40bc3891c2ccd49a83dc7a51513106100ab2681e7ffa072813e8:<AWSアカウントID>-us-east-1
AuroraStack: deploying... [1/1]
AuroraStack: creating CloudFormation changeset...
15:58:58 | UPDATE_FAILED        | AWS::RDS::DBCluster                         | Aurora2CBAB212
Resource handler returned message: "Major Version Upgrade isn't supported in a single member of a global cluster. Use ModifyGlobalCluster to upgrad
e all the members. (Service: Rds, Status Code: 400, Request ID: 3015e353-f443-4909-82b0-7d1cbaaa4401)" (RequestToken: 3ed74a1f-db85-9c96-e25c-f3fab
f7c6038, HandlerErrorCode: InvalidRequest)

$ psql
psql (15.0, server 15.2)
SSL connection (protocol: TLSv1.2, cipher: AES128-SHA256, compression: off)
Type "help" for help.

testDB=> SELECT * FROM VERSION();
                                                   version
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 9.5.0, 64-bit
(1 row)

testDB=> SELECT * FROM aurora_global_db_instance_status();
  server_id  |    session_id     | aws_region | durable_lsn | highest_lsn_rcvd | feedback_epoch | feedback_xmin | oldest_read_view_lsn | visibility_lag_in_m
sec
-------------+-------------------+------------+-------------+------------------+----------------+---------------+----------------------+--------------------
----
 db-instance | MASTER_SESSION_ID | us-east-2  |   352637429 |                  |                |               |                      |

(1 row)

testDB-> \dt
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
SSL SYSCALL error: EOF detected
The connection to the server was lost. Attempting reset:
Succeeded.
psql (15.0, server 15.2)
SSL connection (protocol: TLSv1.2, cipher: AES128-SHA256, compression: off)
testDB->
testDB->
testDB-> SELECT * FROM VERSION();
ERROR:  syntax error at or near "psql"
LINE 1: psql
        ^
testDB=>
testDB=> SELECT * FROM VERSION();
                                                   version
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 9.5.0, 64-bit
(1 row)

testDB=>
testDB=> SELECT * FROM aurora_global_db_instance_status();
  server_id  |              session_id              | aws_region | durable_lsn | highest_lsn_rcvd | feedback_epoch | feedback_xmin | oldest_read_view_lsn |
visibility_lag_in_msec
-------------+--------------------------------------+------------+-------------+------------------+----------------+---------------+----------------------+-
-----------------------
 db-instance | MASTER_SESSION_ID                    | us-east-2  |   353035703 |                  |                |               |                      |

 db-instance | 6cd5d58f-a94d-4db2-92ea-3d939dcdba5d | us-east-1  |   353035691 |        353035700 |              0 |         53658 |            353035691 |
                    37
(2 rows)

    // DB Cluster
    const dbCluster = new cdk.aws_rds.DatabaseCluster(this, "Default", {
      engine: cdk.aws_rds.DatabaseClusterEngine.auroraPostgres({
        version: cdk.aws_rds.AuroraPostgresEngineVersion.VER_15_2,
      }),
      writer: cdk.aws_rds.ClusterInstance.serverlessV2("Instance", {
        allowMajorVersionUpgrade: false,
        autoMinorVersionUpgrade: true,
        enablePerformanceInsights: true,
        parameterGroup: dbParameterGroup15,
        performanceInsightRetention:
          cdk.aws_rds.PerformanceInsightRetention.DEFAULT,
        publiclyAccessible: false,
        instanceIdentifier: "db-instance",
        caCertificate: cdk.aws_rds.CaCertificate.RDS_CA_RDS4096_G1,
      }),
      serverlessV2MaxCapacity: 1.0,
      serverlessV2MinCapacity: 0.5,
      backup: {
        retention: cdk.Duration.days(7),
        preferredWindow: "16:00-16:30",
      },
      cloudwatchLogsExports: ["postgresql"],
      cloudwatchLogsRetention: cdk.aws_logs.RetentionDays.ONE_YEAR,
      clusterIdentifier: "db-cluster",
      copyTagsToSnapshot: true,
      defaultDatabaseName: "testDB",
      deletionProtection: false,
      iamAuthentication: false,
      monitoringInterval: cdk.Duration.minutes(1),
      monitoringRole,
      parameterGroup: dbClusterParameterGroup15,
      preferredMaintenanceWindow: "Sat:17:00-Sat:17:30",
      storageEncrypted: true,
      storageEncryptionKey: cdk.aws_kms.Alias.fromAliasName(
        this,
        "DefaultRdsKey",
        "alias/aws/rds"
      ),
      vpc: props.vpc,
      securityGroups: [props.dbClusterSg],
      subnetGroup,
    });

$ npx cdk diff AuroraSecondaryStack
Stack AuroraSecondaryStack
Resources
[-] AWS::RDS::DBClusterParameterGroup Aurora/DbClusterParameterGroup14 AuroraDbClusterParameterGroup145F7E6F5D destroy
[-] AWS::RDS::DBParameterGroup Aurora/DbParameterGroup14 AuroraDbParameterGroup146D0E289A destroy
[~] AWS::RDS::DBCluster Aurora/Default Aurora2CBAB212
 ├─ [~] DBClusterParameterGroupName
 │   └─ [~] .Ref:
 │       ├─ [-] AuroraDbClusterParameterGroup145F7E6F5D
 │       └─ [+] AuroraDbClusterParameterGroup150C75B72F
 └─ [~] EngineVersion
     ├─ [-] 14.6
     └─ [+] 15.2
[~] AWS::RDS::DBInstance Aurora/Default/Instance AuroraInstance6F9B6C0C may be replaced
 └─ [~] DBParameterGroupName (may cause replacement)
     └─ [~] .Ref:
         ├─ [-] AuroraDbParameterGroup146D0E289A
         └─ [+] AuroraDbParameterGroup158895769D


✨  Number of stacks with differences: 1

$ npx cdk deploy AuroraSecondaryStack

✨  Synthesis time: 8.86s

AuroraSecondaryStack: deploying... [1/1]
AuroraSecondaryStack: creating CloudFormation changeset...

 ✅  AuroraSecondaryStack

✨  Deployment time: 99.37s

Stack ARN:
arn:aws:cloudformation:us-east-2:<AWSアカウントID>:stack/AuroraSecondaryStack/66d5f6f0-5b4a-11ee-84c1-0254663157f3

✨  Total time: 108.23s

$ psql
psql (15.0, server 15.2)
SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, compression: off)
Type "help" for help.

testDB=> SHOW shared_preload_libraries;
  shared_preload_libraries
-----------------------------
 rdsutils,pg_stat_statements
(1 row)

testDB=> SHOW shared_preload_libraries;
SSL SYSCALL error: EOF detected
The connection to the server was lost. Attempting reset: Succeeded.
psql (15.0, server 15.2)
SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, compression: off)
testDB=>
testDB=> SHOW shared_preload_libraries;
 shared_preload_libraries
--------------------------
 rdsutils,pgaudit
(1 row)