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:
- Install Ansible using DNF:
$ sudo dnf install ansible-core
-
If needed, prepare your remote host, or you can run it on your localhost.
-
Clone the System Role Git repository:
$ git clone https://github.com/linux-system-roles/postgresql.git \ linux-system-roles.postgresql
- 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
- Run the Ansible playbook. Use parameter
-K
if your user requires asudo
password:$ ansible-playbook playbook.yaml
- 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.
Browse by channel
Automation
The latest on IT automation for tech, teams, and environments
Artificial intelligence
Updates on the platforms that free customers to run AI workloads anywhere
Open hybrid cloud
Explore how we build a more flexible future with hybrid cloud
Security
The latest on how we reduce risks across environments and technologies
Edge computing
Updates on the platforms that simplify operations at the edge
Infrastructure
The latest on the world’s leading enterprise Linux platform
Applications
Inside our solutions to the toughest application challenges
Original shows
Entertaining stories from the makers and leaders in enterprise tech
Products
- Red Hat Enterprise Linux
- Red Hat OpenShift
- Red Hat Ansible Automation Platform
- Cloud services
- See all products
Tools
- Training and certification
- My account
- Customer support
- Developer resources
- Find a partner
- Red Hat Ecosystem Catalog
- Red Hat value calculator
- Documentation
Try, buy, & sell
Communicate
About Red Hat
We’re the world’s leading provider of enterprise open source solutions—including Linux, cloud, container, and Kubernetes. We deliver hardened solutions that make it easier for enterprises to work across platforms and environments, from the core datacenter to the network edge.
Select a language
Red Hat legal and privacy links
- About Red Hat
- Jobs
- Events
- Locations
- Contact Red Hat
- Red Hat Blog
- Diversity, equity, and inclusion
- Cool Stuff Store
- Red Hat Summit