In this guide we are going to install and configure Postgresql 17 in Rocky Linux Using Ansible. This guide works for other RHEL 8 based derivatives like Alma Linux and Oracle Linux.
Postgresql is an open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.
Ansible is an open-source software provisioning, configuration management, and application-deployment tool enabling infrastructure as code. It runs on many Unix-like systems, and can configure both Unix-like systems as well as Microsoft Windows.
Check also:
Prerequisites
To follow along, ensure that you have the following:
- An updated Rocky Linux server
- SSH access to the server with user having sudo access
- Latest version of Ansible installed locally
Ensuring that the server is up to date
Before proceeding, ensure that the server is up to date using this tasks. We are doing a. dnf update for all packages
1
2
3
4
| - name: Upgrade all packages on servers
dnf:
name: "*"
state: latest
|
Installing necessary packages
Next, let us install some common packages that we will need in our playbook. The `python3-psycopg2x package will be used to connect to the postgres server from ansible.
1
2
3
4
5
| - name: Install required packages
dnf:
name:
- python3-psycopg2
state: present
|
Setting up Postgres 17 Repo
Next, let us set up the repo for Postgres 17. The default Rocky Linux 8 repos contains an outdated version of Postgres. Let us set up these repos that will allow us to install Postgres 17. We are also disabling built in postgres module since it contains outdated version of postgres.
1
2
3
4
5
6
7
8
9
10
11
| - name: Add Postgres 17 repo
shell: |
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
args:
warn: no
- name: Disable built-in PostgreSQL module
shell: |
dnf -qy module disable postgresql
args:
warn: no
|
Installing Postgres 17 in Rocky Linux
Now that we have set up the repos, we can install Postgres. Use this task to achieve that
1
2
3
4
5
6
7
| - name: Install postgresql
dnf:
name:
- postgresql17-server
- postgresql17-contrib
notify:
- Enable Postgresql
|
In the above, we are installing postgresql server and contrib which contains extra functionnality.
Initializing Postgres Database
We need to initialize postgres after installation. Use this task to initialize postgres.
1
2
3
| - name: Initialize the PostgreSQL database
shell: |
/usr/pgsql-17/bin/postgresql-17-setup initdb
|
Configuring Postgres 17
With Postresql install, we would want to do some more configurations.
Let us update the listen address so we can access the service from a remote machine and then change identification to trust:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| - name: Ensure PostgreSQL is listening on *
lineinfile:
dest: /var/lib/pgsql/17/data/postgresql.conf
regexp: '^listen_addresses\s*='
line: "listen_addresses='*'"
state: present
notify: Restart Postgresql
- name: Add new configuration to "pg_hba.conf"
blockinfile:
dest: /var/lib/pgsql/17/data/pg_hba.conf
block: |
host all all 0.0.0.0/0 md5
host all all ::/0 md5
notify: Restart Postgresql
- name: Change peer identification to trust
shell: /bin/sed -i '/^local/s/peer/trust/' /var/lib/pgsql/17/data/pg_hba.conf
args:
warn: no
notify: Restart Postgresql
|
Starting Postgresql Creating a Postgres Super User
Let us also create a user that will allow us to manage database operations. This is like a root user for our DB. We will start by starting the service before doing the user creation.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| - name: Start Postgresql server
systemd:
name: postgresql-17
state: started
notify:
- Enable Postgresql
- name: Create a Superuser PostgreSQL database user
become: yes
become_user: postgres
postgresql_user:
name: "{{ postgres_root_user }}"
password: "{{ postgres_root_pass }}"
role_attr_flags: CREATEDB,SUPERUSER,CREATEROLE
encrypted: yes
state: present
|
Set up handlers
Handlers are just like normal tasks in an Ansible playbook but they run only when if the Task contains a snotify directive. It also indicates that it changed something.
Let us set up handlers for out Postgres installation and set up:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| - name: Restart Postgresql
systemd:
name: postgresql-17
state: restarted
- name: Start Postgresql
systemd:
name: postgresql-17
state: started
- name: Stop Postgresql
systemd:
name: postgresql-17
state: stopped
- name: Enable Postgresql
systemd:
name: postgresql-17
enabled: yes
|
Creating the hosts file
Ansible will execute the tasks against some inventory. The server inventory will be added as a hosts.yaml file that defines how the servers will be reached. This is my hosts file:
1
2
3
4
5
| all:
hosts:
rockylinuxsrv:
ansible_ssh_host: 54.186.157.102
ansible_ssh_user: rocky
|
The whole playbook
This is the whole playbook. Save it as postgres.yaml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
| ---
- name: Install and set up Postgres 17 on Rocky Linux 9
hosts: rockylinuxsrv
become: yes
gather_facts: False
vars:
postgres_root_user: root
postgres_root_pass: GQt5MTyVPuf9vsVWoWDT9YCn
tasks:
- name: Upgrade all packages on servers
dnf:
name: "*"
state: latest
- name: Install required packages
dnf:
name:
- python3-psycopg2
state: present
- name: Add Postgres 17 repo
shell: |
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
args:
warn: no
- name: Disable built-in PostgreSQL module
shell: |
dnf -qy module disable postgresql
args:
warn: no
- name: Install postgresql
dnf:
name:
- postgresql17-server
- postgresql17-contrib
notify:
- Enable Postgresql
- name: Initialize the PostgreSQL database
shell: |
/usr/pgsql-17/bin/postgresql-17-setup initdb
- name: Ensure PostgreSQL is listening on *
lineinfile:
dest: /var/lib/pgsql/17/data/postgresql.conf
regexp: '^listen_addresses\s*='
line: "listen_addresses='*'"
state: present
notify: Restart Postgresql
- name: Add new configuration to "pg_hba.conf"
blockinfile:
dest: /var/lib/pgsql/17/data/pg_hba.conf
block: |
host all all 0.0.0.0/0 md5
host all all ::/0 md5
notify: Restart Postgresql
- name: Change peer identification to trust
shell: /bin/sed -i '/^local/s/peer/trust/' /var/lib/pgsql/17/data/pg_hba.conf
args:
warn: no
notify: Restart Postgresql
- name: Start Postgresql server
systemd:
name: postgresql-17
state: started
notify:
- Enable Postgresql
- name: Create a Superuser PostgreSQL database user
become: yes
become_user: postgres
postgresql_user:
name: "{{ postgres_root_user }}"
password: "{{ postgres_root_pass }}"
role_attr_flags: CREATEDB,SUPERUSER,CREATEROLE
encrypted: yes
state: present
handlers:
- name: Restart Postgresql
systemd:
name: postgresql-17
state: restarted
- name: Start Postgresql
systemd:
name: postgresql-17
state: started
- name: Stop Postgresql
systemd:
name: postgresql-17
state: stopped
- name: Enable Postgresql
systemd:
name: postgresql-17
enabled: yes
|
Running the ansible playbook
To run the ansible playbook, you need ansible installed locally. You can install ansible using OS package manager or if you have python pip you can use it:
1
| sudo pip install ansible
|
You also need to have ssh access to the server. Ensure that you set up connection to the server. I am using ssh key authentication and ssh works for me using this command:
Now you can run the playbook using this command:
1
| ansible-playbook -i hosts.yaml postgres.yaml -vv
|
Once the playbook finish executing you can access Postgres installed on the server.