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)

2 UserDefinedFunctions141 PostgreSQLU sage142 OracleUTL_FILE144 PostgreSQLU sage144 OracleUTL_MAIL&UTL_SMTP(11g&12c) 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)

3 * 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).I tfocusesonthedifferences,incompatibiliti es,andsimilaritiesbetweenthesourcedataba seandAurorainawiderangeoftopicsincluding T-SQL,Configuration,HighAvailabilityandD isasterRecovery(HADR),Indexing,Managemen t,PerformanceTuning,Security, (DMS)toolsforautomatingthemigrationofsch ema, ,examples, , SCT,youmayseeareportthatlistsActioncodes ,whichindicatessomemanualconversionisreq uired, , , :Appendix:MigrationQuickTipsprovidesalis toftipsforadmin-istratorsordeveloperswho havelittleexperiencewithAurora(PostgreSQ LorMySQL).

4 , ,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.

5 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.

6 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)

7 CacheandPoolsCacheandPoolslDifferentcach enames,similarusageDatabasePara-metersDa tabasePara-meterslUseClusterandData-base /ClusterParametersSessionPara-metersSess ionParameterslSEToptionsaresignificantly dif-ferentinPostgreSQLS 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.

8 AutomationandcompatibilityreferonlytoLOB sViewsViewsXMLXMLlDifferentparadigmandsy ntaxwillrequireapplication/driversrewrit eHighAvailabilityandDisasterRecovery(HAD R)OracleAuroraPostgreSQLKeyDifferencesCo mpatibilityActive 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)

9 LPostgreSQLdoesn'tsupportfunctionalindex esthataren'tsingle-columnInvisibleIndexe sInvisibleIndexeslPostgreSQLdoesnotsup-p ortInvisibleIndexesIndex-OrginaizedTable (IOT)Index-OrginaizedTable(IOT)lPostgreS QLdoesnotsup-portIndex-OrganizedTables,p artiallyworkaroundavail-ableLocalandGlob alIndexesLocalandGlobalIndexeslPostgreSQ Ldoesnotsup-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.

10 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