PostgreSQL Primary-Read-Replica Setup API

This repository contains an API designed to automate the deployment of a PostgreSQL primary-read-replica architecture using Terraform and Ansible. With this API, users can easily generate configuration files, provision infrastructure, and configure replication, all through a simple REST interface.

Features

• Dynamic Configuration: Automatically generate Terraform and Ansible configurations based on input parameters.
• Infrastructure Automation: Provision AWS resources such as EC2 instances and security groups using Terraform.
• PostgreSQL Setup: Install PostgreSQL, configure key settings, and enable replication between the primary and replicas using Ansible.
• API-Driven Workflow: Manage the entire setup process with a REST API.

Prerequisites

To use this project, ensure you have the following installed and configured:

• System Requirements:

  • Python 3.8+
  • Terraform CLI
  • Ansible
  • AWS CLI (configured with appropriate permissions)

• Python Libraries: Install required libraries by running:

pip install flask jinja2 pydantic

• AWS Authentication: Configure your AWS CLI with appropriate credentials:

aws configure

API Endpoints

• Generate Configurations
  • Endpoint: /generate-config
  • Method: POST
  • Description: Generates Terraform and Ansible configuration files based on the input parameters.

{
  "pg_version": "14",
  "instance_type": "t2.medium",
  "replica_count": 2,
  "max_connections": 100,
  "shared_buffers": "256MB"
}

• Apply Infrastructure
  • Endpoint: /apply-infra
  • Method: POST
  • Description: Runs Terraform to provision the infrastructure.

{
  "apply": true
}

• Configure Database
  • Endpoint: /configure-db
  • Method: POST
  • Description: Executes Ansible playbooks to install and configure PostgreSQL on the provisioned infrastructure.

{
  "run": true
}

• Check Status
  • Endpoint: /status
  • Method: GET
  • Description: Fetches the current status of the setup process.

Code Overview

Flask API (app.py)

This script serves as the entry point for the application, defining the endpoints for configuration generation, infrastructure provisioning, database setup, and status checking.

from flask import Flask, request, jsonify
import subprocess
from jinja2 import Template

app = Flask(__name__)

TEMPLATE_DIR = "templates"
OUTPUT_DIR = "output"

@app.route('/generate-config', methods=['POST'])
def generate_config():
    params = request.json
    # Generate Terraform configuration
    with open(f"{TEMPLATE_DIR}/main.tf.j2") as f:
        terraform_template = Template(f.read())
    with open(f"{OUTPUT_DIR}/main.tf", "w") as f:
        f.write(terraform_template.render(params))
    
    # Generate Ansible playbook
    with open(f"{TEMPLATE_DIR}/ansible_playbook.yml.j2") as f:
        ansible_template = Template(f.read())
    with open(f"{OUTPUT_DIR}/ansible_playbook.yml", "w") as f:
        f.write(ansible_template.render(params))
    
    return jsonify({"message": "Configurations generated successfully!"}), 200

@app.route('/apply-infra', methods=['POST'])
def apply_infra():
    try:
        subprocess.run(["terraform", "init"], cwd=OUTPUT_DIR, check=True)
        subprocess.run(["terraform", "apply", "-auto-approve"], cwd=OUTPUT_DIR, check=True)
        return jsonify({"message": "Infrastructure provisioned successfully!"}), 200
    except subprocess.CalledProcessError as e:
        return jsonify({"error": str(e)}), 500

@app.route('/configure-db', methods=['POST'])
def configure_db():
    try:
        subprocess.run(["ansible-playbook", "ansible_playbook.yml"], cwd=OUTPUT_DIR, check=True)
        return jsonify({"message": "PostgreSQL configured successfully!"}), 200
    except subprocess.CalledProcessError as e:
        return jsonify({"error": str(e)}), 500

@app.route('/status', methods=['GET'])
def status():
    return jsonify({"status": "Running"}), 200

if __name__ == '__main__':
    app.run(debug=True)

Terraform Template (templates/main.tf.j2)

provider "aws" {
  region = "us-west-2"
}

resource "aws_instance" "pg_primary" {
  ami           = "ami-12345678"
  instance_type = "{{ instance_type }}"
  tags = {
    Name = "PostgreSQL-Primary"
  }
}

resource "aws_instance" "pg_replica" {
  count         = {{ replica_count }}
  ami           = "ami-12345678"
  instance_type = "{{ instance_type }}"
  tags = {
    Name = "PostgreSQL-Replica-${count.index + 1}"
  }
}

Ansible Template (templates/ansible_playbook.yml.j2)

- name: Setup PostgreSQL Primary
  hosts: primary
  tasks:
    - name: Install PostgreSQL
      apt:
        name: postgresql-{{ pg_version }}
        state: present

    - name: Configure PostgreSQL
      lineinfile:
        path: /etc/postgresql/{{ pg_version }}/main/postgresql.conf
        line: "max_connections = {{ max_connections }}"
    
    - name: Enable Replication
      lineinfile:
        path: /etc/postgresql/{{ pg_version }}/main/pg_hba.conf
        line: "host replication all 0.0.0.0/0 md5"

- name: Setup PostgreSQL Replicas
  hosts: replicas
  tasks:
    - name: Install PostgreSQL
      apt:
        name: postgresql-{{ pg_version }}
        state: present

    - name: Configure Replication
      command: >
        psql -c "CREATE_REPLICATION_SLOT ..."

Usage

• Generate Configurations:

curl -X POST http://127.0.0.1:5000/generate-config \
-H "Content-Type: application/json" \
-d '{"pg_version": "14", "instance_type": "t2.medium", "replica_count": 2, "max_connections": 100, "shared_buffers": "256MB"}'

• Apply Infrastructure:

curl -X POST http://127.0.0.1:5000/apply-infra -d '{"apply": true}'

• Configure Database:

curl -X POST http://127.0.0.1:5000/configure-db -d '{"run": true}'

• Check Status:

curl http://127.0.0.1:5000/status