Todos los secretos para migrar de ELCARO a PostgreSQL Alvaro Hernandez

ALVARO HERNANDEZ @ahachete DBA and Java Software Developer OnGres and ToroDB Founder PostgreSQL Community active member Database Frequent Speaker

PostgreSQL is the World's Most Advanced Open Source Relational Database and by the end of this talk you will understand what that means for you, an application developer. What kind of problems PostgreSQL can solve for you, and how much you can rely on PostgreSQL in your daily activities, including unit-testing. The Art of PostgreSQL Dimitri Fontaine 29/nov, 19h Liferay (Madrid) Todos los secretos para migrar de ELCARO a PostgreSQL Dimitri Fontaine is a PostgreSQL Major Contributor (Extensions, Event Triggers, etc) and maintains pgloader, your migration companion (see https:/ /pgloader.io). Dimitri has authored "Mastering PostgreSQL in Application Development", the book that teaches SQL to developers, available at https:/ /masteringpostgresql.com. https:// www.meetup.com/ PostgreSQL-Espana

Why Oracle to PostgreSQL migrations? Cost, cost, cost. Licensing terms (“men in black”). Don’t want to store core data on proprietary RDBMS. Oracle unavailable as-a-service on most clouds2. PostgreSQL is a natural (best) fit for Oracle migrations: plpgsql, DBA similar ops, advanced SQL support. Todos los secretos para migrar de ELCARO a PostgreSQL

An example is worth more than a 1,000 words: how we migrated BlackBerry Messenger (BBM), with 150M+ users, live from Oracle to PostgreSQL Todos los secretos para migrar de ELCARO a PostgreSQL

BBM: not your father’s BBM No longer depending on BlackBerry. Android & iOS. Modernized, being constantly improved. Similar to WeChat: a platform, including payments. 150M+ users. Todos los secretos para migrar de ELCARO a PostgreSQL

Some facts about BBM’s platform ~ 6.000 servers. Previous infra running on-prem in Canada. Current in GCP in Asia region. Oracle/PostgreSQL core part of the infrastructure: id (user login and auth services). DB size some TB, high trafficked, OLTP pattern. Cassandra used for user messages (+400 nodes). Todos los secretos para migrar de ELCARO a PostgreSQL

BBM migration requirements No downtime (near-zero downtime). No data loss (obviously). Data verified/checksummed. Across the Pacific (200-250ms latency). Long testing period with real data. Full PostgreSQL stack architecture on GCP: HA, monitoring, backups, DR, configuration tuning. Fully automated deployment. Todos los secretos para migrar de ELCARO a PostgreSQL

Migration steps Todos los secretos para migrar de ELCARO a PostgreSQL ‣ SQL MIGRATION ‣ DDL MIGRATION ‣ PL/SQL STORED PROCEDURES ‣ POSTGRESQL ARCHITECTURE ON GCP ‣ LIVE DATA MIGRATION TECHNIQUE AND TOOLS ‣ ROLLBACK MECHANISM ‣ TESTING AND PRODUCTION CUT-OVER

SQL migration In this particular case, Hibernate was used and no SQL translation was need. This is not the general case. Oracle SQL has many "oddities" like empty and null strings being the same. Many functions may be different or unsupported: use orafce <--- y orafce es un link a https://github.com/orafce/orafce Oracle does not have reserved keywords, PostgreSQL does: your "user" table will not work! Beware of casing rules, PostgreSQL requires quoting for upper-case identifiers Todos los secretos para migrar de ELCARO a PostgreSQL

DDL migration Main application was using Hibernate, so application needed few changes. Used ora2pg for DDL migration (awesome tool!) However, requires performance-sensitive changes: number in Oracle is a numeric in PostgreSQL. But may be better an integer or bigint, need to know the data. Timestamps are painful (well, always!) Todos los secretos para migrar de ELCARO a PostgreSQL

DDL migration (II) Carefully adjust users and permissions. Data constraints, default values from sequences could be refactored from triggers to normal constraints. Cleanup time! (remove unused indexes…) Ensure all tables have a PK (required for live data migration). Todos los secretos para migrar de ELCARO a PostgreSQL

DDL migration (III): partitioning Data in Oracle was not partitioned. Wanted to use the migration to partition. Partitioning code and functions/triggers created (9.6, no native partitioning as in 10/11). Transparent to data migration as long as schema is compatible between source/destination. Data clean up. Todos los secretos para migrar de ELCARO a PostgreSQL

PL/SQL stored procedures Plpgsql is similar to Oracle’s PL/SQL, but not directly compatible. Procedures need to be ported mostly manually. Create a set of unit tests to define existing valid and invalid inputs for existing code. Port to plpgsql. This was 9.6. PostgreSQL v11 introduced autonomous transactions, very welcomed! BBM did not contain many stored procedures. Todos los secretos para migrar de ELCARO a PostgreSQL

PostgreSQL Architecture on GCP Unmanaged: GCE instances, not CloudSQL. Fully automated deployment with Ansible (+shell, gcloud API for deployment). Env tested with Vagrant. Heavily tuned config file (learn more: PostgreSQL Configuration for Humans). PgBouncer for connection pooling. Patroni for HA. Consul for DCS. WAL-e for backup and DR, DataDog for monitoring. Todos los secretos para migrar de ELCARO a PostgreSQL

PostgreSQL Architecture on GCP (II) Todos los secretos para migrar de ELCARO a PostgreSQL

Live data migration No downtime requirement discards dump/restore solutions (like ora2pg). ETL is not an option either: requires created_at, updated_at fields; works in batches; requires expensive extra indexes or slow scans. A CDC (Change Data Capture) solution is required that supports Oracle as source and PostgreSQL as destination. Todos los secretos para migrar de ELCARO a PostgreSQL

CDC: Change Data Capture Select an arbitrary point in the (source) database timeline (SCN in Oracle, LSN in PG). Todos los secretos para migrar de ELCARO a PostgreSQL ‣ PHASE 1: DATA LOAD.
 OBTAIN A SNAPSHOT OF THE DATABASE AT THE SELECTED SCN. COPY ALL THE DATA FROM ALL TABLES TO DESTINATION. ‣ PPHASE 2. DELTAS. 
 DATABASE (OR EXTERNAL SOFTWARE) MINES THE LOGS (CHANGES TO THE DATABASE) HAPPENED SINCE THAT SCN AND EXTRACTS THEM TO THE DESTINATION DATABASE (AS SQL OR EQUIVALENT).

Supporting Oracle to PostgreSQL CDC Oracle Golden Gate Todos los secretos para migrar de ELCARO a PostgreSQL https:/ /unsplash.com/@gersonrepreza

Golden Gate Implements CDC pattern, supports many srcs and destinations. Wait, Oracle sw to migrate off of Oracle? Yes, site-wide license. Todos los secretos para migrar de ELCARO a PostgreSQL

Migration architecture Todos los secretos para migrar de ELCARO a PostgreSQL

GoldenGate (GG) gotchas PITA to get it working. Took experienced GG admin 2 fulltime weeks to get this setup working. Documentation output → 12 pages of commands and config files. Oracle to PostgreSQL use case poorly documented (if at all). Blogs/posts almost all do batch uploads or only deltas, but not real-time migration. Uses ODBC (by Progress), which brings its own gotchas 
 (e.g.: timestamp(6) to timestamp to make it work). Todos los secretos para migrar de ELCARO a PostgreSQL

GoldenGate (GG) gotchas (II) Synchronization point (between initial data load and CDC) is not enforced. Easy to overlap or miss data. Failure means mostly retry, which requires a lot of manual commands. Ended up fully scripting and automating GG operations. Poor debugging. Key to make it work is a hack: Add (where SCN == CSN)
 “, sqlpredicate 'AS OF SCN XXXXXXXX’;” to table copy definitions config to do initial snapshot. Todos los secretos para migrar de ELCARO a PostgreSQL

GG configuration snippet examples Todos los secretos para migrar de ELCARO a PostgreSQL extract live userid $user password $password rmthost localhost, mgrport 7810 rmttrail ./dirdat/ac table accountservice.account; extract load userid $user password $password rmthost localhost, mgrport 7810 rmttask replicat, group load table schema.table, sqlpredicate 'AS OF SCN XXXXXXXX';

DDL changes for CDC DDL cannot be changed while replication happens.
 However: Before initial load: DROP constraints, FKs, indexes to speed up load. After initial load: re-create all the missing DDL (indexes may be created concurrently). Create a temporal tombstone table to signal the end of data copy. Todos los secretos para migrar de ELCARO a PostgreSQL

Sequence management Sequences are not propagated. During cut-over, once tombstone is propagated, query sequence state in Oracle and apply to sequences in PostgreSQL. Needs to be scripted and fully automated. Optional: increment sequence numbers to a rounded number, to identify when migration happened. Todos los secretos para migrar de ELCARO a PostgreSQL

Rollback mechanism What if after cut-over migration fails (i.e. PostgreSQL is not performant enough, application bugs, etc) but writes are already happening in PostgreSQL? A “rollback” (switchback) mechanism is required. PostgreSQL’s logical decoding was used. Needs to be scripted and fully automated. raw_decoding plugin. Output to file. If rollback, execute the changes via sqlplus back into Oracle. Required code to adjust timestamp and other data types syntax. Todos los secretos para migrar de ELCARO a PostgreSQL

Testing and production cut-over Live migration was running cross-DC (Canada to Asia) for more than 2 months. Re-started frequently for application testing on PG on destination. Cut-over fully scripted, tested 3-5 times and timed. Database-contributed downtime: 5 mins real, another 40 for data checksumming. Todos los secretos para migrar de ELCARO a PostgreSQL

1177 Avenue of the Americas, Suite 500 New York, 10036, NY United States of America +1 (646) 452 7168 [email protected] Carretera de Fuencarral, 44, Edificio 4B, Loft 33 Alcobendas, 28108, MD España +34 918 675 554 [email protected] www.ongres.com