In this guide we are going to install and configure Postgresql 14 in Ubuntu 20.04 using Ansible.
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 up to date Ubuntu 20.04 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 these tasks. First we refresh the repos then we upgrade all packages.
1
2
3
4
5
6
7
8
9
10
| - name: Update apt repo and cache on Ubuntu box
apt:
update_cache: yes
force_apt_get: yes
cache_valid_time: 3600
- name: Upgrade all packages on servers
apt:
upgrade: dist
force_apt_get: yes
|
Installing necessary packages
Next, let us install some common packages that we will need in our playbook.
1
2
3
4
5
6
7
| - name: Install required packages
apt:
name:
- wget
- python3-psycopg2
- acl
state: latest
|
Setting up Postgres 14 Repo
Next, let us set up the repo for Postgres 14. The default Ubuntu 20.04 repos contains an outdated version of Postgres. Let us set up these repos that will allow us to install Postgres 14.
1
2
3
4
5
6
| - name: Set up Postgres 14 repo
shell: |
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
args:
warn: no
|
Installing Postgres 14 in Ubuntu
Now that we have set up the repos, we can install Postgres. Use this task to achieve that
1
2
3
4
5
6
| - name: Install postgresql
apt:
name: postgresql-14
update_cache: yes
notify:
- Enable Postgresql
|
In the above, we are installing postgres but doing a cache update (equivalent to apt-get update) before that.
Configuring Postgres 14
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: /etc/postgresql/14/main/postgresql.conf
regexp: '^listen_addresses\s*='
line: "listen_addresses='*'"
state: present
notify: Restart Postgresql
- name: Add new configuration to "pg_hba.conf"
blockinfile:
dest: /etc/postgresql/14/main/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/' /etc/postgresql/14/main/pg_hba.conf
args:
warn: no
notify: Restart 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:
1
2
3
4
5
6
7
8
9
| - 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 “notify” 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
state: restarted
- name: Start Postgresql
systemd:
name: postgresql
state: started
- name: Stop Postgresql
systemd:
name: postgresql
state: stopped
- name: Enable Postgresql
systemd:
name: postgresql
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
ubuntusrv:
ansible_ssh_host: 10.2.11.9
ansible_ssh_user: ubuntu
|
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
| ---
- name: Install and set up Postgres 14 in Ubuntu 20.04
hosts: ubuntusrv
become: yes
gather_facts: False
vars:
postgres_root_user: root
postgres_root_pass: GQt5MTyVPuf9vsVWoWDT9YCn
allow_world_readable_tmpfiles: true
tasks:
- name: Update apt repo and cache on all Ubuntu boxes
apt:
update_cache: yes
force_apt_get: yes
cache_valid_time: 3600
- name: Upgrade all packages on servers
apt:
upgrade: dist
force_apt_get: yes
- name: Install required packages
apt:
name:
- wget
- python3-psycopg2
- acl
state: latest
- name: Set up Postgres 14 repo
shell: |
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
args:
warn: no
- name: Install postgresql
apt:
name: postgresql-14
update_cache: yes
notify:
- Enable Postgresql
- name: Ensure PostgreSQL is listening on *
lineinfile:
dest: /etc/postgresql/14/main/postgresql.conf
regexp: '^listen_addresses\s*='
line: "listen_addresses='*'"
state: present
notify: Restart Postgresql
- name: Add new configuration to "pg_hba.conf"
blockinfile:
dest: /etc/postgresql/14/main/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/' /etc/postgresql/14/main/pg_hba.conf
args:
warn: no
notify: Restart 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
state: restarted
- name: Start Postgresql
systemd:
name: postgresql
state: started
- name: Stop Postgresql
systemd:
name: postgresql
state: stopped
- name: Enable Postgresql
systemd:
name: postgresql
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.