PostgreSQL Ansible Deployment Project

This project provides a containerized Ansible environment for deploying and configuring PostgreSQL on LVM-backed storage. It automates the installation and configuration of PostgreSQL 16 on remote target servers using Ansible playbooks.

Project Structure

.
├── docker-compose.yml      # Docker Compose configuration for Ansible container
├── Dockerfile             # Custom Ansible container with additional tools
├── README.md              # This file
└── workspace/             # Ansible workspace directory
    ├── ansible.cfg        # Ansible configuration
    ├── inventory.ini      # Target hosts inventory
    ├── postgresql.yml     # PostgreSQL installation playbook
    ├── storage.yml        # LVM storage preparation playbook
    └── vars.yml          # Configuration variables

Features

• Containerized Ansible Environment: Uses Docker to provide a consistent Ansible runtime
• LVM Storage Management: Prepares and mounts LVM logical volumes for PostgreSQL data
• PostgreSQL 16 Installation: Automated installation and configuration of PostgreSQL
• Remote Access Configuration: Configures PostgreSQL for remote connections
• Custom Cluster Setup: Creates PostgreSQL clusters on custom data directories
• Security Configuration: Sets up authentication and access controls

Prerequisites

• Docker and Docker Compose
• SSH access to target servers
• LVM logical volume already created on target server (/dev/vg-pgdata/lv-pgdata)
• Target server running Ubuntu/Debian with sudo access

Configuration

Target Host Configuration

Edit workspace/inventory.ini to specify your target server:

[db_servers]
192.168.68.30 ansible_user=chenyard

PostgreSQL Configuration

Modify workspace/vars.yml to customize your PostgreSQL deployment:

Variable	Default	Description
pg_version	16	PostgreSQL version to install
pg_cluster_name	main	Name of the PostgreSQL cluster
admin_user	admin_user	PostgreSQL admin username
admin_password	postgresql	PostgreSQL admin password
pg_lvm_device	/dev/vg-pgdata/lv-pgdata	LVM device path
pg_filesystem_type	ext4	Filesystem type for storage
pg_mount_path	/pgdata	Mount point for PostgreSQL data
pg_data_dir	/pgdata/postgresql/16/main	PostgreSQL data directory
pg_allowed_cidr	192.168.68.0/24	Network CIDR allowed for remote connections
pg_create_filesystem	false	Whether to create filesystem on LVM device

Usage

1. Setup SSH Keys

Place your SSH private and public keys in the .ssh/ directory:

mkdir -p .ssh
cp ~/.ssh/id_rsa .ssh/
cp ~/.ssh/id_rsa.pub .ssh/

2. Configure Environment

Set the USERNAME environment variable to match your system user:

export USERNAME=$(whoami)

3. Start the Ansible Container

docker-compose up -d
docker-compose exec ansible /bin/sh

4. Prepare Storage (if needed)

If you need to prepare the LVM storage and filesystem:

ansible-playbook storage.yml

5. Deploy PostgreSQL

Run the PostgreSQL installation playbook:

ansible-playbook postgresql.yml

What the Playbooks Do

Storage Playbook (storage.yml)

• Creates mount point for PostgreSQL data
• Formats LVM logical volume (if pg_create_filesystem is true)
• Mounts the LVM volume
• Sets proper ownership for PostgreSQL directories

PostgreSQL Playbook (postgresql.yml)

• Updates system packages
• Installs PostgreSQL 16 and required dependencies
• Stops default PostgreSQL service
• Drops default cluster if it exists
• Creates new cluster on LVM-backed storage
• Configures PostgreSQL for remote connections
• Sets up authentication (SCRAM-SHA-256)
• Creates admin user with full privileges
• Starts and enables PostgreSQL service

Network Architecture

The setup configures PostgreSQL to:

• Listen on all network interfaces (listen_addresses = '*')
• Allow connections from the specified CIDR range (default: 192.168.68.0/24)
• Use SCRAM-SHA-256 authentication for remote connections

Security Notes

• SSH keys are copied into the container with permissive permissions for development use
• Default passwords should be changed in production environments
• Network access is restricted to the configured CIDR range
• Host key checking is disabled for convenience (not recommended for production)

Container Features

The custom Ansible container includes:

• Alpine-based Ansible 2.20.0
• Additional tools: nano, bash, openssh-client, git, sudo
• Non-root user execution
• SSH key mounting for remote access
• Host networking for direct server access

Troubleshooting

• Ensure the LVM logical volume exists before running the playbooks
• Verify SSH connectivity to target hosts
• Check that the target user has sudo privileges
• Confirm network connectivity between container and target servers
• Review Ansible logs for specific error messages