PostgreSQL database for Enterprises – Part II

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.

In the first part of this series, we went through a few basic facts about the open-source PostgreSQL – a database more than 20 years old and owned by the Postgres foundation. Many consider this to be the world’s most advanced open source database. We talked about the support from a large international community, and the latest release of PostgreSQL 10 RC 1 by the PostgreSQL Global Development Group. We mentioned the sophisticated capabilities suitable for large enterprises, even in the open source version, such as Multi-Version Concurrency Control (MVCC) and Write Ahead Logging (WAL). This in turn enables online (hot) backups. The WAL feature also makes Point in Time Recovery (PITR) possible, and also enables Streaming Replication.

One important consideration we noted – it is not possible to create database packages (stored collections of procedures and functions) in the open source version of Postgres, but on the other hand the EnterpriseDB Postgres Advanced Server distribution does allow the creation of database packages, one of the many features that make it more compatible with the Oracle database.

On the Amazon AWS cloud, the Aurora database was made available as a PostgreSQL compatible version in late 2016, due to customer demand and interest in PostgreSQL. Amazon AWS also has started to provide a Database Migration Service (DMS) and a Schema Conversion Tool, however this could fall short of converting Oracle stored procedures and functions – the application code in the database layer – to a target non-Oracle database.  By switching to a PostgreSQL compatible database, the compatibility of PostgreSQL PL/pgSQL with Oracle PL/SQL would ultimately lead to further enhancements in the AWS Schema Conversion tool, such that it could convert most of the application code at the database layer.

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 in the cloud, would certainly not be free. But 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. Using this advanced distribution of Postgres, companies can start using EDB Postgres Advanced Server while also preserving their investments in their PL/SQL code, since they would not need to recode. Even building new applications and databases can be done at much lower cost, and all existing Oracle skills leveraged. High performance, scalability and continual innovation by the parent company of EDBPAS are other attractive features of this distribution.

We will now look more closely at each feature of EDB Postgres. The EDB Postgres Platform from EnterpriseDB is actually available as two DBMS options. The first DBMS option is EDB Postgres Standard, based on the open-source PostgreSQL. The EDB Postgres Standard option includes 24X7 support from EnterpriseDB, as well as EDB-developed tool suites for integration, migration, and management that we will look into shortly. These suites are deployable across many traditional platforms as well as cloud platforms.  The tools in these suites can be downloaded and installed from the EnterpriseDB Stackbuilder utility, or directly from the EnterpriseDB website.

The second and more powerful DBMS option available is the 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.

Let us look at the EDB tool suites now, so we can understand the unique advantages they offer over and above the community edition of PostgreSQL.

The first tool suite is the Integration tool suite. This lets you exchange data across different database management systems in near real time. Importantly, the Integration 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. These can eliminate data silos, so that you can use EDB Postgres as a data hub to read, analyze and write data to and from other data sources, using SQL statements to interact with these sources.    

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.

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

But how can you know in advance what parts of the migration are automatic, and how much manual recoding may be required? Obviously this depends on each source database, the data types in use, and the database code complexity in the functions, procedures, and packages. For complex databases with a lot of code, this may require a professional assessment. 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.  There is also 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.

We continue looking at the EDB tool suites in the next part of this article series. Later, we will also explore the installation and other aspects of this distribution.