Installation and Configuration of PostgreSQL with Ansible

In this post, we will learn how to install and configure PostgreSQL on Centos/Redhat. PostgreSQL is a popular Relational Database with advantages of concurrency without read locks, creating partial indexes and table inheritance.

We will create an Ansible playbook to install PostgreSQL on the server.

Pre-requisites :

  • Python and Ansible should be installed in the host machine.
  • Python should be installed in the target server.
  • A user with sudo privileges should be created.

Refer to the guide on How to Install Ansible and python

Step 1. Create the inventory File

The inventory file is a placeholder where we specify the information of the target server(s), where we intend to install a package or software. Servers can be grouped into meaningful sets like webservers, application servers etc.

For this tutorial, let's name it as pgserver

mkdir -p ./pg/
touch ./pg/hosts
touch ./ansible.cfg

Mention your target server's IP address, Ansible username, Ansible password, or private pem key file in ./pg/hosts file.

[pgserver]
13.127.121.45 ansible_ssh_private_key_file=your.pem ansible_ssh_user=ec2-user

Ansible expects the host file in the default directory which is /etc/ansible/hosts. To override the default file and use project-specific ansible configuration, we can edit the ansible.cfg file

[defaults]
remote_user = root
sudo_user = root
remote_port             = 22
timeout                 = 10
host_key_checking       = False
ssh_executable          = /usr/bin/ssh
private_key_file        = ~/.ssh/id_rsa
inventory               = hosts

[privilege_scalation]

become                  = True
become_method           = sudo
become_user             = root
become_ask_pass         = False

Next, to identify if we have correctly setup everything, let's use the Ansible ping module to make sure connectivity between the host server and our remote/target server can be established

ansible all -i hosts -m ping

Output:

13.127.121.45 | SUCCESS => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/bin/python"
    },
    "changed": false,
    "ping": "pong"
}

We are good to proceed if we receive pong in response.

Step 2. Creating playbook file

We will begin with installing PostgreSQL from the default repository manager.

vi postgres_install.yml
---
- hosts: all
  become: yes
  vars:
    db_user: ansibledb
    db_password: ansibleuser
    db_name: ansible
    
  tasks:
    - name: install postgres on RHEL based distros
      yum:
        name: "{{ item }}"
        state: present
        update_cache: true
      become: true
      loop:
        - postgresql
        - postgresql-server
        - python3
        - python3-pip
      when: ansible_os_family == 'RedHat'

In this playbook we are specifying our hosts, setting some variables that will be used for the DB creation, under task's we are using the yum module to install dependencies and packages, here we are also making sure we have the latest version of python installed.

Step 3. Installing pip and psycopg2-binary with Ansible

We will install pip if it is not already installed, we are making use of shell module to run the command on the target server.

    - name: Download pip installer
      get_url:
        url: https://bootstrap.pypa.io/get-pip.py
        dest: /tmp/get-pip.py

    - name: Install pip
      shell: |
        /usr/bin/python3 /tmp/get-pip.py
    
    - name: Install python-postgres packages packages
      pip: "name={{ item }}  state=present"
      with_items:
        - psycopg2-binary   

The psycopg2-binary is used to interact with PostgreSQL.

Step 4. Initialize PostgreSQL DB and enable it as a service

At this stage, PostgreSQL  DB will be installed but not configured. To configure it, use the command postgresql-setup --initdb which completes the installation. DB is initialized only once, while the playbook can be run any number of times. To handle this scenario we register a task as postgres_data which uses the stat module to get the status of the files.

A pg_hba.conf file is installed when the data directory is initialized by initdb command, so before initializing PostgreSQL we check if the file exists. If yes, then we skip the "Initialize PostgreSQL" task.

Here is an example of a default pg_hba.conf file in the default directory: /var/lib/pgsql/data/pg_hba.conf

# Allow any user on the local system to connect to any database with
# any database user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust

# The same using local loopback TCP/IP connections.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust

# The same as the previous line, but using a separate netmask column
#
# TYPE  DATABASE        USER            IP-ADDRESS      IP-MASK             METHOD
host    all             all             127.0.0.1       255.255.255.255     trust
    - name: "Find out if PostgreSQL is initialized"
      stat:
        path: "/var/lib/pgsql/data/pg_hba.conf"
      register: postgres_data

    - name: "Initialize PostgreSQL"
      shell: "postgresql-setup initdb"
      when: not postgres_data.stat.exist

    - name: Start and enable postgres services
      service: "name={{ item }} state=started enabled=yes"
      with_items:
        - postgresql

We use service module which enables and start our PostgreSQL service, at this point, we have our postgres user created which has access to DB. We will also create our own database named ansible, the ansible database can be created with help of  postgresql_db module.

Step 5. Create database and database user

We will utilize postgres modules in ansible to create a new DB named ansible, a DB username and password will also be created.

    - name: Create first database
      postgresql_db:
        state: present
        name: "{{ db_name }}"
      become: yes
      become_user: postgres
      
    - name: Create db user
      postgresql_user:
        state: present
        name: "{{ db_user }}"
        password: "{{ db_password }}"
      become: yes
      become_user: postgres

    - name: "Grant db user access to app db"
      postgresql_privs:
        type: database
        database: "{{ db_name }}"
        roles: "{{ db_user }}"
        grant_option: no
        privs: all
      become: yes
      become_user: postgres    
        

Step 6.  The complete playbook

The playbook can be found at https://github.com/debugfactor/ansible_postgres_install

Step 7.  Run Ansible playbook

Execute the below command to run the playbook.

ansible-playbook -i hosts postgres_install.yml

Output:

PLAY [all] *********************************************************************************************

TASK [Gathering Facts] *********************************************************************************
[WARNING]: Platform linux on host 13.127.121.45 is using the discovered Python interpreter at
/usr/bin/python, but future installation of another Python interpreter could change this. See
https://docs.ansible.com/ansible/2.9/reference_appendices/interpreter_discovery.html for more
information.
ok: [13.127.121.45]
..
.
.
.
.
.
.
.
.
TASK [Grant db user access to app db] ******************************************
changed: [13.127.121.45]

PLAY RECAP *********************************************************************
13.127.121.45              : ok=10   changed=9    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   

Step 8. Verify PostgreSQL is Installed as a Service

We can check if Postgres is running as a service with the command

sudo systemctl status postgresql.service
[root@ip-172-31-5-70 ec2-user]# systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2021-08-12 16:36:57 UTC; 1h 4min ago
  Process: 4816 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 4811 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 4820 (postgres)
   CGroup: /system.slice/postgresql.service
           ├─4820 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
           ├─4821 postgres: logger process   
           ├─4823 postgres: checkpointer process   
           ├─4824 postgres: writer process   
           ├─4825 postgres: wal writer process   
           ├─4826 postgres: autovacuum launcher process   
           └─4827 postgres: stats collector process   

Aug 12 16:36:56 ip-172-31-5-70.ap-south-1.compute.internal systemd[1]: Starting PostgreSQL database server...
Aug 12 16:36:57 ip-172-31-5-70.ap-south-1.compute.internal systemd[1]: Started PostgreSQL database server.

Here you can see PostgreSQL is running as a service.

Step 9. Verify Database is Created

We will have to switch to postgres user and use psql client to interact with our DB from the command line.

[root@ip-172-31-5-70 ec2-user]# su postgres
bash-4.2$ psql
could not change directory to "/home/ec2-user"
psql (9.2.24)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges    
-----------+----------+----------+-------------+-------------+------------------------
 ansible   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres          +
           |          |          |             |             | postgres=CTc/postgres +
           |          |          |             |             | ansibledb=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

We can see that a database Ansible is created by our playbook.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 ansibledb |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

Conclusion

We are all set up with a new PostgreSQL on RHEL/Centos8. There are several ways we can install PostgreSQL. In this post, we have used Ansible to install and configure a database and create a user.

Mohammed Shahbaaz Shareef

Mohammed Shahbaaz Shareef

DevOps Practitioner with interest in Automation and Modernization