Transcription
Postgres-as-a-Service at Swiss ReDr. Andreas Geppert, Christoph Danielewicz, Franz Faul, Alfonso TangaSwiss Re ManagementAndreas Geppert@swissre.com
ContentsSwiss Re IntroDatabase StrategyPostgres-as-a-Service and Database-as-a-Service at Swiss RePostgres ApplicationsExperiences and Open IssuesConclusion and DiscussionAndreas Geppert Swiss Re Management – IT GTM2
Swiss Re Group at a GlanceAndreas Geppert Swiss Re Management – IT GTM3
Swiss Re’s Vision and MissionAndreas Geppert Swiss Re Management – IT GTM4
Swiss Re’s Database StrategyKey Messages (3 out of 6)Deliver database-asa-serviceProvide open-sourcebased alternativerelational offeringsBroaden the scope tocover datamanagementservices beyondincumbent RDBMSAndreas Geppert Swiss Re Management – IT GTM5
Database- and Postgres-as-a-ServiceDecision to built up a Postgres offering in Swiss Re (2017)as a strategic, open source-based, cost-efficient option to closed source,commercial (relational) DBMSNO (forced) exit out of existing commercial RDBMSDecision to build up Database-as-a-Servicebased on (database) infrastructure by private cloud providerBuild DBaaS integration and management on top of Postgrese.g., internal DBaaS inventory and configuration managementPilot DBaaS with Postgres as first supported DBMSAndreas Geppert Swiss Re Management – IT GTM6
Postgres-as-a-ServiceEnterprise Readyness RequirementsFunctionalityAvailability and DRSecurityBackup and restoreScalability and performanceMonitoringOperations and supportRelease managementMigration supportAndreas Geppert Swiss Re Management – IT GTM7
Database-as-a-ServiceHigh-level Service CataloguePremiumStandardBasicAvailabilityVery highHighnormalDRRTO: fastRPO: zero/smallRTO: mediumfastRPO: 0hRTO: b. e.RPO 1dMaintenanceNo/smalldowntimeSmalldowntimeBest effortScalability /ElasticityVertical andhorizontalVerticalVerticalPerformance &IsolationIsolation(compute, IO)Shared, noisolationShared, noisolationSupport hours Comprehensive& reaction time & fastNormalBest effortCostMediumLowHighAndreas Geppert Swiss Re Management – IT GTM8
Swiss Re DBaaS Big PictureCustomersGUIAPISwiss Re EcosystemDBaaS Integration and Management FrameworkPostgres AdapterDynDB Adapter(IAM, service mgmt.,CMDB )SQLServer AdapterAPIsApp ASR PostgresProviderSC Oracle ProviderSC SQLServerProviderApp BAndreas Geppert Swiss Re Management – IT GTM9
DBaaS API: Database CreationAndreas Geppert Swiss Re Management – IT GTM10
Postgres-as-a-Service: Basic SetupThree different service classes are supported: basic, standard (DR), premium (HA &DR)DBaaS Postgres servers run in IaaS virtual machines (Linux)Each VM contains exactly one Postgres serverA Postgres server hosts one or more application databasesPostgres servers can be shared (i.e., contain databases from multiple applications) orbe private (contain databases from a single application)Virtual MachineAPostgres serverGuest OS(Linux)ZInfrastructure-as-a-ServiceAndreas Geppert Swiss Re Management – IT GTM11
Postgres Server CreationIntegration into Corporate t ServerIdentity &AccessMgmtMonitoringBackup InfraLogging &AuditingAZAndreas Geppert Swiss Re Management – IT GTM12
DBaaS Integration (1)Integration into corporate inventory and configuration managementIntegration into identity and access managementpersonal users: authentication via Active Directorytechnical users: password authentication (SCRAM)Backup/restore/archive/clone integrationenterprise backup infrastructure preferredBarMan under evaluationPatching/patch level monitoring and reportingBillingAndreas Geppert Swiss Re Management – IT GTM13
DBaaS Integration (2)Monitoring integrationDataDog (cloud-based SaaS solution)PGObserver and pg analyze under evaluationAuditing and logging integrationLogon/logoff eventsDDL eventsServer logs go into central ELK instanceAndreas Geppert Swiss Re Management – IT GTM14
Postgres-as-a-Service: Standard SetupDisaster recovery provided through replication to a redundant PostgresserverStandby server runs in a remote data centerReplication is implemented with repmgrAZDC Areplication(repmgr)AZDC BAndreas Geppert Swiss Re Management – IT GTM15
Postgres-as-a-Service: Premium SetupThe Premium service class adds another level of availability throughcascading replicationFirst standby runs in the same data centerfailover in case of a local failure (VM, primary cluster)Second standby runs in the remote data centerfailover in case of disasterNote: blueprint can be instantiated, but anti-affinities within DC cannot )AZDC ADC BAndreas Geppert Swiss Re Management – IT GTM16
Postgres Lifecycle Management ActionsCreate/read/update/delete forserversdatabasesschemasusers and roles as well as grantsOn-demand logical backup and restoreusing pg dumpExport and importSQL executionschema definitiondirect data changesAndreas Geppert Swiss Re Management – IT GTM17
Postgres ApplicationsDBaaS Inventory and CMDB generalized inventory and configuration of all DBaaS artefacts (clusters, databases, schemas, users,etc) and their relationships across all supported DBMSsPostgres Infrastructure Inventory and CMDB (i.e., Postgres Adapter) detailed inventory and configuration of all Postgres artefacts (clusters, databases, schemas, users,etc) and their relationshipsOracle Cross-application dependency analysis Analysis of cross-schema and –application dependencies on database level for several hundreddatabase applicationsfurther internal (infrastructure) applicationsbusiness pilotsunder construction: migration assessment toolplanned: capacity and performance management DWHAndreas Geppert Swiss Re Management – IT GTM18
Experiences, Wishes, and Open IssuesPostgres as a reliable and stable database platformAutomated setup of blueprints meeting differentiated SLAsUsage of Postgres for both, as provider and platform (for CMDB etc) enabledus to start much earlier and progress further than would have been possibleotherwiseWishes for upcoming releasesSecurity: authorization against ADMetering – to break down server cost (compute resources) to individualdatabases)Andreas Geppert Swiss Re Management – IT GTM19
Questions?Comments?Andreas Geppert Oracle PACE20
Andreas Geppert Swiss Re Management – IT GTM21
Legal notice 2018 Swiss Re. All rights reserved. You are not permitted to create any modificationsor derivative works of this presentation or to use it for commercial or other public purposeswithout the prior written permission of Swiss Re.The information and opinions contained in the presentation are provided as at the date ofthe presentation and are subject to change without notice. Although the information usedwas taken from reliable sources, Swiss Re does not accept any responsibility for the accuracyor comprehensiveness of the details given. All liability for the accuracy and completenessthereof or for any damage or loss resulting from the use of the information contained in thispresentation is expressly excluded. Under no circumstances shall Swiss Re or its Groupcompanies be liable for any financial or consequential loss relating to this presentation.Andreas Geppert Oracle PACE22
Usage of Postgres for both, as provider and platform (for CMDB etc) enabled us to start much earlier and progress further than would have been possible otherwise Wishes for upcoming releases Security: authorization against AD Metering -to break down server cost (compute resources) to individual databases) Experiences, Wishes, and Open Issues 19
