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.yamlin 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
-Kif your user requires a
$ 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
- 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
$ 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)
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. ]