Automating a PostgreSQL High Availability Architecture with Ansible

Automating a PostgreSQL High Availability Architecture with Ansible Vibhor Kumar, Chief Performance Architect Marc Linster, Chief Technology Officer November 2020

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.2 • Slides and recording will be available in next 48 hours • Submit questions via Zoom – will be answering at end • We will be sharing info about EDB and Postgres later Welcome – Housekeeping Items

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.3 Agenda • Introduction to EDB • Concepts of high availability • A quick review of EDB reference architectures • EDB tools to create a highly available PostgreSQL architecture • EDB Ansible® collections helping in automating the deployment of reference architecture • Features and capabilities of Ansible collections • Automating the provisioning of the resources in the cloud using Terraform™ • Summary and Q&A

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.4 The largest dedicated PostgreSQL company • More customers: Than any dedicated PostgreSQL company • More experts: Leading PostgreSQL contributors • More innovation: Positioned to lead in enterprise PostgreSQL and hybrid cloud EDB acquires 2ndQuadrant in Sept 2020 +

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.5 EDB supercharges PostgreSQL Largest dedicated PostgreSQL company Major PostgreSQL community leader Over 5,000 customers - 1 in 4 of Fortune 500 Founded in 2004 Over 10 years of consecutive quarterly subscription growth 500+ employees Recognised leader in Relational Database Management Systems (RDBMS) by both Gartner and Forrester 2019 Challengers Leaders Niche Players Visionaries Abilitytoexecute Completeness of vision

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.6 EDB team includes: More PostgreSQL experts • 300+ PostgreSQL technologists • 26 PostgreSQL community contributors and committers • Including founders and leaders like Michael Stonebraker “Father of Postgres” and EDB Advisor Bruce Momjian Co-founder, PostgreSQL Development Corporation and EDB Employee Peter Eisentraut PostgreSQL leader and EDB Employee Robert Haas PostgreSQL Major Contributor, Committer and EDB Employee Simon Riggs Enterprise PostgreSQL Expert and EDB Employee

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.7 High Availability High availability (HA) is a characteristic of a system, which aims to ensure an agreed level of operational performance, usually uptime, for a higher than normal period. https://en.wikipedia.org/wiki/High_availability

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.8 High Availability A note on Target Availability • A target is NOT a guarantee! • Availability is affected by more than just the software application: • Operating system • Hardware • Network • Environment (power, cooling, etc.) • Upgrades/maintenance • Quoted maximum target availabilities apply to EDB software maintenance only! Availability Max downtime/year 99% 3d, 15h, 39m, 29s 99.9% 8h, 45m, 56s 99.99% 52m, 35s 99.999% 5m, 15s 99.9999% 31s

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.9 Calculating Availability Best practice: measure in %: (𝐴𝑔𝑟𝑒𝑒𝑑 𝑆𝑒𝑟𝑣𝑖𝑐𝑒 𝑇𝑖𝑚𝑒 −𝐷𝑜𝑤𝑛𝑡𝑖𝑚𝑒) 𝐴 = X 100 (𝐴𝑔𝑟𝑒𝑒𝑑 𝑆𝑒𝑟𝑣𝑖𝑐𝑒 𝑇𝑖𝑚𝑒) ● Agreed Service Time – defined in SLA (Service Level Agreement) ● Downtime – duration of service unavailability during Agreed Service Time ● Important when planning/deploying a service to understand availability concept ● https://mikegold.org/calculateavailability/

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.10 RPO/RTO: Key Components of High Availability Recovery Point Objective (RPO)/Recovery Time Objective (RTO) Recovery Point Objective Recovery Time Objective http://www.allnetkc.com/definitions.html

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.11 GRO: Key Component of High Availability Geographic Redundancy Objective (GRO) "... sometimes considered the disaster recovery requirement. This objective encompasses what data needs to be replicated off site, how often and how far." https://storageswiss.com/2014/01/22/backup-basics-what-do-slo-rpo-rto-vro-and-gro- mean/#:~:text=Geographic%20Redundancy%20Objective&text=Simply%20put%2C%20users%20are%20more,multiple%2 0geographic%20requirements%20per%20application.

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.12 • A set of pre-designed tried-and-tested deployment patterns for PostgreSQL • Intended as a starting point: • Easy to try out • Can be used as-is • Can be used as a foundation • Core architectures for the database server • Add-on architectures provide additional capabilities What are PostgreSQL Reference Architectures?

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.13 Core PostgreSQL Architectures Database servers • Single Node • Multi-node cluster with asynchronous replication • Multi-node cluster with synchronous replication

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.14 Single Node Property Description Recovery Time Objective Dependent on database size and backup strategy Recovery Point Objective Dependent on backup strategy Geographic Redundancy Objective N/A Target Availability 99.9%

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.15 Multi Node with Asynchronous Replication Property Description Recovery Time Objective < 60 seconds Recovery Point Objective Typically a few seconds or less during failover (un-replicated transactions may be lost). Dependent on backup strategy for full recovery. Geographic Redundancy Objective Dependent on node placement. Typically multi-availability zone. Target Availability 99.99% (99.999% may be possible with failover tuning)

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.16 Multi Node with Synchronous Replication Property Description Recovery Time Objective < 60 seconds Recovery Point Objective Typically a few seconds or less during failover, with no loss of committed transactions. Dependent on backup strategy for full recovery. Geographic Redundancy Objective Dependent on node placement. Typically multi-availability zone. Target Availability 99.99% (99.999% may be possible with failover tuning)

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.18 Deployment Automation - DevOps Tool Survey Technology Decision • Terraform • CloudFormation • Ansible • Chef • Puppet • Salt • Configuration Management Vs Provisioning • Procedural Vs Declarative • Master Vs Master-less • Mutable Infrastructure Vs Immutable Infrastructure • Agent Vs Agentless

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.20 Deployment Automation - What is Ansible? • Technology to automate applications and configure infrastructure • Four level of abstractions for configuration ○ Task - single action ○ Task list - list of actions ○ Role ■ List of actions (or list of lists) grouped by the same 'subject' ■ A way to group tasks together into one container ○ Playbook ■ list of plays, each operating on possibly different hostgroup, applying several roles/tasks/task lists (and special tasks like handlers)

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.21 Deployment Automation - What is Ansible? • Collections ○ Distribution format for Ansible content that can include playbooks, roles, modules, and plugins. • Ansible Playbooks setup and configure Single Node or Multi-Node clusters are available. • Repositories: https://github.com/EnterpriseDB/edb-ansible https://galaxy.ansible.com/edb_devops/edb_postgres

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.22 Deploy Reference Architecture with Single Command • Deployment with ONE command /usr/local/bin/ansible-playbook playbook.yml -u centos --private-key ~/id_rsa.pem • Arguments: ○ playbook.yml ○ -u <server/vm user name> ○ --private-key <SSH Key>

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.23 Deployment Automation - playbook.yml • os: Operating system version • pg_version: Postgres version • pg_type: EPAS/PG ○ EPAS: EDB Advanced Server ○ PostgreSQL • yum_username • ym_password

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.29 pg_users: - name: repuser pass: <password> conn_limit: 10 state: present - name: abc_user state: absent pg_grant_privileges: - roles: "efm_user" database: "edb" privileges: execute schema: pg_catalog objects: pg_current_wal_lsn() type: function Manage database users and privileges pg_grant_roles: - role: pg_monitor user: enterprisedb

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.30 pg_postgres_conf_params: - name: listen_addresses value: "*" - name: maintenance_work_mem value: "128MB" pg_hba_ip_addresses: - contype: "host" users: "all" databases: "all" method: "scram-sha-256" source: "127.0.0.1/32" state: present Manage server configurations and databases pg_databases: - name: edb_gis owner: edb encoding: UTF-8 pg_extensions: - name: "postgis" database: "edb" state: present pg_slots: - name: "physcial_slot" slot_type: "physical" state: present - name: "logical_slot" slot_type: "logical" output_plugin: "test_decoding" state: present database: "edb"

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.31 pg_query: - query: "UPDATE test SET id=2 WHERE id=1;" db: "edb" pg_copy_files: - file: "/localdir/edb-sample.sql" dest: "/usr/edb/as12/share/edb-sample.sql" owner: "enterprisedb" group: "enterprisedb" pg_sql_scripts - file_path: "/usr/edb/as12/share/edb-sample.sql" db: edb Execute query/SQL scripts and manage .pgpass pg_pgpass_values: - host: "*" pg_port: 5444 database: edb user: enterprisedb password: <password>

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.33 Deploying Postgres in Public Cloud • A thin bash CLI that uses ○ Terraform ■ Provision resources in a cloud ■ Maintain the state of the resources ■ Destroy/delete the provisioned resources in a cloud ○ Ansible ■ Deployment and configuration of Postgres

© Copyright EnterpriseDB Corporation, 2020. All rights reserved.34 Conclusion Easy to use deployment patterns for PostgreSQL EDB Reference Architectures make it easy to get your PostgreSQL deployments up and running in battle-hardened configurations • EDB Reference Architectures on Github Automate the deployment patterns for PostgreSQL • Repositories: https://github.com/EnterpriseDB/edb-ansible https://galaxy.ansible.com/edb_devops/edb_postgres • Postgres-deployment repository https://github.com/EnterpriseDB/postgres-deployment Thank You

Automating a PostgreSQL High Availability Architecture with Ansible

More Related Content

What's hot

Similar to Automating a PostgreSQL High Availability Architecture with Ansible

More from EDB

Recently uploaded

In this document

Automating a PostgreSQL High Availability Architecture with Ansible