Deployment Made Easy with Ansible

Reference Architectures are the key starting point for deploying Postgres correctly in a production environment. EDB has specified several reference architectures for departmental, mission critical, and always-on deployments.

In this blog post, we will focus on automated deployment for departmental and mission critical architectures that leverage PostgreSQL, EDB Postgres Advanced Server (EPAS), Failover Manager (EFM), Postgres Enterprise Manager (PEM), Pgpool, PgBouncer, and Barman. Architectures leveraging EDB Postgres Distributed will be discussed elsewhere at a later time.

We have chosen Ansible to automate the deployment and configuration of the software. The roles and plugins described in this post are available under the PostgreSQL license at Ansible Galaxy Hub (see below).

The collection comes with roles that you can easily use in your playbook to deploy a reference architecture. We have some plugins, which help in determining the relation between hosts/nodes in Ansible inventory. Let’s look at how different reference architectures can be deployed using the Ansible collections from EDB.

Deployment Tips

EDB Reference architectures for Postgres:

1. EDB-RA-1 (EDB Reference Architecture one): Single node Postgres with monitoring and backup solution.
This reference architecture is prevalent for development and also for non-mission critical applications. In this architecture, DBA can have one primary server with monitoring (PEM) and backup and recovery (Barman). Below is a diagram showing the EDB-RA-1

To deploy this architecture, DBA/user can create an inventory file as shown below:

    ---
    all:
      children:
        pemserver:
          hosts:
            pemserver1:
              ansible_host: <pem server ssh ip/hostname>
              private_ip: <pem server private ip>
        barmanserver:
          hosts:
            barmanserver1:
              ansible_host: <barman server ssh ip/hostname>
              private_ip: <barman server private ip>
        primary:
          hosts:
            primary1:
              ansible_host: <primary server ssh ip/hostname>
              private_ip: <primary server private ip/hostname>
              barman: true
              barman_server_private_ip: <barman server private ip>
              barman_backup_method: postgres
              pem_agent: true
              pem_server_private_ip: <pem server private ip>

If a DBA/user wants, they can use hostnames instead of public IP addresses. If you deploy the architecture on the on-prem servers, it may be possible that public IPs may be the same as private IPs.

The playbook to deploy the above architecture is given below link: https://github.com/EnterpriseDB/postgres-deployment/blob/master/edbdeploy/data/ansible/EDB-RA-1.yml

With playbook and inventory, DBAs can deploy the architecture with one command:

        
            ansible-playbook -i inventory.yml EDB-RA-1.yml -e "repo_username= <edb_yum_username> repo_password=<edb_yum_password> pg_type=<PG/EPAS>" -u <username> --private-key <private_key_for_login>
        
    

2. EDB-RA-2 (EDB Reference Architecture Two): Three nodes Postgres with streaming replication, monitoring, and backup solution.

This architecture is prevalent in deploying Postgres in production environments and also popular for mission-critical applications. In this architecture, the Postgres database is highly available with two quorum-based synchronous replication and Failover Manager (EFM). A monitoring system (PEM) would give you the visibility of the architecture and Barman for backup and recovery in a disaster. Below is a diagram showing EDB-RA-2 architecture.

To deploy the above architecture, DBA/user can use the following inventory file:

    ---
    all:
      children:
        pemserver:
          hosts:
            pemserver1:
              ansible_host: <pem server ssh ip/hostname>
              private_ip: <pem server private ip>
        barmanserver:
          hosts:
            barmanserver1:
              ansible_host: <barman server ssh ip/hostname>
              private_ip: <barman server private ip>
        primary:
          hosts:
            primary1:
              ansible_host: <primary server ssh ip/hostname>
              private_ip: <primary server private ip>
              barman: true
              barman_server_private_ip: <barman server private ip>
              barman_backup_method: postgres
              pem_agent: true
              pem_server_private_ip: <pem server private ip>
        standby:
          hosts:
            standby1:
              ansible_host: <standby1 server ssh ip/hostname>
              private_ip: <standby1 server private ip>
              barman: true
              barman_server_private_ip: <barman server private ip>
              barman_backup_method: postgres
              replication_type: synchronous
              upstream_node_private_ip: <primary server private ip>
              pem_agent: true
              pem_server_private_ip: <pem server private ip>
            standby2:
              ansible_host: <standby2 server ssh ip/hostname>
              private_ip: <standby2 server private ip>
              barman: true
              barman_server_private_ip: <barman server private ip>
              barman_backup_method: postgres
              replication_type: asynchronous
              upstream_node_private_ip: <primary server private ip>
              pem_agent: true
              pem_server_private_ip: <pem server private ip>

Following is the playbook, which can be used to deploy this architecture: https://github.com/EnterpriseDB/postgres-deployment/blob/master/edbdeploy/data/ansible/EDB-RA-2.yml

And the command to deploy the architecture using playbook is given below:

        
            ansible-playbook -i inventory.yml EDB-RA-2.yml -e "repo_username= <edb_yum_username> repo_password=<edb_yum_password> pg_type=<PG/EPAS>" -u <username> --private-key <private_key_for_login></private_key_for_login>
        
    

3. EDB-RA-3 (EDB Reference Architecture Three): Three Postgres nodes with streaming replication, monitoring, backup solution, and Pgpool for load balancing read-only queries.

This architecture is one of the most popular architectures for production and mission-critical applications. This architecture is a variant of EDB-RA-2 but adds load balancing and query routing capability with Pgpool nodes. Below is a diagram showing EDB-RA-3 architecture:

Like EDB-RA-1 and EDB-RA-2, the following is the inventory template and playbook for the architecture, which DBA can use to deploy the above architecture.

inventory.yml

    ---
    all:
      children:
        pemserver:
          hosts:
            pemserver1:
              ansible_host: <pem server ssh ip/hostname>
              private_ip: <pem server private ip>
        barmanserver:
          hosts:
            barmanserver1:
              ansible_host: <barman server ssh ip/hostname>
              private_ip: <barman server private ip>
        primary:
          hosts:
            primary1:
              ansible_host: <primary server ssh ip/hostname>
              private_ip: <primary server private ip>
              barman: true
              barman_server_private_ip: <barman server private ip>
              barman_backup_method: postgres
              pem_agent: true
              pem_server_private_ip: <pem server private ip>
        standby:
          hosts:
            standby1:
              ansible_host: <standby1 server ssh ip/hostname>
              private_ip: <standby1 server private ip>
              barman: true
              barman_server_private_ip: <barman server private ip>
              barman_backup_method: postgres
              replication_type: synchronous
              upstream_node_private_ip: <primary server private ip>
              pem_agent: true
              pem_server_private_ip: <pem server private ip>
            standby2:
              ansible_host: <standby2 server ssh ip>
              private_ip: <standby2 server private ip>
              barman: true
              barman_server_private_ip: <barman server private ip>
              barman_backup_method: postgres
              replication_type: asynchronous
              upstream_node_private_ip: <primary server private ip>
              pem_agent: true
              pem_server_private_ip: <pem server private ip>
        pgpool2:
          hosts:
            pooler1:
              ansible_host: <pgpool node1 server ssh ip/hostname>
              private_ip: <pgpool node1 server private ip>
              primary_private_ip: <primary server private ip>
            pooler2:
              ansible_host: <pgpool node2 server ssh ip/hostname>
              private_ip: <pgpool node2 server private ip>
              primary_private_ip: <primary server private ip>
            pooler3:
              ansible_host: <pgpool node2 server ssh ip/hostname>
              private_ip: <pgpool node2 server private ip>
              primary_private_ip: <primary server private ip>

Playbook for the above architecture:

https://github.com/EnterpriseDB/postgres-deployment/blob/master/edbdeploy/data/ansible/EDB-RA-3.yml

All of the details of the above architectures are also available in our public repository, in which you will find high availability properties of the architectures.
https://github.com/EnterpriseDB/edb-ref-archs/tree/main/edb-reference-architecture-codes

If you want to use the edb-ansible from the Github repository, the link of the GitHub repository is given below: https://github.com/EnterpriseDB/edb-ansible