Migrating from Proprietary RDBMS to PostgreSQL Database
Sushant Pawar I Database Solution Consultant, Ashnik
Recently, I have come across numerous situations where-in the organizations were considering to move from proprietary RDBMS system to Postgres. While they are willing to move to open source databases, they are sceptical whether PostgreSQL will be able to meet their specific requirements which are currently being taken care of by their existing proprietary RDBMS database.
On my journey from Oracle to Postgres, I have noticed the immense enthusiasm in the Postgres community worldwide to help organization relalise the benefits of open source. I will try to highlight a few key points which can be good enough for any organization to move from Proprietary RDBMS to Open Source Postgres.
1. Providing RDBMS features in Open Source way:
PostgreSQL is one of the most advanced open source databases in the world and the community behind it is working day in and day out to make it the best. Postgres is ACID compliant and offer much better security, reliability, scalability features than many of the other open source databases in the market. Coming from Oracle background, it comes naturally for me to I try and correlate the PostgreSQL features with oracle. There definitely similarities at my levels due to which it has been easier to understand and adapt Postgres with flexibility and agility.
I will list down a few:
- Write Ahead Log (WAL) files which is similar to redo logs in oracle that can be used for point in time recovery of database,
- Multi-version concurrency control feature similar to UNDO in oracle to provide point in time consistent view of data for select queries.
- Supports SQL dump and hot backup, which makes it possible to backup/restore particular schema using SQL dump or restore whole database point in time using hot backup.
The leading open source product companies like EnterpriseDB has developed Postgres Plus Advanced Server, on the foundation of community PostgreSQL that consists of bundled tools to migrate the Oracle/MS SQL server to PostgreSQL, Postgres Enterprise Manager (PEM) a central monitoring tool that can be used to monitor and tune database performance. Postgres Plus advanced Server also provides various security features (enhanced auditing, Row level security), server side code protection and HA options using streaming replication similar to that of Oracle Data guard.
Organization opting for Postgres Plus Advanced server can avail HA (streaming replication, Master/slave replication), Table partition features with no extra licensing cost which is usually is the case for most of the proprietary RDBMS products.Organizations having their databases running on Proprietary RDBMS system can really think of moving to PostgreSQL as it will not only help in saving significant costs but can also get optimal performance on par with proprietary databases.
2. Migration with minimal efforts:
Postgres Plus Advanced Server has come up with migration tool which can be used to migrate from Oracle database to Postgres with minimum effort. PostgreSQL support triggers, functions and custom procedural languages PL/pgSQL making most of the oracle code easy to be migrated to PostgreSQL using migration tool kit with least efforts.
3. No Extra efforts to train DBA/application team:
PostgreSQL and Oracle has a similar architecture hence the DBA teams especially the ones working on Oracle can learn PostgreSQL with minimal efforts. EDB supported Postgres Plus Advanced server provides Oracle compatible database version, hence DBA and developers can still have access to most of the oracle dictionary views.
4. Blend of RDBMS and NoSQL database:
In today’s world, where organization are using Web, mobile, IOT to do their business, it is challenging for them achieve the business goals though relational database systems alone. Most of the time data coming though these sources is unstructured and database system should be capable of storing, managing such unstructured humongous data. EDB Postgres provides the NoSQL capabilities along with the RDBMS comfort. EDB Postgres has introduced JSON & HSTORE data type that makes it capable of storing document and key value type of unstructured data. Organizations that require pure NoSQL technologies like MongoDB or Hadoop, can use EDB Postgres Data Adapters to extract, modify data from external data sources using standard SQL interface.
The Postgres community is continuously working on developing more and more advanced functionalities and versions to make Postgres database, the choice of open source database by organizations. Do watch out this space often to know and learn more about the advanced functionalities of Postgres, if you as an organization are planning to make a move from proprietary RDBMS systems to the ever evolving PostgreSQL database.
Sushant Pawar I Database Solution Consultant, Ashnik
Sushant is working as a Database Solution Consultant with Ashnik having more than 9 years of experience in Database administration during which he worked for companies like Capgemini, J.P. Morgan. Sushant has experience in managing the team of DBAs and well versed with Oracle Single instance, RAC Database implementation, HA Dataguard configuration, Database Administration, Migration and Performance tuning. He is an EnterpriseDB certified PostgreSQL 9.5 Associate and has also acquired Oracle 12C, 10g professional certification. His accolades include appreciation for his contributions on business critical database applications at J.P.Morgan.