Skip to content
Johnny Morano's Tech Articles

Johnny Morano's Tech Articles

Ramblings of an old-fashioned space cowboy

Menu
  • About
  • Privacy Policy
Menu

Deploy a PostgreSQL database with an initial schema using Ansible

Posted on April 9, 2022April 9, 2022 by Johnny Morano

Ansible is a great automation tool to manage operating systems, but also to manage database like PostgreSQL. Many Ansible modules are available to create playbooks which execute various database administration tasks.

In this article we will have a closer look how to ensure that

  • a default database has been created
  • a set of configured extensions have been installed
  • an initial database schema has been deployed

The user that will log on to the remote host using Ansible (and SSH), must be able to become the postgres user using sudo without a password (in this example, can be changed of course).

The tasks below are split in two big tasks, both containing a block. Blocks allow to group certain tasks which might need the same variables (for instance the same tags, become variables, …).

The first block will ensure that the default database has been created, based on a YAML variable called {{ db_name }}. This variable must be set in either a group_vars file, host_vars file or supplied at the command line.

In the same block, Ansible will also ensure that the pg_stat_statements extension is enabled and installed in the above database. pg_stat_statements is a very useful extension to debug or display statistics regarding the SQL statements executed on that specific database.

And the end of the first block, a task was added to loop over a YAML variable called {{ shared_preload_extensions }}. The variable is a supposed to be a list and should contain all extra extensions required to enabled to the above database. Shared preload extensions also need to be configured in the postgresql.conf file, which is not covered in this article.

- name: Default Database
  tags: default_database
  vars:
    ansible_become_user: postgres
    ansible_become_method: sudo
    ansible_become_pass: null
  block:
    - name: Ensure required database
      postgresql_db:
        name: "{{ db_name }}"
        encoding: UTF-8

    - name: Ensure pg_stat_statements extension
      postgresql_ext:
        name: "pg_stat_statements"
        db: "{{ db_name }}"
        schema: public
        state: present

    - name: Ensure shared_preload extensions
      postgresql_ext:
        name: "{{ item }}"
        db: "{{ db_name }}"
        state: present
      loop: "{{ shared_preload_libraries.split(',') }}"
      loop_control:
        label: " {{ item }}"

- name: Ensure initial database schema
  tags: default_database
  block:
    - name: Schema definition file
      template:
        src: "{{ db_name }}/schema_definition.sql.j2"
        dest: /var/lib/postgresql/initial_schema_definition.sql
        owner: postgres
        group: postgres
        mode: 0600
      when: ( role_path + "/templates/" + db_name + "/schema_definition.sql.j2" ) is file
      register: initial_schema

    - name: Apply the schema definition
      ignore_errors: True
      vars:
        ansible_become_user: postgres
        ansible_become_method: sudo
        ansible_become_pass: null
      community.postgresql.postgresql_query:
        db: "{{ db_name }}"
        path_to_script: /var/lib/postgresql/initial_schema_definition.sql
        encoding: UTF-8
        as_single_query: yes
      when: initial_schema.changed

  rescue:
    - debug:
        msg: "No schema definition found for {{db_name}}, skipping..."

The second block of tasks will ensure that an initial database schema is deployed. This block consists of two tasks only.

The first task will upload the schema definition SQL-based file to a specific path on the remote host.

The second task will try to execute that SQL file, if it was changed by the first task. This means of course that if the file was changed manually on the remote host (or even removed), Ansible will update that file again in the first task and it will deploy the full file again in the second task! This might overwrite or break your database.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Recent Posts

  • Use multiple Azure subscriptions in Terraform modules
  • Read the HAProxy UNIX socket file using Perl
  • A Prometheus Exporter framework written in Perl
  • Managing LDAP passwords with Perl
  • Libvirt guest startup issue with AppArmor
  • Deploy a PostgreSQL database with an initial schema using Ansible
  • Using Ansible to finalize Hashicorp Packer images

Categories

  • Automation (8)
  • Blog (60)
  • Database (4)
  • Development (37)
  • Linux (26)
  • Mac OS X (5)
  • Media (2)
  • OpenBSD (3)
  • Perl (34)
  • Photo (2)
  • PostgreSQL (4)
  • Terraform (5)
  • Web (11)

Tags

Ajax (3) Android (1) Ansible (2) API (5) AppArmor (1) Automation (5) Azure (3) azurerm (2) Bash (4) Cloud (2) CPAN (4) CSS (1) Debian (4) Dev (35) DevOps (11) EXIF (1) Facebook (1) Geotag (1) GMail (1) Google (3) Hack (2) Hashicorp (4) Hetzner (2) HTML (4) IMAP (2) IPTables (6) JavaScript (4) Libvirt (2) Linux (25) Logging (2) MacOSX (5) Media (2) Monitoring (6) MySQL (3) OpenBSD (4) Packer (1) Perl (35) PF (2) Postgresql (6) Security (7) SysAdmin (24) Terraform (4) Ubuntu (2) UNIX (9) Web 2.0 (3)

Archive

  • April 2022 (10)
  • March 2022 (6)
  • December 2016 (1)
  • March 2016 (1)
  • November 2015 (1)
  • November 2014 (1)
  • August 2014 (1)
  • May 2014 (1)
  • February 2014 (2)
  • December 2013 (1)
  • October 2013 (2)
  • September 2013 (2)
  • August 2013 (2)
  • October 2012 (1)
  • August 2012 (4)
  • March 2012 (3)
  • July 2011 (1)
  • June 2011 (2)
  • April 2011 (3)
  • March 2011 (4)
  • February 2011 (2)
  • December 2010 (2)
  • October 2010 (4)
  • September 2010 (1)
  • August 2010 (5)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Footer

  • Shihai Corp
  • My Photo website
© 2022 Johnny Morano's Tech Articles | Powered by Superbs Personal Blog theme
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
Do not sell my personal information.
Cookie SettingsAccept
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT