In the previous post, we have introduced an article about how to install PostgreSQL 14 on Ubuntu in Google Cloud Engine which acts as a single database instance. In fact, it's very easy to manage a single database instance but mostly each organization has at least three database instances running as a cluster to serve the data for system application, web, or API. This configuration of the cluster can be done using lightweight repmgr extension which you can find the usability in this article. For the stability and maintainability of the core system, some design has been made as each database cluster for each system workload.

While you have many database instances/clusters to manage, finding a useful tool to manage is very vital as a database administrator. In today's post, we are going to introduce a tool called <Ansible> which are running separately with our database servers but can fully manage database instances. Ansible is configuration management used to set up and configure the computer system. The main purpose of ansible is in the server configuration maintained by RedHat and also available for commercial uses if needed. In order to let ansible performs properly through SSH, we have to install it on a control machine that can be a desktop system or a dedicated server (In our experiment, we will use VM instance as a control machine).

In this experiment, we will provide a few steps such as setting up a VM instance in Google Cloud Platform, Installation of Ansible on VM instance, and a few command samples to manage remotely from ansible server to database server.

Setting Up a VM Instance in Google Cloud Platform

First, you have to start a Google Cloud account with your existing Gmail by providing your VISA card as a payment method to connect with your Google Cloud account. Don't worry Google will not charge you instantly, they will provide $300 credit usages within a trial period for having a taste of their products. Once you set up everything, let's go to the console and you will see the dashboard as below screen:

Next, you can click on the menu "Compute Engine" and sub-menu "VM Instances" to create a new VM instance for installing Ansible

Then, click on the button "Create Instance". We choose the Singapore region with the "e2-micro" instance type as an example 

When the setup is finished you will see the new VM instance in the list below. instance-1 is our database server and instance-2 is an ansible server.

Installation of Ansible on VM Instance

Before installation of Ansible, we have to make sure SSH passwordless is in place from the Ansible server to the database servers. Using the following command to configure

• Ansible server (instance-2) - Using syntax of ssh-keygen to generate the public and private keys for SSH from this document

ssh-keygen -t rsa -C sethathay -b 2048

cat ~/.ssh/id_rsa.pub

• Database servers (instance-1) - Copy the public key generated by the above step to metadata in the google console of your project which looks like below. By doing this in google console, you are ensured that from the ansible server you are able to SSH to any server in the same project zone.

• Finally, check if you can ssh from the ansible server (instance-2) to the database server (instance-1) using the below command

ssh instance-1

Now, let's do the installation of Ansible

-- Add ansible repository into repository list
sudo apt-add-repository ppa:ansible/ansible
sudo apt-get update

sudo apt-get install ansible

Once installation is completed, we can check the version of ansible as below

ansible --version

Sample Commands to Manage Database Instance

By default, Ansible provides a working directory in /etc/ansible. For the purpose of efficient management, we will separately copy to our workplace in another directory /ansible/db

sudo mkdir -p /ansible/db

sudo cp -R /etc/ansible /ansible/db

Next, we have to change the configuration to use the hosts file located in the same directory

sudo vi ansible.cfg

sudo vi hosts

-- Then, enter "instance-1" as it's the name of db server

Finally, checking if the ansible can reach all the database servers 

sethathay@instance-2:/ansible/db/ansible$ ansible -m ping all
instance-1 | SUCCESS => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/bin/python3"
    },
    "changed": false,
    "ping": "pong"
}

Below are a few samples you can use to work with your PostgreSQL service such as checking the status of service, start, and stop service.

SAMPLE 1: CHECKING STORAGE AVAILABLE

ansible -m shell -a "df -h" all

instance-1 | CHANGED | rc=0 >>
Filesystem      Size  Used Avail Use% Mounted on
/dev/root       9.6G  3.3G  6.3G  35% /
devtmpfs        484M     0  484M   0% /dev
tmpfs           487M   28K  487M   1% /dev/shm
tmpfs            98M  924K   97M   1% /run
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           487M     0  487M   0% /sys/fs/cgroup
/dev/loop0       56M   56M     0 100% /snap/core18/2128
/dev/loop1       62M   62M     0 100% /snap/core20/1081
/dev/loop2       62M   62M     0 100% /snap/core20/1169
/dev/loop4      251M  251M     0 100% /snap/google-cloud-sdk/202
/dev/sda15      105M  5.2M  100M   5% /boot/efi
/dev/loop5       68M   68M     0 100% /snap/lxd/21545
/dev/loop7       33M   33M     0 100% /snap/snapd/13640
/dev/loop6       33M   33M     0 100% /snap/snapd/13170
/dev/loop8       56M   56M     0 100% /snap/core18/2246
/dev/loop9       68M   68M     0 100% /snap/lxd/21835
/dev/loop10     251M  251M     0 100% /snap/google-cloud-sdk/204
tmpfs            98M     0   98M   0% /run/user/1001

SAMPLE 2: CHECKING POSTGRESQL SERVICE

ansible -m shell -a "systemctl status postgresql" instance-1

instance-1 | CHANGED | rc=0 >>
● postgresql.service - PostgreSQL database server
     Loaded: loaded (/etc/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sat 2021-11-06 13:39:37 UTC; 14h ago
   Main PID: 533 (postgres)
      Tasks: 7 (limit: 1159)
     Memory: 18.9M
     CGroup: /system.slice/postgresql.service
             ├─533 /app/postgres/product/14/db_1/bin/postgres -D /data01
             ├─573 postgres: checkpointer
             ├─574 postgres: background writer
             ├─575 postgres: walwriter
             ├─576 postgres: autovacuum launcher
             ├─577 postgres: stats collector
             └─578 postgres: logical replication launcher

SAMPLE 3: START STOP POSTGRESQL SERVICE

-- Stop PostgreSQL Service
ansible -m shell -a "sudo systemctl stop postgresql" instance-1

-- Start PostgreSQL Service
ansible -m shell -a "sudo systemctl start postgresql" instance-1

As we have mentioned in this article, you might have thought like why do I need this ansible because I have only one database server? Let's imagine you have a bunch of database servers and your manager would like to know the status of services running across all database instances after patch update OS. If you don't have ansible, you might need to SSH to every server and check but by having Ansible you just enter one command and you get all the replies from your database servers. Make sure you have monitoring tools if you don't want to use Ansible in this scenario. Ansible has much more features than this such as the playbook which we will introduce later.

THANK YOU, Find Us @
Facebook
Telegram
Twitter
LinkedIn