Migrating to PostgreSQL Boriss Mejas Consultant - 2ndQuadrant Air - - PowerPoint PPT Presentation

▶
migrating to postgresql
SMART_READER_LITE
LIVE PREVIEW

Migrating to PostgreSQL Boriss Mejas Consultant - 2ndQuadrant Air - - PowerPoint PPT Presentation

Aug 05, 2023 186 likes •653 views

Migrating to PostgreSQL / PgConf.EU Milano, 16 October 2019 Migrating to PostgreSQL Boriss Mejas Consultant - 2ndQuadrant Air Guitar Player https://www. 2ndQuadrant.com Migrating to PostgreSQL / PgConf.EU Milano, 16 October 2019 Why

slide-1
SLIDE 1

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Boriss Mejías Consultant - 2ndQuadrant Air Guitar Player

Migrating to PostgreSQL

slide-2
SLIDE 2

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Why Migrate to PostgreSQL?

slide-3
SLIDE 3

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

PostgreSQL

  • Open Source

– Supported – Extendable

  • Advanced
  • Reliable
  • Standard Compliant
slide-4
SLIDE 4

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

PostgreSQL

  • It’s an All-Rounder

– Low Latency – Big Data – High Availability – “Document” Database

  • Sometimes better than dedicated solutions

– Scale to petabytes (from Elasticsearch)

slide-5
SLIDE 5

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

PostgreSQL

  • Awesome Community
slide-6
SLIDE 6

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Why Migrate to Open Source?

slide-7
SLIDE 7

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Why Migrate to Open Source? Reason #1 is “Cost”

slide-8
SLIDE 8

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Why Migrate to Open Source? Reason #1 is “Cost” (or it used to be)

slide-9
SLIDE 9

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Top Reasons to Stay in Open Source

  • 1. Competitive features, innovation
  • 2. Freedom from vendor lock-in
  • 3. Quality of solutions
  • 4. Ability to customize and fix
  • 5. Cost

https://www.slideshare.net/blackducksoftware/ 2016-future-of-open-source-survey-results

slide-10
SLIDE 10

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Migration Timeline

  • Effort Assessment
  • Decision (is it worth?)
  • Preparation
  • Testing
  • Migration
  • Cleanup
slide-11
SLIDE 11

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Effort Assessment

  • Schema
  • Data
  • Code

– What language? (SQL / Other) – Where? (Client / Server)

  • Architecture
slide-12
SLIDE 12

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Schema

  • Usually the easiest part

– Available via common tools

  • Map data types as appropriate

– Look for simplifications

  • Consider custom datatypes

– Simpler is better than complex – Complex is better than complicated Zen of Python

slide-13
SLIDE 13

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Data Type

  • PostgreSQL has several data types
  • Classical: text, numbers, boolean, time/date
  • Modern: Arrays, JSON
  • User-defined:

– Composite – Enumerative – Your data type in C

slide-14
SLIDE 14

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Data Type Gotcha

  • Oracle NUMBER to NUMERIC
  • MySQL BOOLEAN to BOOLEAN
  • Oracle NULL

SELECT first_name || second_name || last_name;

slide-15
SLIDE 15

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Data Type Gotcha

  • Oracle NUMBER to NUMERIC
  • MySQL BOOLEAN to BOOLEAN
  • Oracle NULL

SELECT first_name || COALESCE(second_name, '') || last_name;

slide-16
SLIDE 16

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Architecture Assessment

  • High Availability
  • Disaster Recovery
  • Multi-Master
  • Selective Replication
slide-17
SLIDE 17

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Target Architecture

slide-18
SLIDE 18

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Target Architecture

slide-19
SLIDE 19

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Solutions Mapping

  • Rich PostgreSQL ecosystem

– Core – Contrib / Extensions – Third Party (both FLOSS and proprietary)

  • Sometimes difficult to find exact match

– Might not be needed – You must match the purpose, not the tool

slide-20
SLIDE 20

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

slide-21
SLIDE 21

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Application Code

  • Many programming languages and

frameworks have PostgreSQL drivers

– Not an issue (usually)

  • Real issue: SQL variants with different feature

sets:

– Emulate missing features – Remove useless emulations

slide-22
SLIDE 22

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Application Code Gotcha

  • SELECT 1 FROM DUAL;
  • Upper case default in Oracle

– CREATE TABLE DUAL (); – DUAL → dual → “DUAL”

  • Exceptions in store procedures
slide-23
SLIDE 23

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Planning the Migration

  • The Assessment includes (at least) one Plan

– Time – Cost – Contingency / Rollback

slide-24
SLIDE 24

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Take Advantage of the Application

  • Some applications support multiple databases
  • They have done all the major part of the work
  • Functions, procedures, data types. It all works

already with PostgreSQL

  • Just need to migrate the data
slide-25
SLIDE 25

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Don’t Panic

slide-26
SLIDE 26

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Vanilla Deployment

slide-27
SLIDE 27

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Bilberry Deployment

slide-28
SLIDE 28

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Get the Schema Defjnition

slide-29
SLIDE 29

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Migrate Data

slide-30
SLIDE 30

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Migrate Data Downtime

slide-31
SLIDE 31

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Redirect the Application

slide-32
SLIDE 32

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Redirect the Application Showtime!

slide-33
SLIDE 33

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Alternative Strategy: Phasing Out

  • Use PostgreSQL for new services

– Keep old services as they are

  • Useful when standard plans are too

complicated / expensive

  • No need to migrate old data and code

– Easier: “just” plan new system

slide-34
SLIDE 34

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Phasing Out and Integration

  • Integrate new PostgreSQL with existing DBs
  • Preserve continuity of services
  • Foreign Data Wrappers

– Pluggable adaptors for other systems – SQL/MED standard – Some of them are read/write

slide-35
SLIDE 35

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Alternative Strategy: Preparation

  • Modify the existing system before migrating
  • Make it nearer to PostgreSQL

– Stop using incompatible features – Rewrite/simplify queries

  • Enables application compatibility
  • Makes migration easier / cheaper / faster
slide-36
SLIDE 36

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Testing

  • Compatibility
  • Performance
  • The migration process includes writing tests
slide-37
SLIDE 37

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Performance Testing

  • Test must include difficult / critical queries
  • Ensure that newer optimisations don’t cause

regressions on other queries

  • Use pgbench (custom scripts)
  • Analyse the current workload
  • Reproduce it
  • Properly dimension hardware
slide-38
SLIDE 38

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Scripted Migration

  • The migration procedure should be scripted as

much as possible

  • A script can be:

– Repeated – Versioned – Benchmarked – Tested

slide-39
SLIDE 39

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Scripted Migration

  • The migration procedure should be scripted as

much as possible

  • A script can be:

– Repeated – Versioned – Benchmarked – Tested (in staging environment)

slide-40
SLIDE 40

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Scripted Migration

  • The migration procedure should be scripted as

much as possible

  • A script can be:

– Repeated – Versioned – Benchmarked – Tested (in staging environment, please!)

slide-41
SLIDE 41

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Thoughts

  • Focus on the purpose not on emulating
  • Make a plan
  • Test, test, test
  • Learn PostgreSQL
slide-42
SLIDE 42

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Thoughts

  • Focus on the purpose not on emulating
  • Make a plan
  • Test, test, test, test, test, test, test
  • Learn PostgreSQL
slide-43
SLIDE 43

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Thoughts

  • Focus on the purpose not on emulating
  • Make a plan
  • Test, test, test, test, test, test, test
  • Learn PostgreSQL and get help
slide-44
SLIDE 44

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Thoughts

  • Focus on the purpose not on emulating
  • Make a plan
  • Test, test, test, test, test, test, test
  • Learn PostgreSQL and get help
  • Don’t Panic
slide-45
SLIDE 45

https://www.2ndQuadrant.com

Migrating to PostgreSQL / PgConf.EU

Milano, 16 October 2019

Thanks and Remember Benjamin Zander’s Rule #6

Boriss Mejias boriss.mejias@2ndquadrant.com @tchorix