Subscribe to the feed

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
  1. If needed, prepare your remote host, or you can run it on your localhost.

  2. Clone the System Role Git repository:

    $ git clone https://github.com/linux-system-roles/postgresql.git \
        linux-system-roles.postgresql
  1. 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
  1. Run the Ansible playbook. Use parameter -K if your user requires a sudo password:
    $ ansible-playbook playbook.yaml
  1. 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. ]


About the author

Filip Januš is a software engineer at Red Hat. He mostly takes care of databases, focusing on Postgresql and related database tools on Fedora, Red Hat Enterprise Linux, and CentOS. Filip is also studying for his PhD in cybersecurity and blockchain. He is a sports enthusiast and enjoys football, cycling, and badminton.

Read full bio
UI_Icon-Red_Hat-Close-A-Black-RGB

Browse by channel

automation icon

Automation

The latest on IT automation for tech, teams, and environments

AI icon

Artificial intelligence

Updates on the platforms that free customers to run AI workloads anywhere

open hybrid cloud icon

Open hybrid cloud

Explore how we build a more flexible future with hybrid cloud

security icon

Security

The latest on how we reduce risks across environments and technologies

edge icon

Edge computing

Updates on the platforms that simplify operations at the edge

Infrastructure icon

Infrastructure

The latest on the world’s leading enterprise Linux platform

application development icon

Applications

Inside our solutions to the toughest application challenges

Original series icon

Original shows

Entertaining stories from the makers and leaders in enterprise tech