Automate PostgreSQL database deployment using RHEL System Roles

PostgreSQL is a robust open source relational database, and its popularity as a database server has grown in recent years. Setting up and deploying a single PostgreSQL instance quickly is not complex, but it can still be challenging to deploy multiple servers. Also, managing several servers could take a nontrivial amount of time.

For such situations, Red Hat Enterprise Linux (RHEL) offers RHEL System Roles, which provide an abstract configuration interface for certain services using Ansible. Recently, the upstream project added a PostgreSQL role to the list.

[ Get started with Ansible automation controller in this hands-on interactive lab. ]

This new PostgreSQL System Role is intended to speed up and simplify deployment and management of a PostgreSQL server. It's suitable for various kinds of users as it offers quick deployment to run a simple database server without any further configuration. It also supports advanced settings. A significant advantage is configuration and self-tuning based on provided hardware. This feature is supposed to enable higher performance and stability of the database server by following upstream recommendations for the most important configuration options.

The PostgreSQL System Role supports full database server configuration through configuration files. Moreover, you can provide the SQL script for database initialization and many other options like SSL support, password settings, and more.

The upstream GitHub repo provides more information about the PostgreSQL System Role.

Get started with the PostgreSQL System Role

The PostgreSQL System Role is straightforward to deploy and use: Clone the repository and define your playbook. You can find a couple of examples to use as templates in the repository. The role runs on RHEL 8+ systems or Fedora 35 and later.

Here is a basic example of how to run the PostgreSQL System Role directly from the upstream GitHub:

1. Install Ansible using DNF:

   $ sudo dnf install ansible-core

2. If needed, prepare your remote host, or you can run it on your localhost.

3. Clone the System Role Git repository:

   $ git clone https://github.com/linux-system-roles/postgresql.git \
       linux-system-roles.postgresql

4. Define your basic Ansible playbook playbook.yaml in your favorite editor:

   - name: Config postgresql version 13
     hosts: all
     vars:
       postgresql_version: "13"
   
     tasks:
     - name: Configure postgresql using role
       import_role:
         name: linux-system-roles.postgresql
       become: yes
       become_user: root

5. Run the Ansible playbook. Use parameter -K if your user requires a sudo password:

   $ ansible-playbook playbook.yaml

6. Check the connection to a newly deployed and running PostgreSQL server. If needed, connect to the remote host using SSH or run this command on the localhost:

   $ sudo bash -c 'su - postgres'
   
   $ psql

[ Learn more about using RHEL System Roles to manage Linux at scale. ]

Deploy PostgreSQL using advanced configuration

The previous example shows a simple database server deployment with the default configuration. But the PostgreSQL System Role also supports more advanced operations and settings. This example shows a more advanced configuration by deploying a PostgreSQL server with a modified huge_pages option, enabled SSL/TLS connection, and configured access file pg_hba.conf:

Define playbook_adv.yaml:

- name: Config postgresql version 13
  hosts: all
  vars:
    postgresql_version: "13"
    postgresql_certificates:
      - name: test_crt
        dns: www.example.com
        ca: self-sign
    postgresql_ssl_enable: true
    postgresql_pg_hba_conf:
      - type: hostssl
        database: all
        user: all
        auth_method: md5
        address: '127.0.0.1/32'
    postgresql_password: mypass
    postgresql_server_conf:
      huge_pages: try
  tasks:
  - name: Configure postgresql using role
    import_role:
      name: linux-system-roles.postgresql
    become: yes
    become_user: root

This playbook installs PostgreSQL version 13, generates a self-signed certificate for SSL/TLS connections, sets the huge_pages option to value try in the postgresql.conf file, modifies access to the database in the pg_hba.conf file, and sets the password for the super user.

Because this playbook installs a self-signed TLS certificate, it requires additional collections to run. To install the requirements, use the ansible-galaxy command with the requirements file provided with the System Role:

$ ansible-galaxy collection install -r \
    linux-system-roles.postgresql/meta/collection-requirements.yml

Alternatively, for RHEL, use:

# dnf install rhel-system-roles

Or for Fedora:

# dnf install linux-system-roles

Then, test the playbook:

$ ansible-playbook playbook_adv.yaml

Check the connection to a newly deployed PostgreSQL server. If required, connect to your remote host through SSH or use localhost. Connect to the server according to settings in pg_hba.conf:

$ psql -h 127.0.0.1 -U postgres
Password for user postgres: **********
psql (13.10)

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
bits: 256, compression: off)

Then check the huge_pages parameter value:

postgres=# show huge_pages;

huge_pages
------------
try

(1 row)

Wrap up

The new PostgreSQL System Role provided by RHEL is a great tool for quickly and easily deploying and managing a PostgreSQL server. The role offers many configuration options, allowing you to modify the setup to suit your needs. Also, the role supports self-tuning based on the provided hardware, which enables higher performance and stability of the database server. Overall, it is a useful tool for deploying and managing PostgreSQL servers efficiently.

This article demonstrates how you can use the role, currently in the upstream, to set up an SSL/TLS connection, modify access settings, and set the password for the super user. In addition, the role supports self-tuning based on the provided hardware, allowing users to achieve greater performance and stability. We plan to add the PostgreSQL System Role to RHEL and Fedora in future releases.

[ Learn more about how to automate Red Hat Enterprise Linux. ]