Migrating Database to PostgreSQL from SQL Server
PostgreSQL and Microsoft SQL Server have a lot in common and can be reckoned as popular Database Management Systems. They have a wide range of administrative tools and programming APIs. However, SQL is extremely restrictive in terms of licensing policies and has a large sunk and running cost. PostgreSQL on the other hand is open source with less licensing problems. This has given many users a reason to switch from SQL Server to PostgreSQL.
To migrate a database manually from SQL Server to PostgreSQL there are certain steps. First of all, along with their indexes and constraints, table definitions are extracted from the initial database in the form SQL CREATE-statements. Following this, these statements are converted into a destination format before being loaded into the target database. Then, the data is exported from SQL Server stored as a CSV file. Data is then converted into a destination format before being loaded onto the target database. In the form of SQL statements and source code stored procedures, views and triggers are extracted from SQL database. These files and code are then converted into destination format before being loaded onto the target database.
As you can see, this method is very confusing and takes a lot of effort and time. In handling it manually, there is also a risk of data loss due to human error. Hence, it would be advised to use software developed to automate the process of conversion.
For this process, a software company (Intelligent Converters) operating in this sector of database conversion, migration and synchronization since 2001 has developed the SQL Server to PostgreSQL data migration tool. The converter has a large number of benefits as it is able to provide a high performance due to direct reading and writing of data. This is done by avoiding any middle source for conversion. It also is compatible with any version of PostgreSQL and SQL Server. It can also work with cloud solutions. A command line version is available for automation and scheduling of database conversion. To add, it allows for merging of new databases with the previous PostgreSQL database.
If you are wondering how you can only migrate certain records, luckily that feature is also available with this converter. This tool can be used to filter data via the SELECT queries function. Select particular columns and records or otherwise be able to change the data in advance, before converting your data to a PostgreSQL format. This feature can be made use of in the following ways:
- For selecting and renaming individual columns
- To convert multiple tables as a single entity
- To filter records
To edit the column type in the resulting database, the MS SQL to PostgreSQL converter has a specific feature. It is termed as “custom column mapping”. Using this, a dialog window pops up where the NULL-attribute, default value, name and type of any column can be altered. You can select which columns to exclude from the conversion.
In a scenario where remote connection is not possible, the program has an option to export data in the form of a SQL script. For this method, the source database will be exported into a local file with SQL-statements for creating tables (with all indexes and constraints) and to fill them with this data. Following this, the database administrator can load the script file into the PostgreSQL server using any standard client tools.