Skip to content

Instantly share code, notes, and snippets.

@kennwhite
Last active December 23, 2020 00:19
Show Gist options
  • Save kennwhite/37516a77e48426e319f5 to your computer and use it in GitHub Desktop.
Save kennwhite/37516a77e48426e319f5 to your computer and use it in GitHub Desktop.
Ansible Playbook: Create multi-zone Postgres on RDS in a VPC
# Ansible RDS Multi-AZ Postgres
#
# Assumes existing Security Group, VPC, and RDS Subnet Groups.
#
# To install Ansible on OSX:
# sudo easy_install pip
# sudo pip install paramiko PyYAML jinja2 (might be prompted to install XCode & re-run)
# sudo pip install ansible
# sudo pip install boto
# sudo mkdir /etc/ansible
# Need a placeholder hosts file for RDS (replace w/ ssh-accessible ec2 instance later)
# sudo sh -c 'printf "[local]\nlocalhost\n" > /etc/ansible/hosts'
# sudo -R chown [you] /etc/ansible/
#
# Make sure env variables are set:
# export AWS_ACCESS_KEY='AKIxxxx' && export AWS_SECRET_KEY='xyz'
#
# Pre-reqs:
# In the desired Region, create a public single subnet VPC w/ sg above, maybe
# "Ansible Public subnet", w/ AZ us-east-1a, CIDR 10.0.0.0/24
# Note the VPC label (eg vpc-xxx)
# In the desired Region, create a Security Group, maybe: ansible-vpc-sg
# Set to vpc-xxx
# Allow in-bound 5432 tcp from 10.0.0.0/16; optionally: ssh, http/s from 0.0.0.0
# Create it, and note the new Security Group ID, e.g: sg-xxxxx
# Create a new subnet, in that VPC, maybe "Ansible Private subnet"
# with a *different* AZ, maybe: us-east-1c, CIDR 10.0.1.0/24
# Go to RDS, create a DB Subnet Group, eg: ansible-sg1
# in the VPC, add all the subnets (e.g, AZs -1a and -1c, CIDR 10.0.0/24 & 10.0.1.0/24)
#
# Finally: ansible-playbook ./rds_vpc_pg_multi-zone_launch.yml -f 10
#
# IP=$(ansible-playbook ./rds_vpc_pg_multi-zone_launch.yml -f 10 | tee log.out | tail | grep 'EC2 public IP' | cut -d'|' -f2)
#
# ssh -i key ec2-user@$IP
#
# yum install postgresql9
#
# su - postgres -c "PGPASSWORD='password' psql --host=[RDS endpoint] --port=5432 --username=dbadmin --dbname myrdspg"
#
# For any errors about HmacAuthV4Handler or handler ready to communicate,
# double check that AWS credentials allow create on RDS/VPC/Security Group
#
# Disclaimer: Though this works, you're an idiot to use this playbook in production. :-)
#
---
- hosts: localhost
gather_facts: no
vars:
region: us-east-1
size: 100
instance_type: db.m3.medium
db_engine: postgres
# Optional: Postgres DB engine rev
engine_version: 9.3.3
# Optional: RDS Parameter Group
parameter_group: dbpg1
# Required: RDS Subnet Group for multi-zone
subnet: ansible-sg1
# Required: Database security group ID
security_groups: sg-xxxxx
iops: 1000
# Window in format of ddd:hh24:mi-ddd:hh24:mi, eg: Mon:01:00-Mon:01:15 (minimum 30 mins)
maint_window: "Sun:01:00-Sun:01:30"
# Window in format of hh24:mi-ddd:hh24:mi, eg: 01:15-01:45 (minimum 30 mins)
backup_window: "02:00-02:30"
# Days to save backup snapshots
backup_retention: 14
# Do not set zone for Multi-AZ launches
zone:
#
rds_instance_name: test-rds-pg
db_name: myrdspg
username: dbadmin
password: password
#
# For VPC ec2 instance
vpc_subnet_id: subnet-xxxxx
group_id: sg-xxxxxx
keypair_name: myKeyPem
root_volume_size_gbs: 30
tasks:
- name: Create multi-zone Postgres on RDS in a VPC
local_action:
module: rds
command: create
instance_name: "{{ rds_instance_name }}"
region: "{{ region }}"
size: "{{ size }}"
instance_type: "{{ instance_type }}"
db_engine: "{{ db_engine }}"
# Use default (latest) DB engine (e.g., 9.3.x)
# engine_version: "{{ engine_version }}"
subnet: "{{ subnet }}"
# Use default db parameter group
# parameter_group: "{{ parameter_group }}"
multi_zone: yes
port: 5432
db_name: "{{ db_name }}"
username: "{{ username }}"
password: "{{ password }}"
vpc_security_groups: "{{ security_groups }}"
iops: "{{ iops }}"
maint_window: "{{ maint_window }}"
backup_window: "{{ backup_window }}"
backup_retention: "{{ backup_retention }}"
zone: "{{ zone }}"
- name: Create VPC EC2 instance w/ public IP
tags: ec2
local_action:
module: ec2
region: "{{ region }}"
key_name: "{{ keypair_name }}"
group_id: "{{ group_id }}"
instance_type: t2.micro
# amzn-ami-minimal-hvm-2014.03.2.x86_64-ebs ami-a2867bca x86_64 MINIMAL EBS
image: ami-a2867bca
wait: yes
vpc_subnet_id: "{{ vpc_subnet_id }}"
assign_public_ip: yes
instance_tags:
Name: ansible-vm-1
volumes:
- device_name: /dev/xvda
device_type: gp2
volume_size: "{{ root_volume_size_gbs }}"
delete_on_termination: true
register: ec2
- debug: msg="Allocated VPC EC2 public IP|{{ ec2.instances[0].public_ip }}|"
with_items: ec2.instances
@wojtas911
Copy link

How do You know what is the endpoint for PG ?

@gdamjan
Copy link

gdamjan commented Jan 28, 2016

@wojtas911
you use register: rds_info on the rds action, and then you get the endpoint in rds_info.instance.endpoint

@bwahmed
Copy link

bwahmed commented Jan 23, 2019

Hi,
Please help. The user created in RDS does not have admin privileges. Unable to create table also. What to do in this case. Please help.

Thanks,
Ahmed

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment