Download as PDF, PPTX
![© Copyright EnterpriseDB Corporation, 2020. All rights reserved.31 Deploying Postgres in Public Cloud ● Project name: postgres-deployment ● https://github.com/EnterpriseDB/postgres-deployment ● Command line bash script: edb-deployment ● License: BSD ● Provisioning and Deployment automation ○ Resources provisioning with Terraform ○ Deployment using the edb_postgres Ansible collection ● Public Clouds: AWS, Azure & GCloud edb-deployment [<cloud>-server|<cloud>-postgres] [OPTION]... EDB deployment script for aws, azure and gcp Subcommands: aws-server [create|destroy] PROJECT_NAME azure-server [create|destroy] PROJECT_NAME gcloud-server [create|destroy] PROJECT_NAME aws-postgres install PROJECT_NAME azure-postgres install PROJECT_NAME gcloud-postgres install PROJECT_NAME aws-config [show|update] PROJECT_NAME azure-config [show|update] PROJECT_NAME gcloud-config [show|update] PROJECT_NAME aws-project [list|switch] PROJECT_NAME azure-project [list|switch] PROJECT_NAME gcloud-project [list|switch] PROJECT_NAME Other Options: -h, --help Display help and exit](https://image.slidesharecdn.com/szigy4wtmazdtwo7ua7w-signature-1e3e3a426d1554a906ba29b9275f361c5a32658f6e544ff7aa0475aaeee1d249-poli-201221105934/75/Postgres-Deployment-Automation-with-Terraform-and-Ansible-31-2048.jpg)
Uploaded byEDB
Postgres Deployment Automation with Terraform and Ansible
This document provides an overview of PostgreSQL deployment automation using Terraform and Ansible. It begins with a brief introduction of Ansible and Terraform. It then describes EDB's open source projects for PostgreSQL deployment automation, including features and capabilities. The document reviews deployment automation capabilities with Ansible such as configuring repositories, installing database packages, initializing databases, setting up replication, and managing database servers. It also discusses deployment automation with Terraform for provisioning infrastructure on public clouds like AWS, Azure, and GCP. Finally, it presents the roadmap for future features.
Postgres Deployment Automation with Terraform and Ansible
- 1. PostgreSQL deployment automation with Terraformand Ansible Julien Tachoires, PostgreSQL Performance Engineer Postgres Build 2020
- 2. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.2 Agenda ● Ansible & Terraform quick introduction ● EDB open source projects addressing PostgreSQL Deployment Automation needs ● Features and capabilities of these tools ● Futur features and roadmap ● Q&A
- 3. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.3 Who am I ? ● Julien Tachoires, France ● PostgreSQL Performance Engineer at EDB ● 10+ years of experience with PostgreSQL ● Open source contributor ● 6+ years of experience with PostgreSQL reference architecture deployment automation
- 4. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.4 Ansible & Terraform quick introduction
- 5. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.5 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)
- 6. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.6 Ansible Galaxy and Collections • Collections ○ Distribution format for Ansible content that can include playbooks, roles, modules, and plugins. • Ansible Galaxy ○ Ansible Galaxy is an online platform where users can share roles and collections. ○ The ansible-galaxy tool is used to download the desired roles and collections.
- 7. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.7 What is Terraform ? • Tool for building, changing, and versioning infrastructure • Popular service providers management • From single application to entire DC • Configuration files describe to Terraform the desirated state • Execution plan oriented
- 8. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.8 Deployment automation with Ansible
- 9. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.9 PostgreSQL deployment with Ansible ● Ansible collection: edb_devops.edb_postgres ○ https://galaxy.ansible.com/edb_devops/edb_postgres ● Github project name: edb-ansible ○ https://github.com/EnterpriseDB/edb-ansible ● License: BSD ● Deploy reference architecture with single command line ● Target OS: ○ CentOS 7 & 8 ○ RHEL 7 & 8 ● Database engines: ○ PostgreSQL 10, 11, 12 & 13 ○ EPAS (EDB Postgres Advanced Server) 10, 11 & 12
- 10. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.10 Capabilities and Ansible Roles ● Configure RPM repositories -> setup_repo ● Install database packages -> install_dbserver ● Database instance init. -> init_dbserver ● Streaming replication setup -> setup_replication ● PEM monitoring setup -> setup_pem ● Autofailover setup -> setup_efm ● Database management -> manage_dbserver ● PgBouncer setup & mgmt -> setup_pgbouncer / manage_pgbouncer ● PgPoolII setup & mgmt -> setup_pgpool2 / manage_pgpool2
- 11. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.11 Ansible collection installation methods ● With ansible-galaxy ● From github repository $ ansible-galaxy collection install edb_devops.edb_postgres --force $ git clone https://github.com/EnterpriseDB/edb-ansible $ cd edb-ansible/
- 12. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.12 Deploy Reference Architecture with Single Command ● Deployment with ONE command ● Argument: ○ playbook.yml ● Options: ○ -u <server/vm user name> ○ --private-key <SSH Key> $ ansible-playbook playbook.yml -u centos --private-key ~/id_rsa.pem
- 13. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.13 Deployment Automation - playbook.yml • os: Operating system version • pg_version: Postgres version • pg_type: EPAS/PG ○ EPAS: EDB Advanced Server ○ PostgreSQL • yum_username • yum_password --- - hosts: localhost name: Deploy PostgreSQL and components on Instances become: true gather_facts: no # When using collections #collections: # - edb_devops.edb_postgres vars_files: - hosts.yml pre_tasks: - name: Initialize the user defined variables set_fact: os: "CentOS7" pg_version: 13 pg_type: "PG" yum_username: "" yum_password: "" roles: - setup_repo - install_dbserver - init_dbserver - setup_replication - setup_efm - setup_pem - manage_dbserver
- 14. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.14 Deployment Automation - hosts.yml --- - hosts: localhost name: Configure PostgreSQL Replication on Instances become: true gather_facts: no # When using collections #collections: # - edb_devops.edb_postgres vars_files: - hosts.yml pre_tasks: - name: Initialize the user defined variables set_fact: os: "CentOS7" pg_version: 13 pg_type: "PG" yum_username: "" yum_password: "" roles: - setup_repo - install_dbserver - init_dbserver - setup_replication - setup_efm - setup_pem - manage_dbserver --- servers: pemserver: node_type: pemserver public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx primary: node_type: primary pem_agent: true public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx standby1: node_type: standby pem_agent: true replication_type: synchronous public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx standby2: node_type: standby pem_agent: true replication_type: asynchronous public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx
- 15. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.15 Deployment Automation - Single Node hosts.yml --- - hosts: localhost name: Deploy PostgreSQL Single node instance become: true gather_facts: no # When using collections #collections: # - edb_devops.edb_postgres vars_files: - hosts.yml pre_tasks: - name: Initialize the user defined variables set_fact: os: "CentOS7" pg_version: 13 pg_type: "PG" yum_username: "" yum_password: "" roles: - setup_repo - install_dbserver - init_dbserver - manage_dbserver --- servers: single_instance: node_type: primary public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx
- 16. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.16 Deployment Automation - Multi Node hosts.yml --- - hosts: localhost name: Configure PostgreSQL Replication on Instances become: true gather_facts: no # When using collections #collections: # - edb_devops.edb_postgres vars_files: - hosts.yml pre_tasks: - name: Initialize the user defined variables set_fact: os: "CentOS7" pg_version: 13 pg_type: "PG" yum_username: "" yum_password: "" roles: - setup_repo - install_dbserver - init_dbserver - setup_replication - manage_dbserver --- servers: node1: node_type: primary public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx node2: node_type: standby replication_type: synchronous public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx node3: node_type: standby replication_type: asynchronous public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx
- 17. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.17 manage_dbserver Role Features
- 18. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.18 pg_users: - name: repuser pass: <password> conn_limit: 10 state: present - name: abc_user state: absent Manage database users and privileges pg_grant_roles: - role: pg_monitor user: enterprisedb pg_grant_privileges: - roles: "efm_user" database: "edb" privileges: execute schema: pg_catalog objects: pg_current_wal_lsn() type: function
- 19. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.19 pg_postgres_conf_params: - name: listen_addresses value: "*" - name: maintenance_work_mem value: "128MB" Manage server configurations and databases pg_databases: - name: edb_gis owner: edb encoding: UTF-8 pg_hba_ip_addresses: - contype: "host" users: "all" databases: "all" method: "scram-sha-256" source: "127.0.0.1/32" state: present 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"
- 20. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.20 pg_query: - query: "UPDATE test SET id=2 WHERE id=1;" db: "edb" Execute query/SQL scripts and manage .pgpass pg_pgpass_values: - host: "*" pg_port: 5444 database: edb user: enterprisedb password: <password>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
- 21. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.21 Connection Pooler Deployment
- 22. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.22 PgBouncer support in edb_postgres ● Dedicated node location or mutualized with Postgres node ● Deployment with setup_pgbouncer ● User and database lists management with manage_pgbouncer
- 23. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.23 PgBouncer instances location - hosts.yml Dedicated node Mutualized --- servers: ... pooler1: node_type: pgbouncer public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx --- servers: ... pg1: node_type: primary pgbouncer: true public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx
- 24. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.24 PgBouncer deployment example - playbook.yml --- - hosts: localhost name: Deploy PgBouncer instance become: true gather_facts: no vars_files: - hosts.yml pre_tasks: - name: Initialize the user defined variables set_fact: os: "CentOS7" pg_version: 12 pg_type: "PG" # PgBouncer configuration pgbouncer_listen_port: 6432 pgbouncer_listen_addr: "*" pgbouncer_default_pool_size: 20 pgbouncer_max_client_conn: 100 pgbouncer_fd_limit: 2048 pgbouncer_pool_mode: "transaction" roles: - setup_repo - setup_pgbouncer
- 25. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.25 pgbouncer_auth_user_list: - username: "my_user" password: "SCRAM-SHA-256$4096:xxx...xxx" state: present - username: "pgbouncer_admin" password: "xxxxxx" state: present - username: "pgbouncer_stats" password: "xxxxxx" state: present Manage PgBouncer users and pools pgbouncer_databases_list: - dbname: "my_db1" host: "xxx.xxx.xxx.xxx" port: 5432 pool_size: 50 pool_mode: "transaction" max_db_connections: 100 reserve_pool: 10 state: present - dbname: "my_db2" state: absent
- 26. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.26 PgpoolII support in edb_postgres ● Dedicated node location only ● Deployment with setup_pgpool2 ● User list and configuration management with manage_pgpool2 ● Features: ○ PgpoolII instances High Availability ○ Read only queries load balancing ○ Automated write queries routing to right Postgres node
- 27. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.27 PgpoolII Single node Deployment --- - hosts: localhost name: PgpoolII Single node Deployment become: true gather_facts: no # When using collections #collections: # - edb_devops.edb_postgres vars_files: - hosts.yml pre_tasks: - name: Initialize the user defined variables set_fact: os: "CentOS7" pg_version: 13 pg_type: "PG" pgpool2_configuration: - { key: “port”, value: 9999 } - { key: “listen_addresses”, value: “*”, quoted: true } roles: - setup_repo - setup_pgpool2 --- servers: pooler1: node_type: pgpool2 public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx
- 28. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.28 PgpoolII Multi nodes Deployment --- - hosts: localhost name: PgpoolII Multi nodes Deployment with HA and LB become: true gather_facts: no vars_files: - hosts.yml pre_tasks: - name: Initialize the user defined variables set_fact: os: "CentOS7" pg_version: 13 pg_type: "PG" # HA settings pgpool2_watchdog: true pgpool2_vip: “10.0.0.123” pgpool2_vip_dev: “eth0” # Load balancing pgpool2_load_balancing: true # User for PG nodes roles checking pgpool2_sr_check_user: "pgpool2" # Configuration pgpool2_configuration: - { key: “port”, value: 9999 } - { key: “listen_addresses”, value: “*”, quoted: true } roles: - setup_repo - setup_pgpool2 --- servers: pooler1: node_type: pgpool2 public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx pooler2: node_type: pgpool2 public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx pooler3: node_type: pgpool2 public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx pg1: node_type: primary public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx pg1: node_type: standby public_ip: xx.xx.xx.xx private_ip: xx.xx.xx.xx
- 29. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.29 pgpool2_users: - name: "my_user" pass: "xxxxxx" state: present - username: "my_old_user" state: absent Manage PgpoolII users and configuration pgpool2_configuration: - key: "listen_addresses" value: "*" quoted: true state: present - key: "port" value: 9999 state: present
- 30. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.30 Deploying Postgres in Public Cloud
- 31. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.31 Deploying Postgres in Public Cloud ● Project name: postgres-deployment ● https://github.com/EnterpriseDB/postgres-deployment ● Command line bash script: edb-deployment ● License: BSD ● Provisioning and Deployment automation ○ Resources provisioning with Terraform ○ Deployment using the edb_postgres Ansible collection ● Public Clouds: AWS, Azure & GCloud edb-deployment [<cloud>-server|<cloud>-postgres] [OPTION]... EDB deployment script for aws, azure and gcp Subcommands: aws-server [create|destroy] PROJECT_NAME azure-server [create|destroy] PROJECT_NAME gcloud-server [create|destroy] PROJECT_NAME aws-postgres install PROJECT_NAME azure-postgres install PROJECT_NAME gcloud-postgres install PROJECT_NAME aws-config [show|update] PROJECT_NAME azure-config [show|update] PROJECT_NAME gcloud-config [show|update] PROJECT_NAME aws-project [list|switch] PROJECT_NAME azure-project [list|switch] PROJECT_NAME gcloud-project [list|switch] PROJECT_NAME Other Options: -h, --help Display help and exit
- 32. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.32 edb-deployment requirements ● bash ● Latest vendor Cloud CLI or SDK ( AWS, Azure or Google Cloud ) ● curl and wget ● Terraform >= 0.13 ● Ansible >= 2.9
- 33. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.33 How to use the edb-deployment script ● Create a new project: $ edb-deployment aws-server create my_project $ edb-deployment aws-postgres install my_project ● Destroy the Infrastructure in your Cloud Vendor: $ edb-deployment aws-server destroy my_project ● Deploy Postgres in your Cloud vendor:
- 34. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.34 Deployment scripts Roadmap
- 35. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.35 edb_postgres Ansible Collection Roadmap • Backup capabilities with barman & pgBackRest • Debian/Ubuntu support • Postgres auto-tuning role • Cascading Streaming Replication support • Logical Replication Support • Major version upgrade • Auto-Failover with repmgr • Predefined playbooks for reference architectures • Postgres Minor version update
- 36. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.36 Edb-deployment Script Roadmap ● Golang rewrite ● Encrypted volume support ● Post deployment tests
- 37. © Copyright EnterpriseDBCorporation, 2020. All rights reserved.37 Questions ? Thank You