|The PostgresSQL Transition Guide Helps You Make The Switch|
|Written by Nikos Vaggalis|
|Tuesday, 18 January 2022|
There is now an English version of a guide that goes through the considerations you have to make in order to migrate to PostgreSQL from another Database Management System.
Initially released in 2013 association with the French administration, the guide was previously only available in French. Its English version came out in November 2021 with the aim to address a much wider audience.
It's a light read that, without going too deep into technical details, enumerates the advantages of using PostgreSQL by describing its integration, security and robustness mechanisms. As to why you would replace your current DBMS with Postgres, there's plenty of reasons. In PostgreSQL 14 Is Here - A Look At Its Past And Future I give two of them:
There are two reasons behind its success; first that it is truly open source at heart, therefore embraced by a strong and vibrant community and then that it was shaped by the visionary ideas Mike Stonebraker had, which formed the basis of the marvel that followed.
The groundbreaking features that it first introduced were:
Then there's the fact that it constantly occupies the top places in various benchmarks and surveys as I describe in PostgreSQL Is DB-Engines DBMS of the Year For 2020
In yet another confirmation of its popularity and worth, PostgreSQL has taken the annual accolade awarded by DB-Engines. In a short statement, DB-Engines states:
PostgreSQL is the database management system that gained more popularity in our DB-Engines Ranking within the last year than any of the other 360 monitored systems. We thus declare PostgreSQL as the DBMS of the Year 2020.
Add in extensibility. In Move Over To PostgreSQL With Babelfish and MangoDB I've looked into two solutions that allow Postgres to behave like SQL Server and MongoDB (not it's not a typo) through the sophisticated layers of Babelfish and MongoDB (now FerretDB). This approach makes it easy to port your application originally built for SQL Server and MangoDB to work with a Postgres backend engine.
Babelfish is a set of extensions that provide both T-SQL capabilities and a Tabular Data Stream (TDS) listener port as enhancements to PostgreSQL. It supports the SQL Server dialect, T-SQL, and notable features including savepoints, stored procedures, nested transactions, etch.
is a stateless proxy, which converts MongoDB protocol queries to SQL, using PostgreSQL as a database engine. It is compatible with MongoDB drivers, and should work as a drop-in replacement to MongoDB in many cases.
The benefits are getting rid of licensing fees, since you move from propriety SQL Server to open source PostgreSQL. But what about MongoDB, isn't it open source already? Yes, but it has recently changed its license to SSPL (check The SSPL is Not an Open Source License) making it unusable for many Open Source and Commercial Projects.
If those reasons haven't made you considering the move, let's return to the Transition Guide and examine a few more, starting with the use cases:
Integration of PostgreSQL on technical platforms
It runs on the cloud. All major cloud vendors have a (or sometimes multiple) managed PostgreSQL offer.
It is compatible with many storage and backup technologies:
Administration via monitoring tools and logs analysis; high availability, scalability and clustering complete the state of operations. But there's also the developer side of the story.
PostgreSQL offers standard data types (alphanumeric, date, time, BLOB, etc) as well as more complex data types (geospatial, XML, JSON, etc) as described in "Supporting ADTs in a Database System". Table partitioning is implemented as standard while PostgreSQL complies with the modern SQL 2016 standard.
Of course there are many APIs for accessing it from clients written in Java, . NET, Python, Perl. . . even from Tcl through pgtclng. It offers stored procedures, functions, triggers and Foreign Data Wrappers. Wrappers are extensions that allow PostgreSQL to communicate with other data sources. The data sources can be relational databases (PostgreSQL, MySQL, Oracle, etc), NoSQL databases (CouchDB, MongoDB, etc), CSV files, or LDAP directories.
There is a PostgreSQL extension for geographic objects (PostGIS) that conforms to the Open Geospatial Consortium (OGC) Standards.
PostgreSQL can also be used in the field of Business Intelligence as a data warehouse, in conjunction with reporting tools (BusinessObjects, Pentaho, etc). It comes with a built-in and feature rich full-text search engine.
Many free software packages are natively based on PostgreSQL (document management systems (DMS), rules engines, collaboration software, supervision software, etc).
Having persuaded you of its value, the guide now shifts focus to actually making recommendations for migrating by providing a very helpful Decision-making sub-guide which attempts to describe the decision process being used in several companies.
It also hosts a comparison of features that exist in one dbms to their counterparts in PostgreSQL. So to take Oracle as an example:
As such we find Oracle:
These examples also feature DB2 and Informix, but I would have thought that it should have also included other popular databases like SQL Server or MySQL. SQL Server instructions are constrained into a lone paragraph featuring open-source tools that can help facilitate the data migration, as well as referencing Babelfish.
Other than that, switching to another DBMS is a project in its own right and this might involve training staff, hiring support, upgrading of the operating procedures and application adjustment including test costs (technical, functional and non regression test). These should also be taken under consideration.
Finally the guide wraps it up with references to further documenatation.
To sum it up, the PostgreSQL transition guide can prove most helpful when considering the switch or looking to start from scratch and considering if the Postgres product will be a good fit.
It begins with enumerating Postgres's features and advantages, embracing the full spectrum of Operation, Administration, Development, Tool, Licensing and Support. It then compares features found in another dbms to the ones included in Postgres. In closing up it details tools and procedures as well as obvious and hidden costs when considering making the move. With that it manages to address both management's as well as the developers' concerns. Make sure you have it by your side.
or email your comment to: email@example.com
|Last Updated ( Tuesday, 18 January 2022 )|