aws_ec2

Components

Tables

    general_purpose_volume

    instance

    instance_block_device_mapping

    key_pair

    registered_instance

    instance_metadata

Functions

    describe_ami

    key_pair_import

    key_pair_request

Enums

    general_purpose_volume_type

    volume_state

    state

    architecture

    root_device_type

Code examples

Manage EC2 instances

--- installs the ec2 module
SELECT * FROM iasql_install('<modules>');
--- generates a new keypair
SELECT *
FROM key_pair_request ('<prefix>-key-request', '<region>');
--- check new keypair added
SELECT *
FROM key_pair
WHERE name = '<prefix>-key-request';
--- deletes the keypair
DELETE FROM key_pair
WHERE name = '<prefix>-key-request';
--- imports a new keypair
SELECT *
FROM key_pair_import ('<prefix>-key', '<sshRsa>', '<region>');
--- check new keypair added
SELECT *
FROM key_pair
WHERE name = '<prefix>-key';
--- check number of instances
SELECT *
FROM instance
WHERE tags ->> 'name' = '<prefix>-2';
--- deletes security group and instance
BEGIN;
DELETE FROM instance  WHERE tags ->> 'name' = '<prefix>-2';
DELETE FROM security_group WHERE group_name = 'fake-security-group';
COMMIT;
--- check number of instances
SELECT *
FROM instance
WHERE tags ->> 'name' = '<prefix>-1' OR
tags ->> 'name' = '<prefix>-2';
--- check user data
SELECT user_data
FROM instance
WHERE tags ->> 'name' = '<prefix>-1';
--- check number of volumes
SELECT general_purpose_volume.id FROM general_purpose_volume INNER JOIN instance_block_device_mapping
ON general_purpose_volume.id=instance_block_device_mapping.volume_id
WHERE instance_block_device_mapping.instance_id IN (SELECT instance.id FROM instance
WHERE (instance.tags ->> 'name' = '<prefix>-1' OR
instance.tags ->> 'name' = '<prefix>-2') AND region='<region>');
--- create target group and register instance to it
BEGIN;
INSERT INTO target_group (target_group_name, target_type, protocol, port, health_check_path)
VALUES ('<tgName>', '<tgType>', '<protocol>',<tgPort>, '/health');

INSERT INTO registered_instance (instance, target_group_id)
SELECT (SELECT id FROM instance WHERE tags ->> 'name' = '<prefix>-1' AND region='<region>'), (SELECT id FROM target_group WHERE target_group_name = '<tgName>');
COMMIT;
--- check target group count
SELECT *
FROM target_group
WHERE target_group_name = '<tgName>';
--- check registered instance count
SELECT *
FROM registered_instance
WHERE target_group_id = (SELECT id FROM target_group WHERE target_group_name = '<tgName>');
--- check registered instance port
SELECT *
FROM registered_instance
INNER JOIN instance ON instance.id = registered_instance.instance
WHERE target_group_id = (SELECT id FROM target_group WHERE target_group_name = '<tgName>') AND instance.tags ->> 'name' = '<prefix>-1';
--- register instance with custom port to target group
INSERT INTO registered_instance (instance, target_group_id, port)
SELECT (SELECT id FROM instance WHERE tags ->> 'name' = '<prefix>-2' AND region='<region>'), (SELECT id FROM target_group WHERE target_group_name = '<tgName>'),<instancePort>;
--- updates register instance with custom port to target group
UPDATE registered_instance
SET port =<tgName>FROM instance
WHERE instance.id = registered_instance.instance AND target_group_id = (SELECT id FROM target_group WHERE target_group_name = '<prefix>') AND instance.tags ->> 'name' = '-2';
--- stop instance
UPDATE instance SET state = 'stopped'
WHERE tags ->> 'name' = '<prefix>-2';
--- start instance
UPDATE instance SET state = 'running' WHERE tags ->> 'name' = '<prefix>-2';
--- hibernates instance
UPDATE instance SET state = 'hibernate'
WHERE tags ->> 'name' = '<prefix>-2';
--- check number of instances
SELECT *
FROM instance
WHERE tags ->> 'name' = '<prefix>-nosg';
--- deletes one of the registered instances
DELETE FROM registered_instance
USING instance
WHERE instance.tags ->> 'name' = '<prefix>-1' AND instance.id = registered_instance.instance;
--- check instance metadata
SELECT *
FROM instance_metadata
WHERE instance_id = (
SELECT instance_id
FROM instance
WHERE tags ->> 'name' = '<prefix>-1'
);
--- deletes all ec2 instances
DELETE FROM instance
WHERE tags ->> 'name' = '<prefix>-nosg' OR
tags ->> 'name' = '<prefix>-1' OR
tags ->> 'name' = '<prefix>-2';
--- deletes the target group
DELETE FROM target_group
WHERE target_group_name = '<tgName>';

Move instance from region

--- moves the instance to another region
BEGIN;

-- You can't move a registered instance at all, so unregister it
DELETE FROM registered_instance WHERE instance = (
SELECT id FROM instance WHERE tags ->> 'name' = '<prefix>-1'
);
-- We have to make sure the subnet is correct and we have to re-assign the AMI ID because they
-- are different between regions
DELETE FROM instance_block_device_mapping WHERE instance_id = (SELECT id FROM instance WHERE tags ->> 'name' = '<prefix>-1');
UPDATE instance set region='us-east-1', ami = '<ubuntuAmiId>',
subnet_id = (
SELECT id FROM subnet WHERE region = 'us-east-1' AND availability_zone = 'us-east-1a'
) WHERE tags ->> 'name' = '<prefix>-1';

-- Also need to drop the security groups it is currently attached to. This is done with a join
-- table so we get no good constraint checking on the validity here at the moment
DELETE FROM instance_security_groups WHERE instance_id = (
SELECT id FROM instance WHERE tags ->> 'name' = '<prefix>-1'
);

COMMIT;
--- check number of instances
SELECT *
FROM instance
WHERE tags ->> 'name' = '<prefix>-1';

Manage volumes

--- installs the module
SELECT * FROM iasql_install('<modules>');
--- check number of instances
SELECT *
FROM instance
WHERE tags ->> 'name' = '<prefix>-1';
--- check number of volumes
SELECT *
FROM general_purpose_volume INNER JOIN instance_block_device_mapping ON general_purpose_volume.id = instance_block_device_mapping.volume_id
WHERE instance_block_device_mapping.instance_id = (SELECT id FROM instance WHERE tags ->> 'name' = '<prefix>-1');
--- check extra volume has been created
SELECT *
FROM general_purpose_volume INNER JOIN instance_block_device_mapping ON general_purpose_volume.id = instance_block_device_mapping.volume_id
WHERE instance_block_device_mapping.instance_id = (SELECT id FROM instance WHERE tags ->> 'name' = '<prefix>-1');
--- check number of instances
SELECT *
FROM instance
WHERE tags ->> 'name' = '<prefix>-2';
--- check number of volumes
SELECT *
FROM general_purpose_volume INNER JOIN instance_block_device_mapping ON general_purpose_volume.id = instance_block_device_mapping.volume_id
WHERE instance_block_device_mapping.instance_id = (SELECT id FROM instance WHERE tags ->> 'name' = '<prefix>-2');
--- check number of volumes
SELECT *
FROM general_purpose_volume INNER JOIN instance_block_device_mapping ON general_purpose_volume.id = instance_block_device_mapping.volume_id
WHERE instance_block_device_mapping.instance_id = (SELECT id FROM instance WHERE tags ->> 'name' = '<prefix>-1' OR tags ->> 'name' = '<prefix>-2');
--- checks volume count
SELECT *
FROM general_purpose_volume
WHERE tags ->> 'Name' = '<gp2VolumeName>' OR tags ->> 'Name' = '<gp3VolumeName>';
--- tries to update a volume size
UPDATE general_purpose_volume SET size = 150 WHERE tags ->> 'Name' = '<gp3VolumeName>';
--- checks volume update
SELECT *
FROM general_purpose_volume
WHERE tags ->> 'Name' = '<gp3VolumeName>';
--- deletes the volumes
DELETE FROM general_purpose_volume
WHERE tags ->> 'Name' = '<gp2VolumeName>' OR tags ->> 'Name' = '<gp3VolumeName>';
--- gets information about an AMI
SELECT * from describe_ami('<ubuntuAmiId>');