By using AWS re:Post, you agree to the Terms of Use

Questions tagged with Aurora PostgreSQL

Sort by most recent

Browse through the questions and answers listed below or filter and sort to narrow down your results.

Temp space used up in Aurora PostgreSQL whilst generating Index concurrently

I use a Aurora PGSQL cluster ( 4 nodes in total ). The database is partitioned by month, with the largest partition for that table being around 1.3TB of data. One of the columns within the table is a JSONB type. I'm wanting to enable GIN indexing on the column so that I query by fields within the JSONB object. I am creating the GIN Index concurrently as to not affect live traffic. I have been able to create a GIN Index within the QA environment because the data is relatively small. However when I try to create the GIN index within production, the server runs out of temp storage whilst building that index ( [see here](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Managing.html#AuroraPostgreSQL.Managing.TempStorage) for list of temp storage available per node size ). An easy solution for this would be to say 'just scale up the nodes within the cluster', that way there is more temp space to build the GIN index in. This would likely work, but it seems a little overkill. If I scaled the nodes up just to GIN index creation, then scaled them down, I would be in a position whereby there is not currently sufficient hardware to rebuild that GIN Index should it need rebuilding - this seems like a smell in production.. If I scaled the instances up and left them scaled up, the instances would be massively overprovisioned and it would be very expensive. I'm curious as to if there is any workaround for this temp space issue so that I would not have to scale up the servers so drastically. Also if I scaled the servers up, then scaled them down after the GIN indexing completes, would this be risky, is there any reason why the GIN index would have to completely rebuild after the initial build. Thanks
2
answers
0
votes
136
views
asked 2 months ago

Major version upgrade of RDS Aurora Postgres cluster via CloudFormation with custom parameter group fails

I'm trying to upgrade a RDS Aurora cluster via CloudFormation template but it fails with the error `You must explicitly specify a new DB instance parameter group, either default or custom, for the engine version upgrade.`. This error comes from the `DBInstance` (AWS::RDS::DBInstance) `DBParameterGroupName` definition. The CloudFormation template beneath is minimum test template to try out the Blue / Green deployment. It works quite well if I don't specify a `DBParameterGroupName` for the resource `AWS::RDS::DBInstance`. I do not modify the current running parameter, so I don't understand this error message. Is there any solution for this? ```yaml AWSTemplateFormatVersion: '2010-09-09' Parameters: MajorVersionUpgrade: Type: String Description: Swap this between 'Blue' or 'Green' if we are doing a Major version upgrade AllowedValues: - Blue - Green EngineGreen: Description: 'Aurora engine and version' Type: String AllowedValues: - 'aurora-postgresql-10.14' - 'aurora-postgresql-11.16' - 'aurora-postgresql-12.11' - 'aurora-postgresql-13.4' - 'aurora-postgresql-13.7' - 'aurora-postgresql-14.3' EngineBlue: Description: 'Aurora engine and version' Type: String AllowedValues: - 'aurora-postgresql-10.14' - 'aurora-postgresql-11.16' - 'aurora-postgresql-12.11' - 'aurora-postgresql-13.4' - 'aurora-postgresql-13.7' - 'aurora-postgresql-14.3' Mappings: EngineMap: 'aurora-postgresql-10.14': Engine: 'aurora-postgresql' EngineVersion: '10.14' Port: 5432 ClusterParameterGroupFamily: 'aurora-postgresql10' ParameterGroupFamily: 'aurora-postgresql10' 'aurora-postgresql-11.16': Engine: 'aurora-postgresql' EngineVersion: '11.16' Port: 5432 ClusterParameterGroupFamily: 'aurora-postgresql11' ParameterGroupFamily: 'aurora-postgresql11' 'aurora-postgresql-12.11': Engine: 'aurora-postgresql' EngineVersion: '12.11' Port: 5432 ClusterParameterGroupFamily: 'aurora-postgresql12' ParameterGroupFamily: 'aurora-postgresql12' 'aurora-postgresql-13.4': Engine: 'aurora-postgresql' EngineVersion: '13.4' Port: 5432 ClusterParameterGroupFamily: 'aurora-postgresql13' ParameterGroupFamily: 'aurora-postgresql13' 'aurora-postgresql-13.7': Engine: 'aurora-postgresql' EngineVersion: '13.7' Port: 5432 ClusterParameterGroupFamily: 'aurora-postgresql13' ParameterGroupFamily: 'aurora-postgresql13' 'aurora-postgresql-14.3': Engine: 'aurora-postgresql' EngineVersion: '14.3' Port: 5432 ClusterParameterGroupFamily: 'aurora-postgresql14' ParameterGroupFamily: 'aurora-postgresql14' Conditions: BlueDeployment: !Equals [!Ref MajorVersionUpgrade, "Blue"] GreenDeployment: !Equals [!Ref MajorVersionUpgrade, "Green"] Resources: DBClusterParameterGroupGreen: Type: "AWS::RDS::DBClusterParameterGroup" Properties: Description: !Ref 'AWS::StackName' Family: !FindInMap [EngineMap, !Ref EngineGreen, ClusterParameterGroupFamily] Parameters: client_encoding: 'UTF8' DBClusterParameterGroupBlue: Type: "AWS::RDS::DBClusterParameterGroup" Properties: Description: !Ref 'AWS::StackName' Family: !FindInMap [EngineMap, !Ref EngineBlue, ClusterParameterGroupFamily] Parameters: client_encoding: 'UTF8' DBParameterGroupBlue: Type: 'AWS::RDS::DBParameterGroup' Properties: Description: !Ref 'AWS::StackName' Family: !FindInMap [EngineMap, !Ref EngineBlue, ParameterGroupFamily] DBParameterGroupGreen: Type: 'AWS::RDS::DBParameterGroup' Properties: Description: !Ref 'AWS::StackName' Family: !FindInMap [EngineMap, !Ref EngineGreen, ParameterGroupFamily] DBCluster: DeletionPolicy: Snapshot UpdateReplacePolicy: Snapshot Type: 'AWS::RDS::DBCluster' Properties: DatabaseName: 'dbupgradetest' DBClusterParameterGroupName: !If [GreenDeployment, !Ref DBClusterParameterGroupGreen, !Ref DBClusterParameterGroupBlue] Engine: !If [GreenDeployment, !FindInMap [EngineMap, !Ref EngineGreen, Engine], !FindInMap [EngineMap, !Ref EngineBlue, Engine]] EngineMode: provisioned EngineVersion: !If [GreenDeployment, !FindInMap [EngineMap, !Ref EngineGreen, EngineVersion], !FindInMap [EngineMap, !Ref EngineBlue, EngineVersion]] MasterUsername: 'user' MasterUserPassword: 'password123' Port: !If [GreenDeployment, !FindInMap [EngineMap, !Ref EngineGreen, Port], !FindInMap [EngineMap, !Ref EngineBlue, Port]] DBInstance: Type: 'AWS::RDS::DBInstance' Properties: AllowMajorVersionUpgrade: true AutoMinorVersionUpgrade: true DBClusterIdentifier: !Ref DBCluster DBInstanceClass: 'db.t3.medium' # DBParameterGroupName: !If [GreenDeployment, !Ref DBParameterGroupGreen, !Ref DBParameterGroupBlue] # <- this line / definition causes the error Engine: !If [GreenDeployment, !FindInMap [EngineMap, !Ref EngineGreen, Engine], !FindInMap [EngineMap, !Ref EngineBlue, Engine]] ``` Here is an example of the execution order. It only works if `DBParameterGroupName` is not set. ``` aws cloudformation create-stack --parameters ParameterKey=MajorVersionUpgrade,ParameterValue=Blue ParameterKey=EngineBlue,ParameterValue=aurora-postgresql-10.14 ParameterKey=EngineGreen,ParameterValue=aurora-postgresql-11.16 --capabilities CAPABILITY_IAM CAPABILITY_NAMED_IAM --stack-name db-upgrade-test --template-url [path to template] ``` Now switch to version `11.16` by changing the `MajorVersionUpgrade` value from `Blue` to `Green`. Other parameters are not modified. ``` aws cloudformation update-stack --stack-name db-upgrade-test --use-previous-template --parameters ParameterKey=MajorVersionUpgrade,ParameterValue=Green ParameterKey=EngineBlue,ParameterValue=aurora-postgresql-10.14 ParameterKey=EngineGreen,ParameterValue=aurora-postgresql-11.16 --capabilities CAPABILITY_IAM CAPABILITY_NAMED_IAM ``` Now switch to version `12.11` by changing the `MajorVersionUpgrade` value from `Green` to `Blue` and updating the value for `EngineBlue` to `aurora-postgresql-12.11`. ``` aws cloudformation update-stack --stack-name db-upgrade-test --use-previous-template --parameters ParameterKey=MajorVersionUpgrade,ParameterValue=Blue ParameterKey=EngineBlue,ParameterValue=aurora-postgresql-12.11 ParameterKey=EngineGreen,ParameterValue=aurora-postgresql-11.16 --capabilities CAPABILITY_IAM CAPABILITY_NAMED_IAM ```
0
answers
0
votes
51
views
asked 3 months ago