How to install and configure PostgreSQL database using Ansible Playbook
Test Environment
Fedora 33 installed
Here in this article we will see how we can utilize the Ansible modules to automate the installation and configuration of the PostgreSQL database on a Fedora Linux OS. Also we will use the PostgreSQL database modules to create user role and create a database using the user role which was created initially.
If you are interested in watching the video. Here is the youtube video on the same step by step procedure below.
Procedure
Step1: Identify the OS Platform and update the upgrade the system
Here in this step we are fetching the OS platform of the remote managed node on which we are trying to install and configure the PostgreSQL database and upgrade the system by installing the latest packages available in the repositories.
- name: Get the OS platform
command: cat /etc/redhat-release
register: redhatRelease
- name: Print the redhat-release
debug: msg={{ redhatRelease.stdout }}
- name: Update packages
yum: name=* state=latestStep2: Configure the PostgreSQL RPM repository
Once we have our remote managed node in the updated state, we can configure the Postgre SQL RPM repository from which we will be installing the Postgre SQL database server packages.
- name: Install the PostgreSQL RPM repository
dnf:
name: "https://download.postgresql.org/pub/repos/yum/reporpms/F-34-x86_64/pgdg-fedora-repo-latest.noarch.rpm"
state: presentStep3: Install PostgreSQL server package
Here we are going to install the PostgreSQL database server from the repository that we configured in our previous step.
- name: Install PostgreSQL server
dnf:
name: postgresql14-server
state: presentStep4: Check if PostgreSQL database is initialized
As a pre-requisite we will check if the PostgreSQL database server has been initialised by running the setup script. We are incorporating this step before the initialisation step to make sure that the playbook does not through any errors if its executed multiple time and the database server has been already initialised.
- name: Check if Database is initialized
stat:
path: /var/lib/pgsql/14/data/pg_hba.conf
register: resultStep5: Initialize the PostgreSQL database if not already done
Once the pre-requisite step has been executed, we will initialize the database only if the pg_hba.conf file is not present in the required location.
- name: Initialize the database
shell: /usr/pgsql-14/bin/postgresql-14-setup initdb
when: not result.stat.existsStep6: Allow remote connection to PostgreSQL database
Once the installation and initialisation of the database is completed, we are updating the default configuration files to make sure remote connection is enabled with username and password for authentication. For this we are going to update pg_hba.conf as shown below and copy the updated configuration file to required location on the PostgreSQL database server.
Also note that for local connection there is no authentication required as we updated the method to trust.
Updated pg_hba.conf file
...
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
# Allow access from everywhere
host all all 0.0.0.0/0 md5Here is the task to copy the updated pg_hba.conf file.
- name: Allow remote connection to PostgreSQL database
copy:
src: /home/admin/middleware/stack/ansible/playbooks/postgresql-setup/pg_hba.conf
dest: /var/lib/pgsql/14/data/Step7: Allow PostgreSQL database server to listen on all network interfaces
For this we are going to update the default postgresql.conf to listen on all network interfaces on port 5432. The default postgresql.conf file can be obtained from the remote managed node at the following location ‘/var/lib/pgsql/14/data/postgresql.conf’.
...
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*'
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
...Here is the task to copy the updated postgresql.conf file.
- name: Allow PostgreSQL to listen on all interfaces
copy:
src: /home/admin/middleware/stack/ansible/playbooks/postgresql-setup/postgresql.conf
dest: /var/lib/pgsql/14/data/Step8: Start the PostgreSQL database service
Now that we have our PostgreSQL database server installed and configured, we can start the service as shown below.
- name: Start the database service
service:
name: postgresql-14
state: startedStep9: Allow traffic for PostgreSQL database service
By default, the firewall setting does not allow for the remote connection to the PostgreSQL database service. For enabling the remote connection we need to updated the firewall setting to allow traffic for PostgreSQL database service as shown below.
- name: Allow PostgreSQL database service
firewalld:
service: postgresql
permanent: yes
state: enabledStep10: Completed ansible-playbook for PostgreSQL database server installation and configuration
Here is the complete playbook for the installation and configuration of the database service on Fedora linux OS. Please note hosts section contains the IP address of my remote managed node on which i am configuring the database. Please update this with your respective IP address, FQDN or group name of the database server based on the ansible inventory file in your environment.
[admin@fedser32 postgresql-setup]$ pwd
/home/admin/middleware/stack/ansible/playbooks/postgresql-setup
[admin@fedser32 postgresql-setup]$ cat install-postgresql.yml
---
- name: Postgre SQL Database installation and configuration
hosts: 192.168.122.234
become: true
become_user: root
tasks:
- name: Get the OS platform
command: cat /etc/redhat-release
register: redhatRelease
- name: Print the redhat-release
debug: msg={{ redhatRelease.stdout }}
- name: Update packages
yum: name=* state=latest
- name: Configure the PostgreSQL RPM repository
dnf:
name: "https://download.postgresql.org/pub/repos/yum/reporpms/F-34-x86_64/pgdg-fedora-repo-latest.noarch.rpm"
state: present
- name: Install PostgreSQL server
dnf:
name: postgresql14-server
state: present
- name: Check if Database is initialized
stat:
path: /var/lib/pgsql/14/data/pg_hba.conf
register: result
- name: Initialize the database
shell: /usr/pgsql-14/bin/postgresql-14-setup initdb
when: not result.stat.exists
- name: Allow remote connection to PostgreSQL database
copy:
src: /home/admin/middleware/stack/ansible/playbooks/postgresql-setup/pg_hba.conf
dest: /var/lib/pgsql/14/data/
- name: Allow PostgreSQL to listen on all interfaces
copy:
src: /home/admin/middleware/stack/ansible/playbooks/postgresql-setup/postgresql.conf
dest: /var/lib/pgsql/14/data/
- name: Start the database service
service:
name: postgresql-14
state: started
- name: Allow PostgreSQL database service
firewalld:
service: postgresql
permanent: yes
state: enabledStep11: Execute the Playbook to install and configure the PostgreSQL database service
Here is the complete execution of the playbook to install and configure the PostgreSQL database service.
[admin@fedser32 postgresql-setup]$ ansible-playbook install-postgresql.yml
PLAY [Postgre SQL Database installation and configuration] ***************************************************************************************************
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 [Get the OS platform] ***********************************************************************************************************************************
changed: [192.168.122.234]
TASK [Print the redhat-release] ******************************************************************************************************************************
ok: [192.168.122.234] => {
"msg": "Fedora release 33 (Thirty Three)"
}
TASK [Update packages] ***************************************************************************************************************************************
ok: [192.168.122.234]
TASK [Configure the PostgreSQL RPM repository] ***************************************************************************************************************
ok: [192.168.122.234]
TASK [Install PostgreSQL server] *****************************************************************************************************************************
ok: [192.168.122.234]
TASK [Check if Database is initialized] **********************************************************************************************************************
ok: [192.168.122.234]
TASK [Initialize the database] *******************************************************************************************************************************
skipping: [192.168.122.234]
TASK [Allow remote connection to PostgreSQL database] ********************************************************************************************************
ok: [192.168.122.234]
TASK [Allow PostgreSQL to listen on all interfaces] **********************************************************************************************************
ok: [192.168.122.234]
TASK [Start the database service] ****************************************************************************************************************************
ok: [192.168.122.234]
TASK [Allow PostgreSQL database service] *********************************************************************************************************************
ok: [192.168.122.234]
PLAY RECAP ***************************************************************************************************************************************************
192.168.122.234 : ok=11 changed=1 unreachable=0 failed=0 skipped=1 rescued=0 ignored=0
Step12: Install the Ansible PostgreSQL module
We need to install the Ansible PostgreSQL module so that we can 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 postgresql-setup]$ ansible-galaxy collection install community.postgresqlStep13: Setup a user role and database using the PostgreSQL modules
Here is a very basic ansible playbook which used the postgresql modules names postgresql_user and postgresql_db to create a user role and create a database using the role which we created.
[admin@fedser32 postgresql-setup]$ cat configure-postgresql.yml
---
- name: Creating user roles and database
hosts: all
tasks:
- name: Connect to default database and create a user with super prvileges
postgresql_user:
name: dbadmin
password: dbadmin1234
role_attr_flags: CREATEDB,SUPERUSER,CREATEROLE,LOGIN
- name: Create a new database with name "testdatabase"
postgresql_db:
login_host: 192.168.122.234
login_user: dbadmin
login_password: dbadmin1234
port: 5432
name: testdatabaseStep14: Execute the playbook to create user role and database
Now, let’s execute the playbook to create the required user role with the mentioned privileges and create a database thereafter.
[admin@fedser32 postgresql-setup]$ ansible-playbook configure-postgresql.yml
PLAY [Creating user roles and database] **********************************************************************************************************************
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 [Connect to default database and create a user with super prvileges] ************************************************************************************
changed: [192.168.122.234]
TASK [Create a new database with name "testdatabase"] ********************************************************************************************************
ok: [192.168.122.234]
PLAY RECAP ***************************************************************************************************************************************************
192.168.122.234 : ok=3 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0Step15: Validate the user role and database creation on remote node
We can SSH to our remote managed node (ie. database server) and validate whether the user role and database got created as per the playbook as shown below.
List user roles
[root@fedmaster data]# sudo -u postgres psql
psql (14.0)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
dbadmin | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}List databases
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdatabase | dbadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | Hope you enjoyed reading this article. Thank you..
Leave a Reply
You must be logged in to post a comment.