The PostgresSQL transition guide helps you make the switch
There is now an English version of a guide that reviews the considerations you need to make when migrating to PostgreSQL from another database management system.
Originally published in 2013 in association with the French administration, the guide was previously only available in French. Its English version was released in November 2021 with the aim of reaching a much wider audience.
It’s a light read that, without going into too much technical detail, lists 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 are many reasons. In PostgreSQL 14 is here – A look at its past and its future, I give two:
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 of Mike Stonebraker, which formed the basis of the marvel that followed.
The breakthrough features it first introduced were:
- Support for ADTs in a DB system
- Extensible access methods for new data types
- Multiprocessor support: XPRS
- Active databases, rules systems and stored procedures
- Log-centric storage and recovery
Then there’s the fact that it consistently takes top positions in various benchmarks and surveys as I describe in PostgreSQL is the DB-Engines DBMS of the Year for 2020
In further confirmation of its popularity and value, PostgreSQL has won the annual accolade given by DB-Engines. In a short statement, DB-Engines states:
PostgreSQL is the database management system that has gained more popularity in our DB-Engines rankings over the past year than any of the other 360 systems monitored. We are therefore declaring PostgreSQL as the DBMS of the year 2020.
Add extensibility. In Switching to PostgreSQL with Babelfish and MangoDB, I looked at two solutions that make Postgres behave like SQL Server and MohngoDB (no that’s not a typo) through the sophisticated layers of Babelfish and MohngoDB (now FerretDB). This approach makes it easy to port your application originally designed for SQL Server and MangoDB to work with a Postgres backend engine.
Babelfish is a set of extensions that provide both T-SQL functionality and a Tabular Data Stream (TDS) listener as enhancements to PostgreSQL. It supports SQL Server dialect, T-SQL and notable features including savepoints, stored procedures, nested transactions, etc.
is a stateless proxy, which converts MongoDB protocol queries to SQL, using PostgreSQL as the database engine. It is compatible with MongoDB drivers and should work as a direct MongoDB replacement in many cases.
The benefits are getting rid of license fees, since you are moving from proprietary SQL Server to open source PostgreSQL. But what about MongoDB, isn’t it already open source? Yes, but it recently changed its license to SSPL (check SSPL is not an Open Source License), which makes it unusable for many Open Source and commercial projects.
If these reasons didn’t make you consider the move, let’s go back to the Transition Guide and look at a few more, starting with the use cases:
Access to data
PostgreSQL conforms to the SQL:2016 standard, the common query language for many RDBMSs.
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 works on the cloud. All major cloud providers have one (or sometimes several) managed PostgreSQL offerings.
It is compatible with many storage and backup technologies:
- cold backup
- hot backup
- continuous backup
- SQL dump
Administration via monitoring tools and log analysis; high availability, scalability, and clustering complete the state of operations. But there is 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 “ADT support in a database”. Table partitioning is implemented as standard while PostgreSQL conforms to the modern SQL 2016 standard.
Of course, there are many APIs to access it from clients written in Java, . NET, Python, Perl. . . same from Tcl to pgtclng. It offers stored procedures, functions, triggers, and foreign data wrappers. Wrappers are extensions that allow PostgreSQL to communicate with other data sources. 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) which conforms to the Open Geospatial Consortium (OGC) standards.
PostgreSQL can also be used in the field of Business Intelligence as a data warehouse, in connection with reporting tools (BusinessObjects, Pentaho, etc.). It comes with an integrated and feature-rich full-text search engine.
Many free software are natively based on PostgreSQL (document management systems (DMS), rule engines, collaboration software, supervision software, etc.).
Having convinced you of its value, the guide now focuses on making migration recommendations by providing a very useful decision-making sub-guide that attempts to describe the decision-making process used in several companies.
It also hosts a comparison of features that exist in a dbms with their counterparts in PostgreSQL. So, to take Oracle as an example:
As such, we find Oracle:
- Oracle4 Materialized Views
As of 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 with VARCHAR or TEXT.
- CLOB strings are replaced by TEXT.
- There is no Oracle RAC equivalent in PostgreSQL; however, the DATAGUARD feature is provided in PostgreSQL by replication.
- On Oracle, RMAN handles backups and restores; there is no database equivalent under PostgreSQL. Several third-party tools exist, however, including pgBackRest and Barman. Archive journaling is well implemented, almost identically, in both DBMSs.
These examples also include DB2 and Informix, but I would have thought it should have included other popular databases such as SQL Server or MySQL as well. SQL Server instructions are contained in a single paragraph containing open source tools that can facilitate data migration, as well as Babelfish referencing.
Other than that, moving to another DBMS is a project in its own right and it may involve training staff, hiring support, upgrading operating procedures and adjusting the application, including including test costs (technical, functional and non-regression test). These must also be taken into consideration.
Finally, the guide concludes it with references to other documentation.
To sum up, the PostgreSQL transition guide can be very useful when you are thinking of switching or looking to start from scratch and determine if the Postgres product will be suitable.
It begins by listing the features and benefits of Postgres, encompassing the full spectrum of operation, administration, development, tools, licensing, and support. It then compares the features found in another dbms to those included in Postgres. In conclusion, it details the tools and procedures as well as the obvious and hidden costs when considering the move. With this, it manages to address the concerns of management and developers. Make sure you have him by your side.
The guide on Github
The Guide in PDF format
Switch to PostgreSQL with Babelfish and MangoDB
PostgreSQL is the DB-Engines DBMS of the year 2020
PostgreSQL 14 is here – A glimpse of its past and future
A deep dive into PostgreSQL indexes
To be notified of new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.
or send your comment to: [email protected]