Toad World Blog

PostgreSQL database for Enterprises – Part VI

Mar 12, 2018 12:38:00 PM by Porus Homi Havewala

In this article series, we are exploring why and how enterprises are starting to use PostgreSQL. The open-source PostgreSQL is a database more than 20 years old and owned by the Postgres foundation. In particular we will be looking in detail at an enterprise-ready version of the PostgreSQL database, namely EnterpriseDB Postgres Advanced Server (EDBPAS), which is even more closely compatible with Oracle and makes it easier for large enterprises to start using this low-cost database, either on-premises or in the cloud.

EDB Postgres offers deeper database compatibility for Oracle, with extensive support for PL/SQL, functions and packages, Oracle syntax and semantics, popular Oracle database utilities and interfaces, and bidirectional database replication services. Two DBMS options are available from the EDB Postgres Platform, namely EDB Postgres Standard, and EDB Postgres Advanced Server (EDBPAS). In addition to the two DBMS options, EDB also offers a deployment of PostGIS, which is an open source extension providing spatial objects and functions.

In the previous parts, we talked about the EDB tool suites – such as the Integration tool suite, including the EDB Postgres Replication Server, the EDB Postgres Data Adapters (foreign data wrappers), and the EDB Postgres XA (standing for "eXtended Architecture") connector. The second EDB tool suite provided is the Migration tool suite that consists of the EDB Postgres Migration Toolkit, and the EDB Postgres Migration Assessment separately provided by Professional services. The third EDB tool suite is the Management tool suite with tools for management, monitoring, tuning, high availability, backup and disaster recovery. This includes the EDB Postgres Enterprise Manager, the EDB Postgres Failover Manager, and the EDB Postgres Backup and Recovery tool.Next, we talked about the Deployment Options for the EDB Postgres Platform. These range from traditional bare metal, virtualization, and containers, up to the latest hybrid cloud configuration. You can deploy EDB Postgres on the private cloud, public cloud, or the hybrid cloud. A number of companies are looking at using the public cloud for their EDB Postgres databases, and you can do so on Amazon AWS, and, planned for the future, on Google Cloud and other clouds. And, finally, you can also set up a hybrid Postgres database deployment, with applications that span cloud types along with data exchange and replication.

On the support side, EnterpriseDB offers 24 X 7 Global production support; 10 X 5 Developer support, and professional services. A Remote DBA service is also offered. The certification offered by EnterpriseDB consists of PostgreSQL and EDB Postgres programs – Associate level and Professional level, with online certification exams. We also talked about the three different subscription models available for EDB Postgres; these include Enterprise, Standard and Developer subscriptions.

Next, we talked about EDB Postgres Ark, which is actually a Database-as-a -Service implementation on different cloud environments. Take the case where you want to create single or multi-node Postgres clusters. The Database-a-a-service concept comes in handy, since it is based on the cloud – which is a ready-made infrastructure that you don’t need to set up before hand. You can add replication, and you can scale out the cluster size and storage. For scaling up, you can move to more powerful machines. For security purposes, you can encrypt the cluster you have set up.

EDB Postgres Ark also offers features like self-healing of damaged nodes, applying software patches, cloning databases, disaster recovery, and decommissioning of the databases. All this can be done at the click of a button. There are options on whether to use a private cloud or a public cloud; if you prefer public; look on the Amazon marketplace where you can find Ark and deploy it on the AWS cloud. If you prefer a private cloud, then you can download Ark and implement it as a private Database-as-a-Service cloud. This will use OpenStack.

ext.jpg-320x240

Ark can scale out so you can get increased read performance, scale out if you want increased data storage, or scale up so as to increase RAM or CPU. You can set up scaling thresholds for number of server connections or percentage of storage used, and these thresholds can be used to automatically add new replicas to the cluster or increase the storage assigned to the cluster. The user interface also allows for simple up scaling (or down scaling) of the machine types to change RAM or CPU as needed. Of course, at this point of time there is no write performance scale out as in the case of Oracle Real Application Clusters (RAC).

Ark’s Database-as-a-Service (DBaaS) framework can be based on EDB Postgres Advanced Server, which is very compatible with the Oracle database. You can also use Ark in conjunction with EDB Replication Server. So you can bridge from traditional on-premises databases running on SQL Server and Oracle Database, to Ark DBaaS Postgres databases. As an example, you could have an order status website that runs in the Ark DBaaS architecture, and at the same time the order management system remains resident as a legacy database on premises but having continuous data replication to Postgres. The EDB Replication Server supports continuous replication of data from the traditional on-premises systems to the Ark DBaaS databases.

EDB Replication Server can also be used with Ark to minimize downtime for migrations from traditional database platforms, such as Oracle or SQL Server to Postgres-based DBaaS managed databases. As the first step, the EDB Migration Toolkit is used for the initial migration of the source database. The Migration Toolkit can effectively be used to migrate an 'on-premises' Oracle or SQL Server database into an EDB Postgres Advanced Server (EDBPAS) database hosted in an EDB Ark hosted cluster. Next, you can use EDB Replication Server to bring the DBaaS-managed Postgres database instance up-to-date with the original source database. Once the destination database has the same data as the source, you can redirect the application to the DBaaS-managed instance.

So, effectively you have combined the functionality migration capabilities of the EDB Migration toolkit with the continuous data replication capabilities of EDB Replication Server, and this technique can allow you to migrate to open source based solutions with minimal downtime. Additionally, the Quest SharePlex product has a feature called SharePlex for Postgres which can be used to replicate production data easily from Oracle databases to EDB Postgres databases with zero performance impact and almost no downtime.

The EDB Migration Toolkit facilitates migration of database objects and data into an EDB Postgres Advanced Server or PostgreSQL database. Using this toolkit you can migrate from the following source databases: Oracle, MySQL, Sybase, and SQL Server. You can also use the Migration Toolkit to migrate between PostgreSQL and EDB Postgres Advanced Server.

Let us now have a brief look at the version history of EDB Postgres Advanced Server. EDB2005 was released in 2005. Advanced Server 8.1 came a year later in 2006. This version had an integrated debugger, enhanced Oracle compatibility (compared to the earlier version), and a DBA Management server. The next version, Advanced Server 9.0 was released in 2011; it had the new features of streaming replication, parallel Data Loader, replication from SQL Server to PostgreSQL, and Infinite Cache. After this, Advanced Server 9.5 was released in 2016 and it too boasted several new feature,s such as password profiles, audit log tagging, and 12th generation Oracle compatibility.

We will continue in the next part of this article series.

Tags: PostgreSQL SharePlex Oracle EnterpriseDB Postgres

Porus Homi Havewala

Written by Porus Homi Havewala

Porus Homi Havewala is a Double Oracle Certified Master (OCM) in 11g and 10g. He was awarded the prestigious "Oracle ACE Director" title by Oracle USA in 2008. He worked as the Strategic Program Advisor at Oracle Corporation Singapore, and is a regional SME on Oracle Enterprise Manager technology, specifically concentrating on private/hybrid Database cloud capabilities on Oracle systems.

Porus has worked prominently for a number of years at the largest telecommunications company Telstra in Australia, where he personally set up and managed the first production Enterprise Manager Grid Control 10g project in the world in the early 2000s, controlling thousands of Data Center targets with multiple Administrator teams around Australia. He has 26+ years of experience in the IT industry, and 18+ years of experience with Oracle Technology, including as an Oracle Developer and Oracle Senior DBA.

He is the creator and manager of the Enterprise Manager blog with Oracle Press Credentials, the Clouds and Databases blog, and the "Oracle Senior DBA" group on LinkedIn.com with 61,000+ members. Porus is the author of thirteen published technical articles and three white papers on Enterprise Manager in the Oracle Technical Network (OTN), and has authored a number of Enterprise Manager Books. He started his technical writing with a book on Microsoft Foxpro as early as 1994. His recent Enterprise Manager books are as follows: Oracle Enterprise Manager Cloud Control 12c: Managing Data Center Chaos published by PACKT Publishing, and the latest book Oracle Database Cloud Cookbook with Oracle Enterprise Manager 13c Cloud Control published by Oracle Press.

Dedication: I dedicate my pen and all its output to my beloved parents in heaven, they are the ones that have taught me the good values in life.

Disclaimer: My writings in blog entries, articles or books express my views and opinions in my personal capacity and not that of Oracle Corporation.