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:

• Supporting ADTs in a Database System
• Extensible access methods for new data types
• Support for Multiprocessors: XPRS
• Active Databases, Rule Systems and Stored procedures
• Log-centric Storage and Recovery

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.

Specifically:

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.

while MangoDB:

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:

Data access
PostgreSQL conforms to the SQL:2016 standard, the common query language of many RDBMSs.

Security requirements
PostgreSQL meets security needs in terms of availability, integrity, confidentiality and traceability.

Integration of PostgreSQL on technical platforms
PostgreSQL runs on multiple processor architectures and Operating Systems and is compatible with virtualization/containerization.

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: 

• cold backup
• hot backup 
• continuous backup
• SQL dump 

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: 

• Oracle's Materialized views4
  Since PostgreSQL 9. 3, materialized views are a built-in feature.
• Oracle uses PL/SQL and PostgreSQL uses PL/pgSQL; they are quite similar, but some adaptation is required.
• Strings can be replaced by VARCHAR or TEXT.
• CLOB strings are replaced by TEXT.
• There is no Oracle RAC equivalent in PostgreSQL; however, the DATAGUARD function is provided in PostgreSQL by replication.
• On Oracle, RMAN manages backups and restorations; there is no database equivalent under PostgreSQL. Several third-party tools exist, however, including pgBackRest and Barman. Archive logging is well implemented, almost identically, in the two DBMSs.

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.

More Information

The Guide on Github

The Guide as PDF

Related Articles

Move Over To PostgreSQL With Babelfish and MangoDB

PostgreSQL Is DB-Engines DBMS of the Year For 2020

PostgreSQL 14 Is Here - A Look At Its Past And Future

A Deep Dive Into PostgreSQL Indexes

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info