Oracle to Aurora PostgreSQL Migration Playbook

1 OracleDatabase19cToAmazonAurorawithPostg reSQLC ompatibility( ) 2021 AmazonWebServices, scurrentproductofferingsandpracticesasof thedateofissueofthisdocument, sproductsorservices,eachofwhichisprovide d asis withoutwarrantyofanykind, ,representations,contractualcommitments, conditionsorassurancesfromAWS,itsaffilia tes, ,andthisdocumentisnotpartof,nordoesitmod ify, 'sNew20 AWSM igrationToolsandService22 AWSS chemaConversionTool(SCT)23 SCTA ctionCodeIndex32 AWSD atabaseMigrationService(DMS)45 AmazonRDSonOutposts47 AmazonAuroraBacktrack48 AmazonRDSP roxy52 AmazonAuroraServerlessv153 MigrationQuickTips57 SQL& Usage60 OracleCreateTableasSelect(CTAS) CreateTableasSelect(CTAS)68 PostgreSQL Usage68 OracleCommonTableExpression(CTE) CommonTableExpressions(CTE)70 PostgreSQL SERIALType72 PostgreSQL (MVCC) Multi-VersionConcurrencyControl(MVCC)78 PostgreSQL Usage80 OracleMergevsPostgreSQL Merge87 PostgreSQL WindowFunctions90 PostgreSQL Usage90 OracleSequencesvsPostgreSQL Transactions102 PostgreSQL DO109 PostgreSQLU sage110 RANDOMF unction123 PostgreSQLU sage124 OracleDBMS_SQLP ackage(11g&12c) UserDefinedFunctions141 PostgreSQLU sage142 OracleUTL_FILE144 PostgreSQLU sage144 OracleUTL_MAIL&UTL_SMTP(11g&12c)

2 ScheduledLamdawithSES145 PostgreSQLU sage146 Tables& AuroraReplicas165 PostgreSQL TableConstraints168 PostgreSQL TemporaryTables182 PostgreSQL TriggerProcedure187-4- PostgreSQL Usage188 OracleTablespaces& TablespacesandDatafiles195 PostgreSQL UserDefinedTypes201 PostgreSQL AlterTable205 PostgreSQL ViewsandFunctions207 PostgreSQL InvisibleIndexes222 PostgreSQLU sage223 OracleIndex-OrganizedTable(IOT) Cluster Encoding234 PostgreSQL DBLinkandFDWrapper239 PostgreSQL IntegrationwithAmazonS3249 PostgreSQL JSONS upport254 PostgreSQL MaterializedViews259 PostgreSQL Databases263 PostgreSQL LargeObjects(LOBs)275 PostgreSQL Views277 PostgreSQL XMLType&Functions281 PostgreSQL LoggingOptions290 PostgreSQLU sage291 HighAvailabilityandDisasterRecovery(HADR ) Replicates297 PostgreSQL Usage298 OracleRealApplicationClusters(RAC) AuroraArchitecture302 PostgreSQL pg_dump& AmazonAuroraSnapshots(FlashbackTables)32 1 PostgreSQLU sage322-6-OracleRecoveryManager(RMAN) * ErrorLogviaAmazonRDSC onsole337 PostgreSQL Usage338 OracleSGA& MemoryBuffers342 PostgreSQL SessionParameters351 PostgreSQL Sharding401-7-Monitoring402 OracleV$ SystemCatalog&TheStatisticsCollector403 PostgreSQLU sage404 MigrationQuickTips410 MigrationQuickTips411 Glossary413-8-IntroductionThemigrationpr ocessfromasourcedatabase(OracleorSQL Server)toAmazonAurora(PostgreSQLorMySQL) (SCT)andtheAWSD atabaseMigrationService(DMS) , 'tbeautomaticallymigratedusingtheAmazonW ebServicesSchemaConversionTool(AWSSCT).

3 Itfocusesonthedifferences,incompatibilit ies,andsimilaritiesbetweenthesourcedatab aseandAurorainawiderangeoftopicsincludin gT-SQL,Configuration,HighAvailabilityand DisasterRecovery(HADR),Indexing,Manageme nt,PerformanceTuning,Security, (DMS)toolsforautomatingthemigrationofsch ema, ,examples, , SCT,youmayseeareportthatlistsActioncodes ,whichindicatessomemanualconversionisreq uired, , , :Appendix:MigrationQuickTipsprovidesalis toftipsforadmin-istratorsordeveloperswho havelittleexperiencewithAurora(PostgreSQ LorMySQL). , ,commands,guides,bestpractices, ,commands,bestpractices, :Noneorminimallow-riskandlow-effortrewri tesneededHighcompatibility:Somelow-riskr ewritesneeded,easyworkaroundsexistforinc om-patiblefeaturesMediumcompatibility:Mo reinvolvedlow-mediumriskrewritesneeded,s omeredesignmaybeneededforincompatiblefea turesLowcompatibility:Mediumtohighriskre writesneeded,someincompatiblefeaturesreq uireredesignandreasonable-effortworkarou ndsexistVerylowcompatibility:Highriskand /orhigh-effortrewritesneeded,somefeature srequireredesignandworkaroundsarechallen gingNotcompatible:Nopracticalworkarounds yet,mayrequireanapplicationlevelarchi-te cturalsolutiontoworkaroundincompatibilit iesSCT/DMSA utomationLevelLegendSCT/DMSA utomationLevelSymbolDescriptionFullAutom ationPerformsfullyautomaticconversion, :Minor.

4 Notcurrentlysupported, (CTAS)CreateTableAsselect(CTAS)CommonTab leExpression(CTE)CommonTableExpres-sion( CTE)IdentityColumnIdentityColumnlSincePo stgreSQL10,therearenodifferencesbesidest hedatatypesInsertAsSelectInsertAsSelectl ERRORLOG andsubpartitionoptionsarenotsupportedbyP ostgreSQLL ockingLockinglPostgreSQLusesautocommitby default(canbechanged)MERGEMERGElMERGE isnotsupportedbyPostgreSQL,workaroundava il-ableOLAPFunc-tionsOLAPF unctionslGREATESTandLEAST func-tionsmightgetdifferentresultsinPost greSQLlCONNECTBY isnotsup-portedbyPostgreSQL,work-arounda vailableSequencesSequenceslDifferentsynt axforfewoptionsinPostgreSQLT ransactionsTransactionslPostgreSQLdoesn' tsupportSAVEPOINT,ROLLBACKTOSAVEPOINT insideoffunc-tions-12-TablesOracleAurora PostgreSQLKeyDifferencesCompatibilityDat aTypesDataTypeslBFILE,ROWID,UROWID arenotsupportedbyPostgreSQLReadOnlyReadO nlylREADONLY modeisnotsup-portedbyPostgreSQL,shouldus eworkaroundConstraintsConstraintslREF.

5 ENABLE/DISABLE arenotsupportedbyPostgreSQLlConstraintso nviewsarenotsup-portedbyPostgreSQLTempTa bleTempTablelGLOBAL temporarytableisnotsupportedbyPostgreSQL lCan'treadfrommultipleses-sionsinPostgre SQLlTabledroppedaftersessionendsinPostgr eSQLT riggersTriggerslDifferentparadigmandsynt axlSystemtriggersarenotsup-portedbyPostg reSQLT ablespacesTablespaceslAllsupportedbyPost greSQLexceptmanagingthephysicaldatafiles UserDefinedTypesUserDefinedTypeslFORALL statementandDEFAULT optionarenotsup-portedbyPostgreSQLlPostg reSQLdoesn'tsupportcon-structorsofthe"co llection"typeUnusedColumnsUnusedColumnsl PostgreSQLdoesnotsupportunusedcolumnsVir tualColumnsVirtualColumns-13-Configurati onOracleAuroraPostgreSQLKeyDifferencesCo mpatibilityAlertingAlertinglUseEventNoti ficationsSub-scriptionwithAmazonSimpleNo tificationService(SNS)CacheandPoolsCache andPoolslDifferentcachenames.

6 SimilarusageDatabasePara-metersDatabaseP ara-meterslUseClusterandData-base/Cluste rParametersSessionPara-metersSessionPara meterslSEToptionsaresignificantlydif-fer entinPostgreSQLS pecialFeaturesOracleAuroraPostgreSQLKeyD ifferencesCompatibilityCharacterSetChara cterSetlUTF16characterandNCHAR/NVARCHAR datatypesarenotsupportedDatabaseLinksDat abaseLinkslDifferentparadigmandsyntaxDBM S_SCHEDULERDBMS_SCHEDULERlUseSchedulerAW SL ambdaExternalTablesExternalTableslPostgr eSQLdoesn'tsupportEXTERNALTABLEsInlineVi ewsInlineViewsJSONJSONlDifferentparadigm andsyntaxwillrequireapplication/driversr ewriteMaterializedViewsMaterializedViews lPostgreSQLdoesnotsupportautomaticorincr ementalREFRESH-14-OracleAuroraPostgreSQL KeyDifferencesCompatibilityMulti-tenantM ulti-tenantlDistributeload/applications/ usersacrossmultipleinstancesResourceMan- agerResourceManagerlDistributeload/appli cations/usersacrossmultipleinstancesSecu reFileandLOBsSecureFileandLOBslSecureFil esarenotsupportedbyPostgreSQL,automation andcompatibilityreferonlytoLOBsViewsView sXMLXMLlDifferentparadigmandsyntaxwillre quireapplication/driversrewriteHighAvail abilityandDisasterRecovery(HADR)

7 OracleAuroraPostgreSQLKeyDifferencesComp atibilityActive Data GuardActive Data GuardlDistributeload/applications/usersa crossmultipleinstancesRACRAClDistributel oad/applications/usersacrossmultipleinst ancesTrafficDirectorTrafficDirectorlSome featuresmaybereplacedbyAWS RDS ProxyIndexesOracleAuroraPostgreSQLKeyDif ferencesCompatibilityAutomaticIndex-ingA utomaticIndexinglPostgreSQLdoesnotsup-po rtAutomaticIndexingBITMAPBITMAPlPostgreS QLdoesnotsup-portBITMAP index-BRIN indexcanbeusedinsomecases-15-OracleAuror aPostgreSQLKeyDifferencesCompatibilityBT reeBTreeCompositeCompositeFunctionBasedI ndex(FBI)FunctionBasedIndex(FBI)lPostgre SQLdoesn'tsupportfunctionalindexesthatar en'tsingle-columnInvisibleIndexesInvisib leIndexeslPostgreSQLdoesnotsup-portInvis ibleIndexesIndex-OrginaizedTable(IOT)Ind ex-OrginaizedTable(IOT)lPostgreSQLdoesno tsup-portIndex-OrganizedTables,partially workaroundavail-ableLocalandGlobalIndexe sLocalandGlobalIndexeslPostgreSQLdoesnot sup-portdomainindexbutbeha- ,%BULK_EXCEPTIONS,and%BULK_ROWCOUNT arenotsupportedDBMS_OUTPUTDBMS_ ,sim-ilarfunctionalityPhysicalStorageOra cleAuroraPostgreSQLKeyDifferencesCompati bilityPartitionsPartitionslForeignkeysre ferencingto/frompartitionedtablesaresupp ortedontheindividualtablesinPost-greSQLl Somepartitiontypesarenotsup-portedbyPost greSQLS hardingShardinglPostgreSQLdoesn'tsupport shardingSecurityOracleAuroraPostgreSQLKe yDifferencesCompatibilityEncryptionEncry ptionlUseAWSA uroraEncryptionRolesRoleslSyntaxandoptio ndifferences,similarfunctionalitylTherea renousers-onlyrolesinPost-greSQLU sersUserslSyntaxandoptiondifferences.

8 SimilarfunctionalitylTherearenousers-onl yrolesinPost-greSQLF uturecontentOracleAuroraPostgreSQLKeyDif ferencesCompatibilityLogMinerLogMinerlPo stgreSQLdoesnotsupportLogMiner,workaroun davailableBackupandRecovery-18-OracleAur oraPostgreSQLKeyDifferencesCompatibility DataPumpDataPumplNon-compatibletoolFlash backData-baseFlashbackDatabaselStoragele velbackupmanagedbyAmazonRDSF lashbackTableFlashbackTablelStoragelevel backupmanagedbyAmazonRDSRMANRMANlStorage levelbackupmanagedbyAmazonRDSM onitoringOracleAuroraPostgreSQLKeyDiffer encesCompatibilityInformationViewsInform ationViewslTablenamesinqueriesneedtobech angedinPostgreSQL-19-What' , ,fortheopen-sourcedatabases, , :RDSONLY:Thisparagraphisaboutthelatestdb engineversionwhichissupportedonlyinRDS(a ndnotAurora)UpdateAspectLinkUpdatedscree nshotsAWSAWSSCTU pdatedSCTwarninglistsAWSSCTE rrorCodeAmazonRDSonOutpostsAWSRDSO utpostsAmazonRDSP roxyAWSRDSP roxyAmazonAuroraServerlessAWSA uroraServerlessAWSRDSB acktrackAWSRDSB acktrackNewfeaturesforpg_dumpsupportfore xtensiondependenciespg_dumpdumpfilescann otonlybeplaintextfilesbutalsowithcustoma ndcompressedformatsupportforWHERE inCOPYP ostgreSQLpg_dumpOnlinepartitionmergingan dpartitionstrategymodificationOracle19 HybridpartitionedtablesOraclePartitionsR ewritepartitioningtopictopresentdeclarat ivepartitionsPostgreSQLP artitionsMonitoringindexcreationPostgreS QLO verallIndexesMonitoringVACUUM FULL andCLUSTER operationsMonitoringindexcreationNewsyst emviewstomonitorsharedmemoryusageandANAL YZE progressPostgreSQLI nformationViewsNewoptionAND CHAIN (18c+19c)

9 PDB/CDBfeatureslike:CDB fleets,DBCAPDB Clone,PDB CloneswitcherandmoreOracleContainerDatab asesCopyingdatabasesusingtemplatesPostgr eSQLC ontainerDatabasesScalablesequencesOracle SequencesNewOracle19function,JSON_SERIAL IZEO racleJSONNewOracle19addingDISTINCT optionforLISTAGG functionandnewbitmapfunctionstospeedupCO UNT DISTINCTO racleAggregateFunctionsNewtopicofAutomat icIndexingOracleAutomaticIndexingOracle1 9real-timestatisticscollectionandHigh-Fr equencyAutomaticOptimizerStatisticsColle ctionOracleStatisticsTablestorageparamet ersthatcantriggerautovacuumPostgreSQLS tatisticsOracle19 SQL QuarantineOracleExecutionPlansCancelquer iesautomaticallyPostgreSQLE xecutionPlansSQL*LoaderNewtopicSQL*Loade rCasesensitivetopictocalloutthedifferenc ebetweenthetwoenginesNewtopicCaseSensiti veUpgradesdifferencesNewtopicUpgradesAma zonRDS ProxyNewtopicConnectionManagerPostgreSQL G eneralmigrationtipstopichasbeenaddedhere : ,see.

10 (SCT)UsageTheAWSS chemaConversionTool(SCT)isaJavautilityth atconnectstosourceandtargetdatabases,sca nsthesourcedatabaseschemaobjects(tables, views,indexes,procedures,etc.), , , : : # , > ,usethekeyboardshortcut<Ctrl+W>. ,includingdetailsoftheindividualissues,c lickSavetoPDFandopenthePDF (PostgreSQLcom-patible).Scrollfurtherdow ntothesectionDetailedrecommendationsforA mazonAurora(PostgreSQLcompatible) ,AWSSCT ,youcanexploretheindi-vidualissuesandrec ommendationsdiscoveredbyAWS ,expandsampledatabase>dmssample>Procedur ess> (issue811above). , , , , , , , :Minor, :NotcurrentlysupportedbySCT, :ThelinksinthetablepointtotheOracletopic pages, ,somechangesmayberequiredforDMLrelatedto ERRORLOG,subquery,orpartitionsActionCode ActionMessage5064 PostgreSQLdoesn'tsupporttheUPDATE statementwiththeERRORLOG option5065 PostgreSQLdoesn'tsupporttheUPDATE statementforasubquery5067 PostgreSQLdoesn'tsupporttheDELETE statementwiththeERRORLOG option5068 PostgreSQLdoesn'tsupporttheDELETE statementforasubquery5098 PostgreSQLdoesn'tsupporttheDELETE statementforaPARTITION5126 PostgreSQLdoesn tsupporttheMODEL statement5140 Postgr