aws_rds
- Components
- Code examples
Manage RDS instances
--- installs the rds module
SELECT * FROM iasql_install('<modules>');
--- creates an RDS instance
BEGIN;
INSERT INTO rds (db_instance_identifier, allocated_storage, db_instance_class, master_username, master_user_password, availability_zone, engine, engine_version, backup_retention_period, tags)
VALUES ('<prefix>test', 20, 'db.t3.micro', 'test', 'testpass2023', (SELECT name FROM availability_zone WHERE region = '<region>' LIMIT 1), 'postgres', '13.4', 0, '{"name":"<prefix>-1"}');
INSERT INTO rds_security_groups (rds_id, security_group_id) SELECT
(SELECT id FROM rds WHERE db_instance_identifier='<prefix>test'),
(SELECT id FROM security_group WHERE group_name='default' AND region = '<region>');
COMMIT;
--- check adds a new db instance
SELECT *
FROM rds
WHERE tags ->> 'name' = '<prefix>-1';
--- check security group relationship
SELECT *
FROM rds_security_groups
INNER JOIN rds ON rds.id = rds_security_groups.rds_id
WHERE db_instance_identifier = '<prefix>test';
--- changes the postgres version
UPDATE rds SET engine_version = '13.5' WHERE tags ->> 'name' = '<prefix>-1';
--- updates RDS tags
UPDATE rds SET tags = '{"name":"<prefix>-2"}' WHERE tags ->> 'name' = '<prefix>-1';
--- creates an RDS parameter group
INSERT INTO parameter_group (name, family, description)
VALUES ('<parameterGroupName>', '<engineFamily>', '<parameterGroupName>desc');
--- check parameter group insertion
SELECT *
FROM parameter_group
WHERE name = '<parameterGroupName>';
--- changes all boolean parameters for the new parameter group to be true
WITH parameters AS (
SELECT name, params
FROM parameter_group,
jsonb_array_elements(parameters) params
WHERE name = '<parameterGroupName>' AND params ->> 'DataType' = 'boolean' AND params->> 'IsModifiable' = 'true'
), updated_parameters AS (
select name, jsonb_set(params, '{ParameterValue}', '1', true) updated_params
from parameters
)
UPDATE parameter_group
SET parameters = (
SELECT jsonb_agg(updated_params)
FROM updated_parameters
WHERE updated_parameters.name = parameter_group.name
);
--- check all modifiable boolean parameters are true
SELECT params ->> 'ParameterValue' as value
FROM parameter_group, jsonb_array_elements(parameters) as params
WHERE name = '<parameterGroupName>' AND params ->> 'DataType' = 'boolean' AND params ->> 'IsModifiable' = 'true';
--- removes the RDS instance
DELETE FROM rds
WHERE tags ->> 'name' = '<prefix>-2';
--- check rds delete count
SELECT *
FROM rds
WHERE tags ->> 'name' = '<prefix>-2';
--- removes the parameter group and it parameters
DELETE FROM parameter_group
WHERE name = '<parameterGroupName>';
--- check parameter group count after delete
SELECT *
FROM parameter_group
WHERE name = '<parameterGroupName>';
Manage Multi-AZ clusters
--- installs the rds module
SELECT * FROM iasql_install('<modules>');
--- creates an RDS subnet group
INSERT INTO db_subnet_group (name, description, subnets)
VALUES ('<prefix>cluster-test', 'test subnet group', (SELECT ARRAY(
SELECT DISTINCT ON (subnet.availability_zone) subnet_id from subnet inner join vpc on vpc.id = subnet.vpc_id where is_default = true and vpc.region = '<region>' LIMIT 3
)));
--- check subnet group insertion
SELECT *
FROM db_subnet_group
WHERE name = '<prefix>cluster-test';
--- creates an RDS cluster
INSERT INTO db_cluster (db_cluster_identifier, engine, engine_version, allocated_storage, iops, db_cluster_instance_class, master_username, master_user_password, subnet_group_id, tags, deletion_protection) VALUES
('<prefix>cluster-test', 'mysql', '<engineVersion>', 100, 1000, 'db.m5d.xlarge', 'admin', 'admin123456', (select id FROM db_subnet_group WHERE name = '<prefix>cluster-test'), '{"name":"<prefix>-1"}', TRUE);
--- check adds a new db cluster
SELECT *
FROM db_cluster
WHERE tags->>'name' = '<prefix>-1';
--- changes the backup retention period
UPDATE db_cluster SET backup_retention_period=10 WHERE tags->>'name' = '<prefix>-1';
--- check that retention period has been modified
SELECT *
FROM db_cluster
WHERE tags->>'name' = '<prefix>-1' AND backup_retention_period=10;
--- tries to update an instance belonging to a cluster
UPDATE rds SET backup_retention_period=3 WHERE db_cluster_id = (SELECT id FROM db_cluster WHERE db_cluster_identifier='<prefix>cluster-test');
--- check that backup retention period for instances has not been modified
SELECT *
FROM rds
WHERE db_cluster_id = (SELECT id FROM db_cluster WHERE db_cluster_identifier = '<prefix>cluster-test') AND backup_retention_period=3;
--- updates db_cluster tags and removes deletion protection
UPDATE db_cluster SET tags = '{"name":"<prefix>-2"}', deletion_protection=FALSE WHERE tags ->> 'name' = '<prefix>-1';
--- removes the RDS cluster
DELETE FROM db_cluster
WHERE tags->>'name' = '<prefix>-2';
--- check RDS cluster delete count
SELECT *
FROM db_cluster
WHERE tags->>'name' = '<prefix>-2';
--- removes the db subnet group and its parameters
DELETE FROM db_subnet_group
WHERE name = '<prefix>cluster-test';
--- check subnet group count after delete
SELECT *
FROM db_subnet_group
WHERE name = '<prefix>cluster-test';