This is an open-source project and is not officially supported by EDB Support. This repository is maintained and supported by the EDB GitHub members of this repository. Please provide feedback by posting issues and contribute by creating pull requests.

edb_postgres

This Ansible Galaxy Collection brings reference architecture deployment capabilites for PostgreSQL or EnterpriseDB Postgres Advanced Server.

Not all Distribution or versions are supported on all the operating systems available.

The ansible playbook must be executed under an account that has full privileges.

edb-ansible is a repository used for hosting an Ansible Collection that currently supports the following ansible roles:

Role name	Description
tuning	The tuning role configures the system and Postgres instances for optimal performances. Most of the configuration values are calculated automatically from available resources found on the system.
init_dbserver	Initialize the EPAS/PostgreSQL cluster (data) directory.
install_dbserver	Install EPAS/PostgreSQL database server packages.
manage_cnpg	Manage CloudNativePG/EDB Postgres for Kubernetes clusters and covers common tasks.
manage_dbserver	Manage EPAS/PostgreSQL clusters and covers common tasks.
manage_pgbouncer	Manage PgBouncer pools list and users.
manage_pgpool2	Manage Pgpool-II settings and users.
remove_cloudnativepg_sandbox	Remove CloudNativePG Sandbox from Kubernetes cluster.
remove_cloudnativepostgres_sandbox	Remove CloudNative Postgres Sandbox from Kubernetes cluster.
setup_cloudnativepg_sandbox	Setup CloudNativePG Sandbox from Kubernetes cluster.
setup_cloudnativepostgres_sandbox	Setup CloudNative Postgres Sandbox from Kubernetes cluster.
setup_barman	Set up EPAS/PostgreSQL backups with Barman.
setup_barmanserver	Set up Barman (Postgres backup) server.
setup_dbt2	Set up a database server for DBT-2.
setup_dbt2_client	Set up a client (a.k.a. transaction manager) for the client DBT-2.
setup_dbt2_driver	Set up emulated terminals (a.k.a. driver) for DBT-2.
setup_dbt3	Install the DBT-3 benchmark kit.
setup_dbt7	Install the DBT-7 benchmark kit.
setup_efm	Set up EDB Failover Manager (EFM) for PostgreSQL/EPAS HA cluster.
setup_hammerdb	Install HammerDB.
setup_pemagent	Set up Postgres Enterprise Manager (PEM) agent on the Postgres servers.
setup_pemserver	Set up Postgres Enterprise Manager (PEM) server.
setup_pgbackrest	Set up EPAS/PostgreSQL backups with pgBackRest.
setup_pgbackrestserver	Set up pgBackRest server for Postgres backups and recovery.
setup_pgbouncer	Set up PgBouncer connection pooler.
setup_etcd	Set up etcd for patroni cluster.
setup_patroni	Set up for patroni managed cluster.
setup_pgpool2	Set up Pgpool-II connection pooler/load balancer.
setup_replication	Set up the data replication (synchronous/asynchronous).
setup_repmgr	Set up Repmgr for PostgreSQL HA cluster.
setup_repo	Set up the EDB, PostgreSQL Community and EPEL repositories.
setup_touchstone	Set up additional packages and software for characterizing system performance.
manage_dbpatches	Manage applying patches on dbservers part of EFM cluster.

Pre-Requisites

For correctly installed and configuration of the cluster following are requirements:

1. Ansible (on the machine on which playbook will be executed).
2. Operating system privileged user (user with sudo privilege) on all the servers/virtual machines.
3. Machines for the Postgres or EPAS cluster should have at least 2 CPUs and 4 GB of RAM
4. The machine utilized for deploying with ansible can be a minimal instance

Installation

• To install Ansible: Installing Ansible

The edb_ansible Ansible collection can be installed in 3 different approaches:

Installing the edb_postgres Ansible Collection from Ansible Galaxy

Installing the edb_postgres Ansible Collection is done by following the steps below:

• Open the command line
• Type:

  $ ansible-galaxy collection install edb_devops.edb_postgres --force

• Press Enter

This approach automatically makes the edb_postgres collection available to your playbooks.

A message indicating where the collection is installed will be displayed by ansible-galaxy. The collection code should be automatically made readily available for you.

By default the location of your installed collection is: ~/.ansible/collections/ansible_collections

Downloading the edb-ansible repository source code from the repository in GitHub

This method requires to have the ansible-galaxy tool installed.

Downloading the code from the repository can be accomplished by following the steps below:

• Navigate to the repository address: https://github.com/EnterpriseDB/edb-ansible
• Click on the green Code Button located next to the About section
• Click on the Download Zip Link menu option

After the code has been downloaded, the code will be available as a zip file which requires being unzipped to your desired target destination.

After the code has been unzipped, you must go to root folder edb-ansible-master, and install the collection by entering the command below:

$ make install

This approach automatically makes the edb_postgres collection available to your playbooks.

A message indicating where the collection is installed will be displayed by ansible-galaxy. The collection code should be automatically made readily available for you.

By default the location of your installed collection is: ~/.ansible/collections/ansible_collections

Cloning the edb-ansible repository source code from the repository GitHub

This method requires to have the ansible-galaxy tool installed.

Downloading the code from the repository can be accomplished by following the steps below:

$ git clone [email protected]:EnterpriseDB/edb-ansible.git

After the code has been downloaded, the code will be available in your current directory within a directory named: edb-ansible.

You can access the root folder of the repository by entering the command below:

$ cd edb-ansible

You can install the collection by entering the command below:

$ make install

This approach automatically makes the edb_postgres collection available to your playbooks.

A message indicating where the collection is installed will be displayed by ansible-galaxy. The collection code should be automatically made readily available for you.

By default the location of your installed collection is: ~/.ansible/collections/ansible_collections

Example of inventory file

Content of the inventory.yml file:

---
all:
  children:
    pemserver:
      hosts:
        pemserver1:
          ansible_host: 110.0.0.4
          private_ip: 10.0.0.4
    primary:
      hosts:
        primary1:
          ansible_host: 110.0.0.1
          private_ip: 10.0.0.1
          pem_agent: true
          pem_server_private_ip: 10.0.0.4
    standby:
      hosts:
        standby1:
          ansible_host: 110.0.0.2
          private_ip: 10.0.0.2
          upstream_node_private_ip: 10.0.0.1
          replication_type: synchronous
          pem_agent: true
          pem_server_private_ip: 10.0.0.4
        standby2:
          ansible_host: 110.0.0.3
          private_ip: 10.0.0.3
          upstream_node_private_ip: 10.0.0.1
          replication_type: asynchronous
          pem_agent: true
          pem_server_private_ip: 10.0.0.4

Note: don't forget to replace IP addresses.

How to include the roles in your Playbook

Below is an example of how to include all the roles for a deployment in a playbook:

---
- hosts: all
  name: Postgres deployment playbook
  become: yes
  gather_facts: yes

  collections:
    - edb_devops.edb_postgres

  pre_tasks:
    - name: Initialize the user defined variables
      set_fact:
        pg_version: 14
        pg_type: "EPAS"
        repo_username: "<edb-package-repository-username>"
        repo_password: "<edb-package-repository-password>"
        repo_token: "<edb-package-repository-token>"
        disable_logging: false

  roles:
    - role: setup_repo
      when: "'setup_repo' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: install_dbserver
      when: "'install_dbserver' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: init_dbserver
      when: "'init_dbserver' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: setup_replication
      when: "'setup_replication' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: setup_efm
      when: "'setup_efm' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: setup_pgpool2
      when: "'setup_pgpool2' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: manage_pgpool2
      when: "'manage_pgpool2' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: manage_dbserver
      when: "'manage_dbserver' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: setup_pemserver
      when: "'setup_pemserver' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: setup_pemagent
      when: "'setup_pemagent' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: setup_pgbackrest
      when: "'setup_pgbackrest' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: setup_pgbackrestserver
      when: "'setup_pgbackrestserver' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: setup_pgbouncer
      when: "'setup_pgbouncer' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: manage_pgbouncer
      when: "'manage_pgbouncer' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: setup_barmanserver
      when: "'setup_barmanserver' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: setup_barman
      when: "'setup_barman' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: tuning
      when: "'tuning' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"

You can customize the above example to install PostgreSQL, EPAS, EFM or PEM or limit what roles you would like to execute.

If you are planning to try Patroni based cluster below is an example of hot to include roles for patroni cluster playbook:

---
- hosts: all
  name: Postgres deployment playbook
  become: yes
  gather_facts: yes

  collections:
    - edb_devops.edb_postgres

  pre_tasks:
    - name: Initialize the user defined variables
      set_fact:
        pg_version: 14
        pg_type: "EPAS"
        repo_username: "<edb-package-repository-username>"
        repo_password: "<edb-package-repository-password>"
        repo_token: "<edb-package-repository-token>"
        use_patroni: true
        disable_logging: false

  roles:
    - role: setup_repo
      when: "'setup_repo' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: install_dbserver
      when: "'install_dbserver' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: setup_etcd
      when: "'setup_etcd' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"
    - role: setup_patroni
      when: "'setup_patroni' in lookup('edb_devops.edb_postgres.supported_roles', wantlist=True)"

Access to EDB's package repository

By default, the setup_repo role requires to define credentials (variables repo_username and repo_password or repo_token) that will be used to configure the access to EDB's package repository. Having access to EDB package repository is necessary to deploy EDB softwares like EPAS, EFM or PEM.

When deploying softwares coming only from the community repository (PGDG) like PostgreSQL, barman or pgbouncer, it's not needed to configure access to EDB's repository. To disable it, the variable enable_edb_repo must be set to false.

Default user and passwords

The following will occur should a password not be provided for the following accounts:

• pg_superuser
• pg_replication_user
• pg_efm_user
• pg_pem_agent_user
• pg_pem_admin_user

Note:

• The ~/.edb folder and contained files are secured by assigning the permissions to user executing the playbook.
• A password of 20 characters will be automatically created under: ~/.edb folder.
• The naming convention for the password file is: <username>_pass

Playbook examples

Examples of utilizing the playbooks for installing: PostgresSQL, EPAS, etc.. are provided and located within the playbook-examples directory.

SSH port configuration

When using non standard SSH port (different from 22), the port value must be set in two places:

• in the inventory file, for each host, with the host var. ansible_port
• in the playbook or variable file with the variable ssh_port

Playbook execution examples

# To deploy community Postgres version 13
$ ansible-playbook playbook.yml \
  -i inventory.yml \
  -u <ssh-user> \
  --private-key <ssh-private-key> \
  --extra-vars="pg_version=13 pg_type=PG enable_edb_repo=false"

# To deploy EPAS version 12 with the user ec2-user
$ ansible-playbook playbook.yml \
  -i inventory.yml \
  -u <ssh-user> \
  --private-key <ssh-private-key> \
  --extra-vars="pg_version=12 pg_type=EPAS repo_username=<edb-repo-username> repo_password=<edb-repo-password>"
# OR
$ ansible-playbook playbook.yml \
  -i inventory.yml \
  -u <ssh-user> \
  --private-key <ssh-private-key> \
  --extra-vars="pg_version=12 pg_type=EPAS repo_token=<edb-repo-token>"

Database engines supported

PostgreSQL

Distribution	10	11	12	13	14
CentOS 7	✅	✅	✅	✅	✅
Red Hat Linux 7	✅	✅	✅	✅	✅
Oracle Linux 7	✅	✅	✅	✅	✅
RockyLinux 8	✅	✅	✅	✅	✅
AlmaLinux 8	✅	✅	✅	✅	✅
Red Hat Linux 8	✅	✅	✅	✅	✅
Ubuntu 20.04 LTS (Focal) - x86_64	✅	✅	✅	✅	✅
Debian 9 (Stretch) - x86_64	✅	✅	✅	✅	✅
Debian 10 (Buster) - x86_64	✅	✅	✅	✅	✅

EnterpriseDB Postgres Advanced Server

Distribution	10	11	12	13	14
CentOS 7	✅	✅	✅	✅	✅
Red Hat Linux 7	✅	✅	✅	✅	✅
Oracle Linux 7	✅	✅	✅	✅	✅
RockyLinux 8	❌	❌	✅	✅	✅
AlmaLinux 8	❌	❌	✅	✅	✅
Red Hat Linux 8	❌	❌	✅	✅	✅
Ubuntu 20.04 LTS (Focal) - x86_64	❌	❌	❌	✅	✅
Debian 9 (Stretch) - x86_64	❌	✅	✅	✅	✅
Debian 10 (Buster) - x86_64	❌	❌	✅	✅	✅

• ✅ - Tested and supported
• ❌ - Not tested and not supported

License

BSD

Author information

Authors:

• Doug Ortiz
• Vibhor Kumar
• Julien Tachoires
• Mark Wong
• Vincent Phan

Contact: [email protected]