Toad World Blog

PostgreSQL database for Enterprises – Part III

Dec 14, 2017 1:01:00 PM by Porus Homi Havewala

In this article series, we are exploring why and how enterprises are starting to use PostgreSQL. In particular we are looking at an enterprise-ready version of the PostgreSQL database, such as EnterpriseDB Postgres Advanced Server (EDBPAS), which is even more closely compatible to Oracle and makes it easier to start using this low-cost database, either on premises or in the cloud.

We have discussed a few basic facts about the open-source PostgreSQL – a database more than 20 years old and owned by the Postgres foundation. The open source PostgreSQL is of course 100% free with no costs to download and install, but that doesn’t apply to Amazon’s offerings of PostgreSQL-compatible Aurora, where there would be a cost for using the Aurora database on the AWS cloud infrastructure. Likewise, the EnterpriseDB Postgres Advanced Server (EDBPAS) version, whether on premises or on the cloud, would certainly not be free. But we noted that EDBPAS does offer 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 bi-directional database replication services.

We next started to look at the two DBMS options available from the EDB Postgres platform, namely EDB Postgres Standard, and the more powerful EDB Postgres Advanced Server, EDBPAS in short. This option too is based on open-source PostgreSQL, but then adds additional developer and DBA features, and performance, security and database compatibility for Oracle.  The 24x7 support from EnterpriseDB as well as the EDB-developed tool suites are also included.

In addition to the two DBMS options, EDB also offers a deployment of PostGIS. This is an open source extension for the PostgreSQL object relational database providing spatial objects and functions, and it effectively enables storage and query of information about location and mapping. PostGIS was developed and maintained as an independent open source project, and is considered to be very useful for workloads that require geospatial characteristics. As an additional benefit, PostGIS when deployed in EDB Postgres inherits the native features of the database deployment, such as scalability and ACID compliance.

We then talked about the EDB tool suites, to understand the unique advantages they offer over and above the community edition of PostgreSQL. The first, the Integration tool suite, includes the EDB Postgres Replication Server that provides single and multi-master replication solutions for read-write scalability, availability, performance and data integration with databases such as Oracle, SQL Server and Postgres. The Integration suite also includes the EDB Postgres Data Adapters (which are foreign data wrappers) used to connect to different database systems. The EDB Postgres XA (standing for "eXtended Architecture") connector, another tool included in the Integration suite, allows the EDB Postgres Advanced Server to participate in XA two-phase commit transactions as a local resource manager, working with global transaction managers like Tuxedo.

7522.po.png-1100x19998

The second EDB tool suite provided is the Migration tool suite. This consists of the EDB Postgres Migration Toolkit, which provides the capability of fast, flexible and customized database migrations from different source databases like Oracle, SQL Server, Sybase and MySQL to target PostgreSQL and EDBPAS databases. We will explore this migration toolkit in detail later on in the article series, and even test out a migration.

In the case of complex databases with a lot of code, the situation is somewhat different.  Migration of complex databases may require a professional assessment to understand what part of the migration is automatic, and how much manual re-coding may be required. The EDB Services team can optionally be engaged for conducting an EDB Postgres Migration Assessment. The engagement builds a report for you highlighting compatibility, overlaps and mismatches between your source database and the target EDB Postgres Advanced Server database. 

We also mentioned a product from Quest, called "SharePlex® for EDB Postgres". This can be used to replicate production data easily from Oracle databases to EDB Postgres databases with zero performance impact, and almost no downtime. We will take a look at this as well, later on and also explore the installation and other aspects of the EDBPAS distribution.

The third EDB tool suite is the Management tool suite, which consists of mission-critical tools for management, monitoring, tuning, high availability, backup and disaster recovery. The tools in the Management suite include the EDB Postgres Enterprise Manager – this tool allows you to manage, monitor and tune enterprise-wide Postgres installations from a single console. 

Using EDB Postgres Enterprise Manager, you can tune your SQL performance with its SQL profiler, monitor and plan your resource requirements through its capacity manager, and receive alerts to out of normal conditions and much more.  

The Management tool suite also includes the EDB Postgres Failover Manager; this can be used to create a fault-tolerant database cluster to minimize database downtime on database failure – by automating database failover as soon as the primary database fails.

The EDB Postgres Backup and Recovery tool, included in the Management suite, eliminates tedious maintenance, error prone scripts, and manual work with simple commands and a system wide catalogue.

Now, let us talk about how EDB Postgres can be deployed. The options for the EDB Postgres Platform are manifold, from traditional bare metal right up to the latest hybrid cloud configuration.

First of all, traditional bare metal, virtualization, or containers can be used to deploy EDB Postgres. On bare metal, you can maximize database simplicity, control and power since the database is running directly on the hardware of the server. This has been tested and certified on the most popular operating systems. Virtualization, on the other hand, is often the choice for database consolidation, and leads to cost savings and hardware efficiency. Virtualization platforms such as VMware, KVM, Docker containers and others are supported.

Containers provide small units of granularity to ensure greater utilization of system resources. OpenShift and other Docker-based container platforms are supported. EDB is also developing a set of containers that intelligently groups various components for ease of use.

When it comes to the cloud, you can deploy EDB Postgres on the private cloud, pubic cloud, or the hybrid cloud. We continue discussing those cloud options in the next part of this article series.

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