Picgrum
Archive

April 25, 2023

Browsing
In Tech

Methods of Database Migration from MS SQL to PostgreSQL 

No Comments

Nowadays many technical specialists (developers, project managers, database administrators) try to find open-source solutions for every component of corporate infrastructure. Following this trend, PostgreSQL can be a good option to migrate database from such proprietary DBMS as Microsoft SQL Server or its cloud variation Azure SQL. Open-source databases can be beneficial for developers and enterprises in terms of reducing total cost of ownership and risks of the single vendor lock.

Migration Methods

You have options to migrate your MS SQL database to PostgreSQL, either manually or using automated tools.

For manual migration, you can recreate the same database schema in PostgreSQL as you have in MS SQL. This task can be done by generating scripts from MS SQL Server Management Studio and then executing those scripts in PostgreSQL database via SQL Shell or PgAdmin. However, there may be compatibility issues with data\ types between MS SQL and PostgreSQL, resulting in errors during data export.

Alternatively, you can use migration tools to automate the process and save time. Some available migration tools are EnterpriseDB Migration Toolkit and Sqlserver2pgsql. These tools generate the necessary scripts and create the schema in Postgres, simplifying the migration process.

Finally, it is possible to use special commercial software for MS SQL to PostgreSQL database migration that can fully automate procedure within just a few button clicks. 

Manual Database Migration

To migrate the database manually, it is required to generate scripts for either a table or a complete database, transform it according to the target dialect of SQL and finally run those scripts in PostgreSQL. Those scripts for SQL Server database can be generated following the steps below:

  • Connect to the source server from SQL Server Management Studio. Navigate to the database that you want to migrate. Right-click on the database name to open pop-up menu and them select “Tasks” > “Generate Scripts” item.
  • Choose the objects for which you want to generate scripts – either entire database and all its objects or specific database objects.
  • Set the Scripting Option – choose either to open the scripts in a new query window or generate them in a single file or one object script in one file.
  • Review your objects and settings, and then finish. The scripts will be generated.

Once the scripts are generated, you need to … Finally, you can execute them in either PostgreSQL’s SQL Shell or PgAdmin. The PostgreSQL database schema will be created based on the executed scripts.

Database Migration using EnterpriseDB Toolkit

The description of the method above may prove that database migration via generating scripts is a time consuming procedure. There are some intermediary tools available which can partially automate the database migration saving a lot of time and efforts. One of these tools is EnterpriseDB Migration Toolkit.

EnterpriseDB Migration Toolkit can be either downloaded from the official website or installed via Application Stack Builder on the local system.

Steps to install EnterpriseDB Migration Toolkit:

  • Open the Application Stack Builder that is a part of PostgreSQL installation.
  • Verify your Internet connection and specify the PostgreSQL host where to install the Migration Toolkit. 
  • Then the installation wizard will list available applications. Navigate through menu items to Categories > Registration-required and trial products > EnterpriseDB Tools. Select the Migration Toolkit and click ‘Next’.
  • You’ll be asked to select your download directory. Select the desired download folder and click ‘Next’. After the Migration Toolkit is downloaded, confirm installation and then the product is ready to use.

Next thing that is required to use the software is toolkit.properties file built properly. This file contains the configuration and connection information for MS SQL and PostgreSQL databases. Follow these steps to customize the toolkit.properties file for your database migration:

  • Locate the toolkit.properties file, which can be found in C:\Program Files\edb\mtk\etc for Windows users.
  • Use any text editor of your choice, such as VS Code, to modify the file’s contents. The file should contain the following mandatory parameters 
    • SRC_DB_URL: Specifies the connection information of the source database.
    • SRC_DB_USER: Specifies the username for the source database.
    • SRC_DB_PASSWORD: Specifies the password for the source database.
    • TARGET_DB_URL: Specifies the connection information of the target database.
    • TARGET_DB_USER: Specifies the username for the target database.
    • TARGET_DB_PASSWORD: Specifies the password for the target database.

The database URL must comply with the following format:

{TARGET_DB_URL|SRC_DB_URL}=jdbc:edb://host:port/database_id

Once the toolkit.properties file is properly configured, we can proceed with the database migration using the Migration Toolkit. To do this, we will call the runMTK.bat batch file as following:

.\runMTK.bat -sourcedbtype sqlserver -targetdbtype postgres [options, …] <schema_name>

If you need to migrate multiple schemas, you can mention the schema names as a comma-delimited list. If you don’t specify the -sourcedbtype and -targetdbtype parameters in the command, the Migration Toolkit will assume that the source database is Oracle and the target database is EDB PostgreSQL Advanced Server by default.

After completing the steps above, schema and data of the source database will be migrated from SQL Server to PostgreSQL. To ensure that the tables and data have been successfully migrated, you can verify them in Postgres.

Database Migration using Dedicated Commercial Tools

As you can see from the guide above, migrating SQL Server database to PostgreSQL using EnterpriseDB Migration Toolkit still requires a lot of administration work (setup and configuring the application). For those who need easy and smooth database migration, we can suggest to use commercial tools that were specially designed for this purpose. 

One of such tools is MS SQL to PostgreSQL converter developed by famous vendor of database migration software – Intelligent Converters. This tool provides all the features required for efficient and flexible migration: 

  • Support all modern version of on-premises MS SQL and PostgreSQL
  • Support all popular cloud variations of both DBMS (Azure SQL, Heroku, Amazon RDS, etc)
  • Migrate schemas, data, indexes, constraints and views
  • Customize the resulting table structures
  • Pre-process data for migration through SELECT-queries
  • Scheduling and scripting capabilities via command line support

Visit the official vendor site to learn more about MS SQL to PostgreSQL converter. 

by
Read More
In Finance

How-To: Dominate Swing Trading with the Best Stock Trading App

No Comments

Swing trading is a popular stock market strategy that involves buying and holding stocks for short periods, typically a few days to a few weeks. This is to take advantage of price fluctuations. Unlike day trading, swing trading requires you to hold your positions overnight. This makes it a better fit for part-time traders who can’t monitor the market every minute of the day.

Swing trading can be a lucrative strategy, but it’s not easy to master. You need a sound understanding of technical analysis, market trends, and risk management to make smart trades. The good news is that there are several stock trading apps that can help you make smarter trading decisions and increase your chances of success.

Understanding swing trading.

To succeed in swing trading, you need to understand swing trading concepts. Swing traders aim to capitalize on short-term price movements in the market.

Swing traders typically use technical analysis to identify trends and anticipate price movements. They will look for resistance and support levels, moving averages and trend lines, and other indicators that will help them spot potential entry and exit points.

Swing traders also use fundamental analysis to identify stocks with high value and growth potential. They may look at a company’s financial statements, earnings releases, and industry news to get a sense of how the company is performing.

The Importance of Stock Trading Apps

Swing trading requires you to be nimble and able to act on market developments quickly. You may find yourself in a situation where you need to make a quick trading decision, but you’re not in front of your computer. That’s where a stock trading app can be beneficial.

The benefits of using a stock trading app for swing trading.

The best stock trading app offers several benefits for swing traders, including:

  1. Real-time alerts: A stock trading app can send you notifications when a stock hits a certain price, enabling you to buy or sell it quickly.
  2. Accessibility: With a stock trading app, you can trade from anywhere, provided you have an internet connection. You don’t need to be tied to your desk to trade.
  3. Data availability: A stock trading app provides real-time data and charts to make informed trading decisions.
  4. Ease of use: Stock trading apps are intuitive and easy to use. You don’t need to be a technical expert to use them.

 

by
Read More

Омг Omgomg Shop Site