EN | DE | FR | RU
PostgreSQL migration

PostgreSQL Migration

PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. Therefore, the transition to this database system for many organizations is quite appropriate and justified. The causes of migration may be quite different. Either the current database system has some functional flaws or its cost/benefit ratio does not appeal anymore to its owner, or migration may be required when transferring from one version of PostgreSQL server to another. Leaving aside the possible reasons of migration to PostgreSQL, let's dwell more on database tools needed for its implementation.

Mostly the migration process can be more difficult than originally expected by database administrators wishing to make the switch to PostgreSQL. There is a need to undertake a careful evaluation of PostgreSQL features, determining those that are fully or most fully coincide with the features of the current database system. It also requires a detailed analysis of the current system to emphasize which features are actually used at the moment. The 4 key steps in the migration process are:

  1. Impact investigation and migration planning
  2. Migration of the source database model/schema to PostgreSQL schema
  3. Migration of data to PostgreSQL tables
  4. Verification

Tools for Migrating to PostgreSQL Database

Generally, the tasks connected with a migration workflow are diverse and complicated so the special migration toolkits could prove very useful in supporting this process. EMS Data Pump for PostgreSQL is a handy tool for converting databases and importing table data from an ADO-compatible source (e.g. MySQL, MS Access, MS SQL database or any other database with corresponding OLE DB provider) to PostgreSQL databases. Since ADO-oriented applications can access data from any OLE DB source, EMS Data Pump for PostgreSQL is able to import information from such simple data stores as a text file, ODBC sources and spreadsheet to such complex databases as Oracle, SQL Server, IBM DB2 and others.

EMS Data Pump for PostgreSQL has an easy-to-use wizard that will guide you through the entire migration/import process which is divided into separate simple steps. The wizard allows you to build the ADO connection string, select the source tables, fields, indices and constraints for converting, view and edit the SQL script of generating the target PostgreSQL database, select tables to be imported and customize a number of pump options. This customization includes the ability to set the correspondence between the given DB Provider and PostgreSQL data types, specify the schema of the PostgreSQL database where the source tables are to be pumped into, define an SQL criterion for importing data and more.

Due to the possibility to save the current settings in a configuration file and the availability of a special command-line utility, EMS Data Pump for PostgreSQL allows automate the migration/import process. The command-line utility can be placed into the task scheduler launch list and use the configuration file as a parameter.

EMS Data Pump supports the latest PostgreSQL versions, can connect to a remote PostgreSQL server using SSH or HTTP tunneling, has the multilingual capability and allows you to customize the GUI application interface style according to your preferences.

Program Tools to Backup PostgreSQL Data

In their everyday work administrators are often faced with the necessity of making a database snapshot. This snapshot can represent a whole database backup (data and structure) or definition of its scheme only. EMS DB Extract for PostgreSQL is a useful and easy-to-use tool for creating PostgreSQL database backups. It allows you to store metadata of all database objects as well as table data in a SQL script file. The program enables you to select PostgreSQL database objects and data tables for dump and tune many other extract options, such as ability to define constraints for extracted data tables, ability to insert statements for emptying tables before inserting the extracted data and more. DB Extract for PostgreSQL includes a graphical wizard that will guide you through the database extract process step by step, and a command-line service for creating PostgreSQL backups in one-touch on the schedule basis. SSH and HTTP tunneling connections to database server are supported.

Certain administrative tasks, like backup and recovery, performance tuning and security enforcement, surely have to be taken into account when facing a migration. EMS SQL Management Studio for PostgreSQL is a complete solution for PostgreSQL database administration and development.

PostgreSQL export PostgreSQL import PostgreSQL database change management PostgreSQL Migration