Using Ansible to Remote Manage Your Database Instance
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.