How to Automate PostgreSQL Installation using AttuneOps: PostgreSQL Deployment Guide

PostgreSQL is a highly reliable, open-source database system that’s widely used for managing data in both small and large applications. Its robust feature set and exceptional performance make it a top choice for developers and businesses around the world.

To ensure smooth and efficient deployment, especially for larger projects, automating the installation and configuration process is key.

That’s where AttuneOps comes in. AttuneOps is an automation tool designed to simplify repetitive tasks like executing scripts on servers, whether local or remote.

In this blog, we’ll guide you step-by-step through automating the installation and setup of PostgreSQL on an Ubuntu 24.04 server using AttuneOps, saving you time and effort while ensuring consistency across your deployments.

Let’s get started!

Guide to Automate PostgreSQL Installation using AttuneOps

Prerequisites

Before proceeding, ensure you have access to an environment that meets the following requirements:

• Ubuntu 24.04 Server with at least 2GB RAM (this is the Target Node).
• Access to an account with sudo privileges on the Target Node.
• An Automation Node is running either on MacOS or Windows (we will install AttuneOps on this node).
• A reliable internet connection.
• SSH access from the Automation Node to the Target Node.

Download and Install AttuneOps

This section describes the steps to install AttuneOps Community Edition on the automation node:

• Download AttuneOps.
• Once downloaded, you can launch the setup file:
• Follow the instructions to install AttuneOps on the automation node.

Note: This section covers the installation of AttuneOps on a Windows PC. For macOS, you can install AttuneOps by double-clicking the DMG file.

Setting up the Target Node

This section describes the steps to setup the Target Linux Node before you can proceed with executing the deployment scripts using AttuneOps:

• Ensure the OpenSSH Server is installed if not you can install it with the command:

apt install -y openssh-server

• Ensure OpenSSH Server systemd service is started and enabled:

systemctl start ssh
systemctl enable ssh

• Create the automation user and assign a password for the user:

useradd -m -s /bin/bash autousr
passwd autousr

• Ensure the automation user is a member of the sudo group, this is to allow the user to execute privileged commands:

usermod -aG sudo autousr

The Remote node is now ready to receive commands from the AttuneOps Automation Node.

AttuneOps Concepts

AttuneOps is divided into three workspaces, which define the three steps in setting up your deployment workflow. These can be accessed from the AttuneOps UI by clicking the hamburger menu in the top left.

This will open the menu below:

1. Design

Where you define your reusable automated workflows.

Components include:

• Blueprints: Define the reusable components and organise, steps and parameters into one unit. For the PostgreSQL Example, the Deploy PostgreSQL blueprint could consist of:
  • Steps: Steps that define the scripts that need to run to deploy and configure PostgreSQL
  • Parameters: Parameters that define variable values that can be substituted in the steps, this allows the scripts to be dynamic and customizable based on the values from the parameters.
  • Projects: Projects are used to group related Blueprints together.

2. Plan

The Plan Workspace is where you plan a job to run a blueprint and which values are connected to the parameters in the blueprint. A single blueprint can be used for many jobs with different values.

Components Include:

• Values: Values are substituted into Parameters when a job is run. Values have different attributes based on their type.
• Schedules: Schedules can be configured to run many Plans either sequentially or in parallel. AttuneOps must be configured to run as a service to be able to schedule jobs.

3. Run

• Can be used to Run, Debug and view logs for Jobs.

The Section below defines steps to be followed in creating an automation blueprint to deploy and Configure a PostgreSQL Server.

Create Project

• Ensure AttuneOps is running.
• Click the hamburger menu in the top left to open the AttuneOps workspace menu.
• From the Project dropdown, click the three-dot menu and then click Create Project…, specifying a project name.
• Once done, you can switch to the project. In this case, we can call it Deploy and Manage PostgreSQL.

• Once the project is created you can now define blueprints.

Create Blueprints

• Open the hamburger menu and click on Blueprints.
• Click the Create Blueprint button.

• Specify a name for the blueprint and click Create (name the blueprint Deploy PostgreSQL).

Define Execution Steps

The blueprint will be broken down into the steps below:

• Update OS Repo Cache: Update OS repositories and packages.
• Install Prerequisites: Install the required packages for the PostgreSQL installation.
• Install PostgreSQL: Install the PostgreSQL server and client, and create a database and users.
• Load Sample SQL: Create sample tables and load data.

To create a step:

• Select the blueprint you just created, click the three-dot menu, and click Create Step.

• Enter a name for the step (as listed above, for example, Update OS Repo Cache):

• Select Type as Run Script under Linux/Unix Steps.
• Fill in the options as shown below screenshot:

• For Target Node, select Create New from the dropdown. Select Type as Linux/Unix Node and define a name, such as Database Servers.

• For Credential, select Create New from the dropdown. Select Type as Linux/Unix Credential and define a name, such as Automation User.

• Once done, you will be taken to the main Steps screen, where you can define the scripts.

The scripts for each step are defined below. Repeat the procedure for all the steps listed above and use the scripts below:

Update OS Repo Cache

#!/bin/bash
apt update -y && apt upgrade -y

Install Prerequisites

#!/bin/bash
apt-get install -y wget gnupg2

Install PostgreSQL

#!/bin/bash


set -e  # Exit immediately if a command exits with a non-zero status


# Variables
POSTGRES_VERSION="{postgresVersion}"
DB_NAME="{dbName}"
DB_USER="{dbUser}"
DB_PASSWORD="{dbPassword}"


# Add PostgreSQL repository
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt-get update
sudo apt-get install -y postgresql-$POSTGRES_VERSION postgresql-client-$POSTGRES_VERSION


# Configure PostgreSQL to allow access from any host
echo "Configuring PostgreSQL to allow remote access..."
sudo sed -i "s/^#listen_addresses = 'localhost'/listen_addresses = '*'/g" /etc/postgresql/$POSTGRES_VERSION/main/postgresql.conf
echo "host all all 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/$POSTGRES_VERSION/main/pg_hba.conf


# Restart PostgreSQL
sudo systemctl restart postgresql


# Create user and database
echo "Creating user and database..."
sudo -u postgres psql <<EOF
CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD';
CREATE DATABASE $DB_NAME OWNER $DB_USER;
EOF


echo "Setup complete!"
echo "PostgreSQL user: $DB_USER"

Load Sample SQL (Replace with your Custom SQL)

#!/bin/bash
sudo -u postgres psql {dbName} <<EOF
-- Drop existing tables if they exist
DROP TABLE IF NOT EXISTS users;
DROP TABLE IF NOT EXISTS orders;


-- Create a table for users
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- Insert sample user data
INSERT INTO users (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_smith', 'jane@example.com'),
('alice_brown', 'alice@example.com');


-- Create a table for orders
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- Insert sample order data
INSERT INTO orders (user_id, product_name, quantity) VALUES
(1, 'Laptop', 1),
(1, 'Smartphone', 2),
(2, 'Headphones', 1),
(3, 'Monitor', 1);


-- Query to display all users and their orders
SELECT
    u.username,
    u.email,
    o.product_name,
    o.quantity,
    o.order_date
FROM
    users u
LEFT JOIN
    orders o ON u.id = o.user_id;
EOF

Define Parameters

You can define parameters for the blueprints from the Parameters option under the Design workspace of your project.

Parameters allow you to plug in custom values to your blueprints and steps. To create a parameter, click Parameters and then Create Parameters. The parameters defined for this project are:

These values are referenced from the steps using the substitution: {scriptReference}.

To define values for your parameters, click Values from the Plan workspace.

You will have the option to define values for the Parameters:

Specifically for the credentials and nodes, you have to specify the values as below:

Node

Credential

Define Plan

You can plan a job to run a blueprint from this workspace.

Click Plan Trees, then click Create Tree, and specify a plan tree name, for example, Database Deploy Plan.

Once the plan tree is created, click on it to select it, then click the three-dot menu and Create a Plan, providing a name for the plan.

From the Blueprints pane, once you select the plan, you can map projects and blueprints to the plan.

From the Inputs pane of the plan, you can map the input values to the parameters.

Run Job

Once this step is done, you can proceed to run the job from the Run workspace.

Click on the Run/Debug Job option, and you will find a list of plans you created. Upon clicking one of them, you can launch the job as specified by the blueprint by clicking the Play button for the plan or clicking the Play button for each task.

The steps will proceed to execute, updating the system, downloading packages, installing/configuring PostgreSQL, and setting up the database structures before finally loading the sample data.

As the script executes, AttuneOps will output debug, informational, and error logs for each step on its main pane.

If all steps are executed without errors, the job will be marked as a success, and you can validate the installation.

Validating the Installation

If the job was successful, you can test the deployment by connecting to and querying the database via:

• The PostgreSQL CLI
• A DB client, like DBeaver or pgAdmin

For this example, we will use the CLI.

Using PostgreSQL CLI

• Log in to the server via SSH.
• Switch to the Postgres user: sudo su - postgres
• Run the psql utility and connect to the sample database you specified in the parameters. For example: psql orders. You should get a prompt like the one below:

• Run a sample query:

select orders.product_name,orders.quantity,orders.order_date,users.username,users.email,users.created_at from orders inner join users on orders.user_id = users.id;

If you can get results, you can validate that the installation and configuration were executed correctly.

Automate PostgreSQL Installation using AttuneOps: Frequently Asked Questions

Conclusion

By now, you should have a clear understanding of how to automate the PostgreSQL installation process with AttuneOps. With tools like reusable blueprints, dynamic parameters, and scheduling, you can simplify and standardise your database deployments.

This method not only improves efficiency but also ensures consistency, making managing PostgreSQL across multiple servers a breeze. Here’s to smoother, faster deployments!

Salim Said Hemed

I am a DevOps/Site Reliability Engineer specialising in infrastructure automation, web services, and performance monitoring.