How to Automate the PostgreSQL database setup using Ansible role
Test Environment
Fedora 33
What is Ansible
Its a Configuration Management tool used to configure and manage the Linux system for all the types of operations like Installating, Patching, Deployment of Applications, File Management, Service Management, Package Management. Anisble is purely based on Python. Ansible using the SSH protocol based on the native OpenSSH package for communication with the Linux nodes which it manages.
What is PostgreSQL Database
PostgreSQL is a Open source object relational database. It is ACID compliant. It is available in the form of binary packages for most common operating systems today. It offers a number of different client authentication methods. The method used to authenticate a particular client connection can be selected on the basis of (client) host address, database, and user. PostgreSQL uses a concept called roles to handle client authentication and authorization. The installation procedure creates a user account called postgres that is associated with the default postgres role.
What is Ansible Role
Ansible role helps in packaging the related data for a particular configuration management activity. Its helps to package the variables, files, tasks, handlers, templates, defaults etc. and load them automatically once we define our load in a particular directory structure which we will see in the upcoming sections below.
Here in this article we will how we can setup Postgres SQL database using the Ansible role. We will some of the features of the roles like vars, files and tasks to provision the database and also we will see how we can use the Postgre SQL database module to create database and query the database using SQL queries and execute SQL scripts using the Ansible playbook.
This article to related to my previous article. For information related to the configuration files related to postgres database server you can go through it. This article is basically trying to achieve the same thing using the Ansible role feature and with addition of some Ansible Postgres SQL modules in the examples.
If you are interested in watching the video. Here is the youtube video on the same step by step procedure outlined below.
Procedure
Step1: Create roles
Here in this step we are going to create a standard directory structure for a roles named ‘common_postgres’, ‘install_postgres’, ‘create_postgresql_db’, ‘query_postgresql_db’.
Once the role has been created we have created sub directories within them for all the related variables, files, tasks, handlers, templates and default that we want them to be a part of this role and automatically loaded and used when the Ansible roles gets executed. Also, note under each sub directory we need to create the file named main.yml with the required content as this will be default file that gets loaded when the role is executed.
[admin@fedser32 playbooks]$ mkdir automatepostgresql
[admin@fedser32 playbooks]$ cd automatepostgresql/
[admin@fedser32 automatepostgresql]$ touch postgresql_database_setup.yml
[admin@fedser32 automatepostgresql]$ mkdir -p roles/common_postgres roles/install_postgres roles/create_postgresql_db roles/query_postgresql_db
[admin@fedser32 automatepostgresql]$ cd roles/common_postgres
[admin@fedser32 createpostgresqldb]$ mkdir -p tasks handlers library files templates vars defaults meta
[admin@fedser32 createpostgresqldb]$ touch tasks/main.yml handlers/main.yml library/main.yml files/mail.yml templates/main.yml vars/main.yml defaults/main.yml meta/main.yml
[admin@fedser32 common_postgres]$ cd ../..
[admin@fedser32 automatepostgresql]$ cd roles/install_postgres
[admin@fedser32 createpostgresqldb]$ mkdir -p tasks handlers library files templates vars defaults meta
[admin@fedser32 createpostgresqldb]$ touch tasks/main.yml handlers/main.yml library/main.yml files/mail.yml templates/main.yml vars/main.yml defaults/main.yml meta/main.yml
[admin@fedser32 common_postgres]$ cd ../..
[admin@fedser32 automatepostgresql]$ cd roles/create_postgresql_db
[admin@fedser32 createpostgresqldb]$ mkdir -p tasks handlers library files templates vars defaults meta
[admin@fedser32 createpostgresqldb]$ touch tasks/main.yml handlers/main.yml library/main.yml files/mail.yml templates/main.yml vars/main.yml defaults/main.yml meta/main.yml
[admin@fedser32 common_postgres]$ cd ../..
[admin@fedser32 automatepostgresql]$ cd roles/query_postgresql_db
[admin@fedser32 createpostgresqldb]$ mkdir -p tasks handlers library files templates vars defaults meta
[admin@fedser32 createpostgresqldb]$ touch tasks/main.yml handlers/main.yml library/main.yml files/mail.yml templates/main.yml vars/main.yml defaults/main.yml meta/main.yml
[admin@fedser32 common_postgres]$ cd ../..
Step2: Install the Ansible PostgreSQL module
We need to install the Ansible PostgreSQL module so that we can use to managed the PostgreSQL database remotely using the Ansible Playbook. Here is the Ansible module which needs to be installed on the Ansible controller system.
Ansible module – https://galaxy.ansible.com/community/postgresql
[admin@fedser32 automatepostgresql]$ ansible-galaxy collection install community.postgresqlStep3: Create the trigger playbook
This is the trigger playbook from which we will be calling the roles which we are going to define in the next steps.
[admin@fedser32 automatepostgresql]$ cat postgresql_database_setup.yml
---
- name: Postgre SQL database setup
hosts: all
become: true
become_user: root
roles:
- common_postgres
- install_postgres
- create_postgresql_db
# - query_postgresql_db
- { role: 'query_postgresql_db', become: true, become_user: admin }Step4: Create the role commom_postgres
This is a common role which can be reusable. Here we are just getting the distribution details of the Operation system and updating the packages to the latest available version on the managed node.
[admin@fedser32 tasks]$ pwd
/home/admin/middleware/stack/ansible/playbooks/automatepostgresql/roles/common_postgres/tasks
[admin@fedser32 tasks]$ cat main.yml
---
- name: Get Distrubution
debug: msg={{ ansible_distribution }}
register: dist
- name: Print Distribution
debug: msg={{ dist }}
- name: Update packages
yum: name=* state=latest
- name: Print the version
debug: msg={{ ver }}Step5: Create the role install_postgres
In this role we are installing the RPM repository for the Postgres database. Once the RPM repository is configured on the managed node we can install the Postgres SQL database RPM package and initialize the database. If the database has already been initialized it will skip to run the task related to initialize the database. Then we are updating the configuration files to allow for remote connection and for the database to listen on all the interfaces on the managed node.
Also note, we have created two more subdirectory files which are for the vars directory and files directory. From vars directories main.yml file we are going to refer the variables which are defined there and from files directory we are going to copy the configuration files to the database server on the managed node.
Tasks
[admin@fedser32 tasks]$ pwd
/home/admin/middleware/stack/ansible/playbooks/automatepostgresql/roles/install_postgres/tasks
[admin@fedser32 tasks]$ cat main.yml
---
- name: Configure the PostgreSQL RPM repository
dnf:
name: '{{ postgres_rpm_repository }}'
state: present
- name: Install PostgreSQL server
dnf:
name: postgresql{{ postgres_db_version }}-server
state: present
- name: Check if Database is initialized
stat:
path: /var/lib/pgsql/{{ postgres_db_version }}/data/pg_hba.conf
register: result
- name: Initialize the database
shell: /usr/pgsql-{{ postgres_db_version }}/bin/postgresql-{{ postgres_db_version }}-setup initdb
when: not result.stat.exists
- name: Allow remote connection to PostgreSQL database
copy:
src: pg_hba.conf
dest: /var/lib/pgsql/{{ postgres_db_version }}/data/
- name: Allow PostgreSQL to listen on all interfaces
copy:
src: postgresql.conf
dest: /var/lib/pgsql/{{ postgres_db_version }}/data/
- name: Start the database service
service:
name: postgresql-{{ postgres_db_version }}
state: started
- name: Allow PostgreSQL database service
firewalld:
service: postgresql
permanent: yes
state: enabledVariables
[admin@fedser32 vars]$ pwd
/home/admin/middleware/stack/ansible/playbooks/automatepostgresql/roles/install_postgres/vars
[admin@fedser32 vars]$ cat main.yml
---
postgres_rpm_repository: https://download.postgresql.org/pub/repos/yum/reporpms/F-34-x86_64/pgdg-fedora-repo-latest.noarch.rpm
postgres_db_version: 14Files
[admin@fedser32 files]$ pwd
/home/admin/middleware/stack/ansible/playbooks/automatepostgresql/roles/install_postgres/files
[admin@fedser32 files]$ ls -ltr
total 40
-rw-rw-r--. 1 admin admin 0 Dec 21 10:44 mail.yml
-rw-rw-r--. 1 admin admin 4679 Dec 21 12:18 pg_hba.conf
-rw-rw-r--. 1 admin admin 28790 Dec 21 12:18 postgresql.confStep6: Create the role create_postgresql_db
Here in this task we are going to create a user with super privieges by connecting to the database with the default ‘postgres’ user. Once the database user is created we are creating a new database with the super user that we just created.
Also, please note the vars folder with the updated main.yml file which define the postgres database environment variables which we use in the role task to connect to the database.
Tasks
[admin@fedser32 tasks]$ pwd
/home/admin/middleware/stack/ansible/playbooks/automatepostgresql/roles/create_postgresql_db/tasks
[admin@fedser32 tasks]$ cat main.yml
---
- name: Connect to default database and create a user with super prvileges
become: true
become_user: admin
postgresql_user:
name: '{{ postgres_user }}'
password: '{{ postgres_user_password }}'
role_attr_flags: CREATEDB,SUPERUSER,CREATEROLE,LOGIN
- name: Create a new database with name "pgdatabase"
become: true
become_user: admin
postgresql_db:
login_host: '{{ postgres_db_host }}'
login_user: '{{ postgres_user }}'
login_password: '{{ postgres_user_password }}'
port: '{{ postgres_port }}'
name: '{{ postgres_db_name }}'Variables
[admin@fedser32 vars]$ pwd
/home/admin/middleware/stack/ansible/playbooks/automatepostgresql/roles/create_postgresql_db/vars
[admin@fedser32 vars]$ cat main.yml
---
postgres_user: pgadmintest
postgres_user_password: pgadmintest1234
postgres_port: 5432
postgres_db_name: pgdatabase1234Step7: Create the role query_postgresql_db
Here in this task, we are going to query the provisioned database for the version using two different methods, one by running the sql query directly by connecting to the database and other is by copying the sql query script file and executing it on the remote database server.
Here also, we have created the vars folder with the updated main.yml file which define the postgres database environment variables which we use in the role task to connect to the database.
Also, note that for this task we are passing the user to switch to in the role itself, but for the create_postgresql_db role we are explicitly defining the become and become_user in the tasks main.yml file. This difference is approach is just to show you the methods of how we can switch user in the role.
Tasks
[admin@fedser32 tasks]$ pwd
/home/admin/middleware/stack/ansible/playbooks/automatepostgresql/roles/query_postgresql_db/tasks
[admin@fedser32 tasks]$ cat main.yml
- name: Run SQL query to capture database version
postgresql_query:
login_host: '{{ postgres_db_host }}'
login_user: '{{ postgres_user }}'
login_password: '{{ postgres_user_password }}'
port: '{{ postgres_port }}'
db: '{{ postgres_db_name }}'
query: SELECT version()
register: ver
- name: Print the version
debug: msg={{ ver }}
- name: Copy SQL script to provision the database
copy:
src: provisionpgdatabase.sql
dest: /tmp/provisionpgdatabase.sql
- name: Run SQL script to provision the database
postgresql_query:
login_host: '{{ postgres_db_host }}'
login_user: '{{ postgres_user }}'
login_password: '{{ postgres_user_password }}'
port: '{{ postgres_port }}'
db: '{{ postgres_db_name }}'
#path_to_script: "{{ role_path }}/files/provisionpgdatabase.sql"
path_to_script: /tmp/provisionpgdatabase.sql
register: ver
- name: Print the version
debug: msg={{ ver }}Variables
[admin@fedser32 vars]$ pwd
/home/admin/middleware/stack/ansible/playbooks/automatepostgresql/roles/query_postgresql_db/vars
[admin@fedser32 vars]$ cat main.yml
---
postgres_user: pgadmintest
postgres_user_password: pgadmintest1234
postgres_port: 5432
postgres_db_name: pgdatabase1234Files
[admin@fedser32 files]$ pwd
/home/admin/middleware/stack/ansible/playbooks/automatepostgresql/roles/query_postgresql_db/files
[admin@fedser32 files]$ cat provisionpgdatabase.sql
SELECT version()Step8: Execute the playbook with environment variables
[admin@fedser32 automatepostgresql]$ ansible-playbook postgresql_database_setup.yml -e "postgres_db_host=192.168.122.234"
PLAY [Postgre SQL database setup] ****************************************************************************************************************************
TASK [Gathering Facts] ***************************************************************************************************************************************
[DEPRECATION WARNING]: Distribution fedora 33 on host 192.168.122.234 should use /usr/bin/python3, but is using /usr/bin/python for backward compatibility
with prior Ansible releases. A future Ansible release will default to using the discovered platform python for this host. See
https://docs.ansible.com/ansible/2.9/reference_appendices/interpreter_discovery.html for more information. This feature will be removed in version 2.12.
Deprecation warnings can be disabled by setting deprecation_warnings=False in ansible.cfg.
ok: [192.168.122.234]
TASK [common_postgres : Get Distrubution] ********************************************************************************************************************
ok: [192.168.122.234] => {
"msg": "Fedora"
}
TASK [common_postgres : Print Distribution] ******************************************************************************************************************
ok: [192.168.122.234] => {
"msg": {
"changed": false,
"failed": false,
"msg": "Fedora"
}
}
TASK [common_postgres : Update packages] *********************************************************************************************************************
ok: [192.168.122.234]
TASK [install_postgres : Configure the PostgreSQL RPM repository] ********************************************************************************************
ok: [192.168.122.234]
TASK [install_postgres : Install PostgreSQL server] **********************************************************************************************************
ok: [192.168.122.234]
TASK [install_postgres : Check if Database is initialized] ***************************************************************************************************
ok: [192.168.122.234]
TASK [install_postgres : Initialize the database] ************************************************************************************************************
skipping: [192.168.122.234]
TASK [install_postgres : Allow remote connection to PostgreSQL database] *************************************************************************************
ok: [192.168.122.234]
TASK [install_postgres : Allow PostgreSQL to listen on all interfaces] ***************************************************************************************
ok: [192.168.122.234]
TASK [install_postgres : Start the database service] *********************************************************************************************************
ok: [192.168.122.234]
TASK [install_postgres : Allow PostgreSQL database service] **************************************************************************************************
ok: [192.168.122.234]
TASK [create_postgresql_db : Connect to default database and create a user with super prvileges] *************************************************************
changed: [192.168.122.234]
TASK [create_postgresql_db : Create a new database with name "pgdatabase"] ***********************************************************************************
ok: [192.168.122.234]
TASK [query_postgresql_db : Run SQL query to capture database version] ***************************************************************************************
ok: [192.168.122.234]
TASK [query_postgresql_db : Print the version] ***************************************************************************************************************
ok: [192.168.122.234] => {
"msg": {
"changed": false,
"failed": false,
"query": "SELECT version()",
"query_result": [
{
"version": "PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.3.1 20210422 (Red Hat 10.3.1-1), 64-bit"
}
],
"rowcount": 1,
"statusmessage": "SELECT 1"
}
}
TASK [query_postgresql_db : Copy SQL script to provision the database] ***************************************************************************************
ok: [192.168.122.234]
TASK [query_postgresql_db : Run SQL script to provision the database] ****************************************************************************************
ok: [192.168.122.234]
TASK [query_postgresql_db : Print the version] ***************************************************************************************************************
ok: [192.168.122.234] => {
"msg": {
"changed": false,
"failed": false,
"query": "SELECT version()\n",
"query_result": [
{
"version": "PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.3.1 20210422 (Red Hat 10.3.1-1), 64-bit"
}
],
"rowcount": 1,
"statusmessage": "SELECT 1"
}
}
PLAY RECAP ***************************************************************************************************************************************************
192.168.122.234 : ok=18 changed=1 unreachable=0 failed=0 skipped=1 rescued=0 ignored=0Hope you enjoyed reading this article. Thank you..
Leave a Reply
You must be logged in to post a comment.