aws_rds

Components

Tables

    parameter_group

    RDS

Enums

    parameter_group_family

Code examples

Manage RDS

--- 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)
VALUES ('<prefix>test', 20, 'db.t3.micro', 'test', 'testpass2023', (SELECT name FROM availability_zone WHERE region = '<region>' LIMIT 1), 'postgres', '13.4', 0);
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 db_instance_identifier = '<prefix>test';
--- 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 db_instance_identifier = '<prefix>test';
--- 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 db_instance_identifier = '<prefix>test';
--- check rds delete count
SELECT *
FROM rds
WHERE db_instance_identifier = '<prefix>test';
--- 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>';