DEV Community: Daniel Chapman [AWS]

Minimising PostgreSQL RDS minor & major upgrade time with Blue/Green deployments

Daniel Chapman [AWS] — Thu, 17 Oct 2024 13:41:10 +0000

In this blog I am going show you how you can minimise downtime when performing minor/major engine upgrades using blue/green deployments.

This blog post assumes that you are familiar with the RDS service.

What is Blue/Green?

Blue/Green is a deployment technique where you create two separate environments. They are identical and replicate one another, you have your current application running against the "Blue" environment and the "Green" environment can be used to perform upgrades, maintenance etc. Once you are happy you simply "switch over" allow you to run your application(s) against what was the "Green" environment

Why would we want to use Blue/Green?

Allows you to perform your minor or major RDS upgrade in-hours which reduces engineering efforts out of hours
Allows you to perform time consuming maintenance such as vacuum or reindex without the application being connected and thus preventing any I/O related performance bottlenecks or without having to take the system offline to perform these
Application upgrades could be tested on a "proper" production environment to reduce the likelihood of issues once live

In this example, my source database is running PostgreSQL 15.8 and I intend to upgrade this to PostgreSQL 16.4 with minimal downtime

Firstly, you need to create a new custom parameter group for the new engine version (if you are performing a major upgrade)

In my example I have created a PostgreSQL 16 RDS parameter group

The CLI can also be used:

 aws rds create-db-parameter-group \ 
--db-parameter-group-name rds-postgresql-v16 \
--db-parameter-group-family postgres16 \ 
--description rds-postgresql-v16

The next step is for you to enable logical_replication on both the source/target parameter group. The target parameter group will be the one you have just created and the source will be what the RDS instance is currently using

The CLI can be used:

aws rds modify-db-parameter-group \
--db-parameter-group-name rds-postgresql-v16 \
--parameters ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot 

aws rds modify-db-parameter-group \
--db-parameter-group-name rds-postgresql-v15 \
--parameters ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot

You will then have to reboot the source RDS instance if this parameter was not already set, as its a static parameter.

aws rds reboot-db-instance \
--db-instance-identifier rds-postgresql

The next step is for you to create a Blue/Green configuration:

Accept the limitations:

Here you can opt to create the deployment using the same engine version as the source instance, or specify a new engine version. In this example I am selecting the same as the source and I'll perform the upgrade manually

Ensure that you select the correct parameter group for the configuration to use and once you are happy select create

You are then presented with the below

Depending how large your dataset is, this stage could take a while to complete

The CLI can also be used:

aws rds create-blue-green-deployment \ 
--blue-green-deployment-name rds-postgresql-blue-green-v15-to-v16 \
--source-arn arn:aws:rds:eu-west-1:123456789:db:rds-postgresql \
--target-db-parameter-group-name rds-postgresql-v15 \
--target-db-instance-class db.m7g.large

Once the deployment has created this is what you are left with:

You are now in a position to work on the "Green" instance, this could be maintenance such as VACUUM FULL/REINDEX or it could be an application upgrade etc.

I am going to upgrade this instance to the latest version of RDS PostgreSQL

From here, navigate to the modify the "Green" instance and select the new RDS engine version and the new target parameter group. Once happy, modify the instance

The CLI can also be used:

aws rds modify-db-instance \
--db-instance-identifier rds-postgresql-green-7fh0sf \
--engine-version 16.4 \
--db-parameter-group-name rds-postgresql-v16 \
--allow-major-version-upgrade \
--apply-immediately

You can now see that the instance is now upgrading

The beauty of Blue/Green is that whilst this maintenance is occurring your application is still happily running against the Blue instance and is serving traffic as normal.
It essentially allows you to perform the upgrade ahead of time and allows you to 'extend' any maintenance windows you may have.

Once the upgrade has completed, you can see that the "Green" instance here is now running PostgreSQL 16 and my "Blue" instance is still running PostgreSQL 15.

From here, you can connect to the "Green" instance and run any recommended post steps such as ANALYSE, VACUUM, REINDEX, EXTENSION UPGRADES etc

This instance will stay in sync with your "Blue" instance, allowing you to leave this instance here now CDC'ing until you are able to take the outage to "switch over" these instances

Once you are in a position to now "switch over" the process will be

Stop/pause the application(s)
Switch over the RDS instances.
Start/resume the application(s)

On this example I don't have any applications connected so I will not be performing these stages

To switch over the RDS instances the process is as follows:

In the RDS console, select the Blue/Green Deployment. Click actions, switch over

You specify a timeout setting, this is the amount of time the switch over has to complete in before it classes it as a fail and rolls back. I am leaving it here at the default

The CLI can also be used:

aws rds switchover-blue-green-deployment \
--blue-green-deployment-identifier rds-postgresql-blue-green-v15-to-v16 \
--switchover-timeout 300

Switchover actions:

The instances will change to 'switching over' status

As you can see now, the instance that the upgrade was performed on is now labelled as 'New Blue'

You are now in a position to connect your application(s) and test

The 'Old-Blue' instance will stay there until you manually remove, so it is important to remove this instance once you are happy and will not be rolling back - As this will be costing you money to keep hold of

Rollback - if required
If you find yourself in a position where you need to rollback to the previous instance you will need to do the following:

Delete the Blue/Green deployment
Rename the instances so that they match the previous endpoint
Or switch the route53 DNS address for your application to point to the 'Old Blue' instance

Once happy, you can remove the previous instance and the Blue/Green deployment to leave you with just one RDS instance running the new version of RDS PostgreSQL

Congratulations - you've managed perform a major RDS upgrade whilst minimising downtime

Conclusion

RDS Blue/Green deployments offer a efficient way to perform RDS upgrades (minor/major), applying any hardware intense maintenance, parameter changes, application changes without impacting the current instance that is serving production. This enhances security and reliability and allows you to be fully confident in the changes you're proposing before deploying to production.

I hope this blog post helped, and happy building

Feel free to connect

https://www.linkedin.com/in/daniel-chapman-a753b56b/

Migrating from SQLServer to Aurora PostgreSQL

Daniel Chapman [AWS] — Fri, 27 Sep 2024 11:05:12 +0000

In this blog I am going show you how to migrate from SQLServer to Aurora PostgreSQL using the Babelfish extension.

This blog post assumes that you are familiar with the RDS service.

The first thing you need to do is to create an Aurora PostgreSQL custom parameter group with the babelfish setting
"rds.babelfish_status" set to on.

I then created a new security group that allowed connections to my VPC range for ports 1433 & 5432. This will allow me and the DMS service to be able to connect to both endpoints.

From here, I created an Aurora PostgreSQL cluster using this parameter group. I have opted to use Serverless V2 but this is not mandatory. Once your cluster has created, you should see that the two endpoints available (writer/reader) are listening on both TCP 5432 & 1433

You then need to create two IAM roles for the DMS service to use.
These are: dms-vpc-role and dms-cloudwatch-logs-role

arn:aws:iam::<accountnumber>:role/dms-cloudwatch-logs-role
arn:aws:iam::<accountnumber>:role/dms-vpc-role

They need to be set up as the exact ARNs mentioned above, otherwise the DMS service will not recognise them

https://docs.aws.amazon.com/dms/latest/userguide/security-iam.html

Navigate to the DMS service.
From here, navigate to "Subnet groups"

You then need to create a subnet group for the DMS replication server to run out of. This needs to be a CIDR range that can communicate to your source (SQLServer) instance and your target (APG)

https://docs.aws.amazon.com/dms/latest/userguide/subnet-group.html

The CLI can also be used:

aws dms create-replication-subnet-group \
--replication-subnet-group-identifier SQLServerToAPG \ 
--replication-subnet-group-description SQLServerToAPG \ 
--subnet-ids <SUBNETA> <SUBNETB> <SUBNETC> >/dev/null

You are then in a position to create the DMS replication instance

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReplicationInstance.Creating.html

The CLI can also be used:

aws dms create-replication-instance \
--replication-instance-identifier SQLServerToAuroraPostgreSQL \ 
--allocated-storage 50 \ 
--replication-instance-class dms.t3.medium \ 
--vpc-security-group-ids <SG ID created above> \
--no-multi-az \
--replication-subnet-group-identifier <Subnet Group created above> \
--engine-version 3.5.3 \
--no-publicly-accessible >/dev/null

The next step is to create both source and target DMS endpoints

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Endpoints.Creating.html

The CLI can also be used:

Source:

aws dms create-endpoint \
--endpoint-identifier SQLServer-source \
--endpoint-type source \
--engine-name sqlserver \
--ssl-mode require \
--username <username> \
--server-name sqlserversourcerds.abcdef.eu-west-1.rds.amazonaws.com \
--password <password of the SQLServer instance> \
--port 1433 \
--database-name <Local Database you wish to migrate> >/dev/null

Target:

aws dms create-endpoint \
--endpoint-identifier APG-Target \
--endpoint-type target \
--engine-name aurora-postgresql \
--ssl-mode require \
--username <username> \
--server-name aurorapostgresqltarget.cluster-abcdef.eu-west-1.rds.amazonaws.com \
--password <password of the APG cluster> \
--port 5432 \
--database-name babelfish_db \
--postgre-sql-settings '{"MaxFileSize": 512}','{"ExecuteTimeout": 3600}'>/dev/null

You then need to test the endpoints to ensure that the replication instance can communicate to both the source/target endpoints

https://docs.aws.amazon.com/dms/latest/sbs/chap-mariadb2auroramysql.testendpoints.html

From here, connect to the source SQLServer instance. I am using SSMS in this example, but any tooling will work.

The dataset was populated using this document

https://www.sqlservercentral.com/articles/generating-dummy-data

This creates some dummy tables and populates a dataset

We need to gather the DDL from this instance next
In SSMS, select the database you're wanting to export, tasks, generate scripts.

Ensure 'script the entire database' is selected, also ensure 'all database objects' is ticked.

On the next page, under advanced ensure that 'Script-Full-Text-Indexes' are selected and also 'Script Triggers' is set to true.

Save the output to a SQL file.

I then tend to split the large DDL file into multiple separate files, for pre and post migrations steps. For example,

tables-ss.sql    -- This will include all CREATE TABLE statements
FK-ss.sql        -- This will include all FOREIGN KEY statements
functions-ss.sql -- This will include all CREATE FUNCTION statements
defaults-ss.sql  -- This will include all DEFAULT statements
procedure-ss.sql -- This will include all CREATE PROCEDURE statements
function-ss.sql  -- This will include all CREATE FUNCTIONS statements
triggers-ss.sql  -- This will include all CREATE TRIGGERS statements
views-ss.sql     -- This will include all CREATE VIEW statements
indexes-ss.sql   -- This will include all CREATE INDEXES statements

You now need to enable CDC on the source SQLServer instance. This is done via the below commands, example local database here

exec msdb.dbo.rds_cdc_enable_db 'commbuilderdemo';
GO

From here, I connect to the APG cluster but via the 1433 port. This will be using a SQLServer native UI or the SQLCMD command line tool.

Once connected to the master database, I will create the target local database.

In the PostgreSQL endpoint all this simply does is create a new schema.

From there, I then will run the script created above to create all the blank tables and any applicable primary keys

As you can now see, we've connected to the Aurora PostgreSQL cluster using a native SQLServer UI tool and you can now see the tables visible that we have previously created

To access this instance from the PostgreSQL endpoint you would run

psql -h <hostname> -U <username> -d babefish_db << EOF 
set search_path=<LocalDatabaseCreatedAbove_dbo;
\dt
EOF

An example of the above with a populated variables is here

psql -h <hostname> -U <username> -d babefish_db 

babelfish_db=> set search_path=commbuilders_dbo;
SET
babelfish_db=> \dt
                       List of relations
      Schema      |       Name       | Type  |      Owner
------------------+------------------+-------+------------------
 commbuilders_dbo | customer_data    | table | commbuilders_dbo
 commbuilders_dbo | first_names      | table | commbuilders_dbo
 commbuilders_dbo | last_names       | table | commbuilders_dbo
 commbuilders_dbo | numbers          | table | commbuilders_dbo
 commbuilders_dbo | street_type      | table | commbuilders_dbo
 commbuilders_dbo | transaction_data | table | commbuilders_dbo
 commbuilders_dbo | transactions     | table | commbuilders_dbo
(7 rows)

babelfish_db=>

From here, navigate back to the DMS service. This is where we will create a replication task to move the data over from source to target.

Ensure you set the DMS task to 'Do nothing' for 'Target table preparation mode'. This is to ensure that DMS does not drop and re-create the tables. If this was to happen, then the SQLServer endpoint cannot always see the table(s) as the data types will be the PostgreSQL types and not the SQLServer required types.

Here I am selecting all tables, I'm not excluding anything

You need to rename the schema to the localdatabase_dbo. With localdatabase being the name of the "CREATE DATABASE" command above

Here is a summary of the transformation rules that I created:

Once the data has loaded, it will stay in CDC mode until you are ready to switch over.

You can check the status of the task by checking CloudWatch logs or inside the task itself, it shows how many rows it has migrated over:

When you are ready to switch over, this is where you'd stop the source application and then once all connections have dropped you are in a position to stop the replication

You can now run the post scripts that you created before.

FK-ss.sql        
functions-ss.sql 
defaults-ss.sql 
procedure-ss.sql 
function-ss.sql  
triggers-ss.sql  
views-ss.sql     
indexes-ss.sql

Once you have created all the database resources, you now need to run data validation steps.

This will include, row counts validation, object count validation (FK, indexes, constraints etc), column count validation (to ensure all columns on the tables have replicated).

For row counts, I simply ran a row count on all tables and compared.

SELECT COUNT(*) FROM X;

For object counts, I compared sys.objects in both instances, connecting to the 1433 endpoints so I could run the exact same SQL on both.

SELECT type_desc,count(*) from sys.objects 
where schema_id=(SELECT SCHEMA_ID('dbo'))
group by type_desc
order by type_desc;
GO

That way you can easily compare objects and drill down into any differences, if applicable

Once all of those steps have passed and you are happy, you will need to instruct your application team(s) to change their endpoint to the Aurora PostgreSQL endpoint and restart their application(s)

Congratulations - you've managed to migrate from SQLServer to Aurora PostgreSQL.

You can see a walkthrough of this process here on the AWS twitch site, that I presented earlier this year.

https://www.twitch.tv/videos/2043017167?collection=BEUpZw5lUxeLJA

I hope this blog post helped, and happy building

Feel free to connect

https://www.linkedin.com/in/daniel-chapman-a753b56b/