Shulou(Shulou.com)06/01 Report--

Most people do not understand the knowledge points of this article "how to migrate data from SQL Server to PostgreSQL", so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how to migrate data from SQL Server to PostgreSQL" article.

It is not easy to migrate data between different types of databases. In this article, we will compare several ways to convert from SQL Server to PostgreSQL. Microsoft SQL Server is a great database engine, but in some cases it has drawbacks. Free Microsoft SQL Server Express can be successfully used in small databases.

Limitations of SQL Server Express:

The maximum memory used by the database engine is 1GB.

10GB maximum database size.

1MB maximum buffer cache.

CPU uses one (1) slot or the smaller of four (4) cores (there is no limit on the number of SQL user connections).

For large databases, you may need to obtain a paid version of MSSQL Server starting with the standard license. The cost of SQL Server depends on the number of users and the size of the database.

If you can't even afford a standard SQL Server license, PostgreSQL is an alternative.

PostgreSQL is completely free, so license cost is the main advantage of PostgreSQL over Microsoft SQL Server.

Open source tools for database migration between SQL Server and PostgreSQL

Manual data migration is troublesome. A good database migration tool should prompt the user to select objects to migrate, such as tables, indexes, primary keys, and foreign key constraints.

Fortunately, there are some tools that can automate boring manual work.

1. Pgloader is a well-known open source tool that uses COPY commands to import data from SQL Server into PostgreSQL, load data, indexes, and foreign keys, and convert the data into PostgreSQL as expected.

Pgloader loads data from various sources such as MS SQL, SQLite, MySQL, CSV, and so on, into PostgreSQL.

It is licensed under the PostgreSQL license and can be used for free.

Pgloader is a cross-platform software.

Docker images are available.

2. Sqlserver2pgsql is written in Perl. This is another open source migration tool for automatically converting Microsoft SQL Server databases to PostgreSQL databases.

It converts SQL Server mode to PostgreSQL mode

If desired, it can create a Pentaho Data Integrator (Kettle) console to migrate all data from SQL Server to PostgreSQL.

Shortcomings of some open source tools

The tools mentioned above are command-line utilities, so they have no graphical interface. For those who are intimidated by terminals, they are not easy to use.

These tools are limited to one-way migrations that can configure PostgreSQL as a destination

DBConvert database migration software

DBConvert and DBSync for SQL Server and PostgreSQL are popular software tools that minimize database conversion and synchronization challenges between SQL Server and PostgreSQL databases.

DBConvert/DBSync benefits for SQL Server and PostgreSQL:

The DBConvert tool is highly customizable, allowing you to quickly and easily export data from Microsoft SQL Server to Postgres using a comprehensive graphical interface. DBConvert Applications guides you through several steps, from connecting to the source and target databases, configuring migration parameters, and scheduling subsequent runs.

In DBConvert products, the target database is rendered in the form of a tree, which greatly simplifies the configuration of settings. The proven advantage of DBConvert software is that it can also be used effectively by non-professional users.

The automatic verification system carefully examines the structure and relationships of the source database before migration to ensure that any of your data is not lost or damaged, and that there is no risk and error-free data transfer.

After you copy the source database to the target database, keep the database synchronized with the update, insert, and delete synchronization features.

The DBSync application performs two-way replication between SQL Server and PostgreSQL (where two different databases replicate changes to each other at the same time).

In any combination, data migration between the following databases is possible using the SQL Server to PostgreSQL converter / synchronization tool:

Microsoft SQL server

Windows Azure SQL database / Azure SQL data warehouse

Google Cloud SQL for SQL Server

Google Cloud SQL for PostgreSQL

AWS RDS/ aurora

Heroku Postgres .

The above is about the content of this article on "how to migrate data from SQL Server to PostgreSQL". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.