Creating PostgreSQL Database With Ansible

Image courtesy of pexels.com

Ansible is one of the most popular configuration management applications going around right now, if your new to Ansible, things won’t get too difficult in the following post but it should show you how easy it is to create a PostgreSQL Database using Ansible.

If your looking a great way to learn more about Ansible, follow the link to our latest book, Practical Ansible

In this post we will be performing the following steps to create our PostgreSQL Database…If you don’t have Ansible up and running, we also have a Docker Image you could use to try out Ansible and the following steps:

1. Create a hosts file, although not needed in small examples, usually something we do.
2. Create the playbook. This is generally where the magic happens, and will run through the file line by line.
3. Create the SQL file. Not needed, but allows us to create some basic data to implement into the database, to then test later.
4. Run the playbook and test the changes we have made by accessing the database and seeing if our data is available.

In this post we will not be creating a single playbook with all variables and data on the same file, without worrying about roles, or anything like that for now. So lets not delay and get stuck straight into setting this up.

1.Create A Hosts File

Although not really needed, we prefer to run the playbook using a hosts file which means we can test on our localhost and then change the IP address if we wish to push it to a different environment. We only have one entry in our hosts file below, but feel free to add it to your current hosts file if you have one.

First create the hosts file using the touch command:

touch hosts 

Now open the hosts file with your text editor and add in the details of the server you are deploying your database to:

[psql]
127.0.0.1

2.Create The MySQL Playbook

This is the part that does all the heavy lifting. It installs the applications needed, configures them to look the way we want, creates a test database and then adds some basic data. We need somewhere to house all of this, so start by running the command below to create the file named db-server-playbook.yml:

touch db-server-playbook.yml

Open the playbook with your text editor and we start to add in details to create our PostgreSQL database.

NOTE: Don’t add the line numbers at the start of each line as they are simply there to help explain the code.

Enter the first six lines of the playbook. All are pretty standard making sure it deploys the changes to our psql host we specified in our hosts file. It then moves on to add variables for db_user, db_name and db_password. This is not the best way to store variables, especially secrets but, we are just adding them in here for simplicity:

  1 ---
  2 - hosts: psql
  3   vars:
  4     db_user: dbusername
  5     db_name: dbname
  6     db_password: dbpassword

We can now add tasks to our playbook. The following are the main tasks that first install a list of dependencies on the host and then we install the PostgreSQL applications include Psycopg2. Line 22 then verifies that PostgreSQL is running on the host:

  7  tasks:
  8  - name: Install dependencies for PostgreSQL
  9    apt: name={{ item }} update_cache=true state=latest
 10    with_items:
 11    - bash
 12    - openssl
 13    - libssl-dev
 14    - libssl-doc
 15  - name: Install PostgreSQL 
 16    apt: name={{ item }} update_cache=true state=present
 17    with_items:
 18    - postgresql
 19    - postgresql-contrib
 20    - libpq-dev
 21    - python3-psycopg2
 22  - name: Ensure the PostgreSQL service is running
 23    service: name=postgresql state=started enabled=yes

Then next block of tasks will specifically perform PostgreSQL modules using the postgres_db module to create the database we specified in our variables at the start of the playbook. We then use the postgres_user module to create the new user and specify the permissions needed on the database:

 24  - name: Create the database specified in vars
 25    become: true
 26    become_user: postgres
 27    postgresql_db: name={{ db_name }}
 28         template='template0'
 29         state=present
 30  - name: Ensure user has access to the new database
 31    become: true
 32    become_user: postgres
 33    postgresql_user: db={{ db_name }}
 34         name={{ db_user }}
 35         password={{ db_password }}
 36         priv=ALL
 37         state=present
 38  - name: Ensure user does not have unnecessary permissions
 39    become: true
 40    become_user: postgres
 47    postgresql_user: name={{ db_user }}
 48         role_attr_flags=NOSUPERUSER,NOCREATEDB
 49         state=present

Finally we add a tasks that adds in some dummy data into the database. We have added this in as a place holder to demonstrate how you would use an SQL file possibly from an existing database to import data into our new database:

 50  - name: Add some dummy data to our database
 51    become: true
 52    become_user: postgres
 53    shell: psql {{ db_name }} < /tmp/dump.sql

Save the data you have added to the playbook and we will need to make the SQL data before we move onto running the playbook.

3.Create The SQL File

Create a new file, called dump.sql as we have with the command below:

touch /tmp/dump.sql

Open the file in your text editor and we will add in some simple commands to create a table, and add some data to the table and then change the ownership to the user we created in our playbook:

CREATE TABLE IF NOT EXISTS test (           
  message varchar(255) NOT NULL         
  );          
  INSERT INTO test(message) VALUES('Ansible is fun');
ALTER TABLE test OWNER TO "dbusername";

Change the ownership of the /tmp/dump.sql file to the postgres user, so it can be used as part of our playbook:

chown postgres:postgres /tmp/dump.sql

NOTE: If you are setting this up on a remote server, you will need to copy this dump.sql file to the remove host as part of your playbook before you use it.

Save the data and we will now move on to running our new playbook and verifying everything has worked successfully.

4.Run The New Playbook And Test

We should now have everything ready to run the playbook on our host. If you have some issues and need to get a more verbose output, make sure to add the -vvv options at the end of the following command:

ansible-playbook -i hosts db-server-playbook.yml

If everything is successful, you should see something similar to the output below:

PLAY [psql]*********************************************************
TASK [Gathering Facts]**********************************************
ok: [127.0.0.1]
TASK [Ensure bash, OpenSSl, and libssl are the latest versions]*****
ok: [127.0.0.1] => (item=['bash', 'openssl', 'libssl-dev', 'libssl-doc'])
TASK [Install PostgreSQL]*******************************************
ok: [127.0.0.1] => (item=['postgresql', 'postgresql-contrib', 'libpq-dev', 'python3-psycopg2'])
TASK [Ensure the PostgreSQL service is running]*********************
ok: [127.0.0.1]
TASK [Create the database specified in vars]************************
ok: [127.0.0.1]
TASK [Ensure user has access to the new database]*******************
[WARNING]: Module did not set no_log for no_password_changes
ok: [127.0.0.1]
TASK [Ensure user does not have unnecessary permissions]************
ok: [127.0.0.1]
TASK [Add some dummy data to our database]**************************
changed: [127.0.0.1]
PLAY RECAP ********************************************************************
127.0.0.1: ok=8    changed=1    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

On the host we have just created our database on, we can now access the database and verify it is available, the table named “test” has been created, and has sample data in it:

psql dbname -h localhost -U dbusername
Password for user dbusername:
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
dbname=> \dt
         List of relations
 Schema | Name | Type  |   Owner
--------+------+-------+------------
 public | test | table | dbusername
(1 row)
dbname=> select * from test;
    message
----------------
 Ansible is fun
(1 rows)

We’ve chosen an easier option here, but you can see without too much fuss, we’ve been able to create our playbook and deploy our database.

Check out my new book “Practical Ansible: Configuration Management from Start to Finish” for more information on working with Ansible to streamline your configuration management process.

Found this post useful? Kindly tap the clap button! :)

Vince Sesto is a DevOps Engineer, Endurance Athlete and Author.

As a DevOps Engineer I specialise in Linux, Cloud Services and Open Source Applications. Particularly interested in developing my skills in DevOps, Continuous Integration, Security, Reporting/Observability and Python Development.