Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Migrating PostgreSQL to the Cloud

Migrating PostgreSQL to the Cloud

Managing your own PostgreSQL servers is sometimes a burden your business does not want. In this talk we will provide an overview of some of the public cloud offerings available for hosted PostgreSQL and discuss a number of strategies for migrating your databases with a minimum of downtime.

F1e0e0c3c3196a63c9b17a2344fb6a61?s=128

Mike Fowler

April 27, 2018
Tweet

Transcript

  1. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Mike Fowler, Senior Site Reliability Engineer Migrating PostgreSQL to the Cloud
  2. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler About Me Associate: SA, Developer Specialist: Big Data Data Engineer Cloud Architect
  3. None
  4. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler • Hosted PostgreSQL • Overview of public cloud hosting options • Database migration strategies – Dump & Restore – Replication failover – Amazon’s Database Migration Service – PITR + Logical decoding Overview
  5. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler What is hosted PostgreSQL?
  6. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Benefits of Hosted PostgreSQL
  7. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Drawbacks of Hosted PostgreSQL
  8. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Fallacies of Hosted PostgreSQL
  9. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Hosting Options
  10. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Amazon RDS
  11. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Amazon Aurora
  12. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Amazon Aurora https://aws.amazon.com/blogs/aws/amazon-aurora-update-postgresql-compatibility/
  13. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Heroku
  14. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Google Cloud SQL
  15. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Microsoft Azure
  16. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Encryption
  17. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Extensions
  18. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler • Dump & Restore • Replication failover • Amazon’s Database Migration Service • PITR + Logical decoding Migration Strategies
  19. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Dump & Restore
  20. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Strategies to Minimise Downtime
  21. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Replication Failover
  22. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler AWS Database Migration Service
  23. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler PITR & Logical decoding
  24. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler 1. Create a logical replication slot SELECT * FROM pg_create_logical_replication_slot ('logical_slot', 'decoder_raw'); 2. Note the transaction ID (catalog_xmin) SELECT catalog_xmin FROM pg_replication_slots WHERE slot_name = ‘logical_slot’; PITR & Logical decoding
  25. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler 3. Perform a barman backup $ barman backup master 4. Perform a barman PITR $ barman recover –target-xid (catalog_xmin - 1) master latest 5. Start database and verify correct recovery PITR & Logical decoding
  26. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler 5. Perform pg_dump on the readonly barman node 6. Restore to public cloud 7. Read output of logical decoding and write to cloud PITR & Logical decoding
  27. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Summary
  28. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Questions? Mike Fowler gh-mlfowler mlfowler mike dot fowler at claranet dot uk We’re hiring! If you’re interested in helping us do what we do email hr at uk dot clara dot net
  29. None
  30. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Mike Fowler, Senior Site Reliability Engineer Migrating PostgreSQL to the Cloud
  31. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler About Me Associate: SA, Developer Specialist: Big Data Data Engineer Cloud Architect • Senior Site Reliability Engineer in the Public Cloud Practice of claranet • Background in Software Engineering, Systems Engineering, System & Database Administration • Been using PostgreSQL since 7.4 • Contributed to YAWL, PostgreSQL & Terraform open source projects • XMLEXISTS/xpath_exists() • xml_is_well_formed() • Regular speaker at PGDay UK
  32. None
  33. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler • Hosted PostgreSQL • Overview of public cloud hosting options • Database migration strategies – Dump & Restore – Replication failover – Amazon’s Database Migration Service – PITR + Logical decoding Overview
  34. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler What is hosted PostgreSQL? Your database somewhere else A managed service Some providers offer full DBA support Cloud providers give only the infrastructure Typically provisioned through an API or GUI i.e. a self-service environment
  35. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Benefits of Hosted PostgreSQL Reduces adoption costs Installation & configuration is already done Generally sane defaults, some tuning often required Needn’t worry about physical servers Opex instead of Capex Most routine DBA tasks are done for you Easier to grow
  36. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Drawbacks of Hosted PostgreSQL Less control Latency Some features are disabled Migrating existing databases is hard Potential for vendor lock-in Resource limits
  37. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Fallacies of Hosted PostgreSQL Automatic scaling It will fix your slow queries/reports/application You don’t need a DBA They need to be planning for scale They need to help address slowness
  38. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Hosting Options We’ll look only at Public Cloud offerings Amazon Relation Database Service (RDS) Amazon Aurora Heroku Google Cloud SQL Microsoft Azure
  39. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Amazon RDS PostgreSQL 9.3.12 – 10.3 supported Numerous instance types Costs range from $0.018 to $7.96 per hour Select from 1 vCPU up to 64 vCPUs, all 64-bit Memory ranges from 1GB to 488GB Flexible storage options Choose between SSD or Provisioned IOPS Up to 16TB with up to 40,000 IOPS
  40. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Amazon Aurora 6 instance types costing from $0.29 to $9.28 per hour Compatible with PostgreSQL 9.6 Up to 2x throughput of conventional PostgreSQL Up to 16 read replicas with sub-10ms replica lag Auto-growing filesystem up to 64TB Filesystem is shared between 3 availability zones Announced: Multi-master & serverless
  41. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Amazon Aurora https://aws.amazon.com/blogs/aws/amazon-aurora-update-postgresql-compatibility/ Tatsuo Ishii
  42. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Heroku Supports PostgreSQL 9.4, 9.5, 9.6 & 10 Simpler pricing based on choice of tier ($0-8.5k pcm) Tier dictates resource limits Maximum number of rows (Hobby only) Cache size (1GB - 240GB) Storage limit (64GB - 1TB) Connection limit (120 - 500)
  43. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Google Cloud SQL Became GA April 18th Only supports PostgreSQL 9.6 Similar replication options to RDS Posed to be a serious rival to RDS Billing per minute Automatic scaling of filesystem Similar variety of instance types
  44. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Microsoft Azure Became GA April 18th, same day as GCP Supports PostgreSQL 9.5.7 & 9.6.2 Replication is seamless Automated failover PITR Selectable compute units Roughly equates to maximum connections 99.99 availability vs 99.95 for others
  45. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Encryption At Rest (AES-256) Enabled by default for Google & Azure User enabled at create for Amazon (RDS/Aurora) Only available in premium plans for Heroku In transit (SSL) Enforced on Heroku & Azure Can be enforced on Google & Amazon
  46. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Extensions Providers dictate the availability of extensions Well documented except for Aurora Only step required is to run CREATE EXTENSION Many extensions are widely available pgcrypto PostGIS pg_stat_statements hstore
  47. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler • Dump & Restore • Replication failover • Amazon’s Database Migration Service • PITR + Logical decoding Migration Strategies
  48. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Dump & Restore Simplest strategy Perceived as low risk for data loss Less “moving parts” Just a pg_dump & pg_restore Downtime is function of database size
  49. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Strategies to Minimise Downtime Move historic data ahead of time Opportunity to clear out unused data Consider introducing partitions Consider moving the dump closer to the target e.g. Upload to EC2 instance in the same region as the RDS instance and run pg_restore from there Over provision resources Gives higher throughput during data load
  50. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Replication Failover No one supports external masters! Trigger based replication failover Slony, Londiste & Bucardo Can be used on most any version of PostgreSQL Some restrictions apply DDL is not supported Rows must be uniquely identifiable Presents some risk to production environment
  51. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler AWS Database Migration Service Web service supporting migration to/from AWS Can be RDS or an EC2 instance External DB can be anywhere Perform a one time load or continual load Change Data Capture “Supports” heterogeneous migrations Oracle, MySQL, SQL Server, MongoDB Schema Conversion Tool exists to assist
  52. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler PITR & Logical decoding Most involved approach, least downtime Combines point-in-time recovery with the changes captured by logical decoding to create a replica Need to be running at least PostgreSQL 9.4 with WAL level logical and have WAL archiving configured DDL not supported, still need unique rows Using barman for managing WAL http://www.pgbarman.org/
  53. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler 1. Create a logical replication slot SELECT * FROM pg_create_logical_replication_slot ('logical_slot', 'decoder_raw'); 2. Note the transaction ID (catalog_xmin) SELECT catalog_xmin FROM pg_replication_slots WHERE slot_name = ‘logical_slot’; PITR & Logical decoding
  54. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler 3. Perform a barman backup $ barman backup master 4. Perform a barman PITR $ barman recover –target-xid (catalog_xmin - 1) master latest 5. Start database and verify correct recovery PITR & Logical decoding
  55. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler 5. Perform pg_dump on the readonly barman node 6. Restore to public cloud 7. Read output of logical decoding and write to cloud PITR & Logical decoding
  56. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Summary Hosted PostgreSQL gives you high performance PostgreSQL without the hassle of hardware, maintenance and configuration Opex instead of Capex Consider the limitations of your intended platform There are multiple options for migration
  57. Spring Conference, April 26th-27th 2018 Dynamic Earth, Edinburgh Mike Fowler

    mlfowler mike dot fowler at claranet dot uk gh-mlfowler Questions? Mike Fowler gh-mlfowler mlfowler mike dot fowler at claranet dot uk We’re hiring! If you’re interested in helping us do what we do email hr at uk dot clara dot net
  58. None