Easing the pain of migration from SQL Server to PostgreSQL
Sameer Kumar I Senior Solution Architect, Ashnik
Migrating a software (application or database or any layer) that is running on one vendor’s platform to another vendor’s platform is never easy or risk-free. Same applies to relational databases as well. Though fundamentally all the relational database are supposed to adhere to same relational theory, but commercial products have some vendor specific implementations of commands and functions. For example relational databases generally adhere to ANSI-SQL syntax, but there are exceptions while they try to comply with ANSI standards.
In my day job as DB Solution Architect, I come across one or two new migration cases almost every month. And at times these are migration from SQL Server to PostgreSQL. In my experience and opinion SQL Server is one implementation of Relational Theory which has most deviations from ANSI syntax. For example- all RDBMS use double pipe [ || ] as concatenation operator whereas SQL Server (for some good or bad reason) chose it to be +.
Another feature which I have seen being a pain while migrating is ‘case-insensitive searches’. All databases support that feature if you would use UPPER( ) function on string before comparing them. But that is the default and only behavior in SQL Server database, if you have created with a collation ending with CI_AS or CI_AI (meaning case insensitive and ascent sensitive/insensitive). I have faced that challenge of migrating these customers.
When you look at the database while assessing it for migration it all looks fine. The SQL Server database has only tables (no programmable objects e.g. procedures or functions) with no ‘funny’ data-types [again the whole world calls timestamp as data-time and SQL Server uses it as a binary datatype for Optimistic Locking ]. You migrate it over (well quite easily), and finally when you connect the application you start getting error left-right-and-center!
Well PostgreSQL is not just a stable alternative for proprietary databases but is also offers great flexibility to make itself a popular alternative. Let me share two examples where-in PostgreSQL can really make it easy for you to migrate over.
In PostgreSQL you can create user defined casts and user defined operators. So to over come the issues I have mentioned above, let me create user defined operators and test-
+ as Concatenation operator-Let’s first create a function which can concatenate two strings
AS $$ SELECT $1 || $2 $$
Now let’s create a custom operator which has two arguments ( left=string and right=string )
CREATE OPERATOR + ( LEFTARG = varchar,
RIGHTARG = varchar ,
PROCEDURE = concat_with_plus
If you notice the operator uses the function that we have defined above.
Here is how it would work-
postgres=# select ‘sameer’ + ‘kumar’;
Enable Case-insensitive search for all the tables and columns
All we need to do is create an operator ‘=’ overriding the default behavior.Before that let’s first define a function which matches two string irrespective of their case.
$$ LANGUAGE SQL;
Let’s now create an operator ‘=’ using this functions
CREATE OPERATOR =
( LEFTARG = varchar,
RIGHTARG = varchar,
PROCEDURE = varchar_equal_caseinsenstive,
COMMUTATOR = =, NEGATOR = !=,
Here is how it would work-
postgres=# SELECT * FROM test_varchar postgres-# WHERE col1=’SAMEER’; col1
<<more rows in result>>
And if you define an index over UPPER(COL1),
CREATE INDEX upper_col_test_varchar ON
it would be used for index scans/bitmap scans-
postgres=# EXPLAIN SELECT * FROM test_varchar WHERE col1=’SAMEER’;
Bitmap Heap Scan on test_varchar (cost=11.77..394.81 rows=432 width=10)
Recheck Cond: (upper((col1)::text) = ‘SAMEER’::text)
-> Bitmap Index Scan on upper_col_test_varchar (cost=0.00..11.66 rows=432 width=0)
Index Cond: (upper((col1)::text) = ‘SAMEER’::text)
Planning time: 2.685 ms
These are one of the most frequently deployed tricks when I migrate my customer from SQL Server to PostgreSQL. These wrappers are written inside your database. You can create them on template0 database and hence all the databases created thereafter will have these casts. This is not only easy but also does away with the requirement of changing application code.
Stay tuned and we will be sharing more such tips and tricks which helps you break away from the lock-in of proprietary database vendors and move to a much more cost effective solution.
In case you are looking for professional services for migration you may contact my employers Ashnik (www.ashnik.com | firstname.lastname@example.org). We offer wide range of PostgreSQL services – Migration, Heterogeneous Replication, Hardening, Tuning, High Availability, DR Setup and anything that has to do with PostgreSQL.
- Sameer Kumar is the Database Solution Architect working with Ashnik. Sameer and his team works towards providing enterprise level solutions based on open source and cloud technologies. He has an experience in working on various roles ranging from development to performance tuning and from installation and migration to support and services roles.
- Would PostgreSQL leak your data? No! But YOU might!
- Integrating Docker Trusted Registry with Google Chat
- Chaos Engineering with Docker EE