Toad World Blog

PostgreSQL for Enterprises - Part V

Feb 22, 2018 8:26:00 AM 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, which 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.

Now, we will talk about EDB Postgres Ark, another interesting capability.

3225.p31.png-1100x19998

Interesting name, but what is EDB Postgres Ark? It is actually a Database-as-a-Service implementation on various cloud environments. Take the case where you want to create single or multi-node Postgres clusters but you don’t have the infrastructure ready and available in your company. Well, in that case the Database-as-a-Service concept comes in handy, since it is based in the cloud – which is a ready-made infrastructure that you don’t need to set up beforehand. 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 Arck and implement it as a private Database-as-a-Service cloud. This will use OpenStack.

You can work with Ark through the GUI or a JSON-compatible API. As for applications, they would connect to the Postgres clusters through pgPool (this provides query routing and connection pooling for Postgres-based solutions), or if preferred, you can also connect to the members of the cluster directly. DBAs have full access to the databases via SSH. The architecture can be seen below:

1663.p32.png-1100x19998

As mentioned previously, 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 percent 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 in 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 (EDBPAS), which is very compatible with the Oracle database. EDBPAS compatibility for Oracle Database includes the following capabilities : SPL - A native implementation of a stored procedure language that supports the key constructs of PL/SQL, as well as APIs that support key capabilities of Oracle’s JDBC, ODBC, .NET, OCI and Pro*C interfaces, packages that provide the key functionality of the most popular Oracle packages, declarative partitioning to ease migration from Oracle, DBA tools for migrating established work processes and skill sets to EDB Advanced Server, and tools that automatically migrate data definitions, data, stored procedures and packages to EDBPAS.

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.

We continue in the next part of this article series.

Tags: PostgreSQL SharePlex EnterpriseDB Oracle 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.