gulcin/pgconfeu2015

This repository stores Ansible codes of my PGConfEU 2015 talk (Presentation).

This Ansible playbook:

• Provisions Amazon VPC and Amazon EC2 instances
• Installs latest PostgreSQL packages
• Configures a streaming replication with 1 master and 2 standbys

Table of Contents

• Dependencies
  • Debian/Ubuntu Installation
  • Mac OS X Installation
• Installation
• Run
• Verification
  • Prereqiusites
  • Verify
• Videos

Dependencies

Ansible and the Boto package is required to run this playbook.

Debian/Ubuntu Installation

apt-get install python-dev python-setuptools
easy_install pip
pip install ansible boto

See this in action.

Mac OS X Installation

sudo easy_install pip
sudo pip install ansible boto

Installation

1. Clone this repo

git clone https://github.com/gulcin/pgconfeu2015.git 

2. Edit ~/.boto to provide AWS access credentials

[Credentials]
aws_access_key_id = YOUR_ACCESS_KEY_ID
aws_secret_access_key = YOUR_SECRET_ACCESS_KEY

3. [optional] Edit ~/.ansible.cfg to disable host key checks

[defaults]
host_key_checking = False

You can skip this step, however Ansible will first ask you about a confirmation to connect to new created hosts.

Run

You can run this playbook simply by using the ansible-playbook command.

ansible-playbook -i hosts.ini main.yml

See this in action

Verification

We can see details about provisioned AWS EC2 instances by using the AWS Command Line Interface.

Prereqiusites

First time installation and configuration of awscli is required.

sudo pip install awscli

After installing awscli you can configure it and provide your AWS credentials:

aws configure

Verify

To see created instances with their public IP addresses, you can issue the following command:

aws ec2 describe-instances --no-paginate --output=text \
    --filters 'Name=instance-state-name,Values=running' \
    --query 'Reservations[].Instances[].[Tags[?Key==`Name`].Value, PublicIpAddress]' \
    | sed '$!N;s/\n/ /' | grep pg | sort -k2

You can also check if the replication is working correctly with a scenario like this:

• Connect to the master instance

  ssh ubuntu@<IP_ADDRESS>
  

  • See if PostgreSQL processes are running

    ps axw | grep "postgres:"
    

  • Change to the postgres system user

    sudo su - postgres
    

  • Connect to the vienna database

    psql vienna
    

    • Create a test table

      CREATE TABLE test (title text);
      

    • Insert some data to this table

      INSERT INTO test VALUES ('Test row 1');
      INSERT INTO test VALUES ('Test row 2');
      INSERT INTO test VALUES ('Test row 3');
      

• Connect to standby instances

  ssh ubuntu@<IP_ADDRESS>
  

  • See if PostgreSQL processes are running

    ps axw | grep "postgres:"
    

  • Change to the postgres system user

    sudo su - postgres
    

  • Connect to the vienna database

    psql vienna
    

    • Check the recovery status:

      SELECT is_in_recovery();
      

    • Select some data from the test table

      SELECT * FROM test;
      

Videos

You can find Asciinema videos shown in my presentation here

• Install Ansible on Debian-based Systems
• Playbook: First Run
• Playbook: Verify Setup
• Playbook: New Standby